Database Queries Scoped to Tenant

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.

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.

// 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.

• ID: saas-authorization.admin-privilege.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.

  // 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.