Security Boundary Enforcement

A privilege boundary is the promise that a given identity can reach exactly the objects policy authorizes and nothing more. Security boundary enforcement is the sub-problem of keeping that promise true in a running catalog — continuously reconciling the live grant surface against the approved perimeter so access never silently exceeds it. The failure scenario this section prevents is perimeter creep: a hotfix GRANT issued during an incident, a schema migration that re-creates a table without its restrictive ACL, or a provisioning script that hands a service account broader access “just to unblock the deploy.” None of these throw an error. Each widens the boundary by one grant, and the accumulated excess is invisible until an auditor samples it or an attacker walks through it. Enforcement turns the boundary from an aspiration recorded in a runbook into a control that is measured on every reconciliation run and restored the moment it drifts — without taking down the service accounts that depend on it.

The boundary itself is defined upstream: the desired-state graph comes from Role Hierarchy Design, and the narrowing of broad privileges into the smallest workable object grants is the least-privilege methodology that shrinks each identity’s blast radius. This section owns what happens after the boundary is defined — detecting when the live catalog exceeds it and driving it back inside the perimeter safely. It is the enforcement half of Core RBAC Architecture & Privilege Fundamentals.

Figure — The enforcement loop: the live perimeter is extracted and classified against the approved boundary; excess grants are guarded against breaking active service accounts before a transactional apply, then the post-state is re-verified and signed as evidence.

Figure — The boundary is the delta between two surfaces: the live grant surface bulging past the approved perimeter is excess to revoke, and the part of the perimeter the live surface fails to cover is a missing grant. Every excess revoke passes a guard that quarantines a live service account to a read-only replica rather than stripping it mid-transaction, and both paths converge on a verified in-perimeter state.

Prerequisites and Scope

The techniques below target PostgreSQL 12+ and MySQL 8.0.16+, the versions where role membership catalogs (pg_auth_members, mysql.role_edges) and object-grant views behave as described. On the Python side, use Python 3.11+ (for datetime.UTC and structural pattern matching), psycopg 3.1+ for PostgreSQL catalog reads and applies, and pydantic 2.x if you load the approved boundary as a typed manifest.

Enforcement runs under two separate identities, never one. The detection principal is read-only: on PostgreSQL, membership in pg_monitor plus enough visibility to read information_schema.role_table_grants (use pg_read_all_stats or a broadly-granted role, since that view only exposes rows the connected role participates in); on MySQL, SELECT on mysql.tables_priv, mysql.role_edges, and mysql.default_roles. The remediation principal is the only credential that holds WITH ADMIN OPTION memberships (PostgreSQL) or ROLE_ADMIN / the relevant GRANT OPTION (MySQL). Splitting them guarantees a drift scan can never mutate state even if the boundary manifest is wrong. The extraction, normalization, and read-side query-tuning that feed this loop are the concern of Cross-Environment Privilege Extraction & Parsing; this section assumes you can already materialize a live grant set and focuses on classifying and correcting it.

Core Implementation Walkthrough

Enforcement is four stages: extract the live object-grant perimeter, classify each grant against the approved boundary, apply corrections transactionally while protecting active service accounts, then verify and sign the result. Every stage below runs against a live database.

Step 1 — Extract the live object-grant perimeter

The boundary is enforced at the object-grant level, not just role membership, so extraction reads the grant catalog directly. On PostgreSQL, information_schema.role_table_grants yields grantee, object, and privilege type; exclude PUBLIC and the built-in pg_* roles so they never register as spurious excess.

-- PostgreSQL: live table-level grants (the enforced perimeter)
SELECT grantee,
       table_schema,
       table_name,
       privilege_type,
       is_grantable
FROM   information_schema.role_table_grants
WHERE  grantee NOT LIKE 'pg\_%'
  AND  grantee <> 'PUBLIC'
ORDER  BY grantee, table_schema, table_name, privilege_type;

The MySQL equivalent reads mysql.tables_priv; grantees are user@host pairs, and Table_priv is a SET column that must be split into one row per privilege to line up with the PostgreSQL shape.

-- MySQL 8.0+: live table-level grants
SELECT CONCAT(User, '@', Host) AS grantee,
       Db                       AS table_schema,
       Table_name               AS table_name,
       Table_priv               AS privilege_set
FROM   mysql.tables_priv
ORDER  BY grantee, Db, Table_name;

The reader forces a read-only transaction so a detection scan cannot mutate state, and emits a canonical grant tuple (grantee, schema, table, privilege) that the rest of the loop treats identically regardless of engine:

from __future__ import annotations
import psycopg

Grant = tuple[str, str, str, str]   # (grantee, schema, table, privilege)

PG_GRANT_SQL = """
SELECT grantee, table_schema, table_name, privilege_type
FROM   information_schema.role_table_grants
WHERE  grantee NOT LIKE 'pg\\_%' AND grantee <> 'PUBLIC'
"""

def fetch_pg_perimeter(dsn: str) -> set[Grant]:
    with psycopg.connect(dsn, autocommit=True) as conn:
        with conn.cursor() as cur:
            cur.execute("SET default_transaction_read_only = on")
            cur.execute(PG_GRANT_SQL)
            return {
                (grantee, schema, table, priv.upper())
                for grantee, schema, table, priv in cur.fetchall()
            }

Normalizing engine-specific privilege spellings into that single canonical tuple — so SELECT, Select, and a schema-level default ACL all reduce to comparable rows — is the normalization step described in Privilege Scope Mapping. Enforcement consumes its output as ground truth.

Step 2 — Classify each grant against the approved boundary

With the live perimeter and the approved boundary both expressed as canonical grant sets, classification is set arithmetic. Three categories drive three different remediations: excess grants exist live but not in the boundary and must be revoked; missing grants exist in the boundary but not live and must be granted; orphaned grantees hold grants but no longer resolve to an approved identity and must be stripped entirely.

from dataclasses import dataclass

@dataclass(frozen=True)
class BoundaryDelta:
    excess:  frozenset[Grant]    # live, beyond the perimeter → revoke
    missing: frozenset[Grant]    # in boundary, absent live   → grant
    orphaned: frozenset[str]     # grantees with no approved identity

def classify(observed: set[Grant],
             approved: set[Grant],
             approved_identities: set[str]) -> BoundaryDelta:
    excess  = observed - approved
    missing = approved - observed
    live_grantees = {g[0] for g in observed}
    orphaned = live_grantees - approved_identities
    return BoundaryDelta(
        excess=frozenset(excess),
        missing=frozenset(missing),
        orphaned=frozenset(orphaned),
    )

Not every deviation is equal — a wildcard INSERT/UPDATE/DELETE on a customer table is a different order of risk from a single stray SELECT on a lookup table. How much each excess grant weighs toward an overall enforcement decision (block the deploy, page on-call, or record and continue) is governed by Rule-Based Drift Scoring inside Drift Detection Engines & Diff Logic. Some deviations are legitimate and pre-approved — a break-glass grant with an expiry — and those are matched and suppressed by Exception Routing and Whitelisting before they ever reach the excess set.

Step 3 — Apply corrections transactionally, protecting live service accounts

The correction is a plan of REVOKE and GRANT statements, but a naive apply can take down production: revoking the wrong grant from a live service account breaks the workload mid-transaction. The enforcement apply therefore wraps every statement in its own SAVEPOINT so a single failure rolls back one operation rather than the whole plan, and it consults a guard that quarantines — rather than immediately strips — any role backing an active service account.

ACTIVE_SERVICE_ACCOUNTS: set[str] = {"svc_billing_api", "svc_reporting"}

def plan_statements(delta: BoundaryDelta) -> list[tuple[str, str]]:
    """Return (kind, ddl) pairs; revokes before grants so the perimeter
    never transiently widens during a single apply."""
    stmts: list[tuple[str, str]] = []
    for grantee, schema, table, priv in sorted(delta.excess):
        if grantee in ACTIVE_SERVICE_ACCOUNTS:
            stmts.append(("quarantine", grantee))
            continue
        stmts.append(("revoke",
                      f'REVOKE {priv} ON "{schema}"."{table}" FROM "{grantee}";'))
    for grantee, schema, table, priv in sorted(delta.missing):
        stmts.append(("grant",
                      f'GRANT {priv} ON "{schema}"."{table}" TO "{grantee}";'))
    return stmts

Each statement executes under a savepoint. A permission denial or a dependency conflict on one grant is caught, the savepoint is released back, and the run continues — but a hard failure still aborts the outer transaction so the catalog is never left half-enforced:

def apply_plan(dsn: str, statements: list[tuple[str, str]]) -> dict[str, list[str]]:
    result: dict[str, list[str]] = {"applied": [], "quarantined": [], "skipped": []}
    with psycopg.connect(dsn) as conn:            # remediation principal
        with conn.cursor() as cur:
            for kind, payload in statements:
                if kind == "quarantine":
                    result["quarantined"].append(payload)
                    continue
                cur.execute("SAVEPOINT enforce")
                try:
                    cur.execute(payload)
                    cur.execute("RELEASE SAVEPOINT enforce")
                    result["applied"].append(payload)
                except psycopg.errors.DependentObjectsStillExist as exc:
                    cur.execute("ROLLBACK TO SAVEPOINT enforce")
                    result["skipped"].append(f"{payload}  -- {exc.diag.message_primary}")
        conn.commit()
    return result

A quarantined service account is not left with excess access — it is diverted. The enforcement layer flips its connection routing to a read-only replica (or a compliant proxy role) so the workload keeps serving reads while the excess write grant is removed on the primary and re-provisioned correctly, then routing is restored. Managing that temporary, time-bound diversion is the exact pattern in Automating exception routing for temporary access grants. Ordering the REVOKE/GRANT statements so no revoke ever strands a privilege a dependent role still needs mid-plan is the cascade discipline owned by Grant and Revoke Chain Logic; the plan emitted here feeds that sequencer before it touches the catalog.

Step 4 — Verify the restored boundary and emit signed evidence

Enforcement is not complete when the plan applies — it is complete when a fresh extraction proves the perimeter is inside the boundary again. The verify stage re-runs Step 1 and Step 2 and asserts an empty excess set. The residual is the evidence: pre-state grant count, the exact deltas applied, and post-state confirmation, hashed and signed so an auditor can trust it was not edited after the fact.

import hashlib, json, datetime as dt

def verify_and_sign(dsn: str, approved: set[Grant],
                    approved_identities: set[str],
                    applied: dict[str, list[str]],
                    boundary_text: str) -> str:
    post = fetch_pg_perimeter(dsn)
    residual = classify(post, approved, approved_identities)
    report = {
        "verified_at": dt.datetime.now(dt.UTC).isoformat(),
        "boundary_sha256": hashlib.sha256(boundary_text.encode()).hexdigest(),
        "post_grant_count": len(post),
        "applied": applied["applied"],
        "quarantined": applied["quarantined"],
        "residual_excess": sorted(residual.excess),
        "in_perimeter": not residual.excess and not residual.orphaned,
    }
    return json.dumps(report, indent=2, sort_keys=True)

A non-empty residual_excess after apply is a hard alert, not a warning: it means a revoke silently failed or a grant was re-created between apply and verify, and the boundary is still open.

Idempotency and the Dry-Run Safety Contract

Boundary enforcement must be safe to run on a cron every few minutes, forever. The contract has three clauses. First, convergence: because the plan is computed as observed - approved (excess) and approved - observed (missing), a catalog already inside its perimeter yields empty sets, so a second immediate run applies nothing. There is no counter, timestamp, or side effect that makes the second run diverge from the first — an in-perimeter database is a fixed point.

Second, read-only by default. Detection connects with the read-only principal and sets default_transaction_read_only = on; the plan is computed and serialized without ever holding write credentials. Only the separate, explicitly invoked apply stage connects as the remediation principal. In CI, the loop runs in dry-run mode — it classifies and serializes the plan but asserts the excess set is empty, so a pull request that would widen the boundary fails the build before it merges:

def assert_in_perimeter(observed, approved, identities) -> None:
    delta = classify(observed, approved, identities)
    if delta.excess or delta.orphaned:
        raise SystemExit(
            f"boundary breach: {len(delta.excess)} excess grant(s), "
            f"{len(delta.orphaned)} orphaned grantee(s)"
        )   # non-zero exit fails CI

Third, quarantine is reversible and self-healing. A service account routed to a replica is recorded with its original routing so a later run — once the correct grant is re-provisioned — detects the account is back inside the perimeter and restores primary routing automatically. The quarantine state is never a terminal manual step; it is another cell the convergence loop drives back to normal. Because both the observed and approved sets are engine-normalized upstream, the same convergence guarantee holds whether the target is PostgreSQL’s implicit INHERIT cascade or MySQL’s explicit role-activation model.

Compliance Alignment and Evidence Artifacts

Continuous boundary enforcement maps directly onto the access-control clauses auditors test, and the signed verify report is the artifact they ask for.

• SOC 2 CC6.1 / CC6.3 — logical access is restricted to authorized users and least privilege is enforced. The approved boundary manifest is the “authorized” record; the empty-excess assertion on every run is continuous evidence that live state has not exceeded it. The signed report is the sampled-period proof.
• PCI-DSS Req 7 — access to cardholder data is restricted to least privilege by role and need-to-know. The classification output enumerates every excess grant that was removed, with timestamps, giving an assessor the remediation trail rather than just the end state.
• HIPAA §164.312(a)(1) — technical access control with an audit trail. Every enforcement delta (grantee, object, privilege, actor, boundary hash) is an immutable record of a boundary change.
• NIST SP 800-53 AC-6 — least privilege as an enforced, monitored control. Boundary hashing ties each report to the exact approved baseline in force, per NIST SP 800-53 Access Control.

Standardize the connection and apply behavior across engines using the DB-API 2.0 specification so the same evidence shape is produced regardless of target. The report is append-only and content-addressed by boundary_sha256, so a reviewer can prove which baseline governed any given run.

Troubleshooting Matrix

Boundary Enforcement Questions Engineers Ask

How is continuous boundary enforcement different from a periodic access review? An access review is a point-in-time sample: a human (or a report) inspects grants once a quarter and signs off. Boundary enforcement is a control that runs on every reconciliation cycle and acts — it computes observed - approved on each pass and drives the catalog back inside the perimeter the moment excess appears. The review still happens, but it now inspects the signed verify report from Step 4 rather than the raw catalog, because the raw catalog is already provably in-perimeter between reviews.

Why run detection and remediation as two separate database principals? Because a drift scanner that holds write credentials can corrupt the very boundary it is meant to protect if the approved manifest is wrong. The read-only detection principal sets default_transaction_read_only = on and can only ever produce a plan; the remediation principal — the sole holder of WITH ADMIN OPTION (PostgreSQL) or ROLE_ADMIN/GRANT OPTION (MySQL) — is invoked explicitly and separately. Splitting the credentials makes an accidental mutation during a scan structurally impossible, not merely unlikely.

What happens to a live service account when its excess grant has to be revoked? It is quarantined, not stripped mid-transaction. The guard in Step 3 recognizes any grantee in ACTIVE_SERVICE_ACCOUNTS, flips its connection routing to a read-only replica or a compliant proxy role so reads keep serving, removes the excess write grant on the primary, re-provisions the correct grant, and then restores routing. The same time-bound diversion machinery is documented in Automating exception routing for temporary access grants.

Does object-grant enforcement catch privileges inherited through role membership? No — and this is the most common blind spot. information_schema.role_table_grants and mysql.tables_priv expose direct grants only. If an identity reaches an object because it is a member of a role that holds the grant, table-grant enforcement will report the boundary as clean while effective access is wider. You must enforce the membership edges too, using the topology from Role Hierarchy Design, or an auditor will find access the verify report swore was closed.

Is it safe to run boundary enforcement on a cron in production? Yes, and that is the design goal. An in-perimeter database is a fixed point: the second run of an already-converged catalog computes empty excess and missing sets and applies nothing. Detection is read-only by default, apply is a separate explicit stage, and quarantine is reversible and self-healing. The only state that ever changes is the state that is genuinely outside the boundary.

Related

• Role Hierarchy Design — defines the desired-state boundary this section keeps intact under change.
• Grant and Revoke Chain Logic — orders the enforcement plan so no revoke strands a dependent privilege mid-apply.
• Privilege Scope Mapping — normalizes engine-specific grants into the canonical tuples enforcement classifies.
• Automating exception routing for temporary access grants — the time-bound quarantine and re-routing pattern for protecting live service accounts.
• Rule-Based Drift Scoring — weights each excess grant so enforcement can decide whether to block, page, or record.

Up: Core RBAC Architecture & Privilege Fundamentals