Weighting privilege severity by data classification

This guide shows how the sensitivity tier of the object a grant touches becomes a multiplier in the drift score, so the same privilege scores far higher on regulated data than on public data — driven by a version-controlled weight matrix keyed on (privilege, classification, environment).

The same GRANT SELECT is not the same risk everywhere. Read access to a public reference table is nearly noise; read access to a cardholder table is a reportable event. A scorer that ignores what the object holds either drowns you in alerts by treating both as high risk, or misses the one that matters by treating both as low. The dimension that separates them is data classification, and the disciplined way to encode it is a matrix that multiplies privilege weight by a sensitivity tier and an environment factor. This is the classification lens on rule-based drift scoring; the tiers themselves come from tagging database objects by data sensitivity tier.

When to use this — and when not to

Adopt classification-weighted scoring when:

• Your estate mixes regulated and unregulated data in the same databases, so a flat privilege weight over-alerts on the public tables and under-alerts on the sensitive ones.
• You already have, or can build, a classification tag on each object — a sensitivity label the scorer can resolve at score time.
• Compliance scope depends on what data is exposed (PCI cardholder data, HIPAA PHI), so the drift score needs to track sensitivity, not just privilege breadth.

Hold off when:

• Every object in scope shares one classification. A single tier collapses the matrix to a constant and a plain privilege weight is simpler and just as accurate.
• Your classification tags are unreliable or largely missing. Fix coverage first — a scorer fed default tiers everywhere is worse than one you know is uncalibrated, because it looks precise while guessing.
• You need to weight who holds the grant more than what it touches. Principal-based risk is a different axis; classification weighting complements it but does not replace it.

Step-by-step implementation

Step 1 — Resolve the classification tier for the grant’s object

Scoring starts by asking what the object holds. Resolve the sensitivity tier from the object’s tag rather than hard-coding it per grant, so a re-classification updates every future score automatically. In PostgreSQL, a durable place for the label is an object comment or a side classification table; here we read a data_classification table keyed by schema and table.

SELECT c.schema_name || '.' || c.table_name AS object_scope, c.tier
FROM data_classification c
ORDER BY c.schema_name, c.table_name;

Load it into a resolver that fails high — an unclassified object is treated as the most sensitive tier, never the least, so a missing tag can never quietly downgrade a real finding.

DEFAULT_TIER = "restricted"          # unclassified fails high, not low

class ClassificationResolver:
    def __init__(self, tiers: dict[str, str]):
        self._tiers = tiers          # {"cust.cards": "restricted", ...}

    def tier_for(self, object_scope: str) -> str:
        return self._tiers.get(object_scope, DEFAULT_TIER)

Verify the fail-high default and an explicit lookup:

r = ClassificationResolver({"cust.cards": "restricted", "ref.countries": "public"})
assert r.tier_for("ref.countries") == "public"
assert r.tier_for("cust.cards")    == "restricted"
assert r.tier_for("unlabeled.tbl") == "restricted"   # missing tag → fails high

Step 2 — Load the version-controlled weight matrix

Keep the matrix in a checked-in YAML file, not in code, so a change to how severity is weighted is a reviewable diff with an author and a version number. Each row is a (privilege, tier) base weight; a separate block holds environment factors. The scorer builds a lookup keyed on the tuple.

# weights.yaml — reviewed change history is the audit trail for scoring
version: 4
environment: {prod: 1.0, staging: 0.3, dev: 0.1}
weights:
  - {privilege: SELECT,          tier: restricted,   weight: 6.0}
  - {privilege: SELECT,          tier: confidential, weight: 3.0}
  - {privilege: SELECT,          tier: internal,     weight: 1.0}
  - {privilege: SELECT,          tier: public,       weight: 0.4}
  - {privilege: UPDATE,          tier: restricted,   weight: 9.0}
  - {privilege: UPDATE,          tier: confidential, weight: 5.0}
  - {privilege: DELETE,          tier: restricted,   weight: 12.0}
  - {privilege: DELETE,          tier: internal,     weight: 3.0}
  - {privilege: "ALL PRIVILEGES", tier: restricted,  weight: 20.0}
  - {privilege: "ALL PRIVILEGES", tier: confidential, weight: 12.0}

import yaml
from pathlib import Path

def load_matrix(path: str) -> tuple[dict, dict, int]:
    doc = yaml.safe_load(Path(path).read_text())
    cells = {(row["privilege"].upper(), row["tier"]): float(row["weight"])
             for row in doc["weights"]}
    return cells, doc["environment"], doc["version"]

Step 3 — Multiply the tier into the score

The score is the matrix cell for (privilege, tier) multiplied by the environment factor. A cell missing for a (privilege, tier) pair falls back to that privilege’s restricted weight — again failing high — so an incomplete matrix over-scores rather than under-scores.

def classification_score(privilege: str, object_scope: str, environment: str,
                         resolver: ClassificationResolver,
                         cells: dict, env_factors: dict) -> float:
    tier = resolver.tier_for(object_scope)
    priv = privilege.upper()
    base = cells.get((priv, tier)) or cells[(priv, DEFAULT_TIER)]
    return round(base * env_factors[environment], 2)

Verify that the same privilege scores by classification, and that environment scales it:

cells, envf, ver = load_matrix("weights.yaml")
res = ClassificationResolver({"cust.cards": "restricted",
                              "app.sessions": "internal",
                              "ref.countries": "public"})
f = lambda scope, env: classification_score("SELECT", scope, env, res, cells, envf)
print(f("cust.cards", "prod"))     # -> 6.0   restricted
print(f("app.sessions", "prod"))   # -> 1.0   internal
print(f("ref.countries", "prod"))  # -> 0.4   public

Worked example: one SELECT across three classifications on PostgreSQL 15

Scenario: PostgreSQL 15, production. A monitoring role is granted SELECT on three different tables in the same change. With a flat privilege weight all three would score identically; classification weighting spreads them across two orders of magnitude, so only the regulated one clears a 5.0 alert threshold.

rows = [("SELECT","cust.cards","prod"), ("SELECT","cust.cards","staging"),
        ("SELECT","app.sessions","prod"), ("SELECT","ref.countries","prod"),
        ("ALL PRIVILEGES","cust.cards","prod")]
for priv, scope, env in rows:
    print(priv, scope, env, "→", classification_score(priv, scope, env, res, cells, envf))

Expected output:

SELECT cust.cards prod → 6.0
SELECT cust.cards staging → 1.8
SELECT app.sessions prod → 1.0
SELECT ref.countries prod → 0.4
ALL PRIVILEGES cust.cards prod → 20.0

The read on cardholder data in production scores 6.0 and pages; the identical read on session or reference data scores at or below 1.0 and is logged. The environment factor further separates the same restricted read in staging (1.8) from production (6.0). One matrix, five proportionate scores, every input version-controlled.

Gotchas and engine-specific notes

Classification must be inherited, not per-column-guessed. A table’s tier should come from a labeled source of truth, and schema-level defaults should cascade to tables that lack an explicit tag. Deriving sensitivity by pattern-matching column names at score time is brittle and drifts from the real classification the privilege scope mapping contract maintains.

PostgreSQL vs MySQL tag storage. PostgreSQL can hold the label in COMMENT ON TABLE (readable via pg_description/obj_description()), a natural home if you want the classification to live with the object. MySQL 8 exposes table comments through information_schema.tables.table_comment, but comments are lossy for structured tags — most teams keep a dedicated classification table on MySQL and join it in. Either way, resolve to the same tier vocabulary so one matrix scores both engines.

Default tier direction is a policy decision, and it must fail high. The instinct to treat unknowns as low-risk is exactly backwards for security scoring: an unlabeled table is one nobody has vouched for. Both the resolver and the matrix fallback above default to restricted so a coverage gap makes noise, not silence.

Keep the matrix and the classifications versioned together. A score is only reproducible if you can recover both the matrix version and the classification snapshot in force at the time. Record the version field from weights.yaml alongside each emitted score, mirroring the reproducibility discipline that keeps reducing false positives in RBAC drift alerts replayable.

Environment factor is not classification. A restricted table in dev is still restricted; the low dev factor reflects blast radius, not sensitivity. Do not fold environment into the tier — keep them separate axes so you can retune one without disturbing the other.

Compliance note

Classification-weighted scoring is how a drift program demonstrates that its risk ranking tracks the actual sensitivity of exposed data, the core expectation behind PCI-DSS Requirement 7.2 (restrict access by need-to-know) and HIPAA §164.312(a)(1) (access control over ePHI). Because the weight matrix is version-controlled and every score records the matrix version and the object’s tier, an auditor can reconstruct exactly why a given grant on cardholder or PHI data outranked a grant on public data — and confirm the ranking has not been quietly softened. The emitted score record, carrying (privilege, tier, environment, weight, matrix_version), is the evidence artifact for SOC 2 CC6.1 and NIST SP 800-53 RA-2 (security categorization) alike.

Frequently asked questions

Where do the tier weights come from? Anchor them to the regulatory consequence of exposure — a restricted tier holds data whose disclosure is reportable, so its weight must dominate. Set the relative gaps first (restricted ≫ internal ≫ public), version the matrix, then tune against replayed history until real exposures clear the alert threshold and benign reads stay under it.

What if an object has no classification tag? It resolves to the restricted default and scores as if it were the most sensitive tier. That is deliberate: an unlabeled object is an unmanaged risk, and failing high turns a coverage gap into a visible alert instead of a silent under-score.

Why keep environment separate from the tier? Sensitivity is a property of the data; environment is a property of blast radius. A restricted table is restricted in every environment. Keeping them as independent multipliers lets you dial down dev noise without ever lowering how a sensitive object is classified.

Can this share a matrix with the false-positive suppression scorer? Yes — both read the same (privilege, classification, environment) weights. This guide focuses on how the classification axis is sourced and multiplied; the suppression pipeline consumes the resulting score to decide routing.

Related

• Rule-Based Drift Scoring — the scoring engine this classification axis feeds
• Tagging Database Objects by Data Sensitivity Tier — where the tiers this matrix reads are defined
• Reducing False Positives in RBAC Drift Alerts — how the resulting score drives routing

Up: Rule-Based Drift Scoring