Privilege Scope Mapping

Privilege scope mapping is the sub-problem of turning the raw, dialect-specific grant matrix of a running database into a single canonical dataset that a drift engine can diff deterministically. Every downstream comparison — desired-versus-observed, staging-versus-production, this-quarter-versus-last — depends on both sides being expressed in the same privilege ontology. Skip this normalization and the failure mode is immediate and expensive: the engine reports drift that does not exist. PostgreSQL emits SELECT where MySQL emits Select, one engine records a schema-level default ACL while another expands it to per-table rows, and the diff fills with false positives. Alert fatigue sets in, an on-call engineer mutes the channel, and a genuine privilege escalation slips through unnoticed. Scope mapping exists to make the comparison surface honest: one grant, one canonical tuple, on every engine.

This work sits at the base of the Core RBAC Architecture & Privilege Fundamentals model. It consumes the tiered structure defined in Role Hierarchy Design, resolves it into effective permissions, and hands a clean delta to the scoring and remediation stages. Get the mapping right and the rest of the pipeline inherits its determinism; get it wrong and no amount of downstream tuning can recover a trustworthy signal.

Prerequisites: Engine Versions and Catalog Read Permissions

Scope mapping reads system catalogs, so the account that runs it needs deliberate, minimal read access — never the superuser most teams reach for first. The mapper is itself a privileged actor and should model the least-privilege posture it enforces on everything else.

• PostgreSQL 12+ (tested through 16). Reading information_schema.role_table_grants returns only the rows the connecting role can see, so grant extraction runs as a dedicated drift_auditor role holding pg_read_all_settings and membership in pg_monitor. To read every grant regardless of grantor, the auditor needs SELECT on the relevant catalogs, which pg_monitor supplies for the statistics views; membership edges in pg_auth_members and pg_roles are world-readable.
• MySQL 8.0.16+ for native roles. The information_schema.role_edges view (backed by mysql.role_edges) and mysql.default_roles require SELECT on the mysql system schema, granted narrowly via GRANT SELECT ON mysql.* TO 'drift_auditor'@'%'.
• Python 3.11+ with psycopg 3.1+ for PostgreSQL and mysql-connector-python 8.1+ for MySQL. The examples use only the standard library plus these drivers; pydantic 2.x is optional but recommended for manifest validation.
• Cross-engine fleets should read the dialect notes in the cross-environment privilege extraction and parsing domain before wiring additional targets, and reuse the connectors documented in Cross-DB Parser Adapters rather than hand-rolling per-engine SQL.

A safe default is to grant the auditor read on catalogs only and revoke every write privilege, then confirm with has_table_privilege('drift_auditor', 'some_table', 'INSERT') returning false. If the mapper can mutate the objects it inspects, its own runs become a drift source.

The Canonical Scope Tuple

The mapping target is a small, engine-neutral record. Every privilege on every engine collapses to one immutable tuple so that set operations — union, difference, membership — are well defined across the whole fleet.

from dataclasses import dataclass, replace, asdict

@dataclass(frozen=True, slots=True)
class ScopeGrant:
    """Engine-neutral canonical privilege record. Frozen so it is hashable
    and usable directly in set-difference operations against a baseline."""
    grantee: str          # role or user receiving the privilege
    object_kind: str      # 'table' | 'schema' | 'routine' | 'sequence'
    object_id: str        # fully qualified: 'schema.name'
    privilege: str        # canonical verb: 'SELECT' | 'INSERT' | ...
    grantable: bool       # can the grantee re-grant (WITH GRANT / ADMIN OPTION)
    via_role: str | None  # None if direct; else the role it was inherited through

Two normalization rules make this tuple stable. First, privilege verbs are upper-cased and mapped through a per-engine table so MySQL’s Select and Oracle’s SELECT both become the canonical SELECT. Second, object_id is always fully qualified and case-folded according to the engine’s identifier rules — PostgreSQL folds unquoted identifiers to lower case, MySQL’s behavior depends on lower_case_table_names, and quoted identifiers are preserved verbatim. Encoding these rules once, here, is what stops dialect noise from reaching the diff.

Extraction, Membership Resolution, and Deterministic Diff

The mapping runs as four ordered steps. Each produces a verifiable intermediate so a failure can be localized to one stage rather than the whole pipeline.

Step 1 — Extract direct grants inside a read-only snapshot

Direct grants come straight from the catalog. Run the query in a REPEATABLE READ READ ONLY transaction so a concurrent GRANT cannot tear the snapshot mid-extraction.

import psycopg
from psycopg.rows import dict_row

DIRECT_GRANTS_SQL = """
    SELECT grantee, table_schema, table_name, privilege_type, is_grantable
    FROM information_schema.role_table_grants
    WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
    ORDER BY grantee, table_schema, table_name, privilege_type
"""

def extract_direct_grants(dsn: str) -> list[dict]:
    """Deterministically sorted direct table grants from PostgreSQL.
    Read-only repeatable-read transaction prevents snapshot drift."""
    with psycopg.connect(dsn, autocommit=False) as conn:
        conn.execute("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY")
        with conn.cursor(row_factory=dict_row) as cur:
            cur.execute(DIRECT_GRANTS_SQL)
            return cur.fetchall()

Verification: the row count should match SELECT count(*) FROM information_schema.role_table_grants WHERE table_schema NOT IN ('pg_catalog','information_schema') executed independently. A mismatch means the auditor role cannot see grants issued by other grantors — a permissions gap, not drift.

Step 2 — Resolve the membership closure over the role graph

Direct grants are only half the effective permission set. A grantee also holds everything reachable through role membership, provided inheritance is active. Pull the membership edges, then compute the transitive closure. The engine differences in when inheritance applies are covered in Understanding RBAC inheritance in PostgreSQL vs MySQL; here the mapper only needs the static edge set plus each role’s inherit flag.

-- PostgreSQL: membership edges plus the inherit attribute of each member
SELECT m.rolname       AS member,
       g.rolname       AS granted_role,
       am.admin_option AS admin_option,
       m.rolinherit    AS member_inherits
FROM pg_auth_members am
JOIN pg_roles m ON m.oid = am.member
JOIN pg_roles g ON g.oid = am.roleid
ORDER BY member, granted_role;

from collections import defaultdict

def membership_closure(edges: list[dict]) -> dict[str, set[tuple[str, str]]]:
    """Map each role to the set of (ancestor_role, via_role) pairs whose
    privileges it effectively holds. Only edges where the member inherits
    contribute; NOINHERIT members must SET ROLE explicitly and so do not
    accumulate privileges automatically."""
    parents: dict[str, list[str]] = defaultdict(list)
    for e in edges:
        if e["member_inherits"]:
            parents[e["member"]].append(e["granted_role"])

    closure: dict[str, set[tuple[str, str]]] = {}
    for role in parents:
        seen: set[tuple[str, str]] = set()
        stack = [(p, p) for p in parents[role]]
        while stack:
            ancestor, via = stack.pop()
            if (ancestor, via) in seen:
                continue          # guards against multi-step membership cycles
            seen.add((ancestor, via))
            for grandparent in parents.get(ancestor, ()):
                stack.append((grandparent, via))
        closure[role] = seen
    return closure

The seen guard is not optional. Both engines reject a direct membership cycle, but a cycle assembled across several independent GRANT ROLE statements can still exist in the catalog; without the guard the traversal never terminates. The acyclicity invariant here is the same one enforced upstream in Role Hierarchy Design.

Step 3 — Normalize into canonical scope tuples

With direct grants and the closure in hand, project both into ScopeGrant records. This is where every dialect quirk is neutralized exactly once.

PG_PRIVILEGE_MAP = {
    "SELECT": "SELECT", "INSERT": "INSERT", "UPDATE": "UPDATE",
    "DELETE": "DELETE", "TRUNCATE": "TRUNCATE", "REFERENCES": "REFERENCES",
    "TRIGGER": "TRIGGER",
}

def normalize(rows: list[dict],
              closure: dict[str, set[tuple[str, str]]]) -> frozenset[ScopeGrant]:
    """Flatten direct grants and their inherited expansions into one
    canonical, hashable set of ScopeGrant tuples."""
    grants: set[ScopeGrant] = set()
    for r in rows:
        base = ScopeGrant(
            grantee=r["grantee"].lower(),
            object_kind="table",
            object_id=f'{r["table_schema"]}.{r["table_name"]}'.lower(),
            privilege=PG_PRIVILEGE_MAP[r["privilege_type"]],
            grantable=(r["is_grantable"] == "YES"),
            via_role=None,
        )
        grants.add(base)
        # every role that inherits `grantee` also holds this privilege
        for member, closure_pairs in closure.items():
            if any(ancestor == r["grantee"].lower() for ancestor, _ in closure_pairs):
                grants.add(replace(base, grantee=member, via_role=r["grantee"].lower()))
    return frozenset(grants)

Verification: every ScopeGrant.privilege must be a member of the canonical verb set. Assert it — an unmapped verb (say a MySQL-only Create routine) surfacing here is a normalization gap, and catching it now prevents it from masquerading as drift later. The same canonical set is what Rule-Based Drift Scoring assigns weights to, so a leaked dialect verb would also break scoring.

Step 4 — Diff against the desired baseline and emit a plan

The desired state is a version-controlled manifest projected into the identical frozenset[ScopeGrant] shape. The delta is a pure symmetric difference, and the remediation plan is derived directly from it.

def diff_scopes(observed: frozenset[ScopeGrant],
                desired: frozenset[ScopeGrant]) -> dict[str, list[ScopeGrant]]:
    """Symmetric difference partitioned into the two remediation classes.
    excessive = present live but not in baseline  -> candidate REVOKE
    missing   = in baseline but absent live       -> candidate GRANT"""
    return {
        "excessive": sorted(observed - desired, key=lambda g: (g.grantee, g.object_id, g.privilege)),
        "missing":   sorted(desired - observed, key=lambda g: (g.grantee, g.object_id, g.privilege)),
    }

def render_plan(delta: dict[str, list[ScopeGrant]]) -> list[str]:
    """Deterministic DDL plan. Inherited rows (via_role is not None) are
    skipped: they are corrected at their source grant, never re-granted
    directly, which is the contract enforced by grant/revoke chain logic."""
    stmts: list[str] = []
    for g in delta["missing"]:
        if g.via_role is None:
            stmts.append(f'GRANT {g.privilege} ON {g.object_id} TO "{g.grantee}";')
    for g in delta["excessive"]:
        if g.via_role is None:
            stmts.append(f'REVOKE {g.privilege} ON {g.object_id} FROM "{g.grantee}";')
    return stmts

Skipping inherited rows is deliberate. Emitting a direct GRANT to fix an inherited shortfall would fork the privilege from its source and defeat the hierarchy; the correct fix flows through the source grant and the cascade rules in Grant and Revoke Chain Logic. Rows the manifest deliberately tolerates — a break-glass account, a time-boxed CI grant — are filtered before this stage by Exception Routing and Whitelisting so they never enter the plan.

Idempotency and Dry-Run Safety Contract

The mapper must be safe to run every fifteen minutes forever. Two properties guarantee that.

Extraction is side-effect free. Steps 1 and 2 execute in a READ ONLY transaction, so running the mapper a thousand times mutates nothing and produces the same normalized set given the same catalog state. Sorting at every stage — the SQL ORDER BY, the sorted() calls in the diff — means the byte-level output is reproducible, which is what lets the manifest hash below act as a change signal.

Reconciliation converges. The generated plan is a function of the delta, and applying it drives the delta toward empty. A second run against the now-corrected catalog yields an empty plan: GRANT SELECT on an object the grantee already holds is a no-op in both PostgreSQL and MySQL, and REVOKE of an absent privilege is likewise inert. Repeated application is therefore stable — the defining idempotency invariant of the whole Core RBAC Architecture & Privilege Fundamentals model.

Dry-run mode is the default, not a flag you remember to set. render_plan returns SQL as strings and executes nothing; promotion to apply mode wraps those statements in an explicit transaction that is rolled back unless an operator confirms.

def apply_plan(dsn: str, stmts: list[str], commit: bool = False) -> None:
    """Execute the plan transactionally. commit=False (default) rolls back,
    exercising the exact statements without mutating the target — a true
    dry run, not an approximation."""
    with psycopg.connect(dsn, autocommit=False) as conn:
        with conn.cursor() as cur:
            for s in stmts:
                cur.execute(s)
        conn.commit() if commit else conn.rollback()

Compliance Alignment and Evidence Artifacts

Scope mapping is where several access-control controls become auditable rather than aspirational, because it produces a signed, timestamped record of exactly who could touch what.

• SOC 2 CC6.1 / CC6.3 — logical access is restricted to authorized users. The observed frozenset[ScopeGrant], hashed and signed, is direct evidence that access was enumerated and compared to an approved baseline on a fixed cadence.
• PCI-DSS Requirement 7.1 / 7.2 — access limited to least privilege by role. The excessive partition of the delta is a machine-generated exception list; an empty partition is the artifact an assessor wants.
• HIPAA §164.312(a)(1) — technical access control over ePHI. The per-object canonical grant set demonstrates enforcement at object granularity.
• NIST SP 800-53 AC-6 — least privilege. The via_role field provides the justification trail for every effective permission.

The evidence artifact is a canonical JSON document plus a cryptographic manifest hash. Producing the two together makes the snapshot tamper-evident and lets an auditor confirm that the file they were handed is the one the pipeline generated.

import hashlib, json
from datetime import datetime, timezone

def evidence_artifact(observed: frozenset[ScopeGrant],
                      delta: dict[str, list[ScopeGrant]]) -> dict:
    rows = sorted((asdict(g) for g in observed),
                  key=lambda d: (d["grantee"], d["object_id"], d["privilege"]))
    payload = {
        "captured_at": datetime.now(timezone.utc).isoformat(),
        "grant_count": len(rows),
        "excessive": [asdict(g) for g in delta["excessive"]],
        "missing":   [asdict(g) for g in delta["missing"]],
        "grants": rows,
    }
    canonical = json.dumps(payload, sort_keys=True, separators=(",", ":")).encode()
    payload["manifest_sha256"] = hashlib.sha256(canonical).hexdigest()
    return payload

This same artifact shape feeds cross-environment reviews such as Environment Comparison Workflows, where two snapshots are diffed against each other rather than against a static baseline.

Troubleshooting Matrix: Scope-Mapping Failure Modes

For extraction-side failures — schema objects appearing and disappearing under a long scan — pair this matrix with Handling schema drift during catalog extraction, which covers snapshot fencing in depth.

Related

• Role Hierarchy Design — the tiered role structure whose closure this page resolves.
• Grant and Revoke Chain Logic — how the reconciliation plan’s cascades apply safely.
• Security Boundary Enforcement — turning mapped scopes into enforced least-privilege boundaries.
• Rule-Based Drift Scoring — assigning severity weights to the canonical delta this page emits.
• System Catalog Query Optimization — making the extraction step fast on large fleets.

Up: Core RBAC Architecture & Privilege Fundamentals