Multi-tenant database in SQL Server

Before talking about a multi-tenant database, let’s explain in a nutshell what a multi-tenant system is. Multitenancy is a software architecture that allows a single instance of software to be used by multiple organizations or groups of users.

Database side, one possible implementation is that you use a single database instance which will contain data of all the organizations your system serves.

To achieve this result, you could add to all databases’s tables a field containing the id of the tenant to which the row belongs. Then, in every single query you make against the database you have to consider this field to modify or extract only the data of the tenant of the user.

Particular attention must be paid to the last step: if you forget to include the tenant id in the WHERE condition of a query (for example and UPDATE) you could do serious damages, like mix data of different tenants with each other.

So it would be really cool to have a way to avoid having to apply that filter to all queries… This is where SQL Server row level security comes into play.

Row level security is a SQL Server feature introduced with the release of SQL Server 2016 and it can help us solve the problem described above. Let’s see an example…

Let’s take the table “Authors” of the database “Pubs” (search on Google or check this post to see how you can obtain a copy of this database). Here you can see the content of the “Authors” table:

Add a “tenant_id” column of type INT, and assign to each author a tenant id. Now if you run the query again, you will see something similar:

In order to use the row level security feature of SQL Server, we need to create an inline table valued function that will be used to evaluate if a row should be visible or not to the user. For our purpose we are going to create the following function:

CREATE FUNCTION [dbo].[TenantFilter] 
(
	@TenantID	INT
)
RETURNS TABLE 
WITH SCHEMABINDING
AS
RETURN 
(
	SELECT 1 VAL WHERE @TenantID = CONVERT(INT, SESSION_CONTEXT(N'tenant_id'))
)

About the function above, please note the following:

the function must be created with the SCHEMABINDING option
the function must return a row if the visibility condition is true, no row otherwise; moreover it doesn’t matter the column number and the values of the columns you return
we are using the SESSION_CONTEXT function, in a moment we will see why

Now, to apply the function to the table “Authors” you have to use the following SQL statement:

CREATE SECURITY POLICY PolicyTenantFilter ADD FILTER PREDICATE dbo.TenantFilter(tenant_id) ON dbo.Authors WITH (STATE = ON)

Then execute the following command:

EXEC sp_set_session_context 'tenant_id', 1;

And finally run the query:

SELECT * FROM Authors

As you can see in the image above, now you are able to see only records with tenant_id = 1, even if we have no filter condition in the SELECT statement!

Why the use of the function SESSION_CONTEXT? Because it’s an easy way to use some sort of “session variable” in the scope of a SQL Server connection. This implies, as a consequence, that you have to call the procedure “sp_set_session_context” every time you open a new connection from your client application, setting the correct tenant_id value.

A last thing to remember: the security policy created above, protects you from reading, updating and deleting records owned by a different tenant, but doesn’t block you to insert records with a different tenant id than yours.

To solve this problem you have to alter the security predicate adding a block predicate as follow:

ALTER SECURITY POLICY PolicyTenantFilter ADD BLOCK PREDICATE dbo.TenantFilter(tenant_id) ON dbo.Authors

Now if you try to insert a row with tenant_id = 4, you will get an error:

INSERT INTO Authors(au_id, au_lname, au_fname, phone, address, city, state, zip, contract, tenant_id)
 
VALUES('000-00-0001', 'Doug', 'Ross', '333-444-555', 'Oxford street 90', 'Oakland', 'CA', '95129', 1, 4)

Msg 33504, Level 16, State 1, Line 32
The attempted operation failed because the target object 'pubs.dbo.authors' has a block predicate that conflicts with this operation. If the operation is performed on a view, the block predicate might be enforced on the underlying table. Modify the operation to target only the rows that are allowed by the block predicate.
The statement has been terminated.

In the example above you manually set the field tenant_id in the insert statement. It would be nice if the tenant_id could be set automatically, given that you called “sp_set_session_context” before.

You can do this setting a default constraint on the field tenant_id, like this way:

ALTER TABLE dbo.authors ADD CONSTRAINT DF_authors_tenant_id DEFAULT CONVERT(INT, SESSION_CONTEXT(N'tenant_id')) FOR tenant_id

With a default constraint configured this way you can run this insert statement without including the field tenant_id:

INSERT INTO Authors(au_id, au_lname, au_fname, phone, address, city, state, zip, contract) 
VALUES('000-00-0001', 'Doug', 'Ross', '333-444-555', 'Oxford street 90', 'Oakland', 'CA', '95129', 1)

You can learn more about the use of security policies by consulting Microsoft’s docs online, but with this post you should have a basic understanding on how to work with them.

Related Posts