Using MS SQL’s Row Level Security Policy in Entity Framework

My last blog post was about a feature in SQL Server called Row Level Security Policy. It’s quite simple and known feature for people who’re working directly with databases. But most .NET developers are using ORM of some kind to work with DB, and some of them tend to be blind to stuff that is not available through object model in an easy way. And since Entity Framework is most popular ORM in C# environment and my ORM of choice in most cases, I’ll show you how to use RLS in it.

Since I’ll be using DB from my last post you should probably know its contents, if you don’t feel free to click here and continue after you’ll read it first and I’ll assume you’re familiar with my simple DB schema and concept of RLS.

I’ll assume we have database and data that we’ve used last time. So we’re using Entity Framework, we’re querying for some data that have some filter on it and … we’re getting nothing which is, of course, intended behaviour. Since our filter requires a value in SQL session we need to find a way to put it there first, before we’re going to use any LINQ to Entities at all.

Constructor

Using standard Entity Framework DbContext gives you a possibility to execute raw SQL commands against the underlying database. While it’s not used on a daily basis because writing raw SQL in C# code is considered bad practice, it’s sometimes required or just useful. This will be on of those cases and we will execute SP_SET_SESSION_CONTEXT manually because we can’t access it in any other way from Entity Framework. Where should we invoke this? Best place for this would be a constructor, so let fill our DbContext with some code.

public partial class EmployeesDbContext : DbContext
{
    private readonly long companyId;

    //Setting sql session state in constructor
    public EmployeesDbContext(ICompany company) : base("name=EmployeesDbContext")
    {
        companyId = company.Id;
        SetCompanyIdInSqlSession();
    }

    private void SetCompanyIdInSqlSession()
    {

        var sqlParameter = new SqlParameter("@companyId", companyId);
        this.Database.ExecuteSqlCommand(
            sql: "EXEC SP_SET_SESSION_CONTEXT @key=N'CompanyId' ,@value=@companyId",
            parameters: sqlParameter);
    }

    public virtual DbSet<Employee> Employees { get; set; }
}

public partial class EmployeesDbContext : DbContext

{

private readonly long companyId;

//Setting sql session state in constructor

public EmployeesDbContext(ICompany company) : base("name=EmployeesDbContext")

{

companyId = company.Id;

SetCompanyIdInSqlSession();

}

private void SetCompanyIdInSqlSession()

{

var sqlParameter = new SqlParameter("@companyId", companyId);

this.Database.ExecuteSqlCommand(

sql: "EXEC SP_SET_SESSION_CONTEXT @key=N'CompanyId' ,@value=@companyId",

parameters: sqlParameter);

}

public virtual DbSet<Employee> Employees { get; set; }

}

And it won’t work. Our SQL Session still wouldn’t have CompanyId value in it. Why? It’s quite simple DbContext behaviour which is irrelevant to developers in most use cases, at least unless you’re trying to do something like that. By default, EF will open and close SqlConnection before executing every single command. That means we’ve done something useless – we’ve opened SqlConnection, set value in session and closed it. We’ve achieved absolutely nothing.

However, this behaviour can be changed and I know two ways of doing this. First one is passing SqlConnection object to the base constructor which complicates things a bit but is easily achievable with most IoC containers. The second one is much easier and we’re going to use it – we need to open SqlConnection manually, let’s do that just before execution of our raw SQL.

public EmployeesDbContext(ICompany company) : base("name=EmployeesDbContext")
{
    this.Database.Connection.Open();

    companyId = company.Id;
    SetCompanyIdInSqlSession();
}

public EmployeesDbContext(ICompany company) : base("name=EmployeesDbContext")

{

this.Database.Connection.Open();

companyId = company.Id;

SetCompanyIdInSqlSession();

}

Worked like a charm.

But I really don’t like this solution. We’re keeping our connection open for much much longer than we’re going to use it and in for example Azure SQL we’re going to work with limited amount of simultaneously opened connections to the database. In that case, we’re wasting them and we can achieve limit to opened connections before even getting close to resource limit in our service tier. Let’s try another way.

StateChange Event

We’ve already met SqlConnection object lying inside of our DbContext. It does have one very interesting thing – event handler that is invoked every time when connection status is changed. And it’s exactly something we could use right now.

public EmployeesDbContext(ICompany company): base("name=EmployeesDbContext")
{
    companyId = company.Id;
    this.Database.Connection.StateChange += OnConnectionOpened;

}

private void OnConnectionOpened(object sender, StateChangeEventArgs e)
{
    if (e.CurrentState == ConnectionState.Open)
        SetCompanyIdInSqlSession();
}

public EmployeesDbContext(ICompany company): base("name=EmployeesDbContext")

{

companyId = company.Id;

this.Database.Connection.StateChange += OnConnectionOpened;

}

private void OnConnectionOpened(object sender, StateChangeEventArgs e)

{

if (e.CurrentState == ConnectionState.Open)

SetCompanyIdInSqlSession();

}

And that’s all. It works, outside of DbContext, you wouldn’t see the difference. There is one though – session could be set multiple times for one DbContext instance, each time for simple SQL command that will be sent to DB. While execution of this SQL command is blazingly fast and shouldn’t introduce performance overhead, you should really consider your latency because inside of our C# code we will wait for executing that which could mean 100ms for sending SQL command, 1ms to execute it and 100ms to send your response. If you already have problems with high latency they’re going to get worse.

Still, it’s my favourite solution, and if you’re having latency problems you should work them out anyway.

Interceptors

I really don’t like this way but it’s possible to use an interceptor to set session state before execution of anything we would do with Entity Framework. If you don’t know – interceptors allow you to intercept (hence the name) any SQL that goes out from Entity Framework to your DB and change it in probably any way you want. I.e. prepend our SQL it with an invocation of SP_SET_SESSION_CONTEXT. In case you don’t know how to create and attach interceptors – google it or take a look at a sample I’ll link in the summary, any code that I’m showing here will be included in a simple console app, you’ll just need to set up a database.

So shortened version of interceptor would look like that.

internal class SqlSessionStateInterceptor : IDbCommandInterceptor
{
    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        SetCompanyIdInSqlSessionState(command);
    }

    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        SetCompanyIdInSqlSessionState(command);
    }

    //Other interceptor events

    private void SetCompanyIdInSqlSessionState(DbCommand command)
    {
        var companyId = GetCurrentCompanyIdSomehow();

        var setSessionStateSql = "EXEC sp_set_session_context @key=N'CompanyId', @value=@CompanyIdFromEFInterceptor ;";

        //Append constructed command BEFORE anything you're executing in actual command
        command.CommandText = setSessionStateSql + command.CommandText;
        command.Parameters.Insert(0, new SqlParameter("@CompanyIdFromEFInterceptor", companyId));
    }
}

internal class SqlSessionStateInterceptor : IDbCommandInterceptor

{

public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)

{

SetCompanyIdInSqlSessionState(command);

}

public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)

{

SetCompanyIdInSqlSessionState(command);

}

//Other interceptor events

private void SetCompanyIdInSqlSessionState(DbCommand command)

{

var companyId = GetCurrentCompanyIdSomehow();

var setSessionStateSql = "EXEC sp_set_session_context @key=N'CompanyId', @value=@CompanyIdFromEFInterceptor ;";

//Append constructed command BEFORE anything you're executing in actual command

command.CommandText = setSessionStateSql + command.CommandText;

command.Parameters.Insert(0, new SqlParameter("@CompanyIdFromEFInterceptor", companyId));

}

It’s most performant way to do this from C# code that I know of. Basically, because it’s just SQL glued to the front of every SQL code that EF produces. Sadly I don’t really like this approach because I never quite thought of any good way for fetching current state like session data (which would be needed to determine what current company id actually is) in interceptors.

Summary

This post was short. And it was quite easy. There are much more fun features in SQL that we could use if we dig into it a bit and I’ll show you some of those in next few weeks and maybe months (sadly I don’t have much time for researching, writing and other stuff lately, I do hope it’ll change soon though).

And if you need to take a look or play a bit with some code – you can grab some from my repo here. Feel free to do anything you want with it.