Multi-tenant 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); 
}