RBAC Drift Detection & Compliance Sync

Tagging database objects by data sensitivity tier

This page shows how to classify every schema and table into a data sensitivity tier — PII, financial, internal, or public — store that classification as a version-controlled tag map, model it safely in pydantic, and feed it into the drift scorer so a risky grant on sensitive data outranks the same grant on a public lookup table.

A drift engine that treats all objects as equally sensitive misranks its own alerts: a SELECT on public.country_codes and a SELECT on customer.pii_ssn produce identical scores, so the signal that matters drowns in the signal that does not. Sensitivity tiering fixes this at the source. It is the classification half of privilege scope mapping — scope tells you where a privilege applies, sensitivity tells you how much that location matters — and it is the input that lets severity weighting mean something.

When to use this — and when not to

Tier your objects when:

  • Your drift scores rank grants by privilege verb alone and cannot distinguish a read on regulated data from a read on a reference table.
  • You must demonstrate to an auditor that access to PII or financial data is governed more strictly than access to public data.
  • You have a stable-enough schema that a maintained tag map pays back the effort — objects are added in reviewable migrations, not ad hoc.

Do not invest here when:

  • You have a handful of tables and can reason about them by name. A four-tier taxonomy is overhead you will not recoup.
  • Your schema churns so fast that the tag map is stale before it is committed — fix schema governance first, or the classification is fiction.
  • Sensitivity is genuinely uniform (a single-purpose analytics mart of only public data). Tagging everything public adds a column and no information.
From tag map to sensitivity-weighted drift score A version-controlled tag map maps schema-qualified objects to one of four sensitivity tiers: PII, financial, internal, or public. A pydantic model validates each entry and attaches a numeric weight. In parallel, a left join between the tag map and information_schema.tables lists any object with no tier so it can be classified. The validated tier weight then feeds the drift scorer, which multiplies it into each grant delta's score. tag map (version-controlled) schema.object → tier four tiers pii · financial · internal · public weight 3.0 · 2.5 · 1.0 · 0.5 pydantic ObjectTag model Literal tier · validated weight rejects unknown tiers coverage check LEFT JOIN information_schema.tables untagged objects tier IS NULL → classify tier weight → drift scorer
Figure 1 — The tag map is validated, checked for coverage against the live catalog, and its tier weight is multiplied into every grant delta's score.

Step-by-step implementation

Step 1 — Define the tier taxonomy and store the tag map

Pin the taxonomy to a small, closed set. Four tiers cover most regulatory needs — PII, financial, internal, public — each with a numeric weight that the scorer multiplies. Store the map as version-controlled data (YAML or a dedicated catalog table) so every classification change is reviewed like code. A default_tier guards against silent under-classification of anything new.

# sensitivity_tags.yaml — reviewed in the same PR as schema migrations
default_tier: internal            # fail toward caution, never public
objects:
  customer.pii_profile:  pii
  customer.pii_ssn:      pii
  billing.invoices:      financial
  billing.card_tokens:   financial
  ops.job_runs:          internal
  public.country_codes:  public

When the map lives in the database instead of a file, a single narrow table works and can be joined directly in catalog queries:

CREATE TABLE governance.object_sensitivity (
    object_schema text NOT NULL,
    object_name   text NOT NULL,
    tier          text NOT NULL
        CHECK (tier IN ('pii','financial','internal','public')),
    classified_by text NOT NULL,
    classified_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (object_schema, object_name)
);

Verification — the CHECK constraint rejects a typo before it reaches the scorer:

INSERT INTO governance.object_sensitivity
VALUES ('customer','pii_ssn','pii','alice');           -- ok
-- INSERT ... VALUES ('customer','x','secret','alice'); -- ERROR: violates check

Step 2 — Model and validate the map in pydantic

A tag is only trustworthy if it is validated. A pydantic v2 model constrains the tier to the closed set with Literal, derives the numeric weight, and refuses to construct on an unknown tier — so a bad classification fails loudly at load time, not silently at score time.

from typing import Literal
from pydantic import BaseModel, computed_field

Tier = Literal["pii", "financial", "internal", "public"]
TIER_WEIGHT: dict[str, float] = {
    "pii": 3.0, "financial": 2.5, "internal": 1.0, "public": 0.5,
}

class ObjectTag(BaseModel):
    object_schema: str
    object_name: str
    tier: Tier

    @computed_field
    @property
    def weight(self) -> float:
        return TIER_WEIGHT[self.tier]

    @property
    def scope(self) -> str:
        return f"{self.object_schema}.{self.object_name}"

Verification — a valid tier resolves a weight; an unknown tier raises before it can mis-score:

tag = ObjectTag(object_schema="customer", object_name="pii_ssn", tier="pii")
assert tag.weight == 3.0
assert tag.scope == "customer.pii_ssn"

from pydantic import ValidationError
try:
    ObjectTag(object_schema="x", object_name="y", tier="secret")  # not in Literal
except ValidationError:
    print("rejected unknown tier")     # -> rejected unknown tier

Step 3 — Find untagged objects against the live catalog

Coverage is the whole game: an object with no tier falls back to the default and is invisible to review. Left-join the tag map against information_schema.tables and list every base table with no classification. This is the query you run in CI to fail the build when a new table ships untagged.

SELECT t.table_schema, t.table_name
FROM information_schema.tables t
LEFT JOIN governance.object_sensitivity s
  ON  s.object_schema = t.table_schema
  AND s.object_name   = t.table_name
WHERE t.table_type = 'BASE TABLE'
  AND t.table_schema NOT IN ('pg_catalog', 'information_schema')
  AND s.tier IS NULL
ORDER BY t.table_schema, t.table_name;

Verification — a freshly created, unclassified table appears immediately:

 table_schema | table_name
--------------+---------------
 billing      | refund_ledger
 customer     | pii_phone

Worked example: PostgreSQL 15, a new PII column ships untagged

Scenario: PostgreSQL 15. A migration adds customer.pii_phone, and the developer forgets to classify it. The CI coverage check flags it, an engineer tiers it pii, and the drift scorer now weights any grant on it at 3.0 instead of the internal default of 1.0.

untagged = ["customer.pii_phone"]                 # from the Step 3 query
assert untagged, "coverage gate: classify new objects before merge"

# after classification, the tag map resolves a weight:
tags = {t.scope: t for t in [
    ObjectTag(object_schema="customer", object_name="pii_phone", tier="pii"),
]}

def sensitivity_weight(scope: str, default: str = "internal") -> float:
    tag = tags.get(scope)
    return tag.weight if tag else TIER_WEIGHT[default]

print(sensitivity_weight("customer.pii_phone"))   # -> 3.0
print(sensitivity_weight("public.country_codes")) # -> 1.0 (default, untagged)

Expected output:

3.0
1.0

The pii_phone grant now scores three times higher than it would under the default, so a stray SELECT on it rises above the alert threshold instead of hiding among low-tier noise. The public.country_codes read, still untagged, resolves to the cautious internal default rather than being wrongly treated as high-risk. Feeding that weight into severity ranking is the subject of weighting privilege severity by data classification.

Gotchas and engine-specific notes

Default toward caution, never toward public. An unclassified object must fall back to internal (or stricter), not public. A public default silently under-scores exactly the new objects most likely to be misconfigured.

Schema-level tags need explicit precedence. If you tag a whole schema financial and also tag one table pii inside it, define which wins. The safe rule is most-specific-and-most-sensitive: the object tag overrides, and ties resolve to the higher weight.

PostgreSQL views and MySQL differ in catalog shape. information_schema.tables.table_type distinguishes BASE TABLE from VIEW on both engines, but a PostgreSQL view over PII inherits its sensitivity from the underlying tables — classify the view explicitly or it reads as untagged. On MySQL 8, the same coverage join works against information_schema.tables filtered by table_schema NOT IN ('mysql','sys','performance_schema').

Column-level PII hides inside internal tables. A table tiered internal can still hold one PII column. If your regulatory scope is column-granular, extend the tag map to information_schema.columns; table-level tiering is a floor, not a ceiling.

Renames orphan tags. ALTER TABLE ... RENAME breaks the object_name key silently. Reconcile the tag map against the catalog on every run, and treat an orphaned tag (a classification with no matching object) as an error, not a warning.

Compliance note

A maintained sensitivity tag map is direct evidence for data-classification controls: it demonstrates, per object, that PII and financial data were identified and governed distinctly, supporting HIPAA §164.312(a)(1) (access control over ePHI) and PCI-DSS Requirement 7.1 (restricting access by classification). Because the map is version-controlled and the coverage query proves no base table escaped classification, an auditor gets both the taxonomy and the assurance that it is complete as of a given commit. Exported alongside the grant baseline, the tier for each object becomes a column in the access-review artifact — see audit report artifact formats — so a reviewer can filter the review to regulated data in one pass.

Frequently asked questions

How many sensitivity tiers should I use? Four — PII, financial, internal, public — covers most regulatory mappings without becoming unmanageable. More tiers add classification effort and inter-rater disagreement faster than they add signal. Start with four and split only when a real ranking decision demands it.

What happens to an object with no tag? It resolves to the default tier, which must be internal or stricter, never public. The coverage query surfaces every untagged base table so classification gaps are found in CI rather than discovered during an audit.

Should the tag map live in a file or a database table? Either works; the requirement is that changes are reviewed. A YAML file versioned with schema migrations gives clean diffs; a governance table lets you join classification directly into catalog queries. Many teams keep the source of truth in the file and sync it into a table for querying.

How does a sensitivity tier change a drift score? The tier resolves to a numeric weight that the scorer multiplies into each grant delta. A read on a PII object weighted 3.0 scores six times higher than the same read on a public object weighted 0.5, so alerts rank by real risk rather than by privilege verb alone.

Up: Privilege Scope Mapping