Tenant Isolation in a Multi-Tenant SaaS: EF Core Filters Aren't Enough, So I Added SQL Server RLS

On the multi-tenant platform I built for law firms, one question kept me up more than any other: what happens if one firm's client file becomes visible to another firm, even by accident? The answer is simple: the product is over. With attorney-client privilege and data-protection law on the line, "we forgot a Where somewhere" is not something you can recover from. So I didn't trust isolation to a single mechanism. I split it into three independent layers. Here's what those layers are, and why I didn't consider any one of them sufficient on its own.

Who decides the tenant? Only the signed token

The first rule: the client never tells the server which tenant (firm) it belongs to. No header, query string, route parameter or body field determines the tenant. TenantId is read only from the tid claim of a validated JWT. The firm code is used exclusively on the login screen: it's resolved to a tenant, the user is authenticated, and from then on the tenant lives inside the signed token.

That has a practical consequence: the user model is tenant-scoped too. The same phone number at two different firms means two separate accounts. It looks odd at first, but it draws a hard boundary at the identity level, so "which firm is this user in?" is never ambiguous. The super admin lives in an entirely separate table and gets tokens from a separate issuer/audience. A firm token can't reach platform endpoints, and a platform token can't reach firm endpoints.

Layer one: EF Core global query filters

Every tenant-owned entity implements ITenantEntity, and while the model is being built, reflection applies the same filter to all of them: e.TenantId == currentTenant && !e.IsDeleted. Developers don't have to think about the tenant when writing a query, and they can't forget it either.

On the write side, a pipeline that runs during SaveChanges steps in: new records get their TenantId assigned automatically, and if a modified or deleted record's TenantId differs from the current tenant, it throws. So even if another tenant's row somehow ends up in memory, you can't update it.

So why isn't that enough? Because the EF filter lives in application code. Someone can call IgnoreQueryFilters(), write raw SQL for a report, or a future refactor can quietly break the filter. Any protection in the application layer can be punctured by a bug in the application layer.

Layer two: SQL Server Row-Level Security

That's why the real lock sits in the database. Every time a connection opens, a DbConnectionInterceptor writes the current tenant into SQL Server's session context:

internal sealed class TenantSessionContextInterceptor(ITenantContext tenantContext)
    : DbConnectionInterceptor
{
    public override Task ConnectionOpenedAsync(DbConnection connection,
        ConnectionEndEventData eventData, CancellationToken cancellationToken = default) =>
        SessionContext.ApplyAsync(connection, tenantContext.TenantId,
            tenantContext.IsSystem, cancellationToken);
}
 
// EXEC sys.sp_set_session_context @key = N'TenantId', @value = @tenantId;
// EXEC sys.sp_set_session_context @key = N'IsSystem', @value = @isSystem;

There's a small but critical detail here: the value is written on every open, even when the tenant is empty. A connection coming out of the pool may still carry the previous request's tenant. An innocent-looking "skip it if it's null" optimization would mean a request running under another tenant's session context.

On the database side, a predicate function bound to the tenant tables enforces that value:

SELECT 1 AS allowed
WHERE @TenantId = CAST(SESSION_CONTEXT(N'TenantId') AS uniqueidentifier)
   OR CAST(SESSION_CONTEXT(N'IsSystem') AS bit) = 1;

Every tenant table gets a FILTER predicate for reads plus BLOCK predicates for after-insert and after-update. The security policy is created WITH SCHEMABINDING = ON, and the migration tool regenerates it from the table list on every run. So adding a new tenant table and forgetting to bind it to RLS isn't a risk either. The end result: even if the application-side filter is bypassed by a bug, the database won't return, or let you write, another tenant's rows.

The IsSystem flag is a deliberate escape hatch. Background jobs that genuinely need to work across tenants, like nightly notification jobs or platform reports, turn it on. No client request can set it; only marked infrastructure code can.

Layer three: record scope (even within one firm)

Tenant isolation separates firms from each other, but there's a boundary inside the firm too: a junior lawyer should only see the cases they're assigned to, and a client should only see their own. I didn't leave that to controller attributes. Permissions come in pairs (Cases.ViewAll / Cases.ViewAssigned), and the scope is added directly to the query. That way listings, search, counters and notification targeting all go through the same rule. Those last two are easy to miss: if a case you can't see in the list still counts toward the "3 new documents" badge in the menu, that's a leak. And requesting a single out-of-scope record returns 404, not 403, so even the record's existence doesn't leak.

Bonus: tamper-evident logs

At a law firm, the answer to "who saw which document, and when" matters as much as the data itself. Audit, access, security, finance and data-protection logs live in separate tables, each kept as a hash chain per tenant and log type. Every row carries the previous row's hash; inserting, deleting or modifying a row breaks the chain and the verifier catches it. The chain-head row is locked with UPDLOCK, HOLDLOCK, so two concurrent writes can't receive the same sequence number. Logs are also written in the same transaction as the business operation: if the log can't be written, the operation rolls back.

Wrapping up

A single layer of protection can be punctured by a single bug in that layer. Reading the tenant only from a signed token, EF filters and a SaveChanges check on the application side, RLS on the database side: all three work independently, and if any one of them fails, the other two stop the leak. Starting with a shared database and shared schema keeps operations simple, and RLS is what makes that simplicity possible without compromising on security. You'll find the platform's mobile apps, 2FA and token-rotation details on the project page.