Shared Database Tenant Isolation: Where Tenant Filters Break

Shared database multi tenancy is common and valid.

The problem is not the shared table pattern itself. The problem is relying on every query path to remember tenant scope.

If one tenant_id predicate falls out of a join, include, export, or raw SQL path, the app can return valid data from the wrong customer with a perfectly normal response.

That is why shared database isolation needs request-level testing, not just schema review.

If you need the commercial version of this review, use the Shared Database Isolation Audit inside the multi tenant security audit hub.

Why shared database isolation is fragile

Tenant scope is usually enforced at query time, not at the table design level.

• ORM filters can be bypassed by raw SQL or custom query paths
• joins can widen the result set if tenant ownership is not preserved
• child tables may not carry tenant_id directly, so the parent filter is not enough
• reports and exports often use separate queries from the live UI
• background jobs may run without the original tenant context
• pagination and count queries can drift apart from the row query

What usually breaks

The common failures are small, but they matter:

• the tenant filter exists on the parent but not the child table
• the join condition uses object ID but not tenant ID
• Include() or eager loading pulls nested records outside tenant scope
• a repository returns an unscoped IQueryable and later code widens the result
• raw SQL bypasses ORM filters entirely
• admin and reporting paths use broader query logic than the customer UI
• the pagination count query does not match the row query

Example:

SELECT p.id, p.name, o.total
FROM projects p
JOIN orders o ON o.project_id = p.id
WHERE p.tenant_id = @tenantId;

This looks safe at first glance, but it still depends on the child row being owned correctly. If orders is not tenant scoped, the join can still surface the wrong customer’s data.

Safer direction:

SELECT p.id, p.name, o.total
FROM projects p
JOIN orders o
  ON o.project_id = p.id
 AND o.tenant_id = p.tenant_id
WHERE p.tenant_id = @tenantId;

The exact shape can vary by ORM and schema, but the idea is the same: keep tenant boundaries attached through the join, then verify child ownership before projection.

SaaS failure examples

Concrete failures usually look like this:

• paginated dashboard count is scoped, but the returned rows are not, so the page size and visible data disagree
• Include() pulls child records from another tenant because the parent filter did not carry into the nested load
• report export bypasses the ORM filter and returns rows the live list would have blocked
• background job rebuilds the query with only user ID, so it loses the tenant boundary after the request ends
• admin search endpoint is accidentally available to a customer role and returns shared-table results
• raw SQL helper returns rows across the shared table because it never applied the tenant predicate
• cached query result is reused across tenants because the cache key was too broad

Where tenant filters drift

Tenant scope usually drifts in these places:

• joins
• includes or eager loading
• projections and DTO selects
• count queries
• search queries
• reporting and export queries
• background jobs
• cache keys
• raw SQL
• admin and support tools

What safe shared database isolation should enforce

Safe shared database isolation is mostly about making tenant scope non-optional:

• tenant scope is derived server side
• tenant ID is never trusted from client input alone
• global ORM filters are the default, not an optional helper
• raw SQL requires explicit scoped helpers
• joins preserve tenant ownership
• child records are checked before projection
• report and export paths use the same tenant guardrails
• background jobs carry tenant ID
• audit logs record actor, tenant, route, and affected object

Request-level tests for shared database isolation

Test the boundary at the request level, not just in code review:

• baseline request under Tenant A
• replay the same route under Tenant B
• mutate object ID while keeping valid auth
• test nested child records
• test pagination and count endpoints
• test search and filter endpoints
• test report and export endpoints using the same objects
• test raw SQL backed routes if present
• compare status, rows, counts, nested objects, and side effects

Fix direction after a failed isolation test

• make tenant scoping default in the ORM
• prevent unscoped IQueryable from leaking across layers
• require scoped helpers for raw SQL
• keep joins tenant-safe through projection
• verify child ownership before returning nested records
• make count and row queries use the same tenant scope
• review report, export, cache, and background job queries separately
• retest the same failed request after remediation

When this deserves an audit

Shared database isolation should be reviewed before launch, after major query or refactor work, before enterprise buyer review, or when the product adds reports, exports, support tooling, or background jobs.

The risk is highest when the app relies on ORM filters, dynamic query builders, raw SQL, shared tables, cached results, or background workers.

A good audit should prove whether tenant scope survives the live request path, not just whether the schema looks correct.

What evidence belongs in the audit report

A useful report should show:

• baseline tenant, actor, route, object, and filters
• mutated tenant, object ID, role, or filter values
• expected tenant boundary
• actual returned rows, counts, nested objects, or side effects
• affected object, table, route, join, include, report, export, or background job
• query path category if known, such as ORM filter, raw SQL, repository helper, report, export, cache, or background job
• audit log behavior if the route changes or exposes sensitive data
• severity and business impact
• remediation note
• retest result after the fix

Related paths

• Shared Database Isolation Audit
• Multi Tenant Security Audit
• Tenant Isolation Audit
• Export Data Leak Audit
• Sample Report
• Tenant Isolation Testing for SaaS
• Cross Tenant Reporting Leaks
• Export Data Leaks in SaaS