Skip to main content

Sensitive table without a tenant column in a multi-tenant schema

Most tables carry a tenant column such as tenant_id, org_id or workspace_id, but a table holding sensitive data has neither a tenant column nor a key to a tenant-scoped table. Every query against it must scope by hand, and one that forgets reads or changes other tenants' data.

What Vulkro detects​

Rule schema-tenant-column-missing, default severity Medium, risk category Access control.

A schema (per package, in a monorepo) counts as multi-tenant when at least four tables, and more than half of all tables, carry a tenant column. Vulkro then reports each table that:

  • holds personal, health, financial, credential or secret data,
  • has no tenant column, and
  • has no key (declared, or named after the table such as case_id) to a tenant-scoped table.

Tables that are global by nature are not reported: tenant, user, member, role, plan, session, setting and lookup tables, logs, queues and migrations.

Non-compliant code​

-- Every business table is scoped by org_id ...
CREATE TABLE organizations (id uuid PRIMARY KEY, name text NOT NULL);
CREATE TABLE projects  (id uuid PRIMARY KEY, org_id uuid NOT NULL REFERENCES organizations(id), name text NOT NULL);
CREATE TABLE tasks     (id uuid PRIMARY KEY, org_id uuid NOT NULL REFERENCES organizations(id), title text NOT NULL);
CREATE TABLE invoices  (id uuid PRIMARY KEY, org_id uuid NOT NULL REFERENCES organizations(id), total_cents integer NOT NULL);
-- ... except this one, which holds personal data and has no tenant key
CREATE TABLE patients (id uuid PRIMARY KEY, full_name text NOT NULL, date_of_birth date, ssn text);

Compliant code​

CREATE TABLE patients (
  id uuid PRIMARY KEY,
  org_id uuid NOT NULL REFERENCES organizations(id),
  full_name text NOT NULL,
  date_of_birth date,
  ssn text
);

How to fix​

  • Add the tenant column (non-null, indexed, foreign key to the tenant table) and backfill it, or give the table a non-null key to a tenant-scoped parent.
  • Filter by it in one repository or default scope, or with row-level security, so scoping is not left to each query.
  • If the table is global by design, mark it with a vulkro:disable comment on the declaration.

References​

See also: Database schema discovery, API1 BOLA.

Last updated on