Implementing Row-Level Security

Enforce data visibility at the database engine level via session context and EF Core filters.

In this lesson, you will learn how to enforce multi-tenant data isolation at the database level by combining EF Core Global Query Filters with SQL Server’s Session Context.

The Problem: Application-Level Security

Usually, we filter data in C# by adding .Where(x => x.TenantId == currentTenantId) to every query. However, this is error-prone; a developer might forget to apply the filter in a complex query, accidentally exposing data across tenants. To make this foolproof, we push the enforcement down to the database engine.

The Solution: Session Context

SQL Server allows us to store arbitrary key-value pairs in a SESSION_CONTEXT. We can set this value when a connection is opened and use it inside a SQL Security Policy to filter rows automatically.

Step 1: Set the context in your DbContext Override SaveChanges or intercept the connection to set the tenant ID:

public void SetTenantId(int tenantId)
{
    Database.ExecuteSqlRaw("EXEC sp_set_session_context @key=N'TenantId', @value={0}", tenantId);
}

Step 2: Define a Global Query Filter In OnModelCreating, ensure EF Core understands that your entities are scoped to this context:

modelBuilder.Entity<Order>().HasQueryFilter(o => 
    o.TenantId == EF.Property<int>(null, "TenantId"));

Note: We use EF.Property to look up the value stored in the database session, keeping the filter synchronized with the database's actual security state.

Step 3: Database Security Policy Finally, you apply a Security Policy at the database level (via migration) that checks the SESSION_CONTEXT for every SELECT, UPDATE, or DELETE operation:

CREATE SECURITY POLICY TenantFilterPolicy
ADD FILTER PREDICATE dbo.fn_tenantAccessPredicate(TenantId) ON dbo.Orders
WITH (STATE = ON);

By doing this, even if a query bypasses the EF Core filter (or is executed via raw SQL), the database engine will return an empty set or throw an error rather than revealing another tenant's data. You have effectively moved your security boundary from the application code into the database's internal access control layer.

Key Takeaway: By binding your EF Core Global Query Filters to a SQL SESSION_CONTEXT, you create a "defense-in-depth" architecture where the database engine acts as the final authority on row visibility.