Cross-DB Parser Adapters

RBAC drift detection breaks the moment two databases describe the same grant in two different vocabularies. PostgreSQL reports a table privilege through information_schema.role_table_grants with an is_grantable flag of 'YES'; MySQL reports the equivalent through information_schema.table_privileges with IS_GRANTABLE = 'YES'; Oracle reports it through DBA_TAB_PRIVS with GRANTABLE = 'YES' and a separate HIERARCHY column. A diff engine that consumes these raw shapes directly will flag phantom drift on every comparison — not because privileges changed, but because the dialects never agreed on a representation. A parser adapter is the translation layer that eliminates that class of false positive: each engine gets a dedicated adapter that interrogates its catalog and emits one canonical record shape, so everything downstream compares grants, not grammar.

The failure scenario if this layer is neglected is concrete and expensive. Without normalization, a compliance sync that spans a PostgreSQL primary, a MySQL analytics replica, and an Oracle system-of-record produces three incompatible privilege inventories. Reconciling them by hand — or worse, with per-engine if/else branches scattered through the diff logic — means every new engine, catalog version, or privilege type multiplies the surface area for silent extraction errors. The adapter pattern isolates all dialect knowledge behind a stable interface, so the diff engine, the scoring engine, and the audit evidence generator each depend on exactly one schema regardless of how many engines the estate runs.

This page sits under the Cross-Environment Privilege Extraction & Parsing workflow and owns the normalization contract that the rest of that pipeline assumes.

Prerequisites: Engine Versions and Catalog Grants

Adapters read system catalogs, so the account they connect with needs enough visibility to enumerate other roles’ grants without holding any write privilege of its own. Provision a dedicated, read-only extraction principal per engine and scope it tightly:

• PostgreSQL 14+ — the extraction role needs pg_read_all_settings and membership in pg_monitor, or at minimum SELECT on pg_catalog and information_schema. pg_auth_members gained the inherit_option and set_option columns in PostgreSQL 16; adapters must not hard-code those columns when targeting mixed 14/15/16 fleets. Python driver: asyncpg 0.29+ or psycopg 3.1+.
• MySQL 8.0.13+ — role support and mysql.role_edges only exist from 8.0. Grant the extraction user SELECT on mysql.* and the ROLE_ADMIN dynamic privilege is not required for read-only enumeration. Python driver: aiomysql 0.2+ or mysql-connector-python 8.0+.
• Oracle 12c+ — the extraction schema needs SELECT on DBA_ROLE_PRIVS, DBA_SYS_PRIVS, and DBA_TAB_PRIVS, typically via the SELECT_CATALOG_ROLE. Python driver: python-oracledb 2.0+ (thin mode).

Because these queries touch catalog tables that a busy instance reads constantly, pair every adapter with the query shaping described in System Catalog Query Optimization so extraction never escalates into lock contention on production metadata. All examples below target Python 3.11+.

The Canonical GrantRecord Contract

Every adapter, whatever engine it wraps, must emit the same immutable record. Freezing the dataclass makes each grant hashable, which lets the diff engine put entire privilege snapshots into a frozenset and compute drift with set algebra rather than nested loops:

from dataclasses import dataclass


@dataclass(frozen=True, slots=True)
class GrantRecord:
    grantee: str          # normalized to lowercase, unquoted
    object_schema: str    # "" for server-wide / system-level privileges
    object_name: str      # "" for schema- or system-level privileges
    object_type: str      # TABLE | VIEW | SCHEMA | PROCEDURE | ROLE | SYSTEM
    privilege: str        # SELECT | INSERT | UPDATE | DELETE | EXECUTE | USAGE | ...
    is_grantable: bool    # WITH GRANT OPTION / ADMIN OPTION / GRANTABLE = 'YES'
    grantor: str          # normalized to lowercase; "" when the catalog omits it
    source_engine: str    # postgres | mysql | oracle

The normalization rules are as important as the fields. Identifiers are lowercased and unquoted so "App_Role" in Oracle and app_role in PostgreSQL collapse to the same grantee. Privilege verbs are upper-cased and mapped to a shared vocabulary — Oracle’s EXECUTE on a package and PostgreSQL’s EXECUTE on a function both become EXECUTE with object_type = 'PROCEDURE'. Role-membership edges (which engine catalogs model very differently) all normalize to privilege = 'MEMBER', object_type = 'ROLE', and object_name set to the granted role. This is the same canonical target the Schema Validation Pipelines stage validates against before any record reaches the diff engine.

Building an Adapter: Catalog Interrogation to Normalized Grants

Each adapter follows the same three-move shape: run a projection-only catalog query, map rows into GrantRecord, and expose the result behind one async method. The steps below implement the three most common engines and the dispatcher that selects between them.

Step 1 — PostgreSQL adapter

PostgreSQL splits the information across information_schema.role_table_grants (object grants) and pg_auth_members joined to pg_roles (role membership). Project explicit columns so the planner uses catalog indexes instead of scanning:

import asyncpg

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

PG_ROLE_MEMBERS = """
    SELECT m.rolname   AS grantee,
           r.rolname   AS granted_role,
           g.rolname   AS grantor,
           am.admin_option
    FROM pg_auth_members am
    JOIN pg_roles r ON r.oid = am.roleid
    JOIN pg_roles m ON m.oid = am.member
    JOIN pg_roles g ON g.oid = am.grantor
"""


async def extract_postgres(conn: asyncpg.Connection) -> list[GrantRecord]:
    records: list[GrantRecord] = []

    for row in await conn.fetch(PG_OBJECT_GRANTS):
        records.append(GrantRecord(
            grantee=row["grantee"].lower(),
            object_schema=row["table_schema"].lower(),
            object_name=row["table_name"].lower(),
            object_type="TABLE",
            privilege=row["privilege_type"].upper(),
            is_grantable=row["is_grantable"] == "YES",
            grantor=(row["grantor"] or "").lower(),
            source_engine="postgres",
        ))

    for row in await conn.fetch(PG_ROLE_MEMBERS):
        records.append(GrantRecord(
            grantee=row["grantee"].lower(),
            object_schema="",
            object_name=row["granted_role"].lower(),
            object_type="ROLE",
            privilege="MEMBER",
            is_grantable=row["admin_option"],
            grantor=(row["grantor"] or "").lower(),
            source_engine="postgres",
        ))

    return records

information_schema.role_table_grants already resolves grants the current role can see, and filtering out the two system schemas keeps the payload to application-relevant objects. Membership admin_option maps directly to is_grantable, preserving the “can this principal re-grant?” signal that separation-of-duties audits depend on.

Step 2 — MySQL adapter

MySQL exposes object grants through information_schema.table_privileges and role edges through mysql.role_edges. The column names differ from PostgreSQL, which is exactly the divergence the adapter absorbs:

import aiomysql

MYSQL_OBJECT_GRANTS = """
    SELECT GRANTEE, TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, IS_GRANTABLE
    FROM information_schema.table_privileges
    WHERE TABLE_SCHEMA NOT IN ('mysql', 'sys',
                               'information_schema', 'performance_schema')
"""

MYSQL_ROLE_EDGES = """
    SELECT FROM_USER, FROM_HOST, TO_USER, WITH_ADMIN_OPTION
    FROM mysql.role_edges
"""


def _strip_grantee(grantee: str) -> str:
    # information_schema renders GRANTEE as 'user'@'host'; keep the user part.
    return grantee.split("@", 1)[0].strip("'").lower()


async def extract_mysql(conn: aiomysql.Connection) -> list[GrantRecord]:
    records: list[GrantRecord] = []

    async with conn.cursor() as cur:
        await cur.execute(MYSQL_OBJECT_GRANTS)
        for grantee, schema, table, priv, grantable in await cur.fetchall():
            records.append(GrantRecord(
                grantee=_strip_grantee(grantee),
                object_schema=schema.lower(),
                object_name=table.lower(),
                object_type="TABLE",
                privilege=priv.upper(),
                is_grantable=grantable == "YES",
                grantor="",  # MySQL catalogs do not record the grantor here
                source_engine="mysql",
            ))

        await cur.execute(MYSQL_ROLE_EDGES)
        for from_user, _from_host, to_user, admin in await cur.fetchall():
            records.append(GrantRecord(
                grantee=to_user.lower(),
                object_schema="",
                object_name=from_user.lower(),
                object_type="ROLE",
                privilege="MEMBER",
                is_grantable=admin == "Y",
                grantor="",
                source_engine="mysql",
            ))

    return records

Two dialect quirks are handled here explicitly. MySQL renders grantees as 'user'@'host', so the adapter strips the host to align with PostgreSQL’s host-less role names — a deliberate normalization decision that must be documented, because it collapses app@'10.0.%' and app@'localhost' into one grantee. And mysql.role_edges records the admin option as the single character 'Y', not the SQL-standard 'YES'. The side-by-side mechanics of pg_auth_members versus role_edges are covered in depth in Understanding RBAC Inheritance in PostgreSQL vs MySQL.

Step 3 — Oracle adapter

Oracle spreads privileges across three data-dictionary views: DBA_TAB_PRIVS for object grants, DBA_SYS_PRIVS for system privileges, and DBA_ROLE_PRIVS for role membership. The adapter reads all three and folds them into the same record type:

import oracledb

ORACLE_TAB_PRIVS = """
    SELECT GRANTEE, OWNER, TABLE_NAME, PRIVILEGE, GRANTABLE, GRANTOR
    FROM DBA_TAB_PRIVS
    WHERE OWNER NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP')
"""

ORACLE_SYS_PRIVS = "SELECT GRANTEE, PRIVILEGE, ADMIN_OPTION FROM DBA_SYS_PRIVS"

ORACLE_ROLE_PRIVS = """
    SELECT GRANTEE, GRANTED_ROLE, ADMIN_OPTION FROM DBA_ROLE_PRIVS
"""


async def extract_oracle(conn: oracledb.AsyncConnection) -> list[GrantRecord]:
    records: list[GrantRecord] = []

    with conn.cursor() as cur:
        for grantee, owner, table, priv, grantable, grantor in await cur.execute(
            ORACLE_TAB_PRIVS
        ):
            records.append(GrantRecord(
                grantee=grantee.lower(),
                object_schema=owner.lower(),
                object_name=table.lower(),
                object_type="TABLE",
                privilege=priv.upper(),
                is_grantable=grantable == "YES",
                grantor=(grantor or "").lower(),
                source_engine="oracle",
            ))

        for grantee, priv, admin in await cur.execute(ORACLE_SYS_PRIVS):
            records.append(GrantRecord(
                grantee=grantee.lower(),
                object_schema="",
                object_name="",
                object_type="SYSTEM",
                privilege=priv.upper(),
                is_grantable=admin == "YES",
                grantor="",
                source_engine="oracle",
            ))

        for grantee, role, admin in await cur.execute(ORACLE_ROLE_PRIVS):
            records.append(GrantRecord(
                grantee=grantee.lower(),
                object_schema="",
                object_name=role.lower(),
                object_type="ROLE",
                privilege="MEMBER",
                is_grantable=admin == "YES",
                grantor="",
                source_engine="oracle",
            ))

    return records

Oracle’s SYSTEM privileges (CREATE SESSION, SELECT ANY TABLE, and similar) have no direct PostgreSQL or MySQL analogue, so they map to object_type = 'SYSTEM' with empty schema and object fields. Preserving them rather than dropping them matters: SELECT ANY TABLE is a bypass of object-level RBAC and must survive normalization so the scoring engine can weight it as high-severity drift. The full walkthrough of dictionary extraction lives in Extracting User Grants from the Oracle Data Dictionary.

Step 4 — The adapter registry

A thin dispatcher keeps engine selection out of the calling code. Registering adapters by source_engine name means adding a fourth engine — Snowflake, SQL Server — is a one-line registration, never a change to the pipeline:

from collections.abc import Awaitable, Callable

Adapter = Callable[[object], Awaitable[list[GrantRecord]]]

_REGISTRY: dict[str, Adapter] = {
    "postgres": extract_postgres,
    "mysql": extract_mysql,
    "oracle": extract_oracle,
}


async def extract(engine: str, conn: object) -> list[GrantRecord]:
    try:
        adapter = _REGISTRY[engine]
    except KeyError:
        raise ValueError(f"no parser adapter registered for engine {engine!r}")
    return sorted(await adapter(conn), key=lambda r: (
        r.grantee, r.object_schema, r.object_name, r.privilege,
    ))

Sorting the output before it leaves the adapter guarantees a deterministic ordering across runs, which is a precondition for the reproducible snapshots the diff engine hashes. Once records are normalized, high-volume estates fan the per-instance extraction calls out concurrently using Async Privilege Batching so hundreds of connections resolve without blocking a single coordinator thread.

Idempotency and Read-Only Safety Contract

Adapters are pure read operations, and that property must be enforced, not assumed. Three guarantees define the safety contract:

• No writes, ever. Every adapter issues only SELECT against catalog views. Run the extraction connection in an explicitly read-only transaction — SET TRANSACTION READ ONLY on PostgreSQL and Oracle, START TRANSACTION READ ONLY on MySQL — so a coding error that slips a DDL statement in is rejected by the engine rather than mutating state.
• Referentially stable output. Given an unchanged catalog, extract() returns byte-identical GrantRecord lists on every invocation. Because the dataclass is frozen and the output is sorted, hashing the serialized list yields a stable snapshot fingerprint. Two runs against a quiescent database produce the same fingerprint; a changed fingerprint is real drift, never ordering noise.
• Native dry-run. Since extraction never modifies the target, the adapter is the dry-run mode for the wider pipeline. Compliance teams can run it against production continuously to gather evidence with zero risk, and the remediation stage that would issue GRANT/REVOKE is a separate, opt-in component that never lives inside an adapter.

Repeated runs converge trivially because there is no state to converge — the adapter reports what the catalog holds at query time, and idempotency is a property of the pure function rather than something the caller must orchestrate.

Compliance Alignment and Evidence Artifacts

A normalized privilege graph is the raw material auditors ask for, so adapters should emit their output in a form that doubles as an evidence artifact. Serializing the sorted GrantRecord list to newline-delimited JSON, prefixed with an extraction manifest, produces a portable record that maps cleanly onto access-control controls:

import hashlib
import json
from datetime import datetime, timezone


def to_evidence(engine: str, instance: str,
                records: list[GrantRecord]) -> str:
    rows = [r.__dict__ for r in records]
    body = json.dumps(rows, sort_keys=True, separators=(",", ":"))
    manifest = {
        "engine": engine,
        "instance": instance,
        "extracted_at": datetime.now(timezone.utc).isoformat(),
        "record_count": len(records),
        "snapshot_sha256": hashlib.sha256(body.encode()).hexdigest(),
    }
    return json.dumps(manifest) + "\n" + body

The snapshot_sha256 is the compliance anchor: it lets an auditor confirm that the privilege set reviewed in an evidence bundle is bit-for-bit the set the pipeline actually extracted. This directly supports SOC 2 CC6.1 and CC6.3 (logical access is restricted and periodically evaluated), PCI-DSS Requirement 7 (need-to-know access enforcement with reviewable grants), and HIPAA §164.312(a)(1) (technical access control), because every control needs consistent, tamper-evident privilege enumeration across all data stores — which is precisely what the canonical record delivers regardless of engine. Structural conformance of these artifacts is enforced upstream by the Schema Validation Pipelines stage before they are archived.

Adapter Failure Modes and Remediation

Cross-engine extraction fails in engine-specific ways. These are the signatures that recur in production and their fixes:

When an adapter encounters a structural anomaly it cannot normalize — an unknown object_type, a privilege verb absent from the shared vocabulary — it should raise rather than silently emit a malformed record, letting the validation stage classify and route the failure instead of corrupting the diff input.

Techniques That Build on Cross-DB Adapters

The adapters here are the entry point for several engine-specific how-tos in this section:

• Extracting User Grants from the Oracle Data Dictionary — the detailed DBA_* view queries behind the Oracle adapter.
• Python Scripts for Async Batch Privilege Scraping — fanning the adapter calls across a large fleet without saturating connection pools.
• Handling Schema Drift During Catalog Extraction — what to do when the objects an adapter references are renamed or dropped mid-extraction.
• Understanding RBAC Inheritance in PostgreSQL vs MySQL — the inheritance models the membership normalization has to reconcile.

Related

• System Catalog Query Optimization — index-aware catalog queries that keep adapters off production locks.
• Async Privilege Batching — concurrent extraction across many instances.
• Schema Validation Pipelines — validating normalized records before the diff engine.
• Privilege Scope Mapping — the scope model the canonical record encodes.

Up: Cross-Environment Privilege Extraction & Parsing