# Every database query is scoped to the active tenant

- **Pattern:** `ab-002332` (`saas-multi-tenancy.data-isolation.every-query-tenant-scoped`)
- **Severity:** critical
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-002332
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-002332)

## Why it matters

A single unscoped query against a tenant-owned table is all it takes for one customer to read, overwrite, or delete another's data — the textbook definition of CWE-639 (Authorization Bypass Through User-Controlled Key) and OWASP A01 (Broken Access Control). In SaaS products this failure is catastrophic: it breaches contractual data-isolation guarantees, triggers GDPR and SOC 2 breach-notification obligations, and can expose trade-secret or PII data from every tenant in the database. The attack surface is wide because any route that touches a tenant-scoped table without a filter is exploitable by any authenticated user — no privilege escalation needed.

## Severity rationale

Critical because a single missing tenant filter gives any authenticated user unrestricted read and write access to every other tenant's data in the same table.

## Remediation

Enforce isolation at the database layer — application-level filters are too easy to miss in new routes. For Prisma, add a global middleware in `src/lib/prisma.ts` that injects the tenant scope on every query:

```ts
// src/middleware/tenantScope.ts
export function withTenantScope(tenantId: string) {
  return { $allOperations: ({ args, query }) => {
    args.where = { ...args.where, organizationId: tenantId };
    return query(args);
  }};
}
```

For Supabase, enable RLS on every tenant-owned table and write policies that read the org claim from the JWT — the anon/user client cannot bypass RLS. After wiring either approach, add a cross-tenant probe test: impersonate Tenant A, attempt to fetch a record whose `organizationId` is Tenant B — the query must return nothing.

## Detection

- **ID:** `every-query-tenant-scoped`
- **Severity:** `critical`
- **What to look for:** Examine all database query files — ORM queries, raw SQL, service layers, repositories. For every table that has a tenant identifier column (`tenant_id`, `organization_id`, `team_id`, `workspace_id`, or equivalent), verify that every SELECT, UPDATE, and DELETE query against that table filters by the tenant identifier. Check for ORM-level global scopes, Prisma middleware tenant filters, Supabase RLS policies, or consistent manual `where` clause patterns.
- **Pass criteria:** Enumerate all database query locations touching tenant-owned tables and confirm every query includes a tenant scope via one of: (a) database-enforced RLS that cannot be bypassed by application code. Quote the actual RLS policy or middleware configuration used, (b) ORM global filter applied at the connection/context level, (c) repository layer that injects tenant scope on all query methods, or (d) explicit `where tenantId = [session tenant]` on every individual query. Spot-check at least 5 different query locations across different entity types. On pass, report the count of scoped queries vs. total queries found.
- **Fail criteria:** Any query against a tenant-scoped table that omits the tenant filter — even a single instance — does not count as pass — even a single instance — constitutes a fail. Also fail if tenant filtering is inconsistent (some queries filtered, some not) without a global enforcement mechanism that covers the unfiltered ones.
- **Skip (N/A) when:** No database is detected (no ORM, no database dependencies, no schema files). Signal: absence of prisma, drizzle, typeorm, sequelize, mongoose, pg, mysql2, @supabase/supabase-js in `package.json` and no database schema files.
- **Detail on fail:** Describe which tables and query locations were found without tenant scoping. Example: `"Queries against 'documents' table in src/lib/documents.ts and src/api/search/route.ts do not filter by organizationId. No global RLS or ORM scope found to compensate."`
- **Cross-reference:** Compare with `saas-multi-tenancy.data-isolation.no-cross-tenant-api-responses` — query scoping (this check) prevents leaks at the DB layer; API response checks catch leaks at the transport layer.
- **Remediation:** Every query missing tenant scope is a potential cross-tenant data exposure. The safest approach is to enforce isolation at the database layer rather than in application code:

  **For Supabase:** Enable Row Level Security on every tenant-scoped table and write policies that filter by a tenant claim from the JWT. Application code cannot bypass RLS when using the anon/user client.

  **For Prisma:** Add a Prisma middleware or extension that injects `where: { tenantId: ctx.tenantId }` on every find/update/delete operation for scoped models. Alternatively, use the repository pattern where every repository method receives the tenantId as a required parameter.

  **For Drizzle/raw SQL:** Create a typed query builder factory that accepts a `tenantId` and wraps all queries with the appropriate `where` clause. Never call the base query builder directly in route handlers.

  After implementing, write a test that impersonates Tenant A's session and attempts to read Tenant B's records by ID — it should return nothing:

  ```ts
  // src/middleware/tenantScope.ts
  export function withTenantScope(tenantId: string) {
    return { $allOperations: ({ args, query }) => {
      args.where = { ...args.where, organizationId: tenantId };
      return query(args);
    }};
  }
  ```

---

## External references

- cwe CWE-285
- cwe CWE-639
- owasp A01
- nist AC-3

Taxons: access-control, data-integrity

HTML version: https://auditbuffet.com/patterns/ab-002332