Enforce Tenant Isolation in Tests, Database Constraints, Authorization, and Operational Tooling

Learning outcomes

1. Assume one layer will eventually be bypassed

Global filters can be disabled. A privileged admin tool can be misconfigured. A background job can construct the wrong context. Raw SQL can bypass EF entirely. Defense in depth means a single coding mistake should not automatically become a cross-tenant incident.

For ServiceHub, the enforcement stack is: trusted tenant resolution → authorization → EF filter defaults → tenant-aware writes → relational constraints → least-privilege database access → optional database row-level security where supported → adversarial tests and audit.

2. Tenant identity is server-owned during writes

public sealed record CreateWorkOrderCommand(    string WorkOrderNumber,    string Summary,    string Street,    string City,    string Region,    string PostalCode,    string CountryCode);// Add this overload inside the existing WorkOrder partial class.public static WorkOrder OpenForTenant(    string tenantId, string number, string summary, ServiceAddress address){    var row = Open(number, summary, address);   // established Chapter 06 factory    row.TenantId = tenantId;    return row;}public async Task<int> HandleAsync(CreateWorkOrderCommand command, CancellationToken ct){    var address = new ServiceAddress(        command.Street, command.City, command.Region,        command.PostalCode, command.CountryCode);    var row = WorkOrder.OpenForTenant(        db.TenantId, command.WorkOrderNumber, command.Summary, address);    db.Add(row);    await db.SaveChangesAsync(ct);    return row.Id;}

If an external DTO contains TenantId, treat it as a resource-selection claim to validate against authorization, never as the persisted truth. Better APIs often omit it entirely for tenant-local operations.

3. Database constraints should understand tenant ownership

A plain foreign key from work_orders.assigned_technician_id to technicians.id can permit a work order in tenant A to reference a technician in tenant B if IDs are globally valid. This lesson therefore extends the tenancy migration to add/backfill a required tenant_id on technicians, then strengthens the relationship by including the tenant discriminator in the principal and foreign key. Review that migration like any other ownership backfill; do not assign tenants with a blanket production default.

modelBuilder.Entity<Technician>(b =>{    b.HasAlternateKey(t => new { t.TenantId, t.Id });});modelBuilder.Entity<WorkOrder>(b =>{    b.HasOne(w => w.AssignedTechnician)        .WithMany(t => t.WorkOrders)        .HasForeignKey(w => new { w.TenantId, w.AssignedTechnicianId })        .HasPrincipalKey(t => new { t.TenantId, t.Id });});

FOREIGN KEY (tenant_id, assigned_technician_id)REFERENCES technicians (tenant_id, technician_id)

This does not implement row-level authorization, but it blocks an important class of cross-tenant relationship corruption even if EF filters are bypassed.

4. Adversarial tests: prove the negative cases

[Fact]public async Task TenantA_cannot_read_TenantB_row(){    await using var a = factory.CreateForTenant("tenant-a");    var visible = await a.WorkOrders.AsNoTracking().Select(w => w.TenantId).Distinct().ToListAsync();    Assert.Equal(new[] { "tenant-a" }, visible);}[Fact]public async Task Cross_tenant_technician_assignment_fails_constraint(){    // Seed A work order + B technician, then deliberately set the wrong FK in a low-level test.    await Assert.ThrowsAsync<DbUpdateException>(() => attacker.SaveChangesAsync());}[Fact]public async Task Recycle_bin_keeps_TenantFilter(){    var sql = db.WorkOrders        .IgnoreQueryFilters(new[] { "SoftDeleteFilter" })        .ToQueryString();    Assert.Contains("tenant_id", sql, StringComparison.OrdinalIgnoreCase);}

Also test raw SQL helpers, scheduled jobs, message consumers, exports, support/admin endpoints, and migration/backfill scripts. The dangerous code is often outside the normal request handler.

5. Deliberate failure: admin equals “filters off everywhere”

if (user.IsAdmin)    query = query.IgnoreQueryFilters();

“Admin” is too broad. Split capabilities: tenant recycle-bin read, tenant restore, cross-tenant support lookup, compliance export, and migration repair are different privileges. Cross-tenant operations should require a break-glass or explicit operational role, a reason/ticket, and an audit record.

6. SQLite: strong constraints, no built-in row-level security policy

The mandatory free lab uses SQLite, which can enforce foreign keys, uniqueness, checks, and transactions, but does not provide SQL Server-style row-level security policies. That means a shared-database SQLite application credential can still read any row through raw SQL. Application authorization/filtering and constrained operational tooling remain essential.

PRAGMA foreign_keys;PRAGMA foreign_key_list('work_orders');PRAGMA index_list('work_orders');

7. Optional SQL Server defense: row-level security

SQL Server and Azure SQL support row-level security (RLS) with filter and block predicates. A common application pattern stores the authorized tenant in SESSION_CONTEXT after opening a connection, and an RLS security policy filters/blocks rows by tenant_id. This is an additional database boundary, not a reason to remove EF filters or authorization.

CREATE FUNCTION Security.fn_tenantPredicate(@TenantId nvarchar(64))RETURNS TABLE WITH SCHEMABINDINGAS RETURN SELECT 1 AS allowedWHERE @TenantId = CAST(SESSION_CONTEXT(N'TenantId') AS nvarchar(64));GOCREATE SECURITY POLICY Security.WorkOrderTenantPolicyADD FILTER PREDICATE Security.fn_tenantPredicate(tenant_id) ON dbo.work_orders,ADD BLOCK PREDICATE Security.fn_tenantPredicate(tenant_id) ON dbo.work_orders AFTER INSERTWITH (STATE = ON);GO

Connection pooling matters: session state is attached to the physical connection. The application must set/verify it for every opened connection according to the provider’s documented semantics. Test the policy with the actual production driver and pooling configuration.

8. Least privilege and operational tooling

Separate runtime and migration credentials where feasible. A query filter cannot protect against a database principal with broad direct table access, and a database policy cannot validate an application user’s business authorization unless identity is safely propagated.

9. Production telemetry and incident runbook

Security telemetry should answer: How often are query filters disabled? Which capability initiated it? Were cross-tenant result sets expected? Are there tenant-mismatch constraint failures? Are contexts ever created without tenant scope? Did a tenant catalog mapping change? Keep tenant IDs out of high-cardinality public metrics but include them in protected audit records when incident response requires it.

Runbook steps: disable the affected capability, identify tenant/time window, preserve logs, verify database constraints/RLS state, rotate credentials if direct access was involved, compare audit export counts, restore/correct data if needed, then add a regression test for the exact bypass path.

10. Mandatory final lab: red-team the boundary

1. Seed two tenants with overlapping human-readable work-order numbers so a missing tenant predicate cannot hide behind globally unique data.
2. Run normal reads, recycle-bin reads, direct key lookups, and projections under both tenant contexts.
3. Attempt a cross-tenant technician assignment and assert the composite foreign key rejects it.
4. Attempt an unauthorized TenantFilter bypass through the application service; assert authorization fails before SQL executes.
5. Run an intentionally privileged cross-tenant audit query, emit an audit event, and verify the result set is labeled/handled as multi-tenant data.
6. On SQLite, record the absence of RLS and verify PRAGMA foreign_keys=1. Optionally repeat on SQL Server with RLS in a disposable database.
7. Document recovery and credential ownership, then delete the disposable lab databases.

11. Production judgment and bridge

Tenant isolation is a system property, not an EF feature. Global and named filters create excellent safe defaults; relational constraints preserve tenant-consistent relationships; authorization controls who may bypass; database policies can add another boundary on capable providers; and tests/audit make violations observable. Chapter 23 turns this philosophy into a broader EF testing strategy: deciding what can be unit-tested and what must execute against a real relational provider.

Authoritative references

• Global query filters — bypass behavior and application filter limitations
• EF Core multi-tenancy — tenant scoping patterns and topology support
• EF Core foreign/principal keys — composite principal/foreign key enforcement
• SQLite foreign key support — free local database enforcement and PRAGMA behavior
• SQL Server row-level security — filter/block predicates and SESSION_CONTEXT-based patterns
• CREATE SECURITY POLICY — SQL Server/Azure SQL RLS policy syntax and permissions