Freigeben über

Row level security in EntityFramework 6 (EF6)

  • Artikel

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
  • POCO
  • Database interceptor
  • Context

Sample

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.

https://github.com/newsoftinc/Newsoft.Sample.RowLevelSecurity

Interface

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.

ISecuredByTenant.cs

1

POCO

Let’s take the northwind employee table and implement our interface.

2

Database interceptor

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.

Context

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.

3

 

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.

4

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.

 

Using it

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

context2.DisableFilter("SecuredByTenant ");

To enable a filter

context2.EnableFilter("SecuredByTenant ");

5

 

Conclusion

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.

1b8bb48

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.

Comments

  • Anonymous
    February 09, 2016
    Excellent approach David. Thanks for sharing!!
  • Anonymous
    February 12, 2016
    Hi, Thank you for the excellent article. I went through the code and found that tenant id is to be set on querying. Is it the recommended approach for security seasons? Or is it just one of the approach? In an Asp.net 5 project with EF7, I inject an IHttpContextAccessor into my repository's constructor, then I take the tenant id from user's claim via the injected HttpContext. The repository only expose a TenantId-filtered IEnumerable. There is nowhere else to get or set TenantId, all queries are pre-filtered, is there any security risks in this approach please?
    • Anonymous
      February 18, 2016
      While using a dependency injection approach for your tenant is good, your approach implement a strong coupling between an HttpContext and your DbContext, this is definately something to avoid.For the purpose of the demo I wanted to keep things simple and expose a SetCurrentTenant method. In production,that use case is replaced with the dependency injection of the tenant in the context using a very limited interface ICurrentTenant, that really just provide the Id of the current tenant.Now within your DbContext constructor , you can just resolve the dependency , for NInject something like public TenantAwareDbContext() {var kernel = new StandardKernel();tenantId = kernel.Get().Id; }
  • Anonymous
    March 15, 2016
    This is an excellent article, thanks! I'm a little torn between using this approach, and the approach outlined here https://azure.microsoft.com/en-us/documentation/articles/web-sites-dotnet-entity-framework-row-level-security/ Any opinion on the subject?
    • Anonymous
      March 30, 2016
      I would say it depends on the scale of your application and how you want to approach caching of objects. We use L2 caching (EFCache or NCache) in most of our projects that provides scalable caching for distributed application.Concurrent transactions on the database will not reflect at the application level for all instances and for this reason I prefer an applicative approach to securing the data.
  • Anonymous
    May 09, 2016
    Hi, thanks for a great article (seeing code as pictures made me smile though). I believe that SaveChanges should throw if the tenant id is null and the entity is ISecuredByTenant. Something like this:if (iSecuredByTenantEntry != null) { if(_currentTenantId == null ) { throw new ApplicationException("Saving entity protected with tenant id and tenant id is not set."); } iSecuredByTenantEntry.SecuredByTenantId = _currentTenantId.Value; }
  • Anonymous
    January 12, 2017
    Thanks for the article. I like the approach. One question: How would one control foreign key values? Example: Suppose a malicious user has changed the value of a foreign key so that his record will reference a row that he does not own. Is there any way to use the same feature to protect against that sort of attack?(if you are wondering how an fk would be exposed, consider a REST api where id's to related objects are in the resource being submitted)
  • Anonymous
    March 30, 2017
    Hi, Thank you for the excellent article. i am wondering to the same but with dbfirst. is it possible, if you can give me a hand would be great. thankgs