Row Level Security in SQL Server

27 September 2019 Admin DataBases, SQL Server, Patterns, Performance

Row-Level Security let us control access to data at row level for any SQL Server table based on users, roles, membership or working context. It tremendously simplifies and improve the options we have to secure, filter, implement restrictions or eventually deal with our data over certain scenarios. What is most relevant for me is that all the security policy can be set centrally at a database level preventing developers from taking care of adding custom where clauses to enforce security. It makes the management of data much more reliable and maintainable.

Let's see how to implement Row-Level Security for a typical multi-tenant scenario, namely, a database whose tables (or the most part of them) are divided into tenants or customers. All the users included in a given tenant will just have access to data for that tenant so that all the data must be properly managed and filtered by the working tenant (select, update, insert and delete actions). 

What is a multi-tenancy scenario?

Let's get straight to the point by putting the differences between a single-tenant and a multi-tenant architecture:

Single-tenant means that a single instance of a given application serve a single customer. With single-tenancy approach, each customer has typically its own separate database and software instance.

Multi-tenant means that a a single instance of a given application serves multiple tenants or customers. Each of them shares the software application and also a single multi-tenant database. Each tenant data is isolated and remains no accessible to other tenants. This is a crucial point to meet in this architectures yet offering good performance as the database size could be very large.

How to implement a multi-tenancy scenario at database level?

This multi-tenant scenario is perfect to apply the Row-level Security feature. These are some key points from my point of view:

  • The main design for relational tables should consider the inclusion of a TenantId field for each table whose data must belong to a given tenant / customer (as from here, I'll use only the term tenant). The concrete field name does not matter but we must store in that field the unique identifier for each tenant along with the rest of data columns for each row and table specific for that tenant.
  • Personally, I like that TenantId field to be the first one for creating primary keys in relational tables. It ensures clustered index will be used primarily when Row Level Security infrastructure filters data on tables under the corresponding security policy. I can say that it works very fine after making a lot of testing in applications I have been working with for years.

Having said that, these are the main steps I recommend to create our Row Level Security policy:

1) Create a specific schema to store related functions and objects we will use on wards:

CREATE SCHEMA Security;  
GO

2) Create a function within the previous schema to filter data on tables included in the security policy:

CREATE FUNCTION Security.SetSecurityTenantPredicate(@TenantId AS uniqueidentifier)  
             RETURNS TABLE  
       WITH SCHEMABINDING  
       AS  
             RETURN 
             SELECT 1 AS SetSecurityTenantPredicate_result   
             WHERE @TenantId = cast(isnull(SESSION_CONTEXT(N'TenantId'),'00000000-0000-0000-0000-000000000000') as uniqueidentifier) 
GO

This function will be applied for each row for each table included in the security policy. The @TenantId input param will be filled with the content of the TenantId field included in the current row being processed. Based on the returned value from this function (1 or 0), the processed row will be returned or not to the incoming query in case of select actions, updated or not for update actions, deleted or not for delete actions and inserted or not for insert actions. Besides with insert actions, a exception will be thrown  if the current tenant session does not match the provided value for the TenantId field. The error message will be something similar to "The attempted operation failed because the target object {TableName} has a block predicate that conflicts with this operation".

Notice I am getting a value from the session context with key TenantId (see SESSION_CONTEXT(N'TenantId')). So, values from TenantId field for each row for each table are compared to this session-based value in order to determine whether the row must be returned or processed as per concrete CRUD action. That session value must exist whenever a query is executed and match the TenantId value associated to the working tenant. This is crucial to understand and implement. Otherwise, our strategy will fail.

3) Create the security policy using the previous function as predicate:

CREATE SECURITY POLICY TenantFilter  
	ADD FILTER PREDICATE Security.SetSecurityTenantPredicate(TenantId) ON dbo.Table1,
	ADD FILTER PREDICATE Security.SetSecurityTenantPredicate(TenantId) ON dbo.Table2,
	ADD BLOCK PREDICATE Security.SetSecurityTenantPredicate(TenantId) ON dbo.Table1,
	ADD BLOCK PREDICATE Security.SetSecurityTenantPredicate(TenantId) ON dbo.Table2
GO

Filter keyword is used to filter select statements and block keyword for update, insert and delete actions.

As simple samples, Table1 and Table2 could have the following generic structure:

CREATE TABLE Table1  
(  
	TenantId uniqueidentifier,  
	Id int,  
	Field1 nvarchar(10),  
	Field2 nvarchar(10)
	PRIMARY KEY (TenantId, Id) /* Composite PK */
);

CREATE TABLE Table2  
(  
	TenantId uniqueidentifier,  
	Id int,  
	Field1 nvarchar(10),  
	Field2 nvarchar(10)
	PRIMARY KEY (TenantId, Id) /* Composite PK */
);

I prefer creating TenantId fields with uniqueidentifier type (16 bytes) but you can create them as you best think as long as you enforce uniqueness for them among tenants. For instance, you can create them as int (4 bytes) or bigint (8 bytes) to make smaller clustered indexes expecting performance to be better. You can try by yourself. However, I have not noticed any sensible improvement on performance when employing these types in my applications...

4) Analyze your application and identify where you must inject the TenantId value for setting the corresponding Session value.

In my case, working with Entity Framework, a good way to achieve this goal is by using Interceptors. We can incorporate the following code whenever a connection to the database is opened:

      /* Create command and set context */
      DbCommand cmd = connection.CreateCommand();
      cmd.CommandText = "EXEC sp_set_session_context @key = N'TenantId', @value = @TenantId;";
      DbParameter param = cmd.CreateParameter();
      param.ParameterName = "@TenantId";
      param.Value = tenantId; /* Extracted from Http Header */
      cmd.Parameters.Add(param);
      if (cmd.Connection.State == System.Data.ConnectionState.Open)
      {   
        /* Execute query if connection is open */
        cmd.ExecuteNonQuery();
      }

Note the use of the built-in SQL Server sp_set_session_context to set the SQL Server Session with key TenantId. In addition to this, tenantId local variable must be set previously to the execution of this code. This value must match the one coming from the user application. The way of providing it to your back-end is up to you, maybe you can send it in a custom object wrapped in a HTTP header as I am doing or picking any other type of strategy that suits better for you.

5) Update SQL tables to insert proper default values for TenantId field

When inserting data, we must provide the TenantId value. As we are storing that value in a session context variable, we can take advantage of it and enhance the tables as displayed below:

CREATE TABLE Table1  
(  
	TenantId uniqueidentifier,  
	Id int,  
	Field1 nvarchar(10),  
	Field2 nvarchar(10)
	PRIMARY KEY (TenantId, Id) /* Composite PK */
);
ALTER TABLE [dbo].[Table1] ADD  DEFAULT ([dbo].[GetTenantId]()) FOR [TenantId]
GO
CREATE TABLE Table2  
(  
	TenantId uniqueidentifier,  
	Id int,  
	Field1 nvarchar(10),  
	Field2 nvarchar(10)
	PRIMARY KEY (TenantId, Id) /* Composite PK */
); 
ALTER TABLE [dbo].[Table2] ADD  DEFAULT ([dbo].[GetTenantId]()) FOR [TenantId]
GO

As you can see above, default values for TenantId fields are provided by a new GetTenantId() function. Below is the code for this SQL custom function:

CREATE FUNCTION [dbo].[GetTenantId]()
RETURNS uniqueidentifier
AS 
BEGIN
   DECLARE @tenantid uniqueidentifier
   select @tenantid = cast(SESSION_CONTEXT(N'TenantId') as uniqueidentifier)
   RETURN @tenantid
END

Notice as TenantId value is returned as from SESSION_CONTEXT(N'TenantId') session variable.

Code Sample

Here is a complete demo sample with inline explanations for you to try in SQL Server Management Studio:

CREATE SCHEMA Security;  
GO

CREATE FUNCTION Security.SetSecurityTenantPredicate(@TenantId AS uniqueidentifier)  
             RETURNS TABLE  
       WITH SCHEMABINDING  
       AS  
             RETURN 
             SELECT 1 AS SetSecurityTenantPredicate_result   
             WHERE @TenantId = cast(isnull(SESSION_CONTEXT(N'TenantId'),'00000000-0000-0000-0000-000000000000') as uniqueidentifier) 
GO

SET NOCOUNT ON
BEGIN TRAN

/* Create table */
CREATE TABLE dbo.Table1  
(  
	TenantId UNIQUEIDENTIFIER,  Id INT,  Field1 NVARCHAR(10),  Field2 NVARCHAR(10)
	PRIMARY KEY (TenantId, Id) /* Composite PK */
);
GO

/* Create Security Policy */
CREATE SECURITY POLICY TenantFilter  
	ADD FILTER PREDICATE Security.SetSecurityTenantPredicate(TenantId) ON dbo.Table1,
	ADD BLOCK PREDICATE Security.SetSecurityTenantPredicate(TenantId) ON dbo.Table1
GO

/* Set the Working Tenant with a fixed value for demo purposes */
DECLARE @TenantId1 UNIQUEIDENTIFIER
SET @TenantId1 = 'A5B2F100-F40B-48AE-A65B-6FC2465D1D51'

/* Set the Working Session TenantId */
/* Without this action, an exception will be thrown: 
   The attempted operation failed because the target object 'dbo.Table1' has a block predicate that conflicts with this operation */
EXEC sp_set_Session_context 'TenantId', @TenantId1

/* Insert demo values */
INSERT INTO Table1(TenantId, ID, Field1, Field2)
VALUES (@TenantId1,1,'AAA','BBB')
INSERT INTO Table1(TenantId, ID, Field1, Field2)
VALUES (@TenantId1,2,'CCC','DDD')

/* Insert DEMO values for another tenant (@TenantId2) */
DECLARE @TenantId2 UNIQUEIDENTIFIER
SET @TenantId2 = '2917F585-60C2-4A23-832B-5C3712090ABB'
EXEC sp_set_Session_context 'TenantId', @TenantId2
INSERT INTO Table1(TenantId, ID, Field1, Field2)
VALUES (@TenantId2,1,'EEE','FFF')
INSERT INTO Table1(TenantId, ID, Field1, Field2)
VALUES (@TenantId2,2,'GGG','HHH')

/* Set Working Session TenantId with the first tenant (@TenantId1) */
EXEC sp_set_Session_context 'TenantId', @TenantId1

/* Retrieve Working Session TenantId for demo purposes */
SELECT SESSION_CONTEXT(N'TenantId') as WorkingSessionTenantId

/* Retrieve results from Table1 */
/* Only results matching TenantId with WorkingSessionTenantId will be returned */
SELECT * FROM Table1 

/* If WorkingSessionTenantId were null, no results would be returned */
EXEC sp_set_Session_context 'TenantId', null
SELECT * FROM Table1

ROLLBACK TRAN
go

/* Clean-up actions */
DROP FUNCTION SECURITY.SetSecurityTenantPredicate
GO
DROP SCHEMA SECURITY
GO

And here are the results:

First Query (Working Session TenantId with A5B2F100-F40B-48AE-A65B-6FC2465D1D51 value)

TenantId Id Field1 Field2
------------------------------------ ----------- ---------- ----------
A5B2F100-F40B-48AE-A65B-6FC2465D1D51 1 AAA BBB
A5B2F100-F40B-48AE-A65B-6FC2465D1D51 2 CCC DDD

Second Query (Working Session TenantId with null value)

No results

Improvements and Other Considerations

Be very careful when assigning values to the SESSION_CONTEXT(N'TenantId') session variable trying to always set some valid value or at least try to assign default ones. Otherwise, application could crash when dealing with null or empty session values.

Use Interceptors to log useful information and keep track your application behavior.

Use SQL Server Profiler or SQL Server Extended Events to monitor your SQL Server activity, check filters are performed correctly and performance is good enough (see use of indexes, number of reads, CPU utilization, etc.)

Tags : ,

Add comment