Secure data in a single Multi-Tenant Database in 3 simple steps
Tags: dotnet, csharp, productivity
Published: 2021-04-20 18:18:13

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](https://docs.microsoft.com/en-us/azure/azure-sql/database/saas-tenancy-app-design-patterns). If you’d like to read more about Row-Level Security, Microsoft has quite a good doc on that [here](https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver15#Description).