Building multitenant application – Part 1: Multitenant database using Row Level Security

In this and upcoming posts, I’ll be sharing what I have learned while implementing a multi-tenant solution. Specifically, in this post, I share my understanding about Row Level Security in SQL server, and how I have utilized it to host data for multiple organizations in a way that is transparent to the users.

Scenario

Suppose you are a service provider building a web application for companies to manage their employees. Each organization who wants to use your software can sign up for the service. To save cost, you decide to host all organizations’ data in a same database, effectively making the database multitenant.

In your database, you may have a schema similar to the following:

In your web API, you may have queries that filter the employees based on the organization id. For instance, your SQL to retrieve the list of employees for a company may look something like:

SELECT * FROM dbo.Employees WHERE OrganizationId = {Insert_Organization_Id}

In your web API, if you use Entity Framework, your logic may look something like below:

public IList<Employee> GetAllEmployees(int organizationId) {
     return _dbContext.Employees.Where(employee => employee.OrganizationId == organizationId); 
}

In all your CRUD requests, you will need to filter by the organization id. While this scenario has been simplified, in a real application, having to do filtering in every CRUD operations can make your codes a bit more complex. Not only do your CRUD operations need to aware of multitenant when querying or operating a list of employees, even when the user passes in an employee’s id, you may still want to check the organization to ensure the user can only view data of his or her organization. An example implementation may look something like below.

public async Task<IActionResult<Employee>> GetById(int employeeId) {
     int organizationId = GetOrganizationIdOfLoggedInUser(); 
     Employee employee = _employeeService.GetById(employeeId); 
     if (employee.OrganizationId != organizationId) {
         // throw 403 Forbidden error. 
     } 
     return OkResult(employee); 
}

With row level security, you can simplify your CRUD operations, and at the same time, strengthen the validation by doing the filtering at the database layer.

How does row level security work?

A table value function and a security policy form the basis for row level security. You write the table value function to determine whether to include a row in a query result. The function accepts a parameter which represents a column value of the target table. The parameter usually represents the value of the primary key column of the row. The function returns data which the server use to determine whether to include or exclude the row. If the function returns a value, then the server includes the row, otherwise it excludes it.

Below show an example of a table value function:

CREATE FUNCTION [Security].[fn_securitypredicate_employees](@Id int)
	                RETURNS TABLE 
	                WITH SCHEMABINDING 
                AS 
	                RETURN SELECT 1 AS fn_securitypredicate_result 
	                FROM dbo.Employees 
	                WHERE Employees.Id = @Id AND 
	                Employees.OrganizationId 
					=  (Select OrganizationId from dbo.Employees WHERE AzureAdObjectId = CAST(SESSION_CONTEXT(N'azureAdObjectId') AS nvarchar(100)));

The server can use this function when filtering a result of a query on the Employees table. For each row in the result, it calls the function, passing the value of the id column. If the select statement returns a result, the server includes the row; otherwise, the server excludes it. In the above example, the function retrieves the azure object id from the session context, and use it to filter out employees that do not belong to the same organization as that of the employee with the given azure object id. In subsequent requests to retrieve employees data, the security predicate will filter the result to return only the employees that belong to the same organization as that of the logged in user. In the next post, I will show an example of how the web API presets the azure object id of the logged in user into the session context.

Having the table value function is not enough, you also need to associate the function to the table where you want to apply the filtering. You create the association by defining a security policy, as shown in the example below.

CREATE SECURITY POLICY [Security].[EmployeesFilter] 
                ADD FILTER PREDICATE[Security].[fn_securitypredicate_employees]([Id]) ON[dbo].[Employees]
                WITH(STATE = ON, SCHEMABINDING = ON)

With the above security policy, for each row in a result of records from Employees table, the database server will call the Security.fn_securitypredicate_employees function, passing the value of the id column.

Suppose I initially have the following records in the Employees table:

Id	First Name	Last Name	Organization Id	Azure Ad Object Id
1002	John	Doe	1	441def63-6f4b-483f-8b97-cc47c431d24f
1003	Jane	Doe	2	e3be5238-1ece-4d72-8122-7c01bc398acb
1004	Jin	Doe	2	8c1c5f19-fe53-427d-8326-236c8a76813e

Without the security policy ([Security].[EmployeesFilter]), or with the security disabled (STATE = OFF), the following select query will return the above three records:

SELECT * FROM dbo.Employees;

However, as soon as I enable the security policy, the above query will return an empty result.

Id	First Name	Last Name	Organization Id	Azure Ad Object Id

Let’s test the security policy and table value function by setting the azureAdObjectId in the session context, using the query below:

EXEC sp_set_session_context @key = "azureAdObjectId", @value = "e3be5238-1ece-4d72-8122-7c01bc398acb";

The above query sets the azure object id to be the same as that of Jane Doe, the employee which belongs to the organization which has id = 2. With the security policy enabled, executing the select statement returns the following results, as shown below:

Id	First Name	Last Name	Organization Id	Azure Ad Object Id
1003	Jane	Doe	2	e3be5238-1ece-4d72-8122-7c01bc398acb
1004	Jin	Doe	2	8c1c5f19-fe53-427d-8326-236c8a76813e

Notice that the result does not include John Doe, as expected, since Jane Doe does not belong to organization 2.

Conclusion

In this post, I show an example of presetting the metadata for filtering by setting and querying session context. However, you can also do filtering based on the currently logged in database user. In the next post, I’ll share exactly how to establish the metadata in the session context from an ASP.NET core web API. To learn more about RLS, I recommend reading Microsoft documentation on the topic. You can check out the links in the Reference sections.

References

Row Level Security

Introduction to Row-Level Security in SQL Server (sqlshack.com)