Entity Framework Core Row Level Security using SESSION_CONTEXT

Question

I am writing a multi-tenant application using Row Level Security using ASP.NET Core and Entity Framework 7 (Core). Since my database is hosted on Microsoft SQL Server, I have used this method to enforce RLS.

Now all I need is to set desired tenant_id in the SESSION_CONTEXT.

First problem I faced was to run a stored procedure using EF7. A solution seems to be:

var resp = context.Set<SessionVars>().FromSql(
          "EXECUTE sp_set_session_context @key = N'my_tenant', @value = {0};
           SELECT * FROM mySessionVars", desiredTenant).ToList();

Using the above command I can clearly see that the SESSION_CONTEXT is successfully set. Now I expect to see that the next queries on the same context are filtered according to the tenant I set in SESSION_CONTEXT.

int visibleRows = context.MyModel.ToList().Count;

Unfortunately the results are not as expected. It behaves like the rows were retrieved before SESSION_CONTEXT was set.

Is this caused by the Eager Loading of EF7? IS EF7 using cashed data? How can I overcome this?

I expect to be able to set any value I want for the SESSION_CONTEXT and this to be hold in the context until changed or until connection is closed.

A couple of blogs show a different approach, craigpickles.com/… not sure if that is helpful or not. but thought i would share. — Mike Miller
– Mike Miller, Commented Aug 15, 2016 at 16:33
As of Entity Framework Core 2.0, Global Query Filters is a better solution to Row Level Security instead of using the database. gunnarpeipman.com/2017/08/ef-core-global-query-filters — Greg Gum
– Greg Gum, Commented Mar 4, 2018 at 11:59
@Greg0 Query filters are nice feature, but I prefer injecting session context so that the server will validate the tenancy. — tourili
– tourili, Commented Jun 29, 2018 at 12:59

cgalev · Accepted Answer · 2016-09-09 14:23:14Z

4

I was abble to found an answer mysels by reading this article,

EF6 and future versions we have taken the approach that if the calling code chooses to open the connection by calling context.Database.Connection.Open() then it has a good reason for doing so and the framework will assume that it wants control over opening and closing of the connection and will no longer close the connection automatically.

The solution is to open the connection before executing any EF command.

context.Database.Connection.Open();

edited Sep 9, 2016 at 14:23

answered Sep 9, 2016 at 14:14

cgalev

4313 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Greg Gum Over a year ago

How did it go with this? I am looking to do the very same thing.

tourili Over a year ago

Or first inject your context and then retreive your session vars from the client ?

Collectives™ on Stack Overflow

Entity Framework Core Row Level Security using SESSION_CONTEXT

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related