Least-Privilege Database Accounts, Migration Permissions, Row-Level Security, and Tenant Defense in Depth

Learning outcomes

1. The database account defines the blast radius after an application bug

Suppose a ServiceHub endpoint accidentally calls IgnoreQueryFilters() or a raw SQL statement omits the tenant predicate. If the runtime database identity can read every tenant table, alter schema, disable security policies, create users and drop databases, a single application defect inherits that entire blast radius.

Least privilege means granting an identity only the operations and objects needed for its role. Runtime data access usually needs a narrower set of Data Manipulation Language (DML) permissions than schema deployment, which uses Data Definition Language (DDL).

2. Separate runtime and migration identities

The exact statements/roles vary by SQL Server, PostgreSQL, MySQL/MariaDB, Oracle and SQLite. The principle is portable; the grants are not. For SQLite, operating-system file permissions are a major part of access control because there is no server login/role model.

3. Keep the Chapter 22 tenant controls layered

ServiceHub already has EF Core named tenant/soft-delete/active filters and tenant-aware composite relationship constraints. Each protects a different failure mode:

Defense in depth is not duplication: each layer changes the failure mode and provides an independent test/operational control.

4. SQL Server/Azure SQL RLS: filter predicates and block predicates

SQL Server row-level security (RLS) uses a security policy backed by inline table-valued predicate functions. Filter predicates restrict visible rows for reads and applicable update/delete operations. Block predicates can reject writes that would violate the policy. This is database behavior, not an EF feature, and must be tested against SQL Server/Azure SQL rather than SQLite.

CREATE SCHEMA Security;GOCREATE FUNCTION Security.fn_tenant_access(@tenant_id nvarchar(64))RETURNS TABLEWITH SCHEMABINDINGASRETURN SELECT 1 AS allowedWHERE @tenant_id = CAST(SESSION_CONTEXT(N'TenantId') AS nvarchar(64));GOCREATE SECURITY POLICY Security.WorkOrderTenantPolicyADD FILTER PREDICATE Security.fn_tenant_access(tenant_id)    ON dbo.work_orders,ADD BLOCK PREDICATE Security.fn_tenant_access(tenant_id)    ON dbo.work_orders AFTER INSERT,ADD BLOCK PREDICATE Security.fn_tenant_access(tenant_id)    ON dbo.work_orders AFTER UPDATEWITH (STATE = ON);GO

The application must set and verify the per-connection/session tenant context correctly. Pooled physical connections make session state security-sensitive; clear/reset or deterministically overwrite tenant context whenever a connection is checked out. Never assume EF DbContext pooling and SQL connection pooling reset arbitrary server session context for you.

5. Deliberate failure: use one db_owner-style credential everywhere

web requests ─────┐background jobs ───┼── same schema-owner credential ── databasemigration job ─────┤support script ────┘

This collapses all trust boundaries. A SQL injection bug can become DDL; a compromised background worker can alter security policy; an accidental migration call from every replica can race schema changes. Repair it with separate identities, narrowly scoped grants, one coordinated migration owner, and explicit admin pathways.

6. Raw SQL/admin paths need authorization before database execution

An internal endpoint that executes a reviewed maintenance query is still an administrative capability. Protect it with authentication, authorization policy, audited intent, bounded query shapes, and a database identity appropriate to that operation. The example uses a deliberately small ITenantAuditAuthorizer seam so the security decision is explicit and independently testable rather than coupled to a particular web framework. Never let an ordinary user toggle IgnoreQueryFilters() through a generic flag such as ?includeAllTenants=true.

public interface ITenantAuditAuthorizer{    Task<bool> CanReadDeletedAsync(        ClaimsPrincipal user, string tenantId, CancellationToken ct);}public sealed record AuditRow(int Id, string WorkOrderNumber);public sealed class TenantAuditReader(    IDbContextFactory<ServiceHubContext> contextFactory,    ITenantAuditAuthorizer authorization){    public async Task<IReadOnlyList<AuditRow>> ReadDeletedAsync(        ClaimsPrincipal user,        string tenantId,        CancellationToken ct)    {        if (!await authorization.CanReadDeletedAsync(user, tenantId, ct))            throw new UnauthorizedAccessException();        await using var db = await contextFactory.CreateDbContextAsync(ct);        db.AssignTenant(tenantId);        return await db.WorkOrders            .IgnoreQueryFilters("SoftDeleteFilter")            .Where(w => w.TenantId == tenantId && w.IsDeleted)            .Select(w => new AuditRow(w.Id, w.WorkOrderNumber))            .ToListAsync(ct);    }}

Selective filter disabling is paired with an explicit tenant predicate and authorization check. The database account should still be unable to alter security policy or schema.

7. Verify privileges and RLS independently of EF

Integration tests should connect using the actual runtime test identity, not a schema owner. Attempt an operation the runtime should not have—such as creating a table or selecting a restricted admin object—and assert denial. For RLS, execute the same SQL under two tenant contexts and verify the database changes result visibility even if EF filters are absent.

runtime identity:  SELECT/INSERT/UPDATE/DELETE approved ServiceHub objects: allowed  CREATE TABLE / ALTER SECURITY POLICY: denied  cross-tenant FK mutation: denied by constraint/policy  IgnoreQueryFilters ordinary endpoint: not reachable by authorization  SQL Server RLS test (optional): tenant-a session cannot read tenant-b rowsmigration identity:  reviewed schema migration: allowed  application request path: not configured with this credential

8. Mandatory lab: prove defense in depth on free SQLite, then add optional RLS

1. Use Chapter 22’s SQLite tenant schema and composite tenant-aware relationship constraint.
2. Write an adversarial test that calls an internal query with filters disabled but still requires an explicit authorized tenant.
3. Attempt a cross-tenant Technician/WorkOrder relationship and verify the database rejects it.
4. Document the SQLite file/OS permission boundary and why it differs from a server database role model.
5. For an available SQL Server/Azure SQL lab, create a low-privileged test runtime user and optional RLS policy; verify read filtering and a blocked wrong-tenant write.
6. Use a separate migration credential to apply schema changes, then confirm the runtime identity cannot execute equivalent DDL.
7. Clean up test users/policies/database resources.

9. Production judgment and bridge

EF Core is not a database firewall. Secure production access combines caller authorization, safe query construction, tenant-aware modeling, least-privileged runtime credentials, coordinated migration ownership, provider/database controls such as RLS where justified, and adversarial tests using the same identities. Lesson 5 finishes the chapter by protecting the diagnostic and backup artifacts created when these systems fail.

Authoritative references

• SQL Server row-level security — filter/block predicates, permissions and security-policy behavior
• SQL Server database permissions — database-engine permission model for least-privilege design
• EF Core global query filters — tenant filters, named filters and bypass semantics
• OWASP SQL Injection Prevention Cheat Sheet — least privilege as injection defense in depth
• PostgreSQL row security policies — provider-specific alternative illustrating that RLS semantics are database-owned
• SQLite provider limitations — mandatory local provider boundary and absence of server-role/RLS semantics