# Database Queries Scoped to Tenant

- **Pattern:** `ab-002268` (`saas-authorization.admin-privilege.db-queries-tenant-scoped`)
- **Severity:** high
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-002268
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-002268)

## Why it matters

A database query in a multi-tenant route that omits the tenant ID filter returns records from all tenants — every authenticated user can read every organization's data if they can reach the endpoint. CWE-284 (Improper Access Control) and CWE-639 (Authorization Bypass Through User-Controlled Key) apply when the query is not scoped to the active tenant. OWASP A01 (Broken Access Control) covers the failure. NIST 800-53 AC-3 and SC-4 (Information in Shared Resources) require that queries not cross tenant boundaries. List endpoints (`findMany`, `SELECT *`) without a tenant filter are the highest-risk variant — a single call returns the entire dataset.

## Severity rationale

High because any authenticated user in the system can enumerate another tenant's complete dataset through an unscoped list query with a single authenticated request.

## Remediation

Include the tenant filter in the WHERE clause of every database query in a tenant-scoped context. Derive the tenant ID from the session, never from request parameters.

```typescript
// app/api/projects/route.ts (multi-tenant)
const orgId = session.user.activeOrgId; // From session — not from params

const projects = await db.project.findMany({
  where: {
    orgId,           // Tenant scope — required on every query
    archived: false,
  },
});
```

Consider a Prisma middleware or query extension that automatically appends the tenant filter when a tenant context is active — this prevents new routes from accidentally omitting the filter.

## Detection

- **ID:** `db-queries-tenant-scoped`
- **Severity:** `high`
- **What to look for:** Count all relevant instances and enumerate each. In multi-tenant routes, examine database queries. Do they include a `WHERE orgId = ?` (or equivalent tenant filter) alongside other query conditions? Look specifically at list endpoints (`findMany`, `SELECT *`) where missing a tenant filter would return all records across all tenants. Also check queries that look up individual records — do they include the tenant ID to prevent cross-tenant lookup by ID?
- **Pass criteria:** All database queries in tenant-scoped contexts include the tenant ID as a WHERE condition, derived from the session (not from user input directly). At least 1 implementation must be verified.
- **Fail criteria:** Any database query in a tenant-scoped context omits the tenant ID filter, or relies on a join that could be removed in future refactors without failing tests.
- **Skip (N/A) when:** Multi-tenant concept not detected.
- **Detail on fail:** `"Found N database query/queries in multi-tenant routes that do not include a tenant ID filter. Cross-tenant data may be accessible."` (List the specific queries and routes.)
- **Remediation:** Always include the tenant filter in the WHERE clause — not as a post-fetch check, but as part of the query itself to ensure it's atomic with the data access.

  ```typescript
  // app/api/projects/route.ts (multi-tenant)
  const orgId = session.user.activeOrgId; // From session

  const projects = await db.project.findMany({
    where: {
      orgId,        // Tenant scope — always required
      archived: false,
    },
  });
  ```

  Consider using a Prisma middleware or query extension that automatically appends the tenant filter to all queries when a tenant context is active. This prevents accidentally omitting the filter in new routes.

  For a deeper analysis of multi-tenant isolation patterns, the Multi-Tenancy Audit in the SaaS Readiness Pack examines tenant boundaries in detail.

## External references

- cwe CWE-284
- cwe CWE-639
- owasp A01
- nist AC-3
- nist SC-4

Taxons: access-control

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