Tenant ID Filtering in SaaS: Why One Missing Predicate Breaks Isolation

Tenant ID filtering looks simple in theory.

In practice it has to survive every repository helper, every query shape, and every include chain. The danger is not just missing tenant_id on one obvious query. The danger is predicate drift across helpers, joins, projections, counts, reports, and exports.

That means a valid authenticated request can still return wrong tenant data if the predicate falls out of the code path.

This is why tenant ID filtering is a runtime boundary problem, not just a coding convention.

If you want this tested directly, use the Shared Database Isolation Audit and the broader Multi Tenant Security Audit.

What the tenant predicate has to survive

The tenant predicate has to stay attached through:

• repository methods
• ORM global filters
• query builders
• search filters
• pagination
• count queries
• joins
• projections and select DTOs
• includes and eager loading
• exports and reports
• background jobs

Why tenant ID filtering slips

The most common drift points are:

• a repository returns IQueryable too early, so the caller owns the tenant predicate and forgets it
• the caller adds search or sort filters but omits tenant scope
• raw SQL bypasses global filters and reads from the shared table directly
• projection removes tenant context before authorization is complete
• the count query and the data query use different scopes
• export and report queries do not reuse the scoped path
• a background worker receives user ID but not tenant ID

Unsafe:

var projects = db.Projects
    .Where(p => p.Name.Contains(search))
    .ToList();

Safer direction:

var tenantId = tenantContext.TenantId;
var projects = db.Projects
    .Where(p => p.TenantId == tenantId)
    .Where(p => p.Name.Contains(search))
    .ToList();

The key point is simple: tenant ID should come from server-side tenant context, and the base query should apply tenant scope before user-controlled filters, pagination, or projection.

SaaS failure examples

Concrete failures usually look like this:

• list endpoint returns another tenant’s records after pagination because the count and row queries no longer match
• search helper widens scope when filters change and the tenant predicate drops out of the composed query
• count endpoint leaks global row totals even though the row query stayed scoped
• include pulls nested records outside tenant scope because the child query was not filtered the same way
• report query does not inherit the ORM filter and pulls a broader dataset than the UI
• export path rebuilds the query without the tenant predicate and returns the wrong file contents
• background job generates a report using only user ID, so the tenant boundary is lost after the request ends
• admin helper is accidentally reused in a customer route and exposes data intended for internal use

Request-level tests for tenant filtering drift

Test the boundary at the request level:

1. capture the baseline list request under Tenant A
2. replay the same route under Tenant B
3. mutate object ID while keeping valid auth
4. test the search query
5. test pagination
6. test the count endpoint
7. test the nested include response
8. test export and report paths using the same filters
9. test missing tenant context if applicable
10. compare records, counts, nested objects, and side effects

Where predicate drift usually appears

Predicate drift usually shows up in:

• search
• filters
• sorting
• pagination
• counts
• joins
• includes
• projections
• raw SQL
• cached queries
• exports
• reports
• background jobs
• support and admin tools

What safe tenant ID filtering should enforce

Safe tenant ID filtering is mostly about making the predicate non-optional:

• tenant context is derived server side
• tenant ID is applied before user-controlled filters
• tenant filter is part of the base query
• missing tenant context fails closed
• raw SQL uses scoped helpers
• count and row queries use the same tenant scope
• includes and child records preserve ownership
• reports and exports do not rebuild unscoped queries
• background jobs carry tenant ID explicitly
• audit logs record actor, tenant, route, and affected object

Fix direction after a failed tenant filtering test

• make tenant filtering part of the base query
• derive tenant context server side
• prevent unscoped IQueryable from leaking across layers
• make raw SQL use scoped helpers
• make count and row queries share the same tenant scope
• preserve tenant ownership through joins, includes, and projections
• ensure reports, exports, and background jobs carry tenant ID explicitly
• fail closed when tenant context is missing
• retest the same failed request after remediation

When this deserves an audit

Tenant ID filtering should be reviewed before launch, after query refactors, after adding reporting or export features, or when repository helpers and ORM filters are reused across multiple routes.

The risk is highest when the app relies on dynamic query builders, global ORM filters, raw SQL, shared repositories, count queries, nested includes, exports, reports, or background jobs.

A good audit should prove whether the tenant predicate survives the live request path, not just whether the codebase has a tenant ID column.

What evidence belongs in the audit report

A useful report should show:

• baseline tenant, actor, route, object, and filters
• mutated tenant, object ID, role, search, filter, or pagination values
• tenant context used by the request
• expected tenant boundary
• actual records, counts, nested objects, or side effects
• affected query path, such as repository, ORM filter, query helper, raw SQL, include, count query, report, export, or background job
• whether the issue came from missing predicate, scope drift, unscoped helper, mismatched count query, or background job context loss
• severity and business impact
• remediation note
• retest result after the fix

Related paths

• Shared Database Isolation Audit
• Multi Tenant Security Audit
• Tenant Isolation Audit
• Sample Report
• Shared Database Tenant Isolation
• SaaS Database Schema Patterns
• Tenant Isolation Testing for SaaS