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