Entityframework db connection interceptor (for RLS)

Implement RLS in Azure SQL Server

Create 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) -- Sample data column
);

CompanyProviderAccess links companies to the providers they are allowed to access. Orders contains provider-related data that companies can access only if they have a link in CompanyProviderAccess.

Create 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'))
);

How it Works

The function compares the provider’s ID in the query to the provider IDs linked to the company making the request. The current company ID is stored in the session context, ensuring the access check is dynamic.

Apply the Security Policy to Restrict Access

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

What This Does

Automatically filters all queries on the Orders table to only return orders for providers that the requesting company is allowed to access.
Works transparently—developers do not need to add additional filtering logic.

Row Level Security

For RLS to be applied in SQL Database, the session parameter must be set when the query runs. In the example the session parameter is companyId.

Create an interceptor to have it set on every query run by EF.

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;
    }
}

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; }
}

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

builder.Services.AddSingleton<CompanySessionInterceptor>();