Tenant Isolation and Ownership Boundaries

Learning outcomes

In a multi-tenant application, many customer organizations share one software platform. The database must preserve a critical invariant: one tenant must not read, modify, or infer another tenant's data unless explicitly authorized. Tenant isolation must be visible in keys, foreign keys, indexes, queries, and authorization rules.

Three common isolation models

Shared-schema baseline

Customer(  tenant_id,  customer_id,  ...)

Every tenant-owned row needs an unambiguous tenant owner.

Tenant ownership propagates

You can derive tenant ownership through joins, but storing tenant_id on hot child tables often simplifies isolation, partitioning, and indexing.

Composite keys for tenant safety

PRIMARY KEY (tenant_id, customer_id)FOREIGN KEY (tenant_id, customer_id)REFERENCES Customer(tenant_id, customer_id)

This prevents a row from pairing tenant A with customer_id belonging to tenant B.

Global surrogate IDs

If customer_id is globally unique, tenant_id may not be needed in the PK, but tenant ownership still needs enforcement. Options include composite unique constraints and tenant-aware FKs.

Tenant-scoped uniqueness

UNIQUE (tenant_id, work_order_number)UNIQUE (tenant_id, sku)

allows different tenants to use the same business code while preserving uniqueness inside each tenant.

Global uniqueness when required

Some values, such as externally issued tokens or shared infrastructure names, may need global uniqueness. Do not mechanically prefix every constraint with tenant_id; choose the domain scope.

Tenant-aware indexes

(tenant_id, customer_id)(tenant_id, status_code, opened_at)

Tenant-first indexes often align with application predicates and keep access inside one tenant range.

Always scope queries

Dangerous:

SELECT * FROM work_orderWHERE work_order_id = :id;

Safer:

SELECT * FROM work_orderWHERE tenant_id = :tenant  AND work_order_id = :id;

Row-level security

A DB policy can add another layer:

tenant_id = current_setting('app.tenant_id')

Exact syntax varies by DBMS. Connection-pool context must be set/reset safely.

Connection pooling hazard

If tenant context persists on a reused connection, the next request can inherit the wrong tenant. Context-setting and cleanup must be deterministic.

Background jobs

Jobs often process many tenants. They need explicit tenant iteration and should not bypass isolation accidentally just because they run with elevated credentials.

Support/admin access

Cross-tenant support access should be:

• separate from normal tenant sessions;
• strongly authorized;
• audited;
• time-limited where practical.

Tenant deletion/export

Explicit tenant_id makes:

export tenant 17delete tenant 17reconcile tenant 17

far easier than discovering ownership indirectly across dozens of tables.

Partitioning by tenant

Large systems may partition or shard using tenant_id. This can improve locality and isolation but creates hot-tenant and rebalancing considerations.

Noisy-neighbor problem

Security isolation does not guarantee performance isolation. One tenant with extreme workload can consume shared CPU/I/O. Rate limits, resource governance, or separate placement may be needed.

WorkshopHub tenant model

Isolation tests

Automate tests that attempt:

• read A's row using B's session;
• create B child referencing A parent;
• update by ID without tenant predicate;
• reuse pooled connection after tenant switch;
• run export/delete for one tenant only.

Practice: cross-tenant FK

Summary and next lesson

Tenant isolation is a schema invariant, not just a WHERE-clause convention. Tenant-aware keys, foreign keys, indexes, scoped queries, row-level policies, and explicit admin pathways provide defense in depth. The next lesson turns to auditability and provenance.

References

• DBMS documentation on row-level security and roles.
• OWASP multi-tenant application security guidance.
• Cloud/database architecture guidance for tenant isolation models.