Matter confidentiality

Prevent cross-matter access at query time

If an attorney queries a contract table, the result must contain only rows for matters they are authorized for — with no dependence on the application adding a WHERE clause. The authorization mapping is data, so access changes are auditable business events, not code deployments.

The authorization mapping

Row access policies evaluate per query. The mapping table links a role (or an employee identity) to the matters they may see.

CREATE TABLE security.matter_access_map (
  role_name  STRING,
  matter_id  STRING,
  access_level STRING,      -- READ | WRITE
  valid_from DATE,
  valid_to   DATE
);

Row access policy

Applied to every table that carries matter_id. Administrators bypass it by design; everyone else is scoped.

CREATE OR REPLACE ROW ACCESS POLICY security.matter_access_policy
  AS (matter_id STRING) RETURNS BOOLEAN ->
    CURRENT_ROLE() IN ('ACCOUNTADMIN','SYSADMIN','COMPLIANCE_ADMIN','AUDITOR')
    OR EXISTS (
        SELECT 1
        FROM security.matter_access_map m
        WHERE m.role_name = CURRENT_ROLE()
          AND m.matter_id = matter_id
          AND CURRENT_DATE() BETWEEN m.valid_from AND COALESCE(m.valid_to,'9999-12-31')
    );

ALTER TABLE core.fact_clause
  ADD ROW ACCESS POLICY security.matter_access_policy ON (matter_id);

Dynamic data masking

Row policies hide rows; masking policies hide columns. Applied to client PII and commercially sensitive metadata.

CREATE OR REPLACE MASKING POLICY security.pii_mask
  AS (val STRING) RETURNS STRING ->
    CASE
      WHEN CURRENT_ROLE() IN ('COMPLIANCE_ADMIN','ACCOUNTADMIN') THEN val
      WHEN CURRENT_ROLE() IN ('ATTORNEY','PARALEGAL') THEN '***REDACTED***'
      ELSE NULL
    END;

ALTER TABLE core.dim_client
  MODIFY COLUMN contact_email SET MASKING POLICY security.pii_mask;
ALTER TABLE core.dim_client
  MODIFY COLUMN contact_phone SET MASKING POLICY security.pii_mask;

Classification tags

Tag columns once (PII, CONFIDENTIAL, ATTORNEY_WORK_PRODUCT) and attach tag-based masking for consistent, self-documenting protection.

Implementation notes

• Row access policies and certain constraints are mutually exclusive — model keys without relying on blocked constraint types on protected tables.
• Policy-protected data must be read through secure views or the base tables directly; never copy unfiltered rows into a permissive table.
• Attach policies to every table carrying matter_id, including bridges, to avoid a leak through a join.
• Include an AUDITOR bypass so verification can enumerate the full picture.
• Document role reachability: which roles inherit the bypass, and why.

Full DDL: sql/03_row_access_and_masking.sql.