Implementing Row-Level Security in Entity Framework with Connection Interceptors

Goal

Implement Row-Level Security (RLS) in Azure SQL Server that automatically filters queries based on the current user’s company. Entity Framework will set the session context on every connection using a custom interceptor.

SQL Server Setup

Create the Data Model

CREATE TABLE Companies (
    CompanyId UNIQUEIDENTIFIER PRIMARY KEY,
    Name NVARCHAR(255)
);

CREATE TABLE Providers (
    ProviderId UNIQUEIDENTIFIER PRIMARY KEY,
    Name NVARCHAR(255)
);

CREATE TABLE CompanyProviderAccess (
    CompanyId UNIQUEIDENTIFIER REFERENCES Companies(CompanyId),
    ProviderId UNIQUEIDENTIFIER REFERENCES Providers(ProviderId),
    PRIMARY KEY (CompanyId, ProviderId)
);

CREATE TABLE Orders (
    OrderId UNIQUEIDENTIFIER PRIMARY KEY,
    ProviderId UNIQUEIDENTIFIER REFERENCES Providers(ProviderId),
    Data NVARCHAR(MAX)
);

CompanyProviderAccess links companies to the providers they can access. Orders will be filtered based on these access rights.

Create the Security Predicate Function

CREATE FUNCTION dbo.fn_OrderAccessPredicate(@ProviderId UNIQUEIDENTIFIER)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    SELECT 1 AS fn_result
    FROM CompanyProviderAccess cpa
    WHERE cpa.ProviderId = @ProviderId
    AND cpa.CompanyId = CONVERT(UNIQUEIDENTIFIER, SESSION_CONTEXT(N'current_company'))
);

The function checks if the provider ID matches any provider the current company has access to. The company ID comes from SESSION_CONTEXT.

Apply the Security Policy

CREATE SECURITY POLICY OrderSecurityPolicy
ADD FILTER PREDICATE dbo.fn_OrderAccessPredicate(ProviderId) ON dbo.Orders
WITH (STATE = ON);

All queries on Orders now automatically filter to only return accessible provider data.

Entity Framework Integration

Create the Connection Interceptor

using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.Data.SqlClient;
using System.Data.Common;
using System.Threading;
using System.Threading.Tasks;

public class CompanySessionInterceptor : DbConnectionInterceptor
{
    private readonly IHttpContextAccessor _httpContextAccessor;

    public CompanySessionInterceptor(IHttpContextAccessor httpContextAccessor)
    {
        _httpContextAccessor = httpContextAccessor;
    }

    public override Task ConnectionOpenedAsync(DbConnection connection, ConnectionOpenedEventData eventData, CancellationToken cancellationToken = default)
    {
        if (connection is SqlConnection sqlConnection)
        {
            var companyId = _httpContextAccessor.HttpContext?.User?.FindFirst("company_id")?.Value;
            if (!string.IsNullOrEmpty(companyId))
            {
                using var command = sqlConnection.CreateCommand();
                command.CommandText = "EXEC sp_set_session_context @key=N'current_company', @value=@companyId";
                command.Parameters.Add(new SqlParameter("@companyId", companyId));
                command.ExecuteNonQuery();
            }
        }
        return Task.CompletedTask;
    }
}

Register the Interceptor in DbContext

public class AppDbContext : DbContext
{
    private readonly CompanySessionInterceptor _companySessionInterceptor;

    public AppDbContext(DbContextOptions<AppDbContext> options, CompanySessionInterceptor companySessionInterceptor)
        : base(options)
    {
        _companySessionInterceptor = companySessionInterceptor;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.AddInterceptors(_companySessionInterceptor);
    }

    public DbSet<Order> Orders { get; set; }
}

Configure Dependency Injection

builder.Services.AddHttpContextAccessor();
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

builder.Services.AddSingleton<CompanySessionInterceptor>();

Every database connection now automatically sets the session context, and RLS filters apply transparently to all queries.