Row-Level Security in Azure SQL with Entity Framework

• Part 1: Row-Level Security in Azure SQL > Filter Predicates
• Part 2: Row-Level Security in Azure SQL > Block Predicates

In the previous two blog posts about new features in Azure SQL Database, Row-Level Security (RLS), we showed how you could restrict select and write operations on a database table.

In this blog post, we will look at how Entity Framework can be used alongside RLS to prevent users from accessing certain information stored within an Azure SQL database.

In this example, first we will create a table that will contain a list of projects and create the predicate and security policy as we have done in the previous blog posts.

This time however we are going to use the SESSION_CONTEXT which is a new feature in SQL Server that allows key value pairs to be stored and used throughout the current SQL session (similar to ASP.NET's session state).

CREATE TABLE Project
    (
    Id int NOT NULL PRIMARY KEY IDENTITY(1,1),
    Name varchar(100),
    UserId varchar(128) DEFAULT CAST(SESSION_CONTEXT(N'UserId') AS nvarchar(128))
    );

CREATE SCHEMA Security
go

CREATE FUNCTION Security.projectSecurityPredicate(@UserId nvarchar(128))
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS myResult
    WHERE @UserId = CAST(SESSION_CONTEXT(N'UserId') AS nvarchar(128))
go

CREATE SECURITY POLICY Security.projectSecurityPolicy
    ADD FILTER PREDICATE Security.projectSecurityPredicate(UserId) ON dbo.Project,
    ADD BLOCK PREDICATE Security.projectSecurityPredicate(UserId) ON dbo.Project
go

The Project table defined above has a column called UserId that has a default constraint that sets the default value to the current session's userId value. We also use the session's userId within the security predicate.

Next, we need to insert some rows. Before we do this, we set the session context userId value to a GUID identifying a particular user.

EXEC sp_set_session_context 'UserId', 'ce2cc018-9255-48be-887b-928a17c86a97';
INSERT Project(name) VALUES
('Project ABC'),
('Project XYZ')

EXEC sp_set_session_context 'UserId', 'ce2cc018-9255-48be-887b-928a17c86a97';
INSERT Project(name) VALUES
('Project 1'),
('Project 2'),
('Some other project')

Now that our database is all set up, we need to configure Entity Framework to let our database know who the current user is as all users in our situation connect to the database using the same SQL login defined in the connection string.

The solution is to have Entity Framework set the session context userId value before opening a connection to the database using the current authenticated user's id.

We can do this using a DbConnectionInteceptor.

using System.Web;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Infrastructure.Interception;
using Microsoft.AspNet.Identity;

namespace RLS.EntityFramework
{
    public class SessionContextInterceptor : IDbConnectionInterceptor
    {
        public void Opened(DbConnection connection, DbConnectionInterceptionContext interceptionContext)
        {
            var userId = HttpContext.Current.User.Identity.GetUserId();
            if (userId != null)
            {
                DbCommand cmd = connection.CreateCommand();
                cmd.CommandText = "EXEC sp_set_session_context @key=N'UserId', @value=@UserId";
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@UserId";
                param.Value = userId;
                cmd.Parameters.Add(param);
                cmd.ExecuteNonQuery();
            }
        }

        public void Opening(DbConnection connection, 
		DbConnectionInterceptionContext interceptionContext)
        {
        }

        public void BeganTransaction(DbConnection connection, 
		BeginTransactionInterceptionContext interceptionContext)
        {
        }

        public void BeginningTransaction(DbConnection connection, 
		BeginTransactionInterceptionContext interceptionContext)
        {
        }

        public void Closed(DbConnection connection, 
		DbConnectionInterceptionContext interceptionContext)
        {
        }

        public void Closing(DbConnection connection, 
		DbConnectionInterceptionContext interceptionContext)
        {
        }

        public void ConnectionStringGetting(DbConnection connection, 
        DbConnectionInterceptionContext<string> interceptionContext)
        {
        }

        public void ConnectionStringGot(DbConnection connection, 
        DbConnectionInterceptionContext<string> interceptionContext)
        {
        }

        public void ConnectionStringSet(DbConnection connection, 
        DbConnectionPropertyInterceptionContext<string> interceptionContext)
        {
        }

        public void ConnectionStringSetting(DbConnection connection, 
        DbConnectionPropertyInterceptionContext<string> interceptionContext)
        {
        }

        public void ConnectionTimeoutGetting(DbConnection connection, 
        DbConnectionInterceptionContext<int> interceptionContext)
        {
        }

        public void ConnectionTimeoutGot(DbConnection connection, 
        DbConnectionInterceptionContext<int> interceptionContext)
        {
        }

        public void DataSourceGetting(DbConnection connection, 
        DbConnectionInterceptionContext<string> interceptionContext)
        {
        }

        public void DataSourceGot(DbConnection connection, 
        DbConnectionInterceptionContext<string> interceptionContext)
        {
        }

        public void DatabaseGetting(DbConnection connection, 
        DbConnectionInterceptionContext<string> interceptionContext)
        {
        }

        public void DatabaseGot(DbConnection connection, 
        DbConnectionInterceptionContext<string> interceptionContext)
        {
        }

        public void Disposed(DbConnection connection, 
        DbConnectionInterceptionContext interceptionContext)
        {
        }

        public void Disposing(DbConnection connection, 
        DbConnectionInterceptionContext interceptionContext)
        {
        }

        public void EnlistedTransaction(DbConnection connection, 
        EnlistTransactionInterceptionContext interceptionContext)
        {
        }

        public void EnlistingTransaction(DbConnection connection, 
        EnlistTransactionInterceptionContext interceptionContext)
        {
        }

        public void ServerVersionGetting(DbConnection connection, 
        DbConnectionInterceptionContext<string> interceptionContext)
        {
        }

        public void ServerVersionGot(DbConnection connection, 
        DbConnectionInterceptionContext<string> interceptionContext)
        {
        }

        public void StateGetting(DbConnection connection, 
        DbConnectionInterceptionContext<System.Data.ConnectionState> interceptionContext)
        {
        }

        public void StateGot(DbConnection connection, 
        DbConnectionInterceptionContext<System.Data.ConnectionState> interceptionContext)
        {
        }
    }

    public class SessionContextConfiguration : DbConfiguration
    {
        public SessionContextConfiguration()
        {
            AddInterceptor(new SessionContextInterceptor());
        }
    }
}

That's the only change we need. If we now retrieve a list of Projects using Entity Framework, it will restrict the Projects returned based on the current authenticated user. Similarly, if we insert a new Project without specifying a userId, it will be set to the current authenticated user's id.

CodeProject