Secure data in a single Multi-Tenant Database in 3 simple steps

Often our applications have to support multiple customers, which are often called tenants. One of the popular options is a single multi-tenant database. This model requires each database table to contain an additional column, which is responsible for distinguishing to which tenant a data row belongs. In this post, I’ll show you how you can ensure that each tenant’s data is not available for other tenants.

Row Level Security

The solution that I often use is the MSSQL database’s mechanism called Row-Level security. The idea is quite simple. The database will only return results that fulfill the predicate. In my case, the predicate is simply the TenantId set for the current context. So the result contains only rows that have TenantId equal to the one set in session context. The session context can be set when you use both Entity Framework or Dapper. Let’s take a look at the code!

Security schema

First, let’s create a separate security schema. It’s recommended by Microsoft to keep security predicates and related functions in a separate schema.

CREATE SCHEMA [Security]

Security predicate

The next step is to create Security Predicate Function

CREATE FUNCTION [Security].[SecurityPredicate](@TenantId UNIQUEIDENTIFIER)
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    RETURN
        SELECT 1 AS Result
    WHERE CAST(SESSION_CONTEXT(N'TenantId') AS UNIQUEIDENTIFIER) = @TenantId

As you can see, this function takes tenantId, which is always a GUID in my case, and checks, whether it’s equal to TenantId for the current session.

Add a policy

The last step for setting up the database is to add policy for specific table.

CREATE SECURITY POLICY [Security].[BlogPostsPolicy]
    ADD FILTER PREDICATE [Security].[SecurityPredicate]([TenantId]) 
        ON [dbo].[BlogPosts],
    ADD BLOCK PREDICATE [Security].[SecurityPredicate]([TenantId]) 
        ON [dbo].[BlogPosts] AFTER INSERT,
    ADD BLOCK PREDICATE [Security].[SecurityPredicate]([TenantId]) 
        ON [dbo].[BlogPosts] AFTER UPDATE,
    ADD BLOCK PREDICATE [Security].[SecurityPredicate]([TenantId]) 
        ON [dbo].[BlogPosts] BEFORE DELETE,
    ADD BLOCK PREDICATE [Security].[SecurityPredicate]([TenantId]) 
        ON [dbo].[BlogPosts] BEFORE UPDATE
WITH (STATE = ON, SCHEMABINDING = ON)

As you can see, I am using different predicates here:

• Filter predicates filters out rows that do not belong to tenant specified in session context for read operations
• Block predicates explicitly block write operations that violate the predicate

Working with the data

In order to work with data you have to execute following query, which will set session context of the tenant that you want to work with:

SP_SET_SESSION_CONTEXT N'TenantId', '{YOUR-TENANT-ID}'

Note, that if you do this in SQL Server Management Studio, it only works for the current tab. To use it with Entity Framework or Dapper, you just have to execute the same query (with session context) before executing the right query.

For Entity Framework, you can use following code to set it up

var connection = Database.GetDbConnection();

connection.StateChange += (_, @event) =>
{
    if (@event.CurrentState == ConnectionState.Open)
        connection.Execute("EXEC SP_SET_SESSION_CONTEXT @key=N'TenantId',        
            @value=@tenantId", new
            {
                tenantId = _tenantId
            });
};

If you work with Dapper, you can execute this code before each query

_connection.Execute("EXEC SP_SET_SESSION_CONTEXT @key=N'TenantId',     
    @value=@tenantId", new {tenantId});

And it’ll do the job 🙂

Summary

That’s it. Quite a simple approach for a single multi-tenant database that protects you from displaying data to the invalid tenant using a database mechanism. I really like this approach, because it protects you at the lowest possible level. Unless you do a mistake and for some reason set the tenant context to another tenant context, you should be quite safe and decrease the likelihood of a mistake.

Of course, there are few other options available when it comes to the multi-tenancy model. You can check them out here. If you’d like to read more about Row-Level Security, Microsoft has quite a good doc on that here.