Sandboxes

SQL Sandboxes

It would be neat if we could define "sandboxes", which would be subsets of the SQL schema that some queries should be limited to.

This would be a bit like row level security (as implemented by Postgres and SQL Server), but enforced at the application level.

Sandboxes would be parameterized, so you could define one to represent the view of the database that a user has, parameterized by the user's ID.

It would look something like this:

create sandbox ForUser(@userId) {
    read, write Preferences where UserId = @userId;
    read Documents d
         join Organizations o on o.Id = d.OrganizationId
         join Users u on u.OrganizationId = o.Id and u.Id = @userId;
}

This would give the query read and write access to the preferences table, but only for their own preferences, and read-only access to the documents associated with their organization.

Ideally the type provider would let you use this sandbox both statically:

type SandboxedQuery = SQL<"select * from Documents where id = @id", sandbox = "ForUser">

let command = SandboxedQuery.Sandbox(userId = 1).Command(id = 2)

And dynamically:

type DynamicSandbox = Sandbox<"ForUser">
// parses query and adds sandbox filtering at runtime
let command = DynamicSandbox.ToCommand("select * from Documents")

This would be great for implementing dynamic report builder functionality on multi-tenant databases.