Editor’s note: The following post was written by Office Servers and Serices MVP David Bérubé as part of our Technical Tuesday series.
There are several situations where you need to manage row level security for your database:
- Multi-tenancy (SaaS)
- Role or claim based row level security
- Logical delete
- Enabled/Disabled data
For this article, we will take the example of a multi-tenancy context where we want to abstract tenancy from the queries, making sure developers are not required to specify the current tenant on every query.
One approach to multi-tenancy database in entity framework is to use a shared database with shared schema, securing tables using row level security concepts. Every tenant of your application will be hosted under the same database, sharing the same tables.
In order to abstract the tenant filters from the business logic, filters should be applied at the data-access layer, entity framework. Unfortunately, EF6 does not provide support out of the box to configure query filters. We can however use EntityFramework.DynamicFilters to achieve this.
Let’s break down the process:
- Interface / convention
- Database interceptor
There is a github repository I created for this article. Just clone the repository and restore the SRC/Northwind.bak file to MS Sql server.
First, let’s create an interface that will provide an abstraction for our tenant ID. This will represent the required field that we will add to our table later. In my current project we are using Guids as keys for our database; just adapt the concept to your requirements.
Let’s take the northwind employee table and implement our interface.
The database interceptor will be configured later in our context, and the interceptor will apply configured conventions to our DbQuery before they are executed to the server. To achieve this, the DynamicFilterInterceptor is implementing IDbCommandTreeInterceptor. The TreeCreated method will make sure any query expressions that match our conventions are replaced to include our filters.
There are different approaches to configure the context to use our filters. I prefer creating an inherited DbContext that will register our interceptors and define our conventions.
The Init method calls an extension method InitializeDynamicFilters that registers a DbContext for the DbInterceptor.
The modelCreating method is called after the context has been initialized, and provides a modelbuilder used to configure our conventions.
We create a filter named SecuredByTenant that will secure every POCO that implements ISecuredByTenant, where the SecuredByTenantId is equals to our tenantId var.
One important part is the SaveChanges override that will ensure that any rows created within that context that does implement our ISecuredByTenant interface will be assigned with our provided tenant ID.
That’s it! All you need to do from here is implement ISecuredByTenant on any POCO that needs to be secured by tenant and they will be managed automatically.
To disable a filter
To enable a filter
This solution provide an easy way to implement tenants in any project using entity framework. It has been tested with different database scenarios and will support complex join queries. It also provides a good way to abstract certain behaviors from your business logic. The same concepts can be used for different applications.We use it with the following interfaces: ISecuredByRole, IDisabledRow, IReadOnlyRow, IDeletedRow.
About the author
David Bérubé is a solution architect specialized in security and software architecture. He has over 12 years of experience working with different Microsoft technologies, developing software and cloud solutions for his customers. He is also active in different online communities, working with the Center of Internet Security to write security benchmarks for Microsoft products, and contributing to different open source projects and online forums.