RBAC Drift Detection & Compliance Sync

Extracting user grants from the Oracle data dictionary

This page shows you how to pull a complete, point-in-time snapshot of a principal’s privileges out of the Oracle data dictionary — object grants, system grants, and recursively resolved role grants — fast enough to run continuously and deterministic enough to diff.

When to reach for data-dictionary grant extraction

Direct dictionary interrogation is the right approach in a narrow set of conditions; pick something else outside them.

  • Use it when Oracle is one engine in a heterogeneous fleet and you need its grants translated into the same canonical tuples as your PostgreSQL and MySQL sources, so a single diff engine can reason across all of them.
  • Use it when the extraction account is read-only and every run must be reproducible — a repeated scan against an unchanged dictionary has to yield byte-identical output so drift detection sees no phantom change.
  • Skip it when you only need a one-off answer for a single schema during an incident; DBA_TAB_PRIVS inspected interactively in SQL*Plus is faster than standing up a pipeline.

This page is the Oracle-specific implementation of one technique within System Catalog Query Optimization; the grant rows it produces flow into Cross-Environment Privilege Extraction & Parsing for normalization. Raw sweeps against DBA_SYS_PRIVS, DBA_TAB_PRIVS, and DBA_ROLE_PRIVS degrade into hash joins and temporary-tablespace spills when fanned across hundreds of schemas, so every step below is built around predicate-anchored queries, bounded cursor fetches, and explicit optimizer directives that keep execution plans predictable.

Bind-anchored, SCN-tagged Oracle grant extraction flow A single bind variable predicate, GRANTEE equals colon grantee, fans out to three data-dictionary views: DBA_SYS_PRIVS for system grants, DBA_TAB_PRIVS for object grants, and DBA_ROLE_PRIVS for direct role-membership edges. DBA_ROLE_PRIVS flows into a CONNECT BY NOCYCLE node that recursively expands the role graph, recording LEVEL as depth. The system-grant stream, the object-grant stream, and the recursively expanded role stream all merge through a bounded fetchmany() stage whose arraysize equals the batch size and which is tagged once with a system change number checkpoint. The merged, sorted output is a set of canonical grant tuples that are deterministic and diffable. recurse GRANTEE = :grantee bind variable · shareable cursor DBA_SYS_PRIVS system grants DBA_TAB_PRIVS object grants DBA_ROLE_PRIVS role membership · direct edges CONNECT BY NOCYCLE recursive role expansion PRIOR granted_role = grantee · LEVEL SCN checkpoint Bounded fetchmany() arraysize = batch_size · one point-in-time SCN Canonical grant tuples sorted · deterministic · diffable

Step-by-step implementation

The extractor has four moving parts: a read-only catalog account, an index-anchored object-grant query, a recursive role-resolution query, and a bounded-fetch Python driver that tags each run with a system change number. Build them in order and verify each before moving on.

1. Provision a read-only extraction account

The safety of the whole pipeline rests on the extraction principal holding no DDL and no GRANT. SELECT_CATALOG_ROLE gives read access to the DBA_* views without any mutating privilege. In a multitenant container database, create the account as a common user and open its CONTAINER_DATA so it can read PDB-scoped dictionary rows from the root:

CREATE USER c##drift_extract IDENTIFIED BY "REDACTED" CONTAINER=ALL;
GRANT CREATE SESSION TO c##drift_extract CONTAINER=ALL;
GRANT SELECT_CATALOG_ROLE TO c##drift_extract CONTAINER=ALL;
-- let the common user see PDB-level dictionary rows queried from CDB$ROOT
ALTER USER c##drift_extract SET CONTAINER_DATA=ALL CONTAINER=CURRENT;

Verify the account carries no privilege that could alter state — the result set must contain only CREATE SESSION:

-- Expect exactly one row: CREATE SESSION. Any ANY/DROP/ALTER privilege is a finding.
SELECT privilege FROM session_privs ORDER BY privilege;

2. Anchor the object-grant query to an indexed grantee predicate

Instead of an unfiltered SELECT * FROM DBA_TAB_PRIVS, filter on the grantee with a bind variable so the cursor is shareable, and pin the plan with hints. NO_MERGE stops the optimizer from merging the view’s inline definition in a way that loses filter selectivity:

SELECT /*+ NO_MERGE INDEX(p I_TABPRIVS_GRANTEE) */
       p.GRANTEE,
       p.OWNER         AS object_schema,
       p.TABLE_NAME    AS object_name,
       p.PRIVILEGE,
       p.GRANTABLE,
       p.HIERARCHY
FROM   DBA_TAB_PRIVS p
WHERE  p.GRANTEE = :grantee   -- bind variable enables cursor sharing
ORDER BY p.OWNER, p.TABLE_NAME, p.PRIVILEGE;

The INDEX hint is advisory: if I_TABPRIVS_GRANTEE does not exist in your release the hint is silently ignored and the optimizer falls back to a full scan. Confirm the index is present before relying on it:

-- Expect at least one row naming the grantee index on the base table TABPRIVS$.
SELECT index_name FROM dba_indexes WHERE table_name = 'TABPRIVS';

For authoritative base-table structures and view definitions, consult the Oracle Database Reference.

3. Resolve role grants recursively with CONNECT BY

A grantee’s effective privileges include everything reachable through the roles granted to it, transitively. DBA_ROLE_PRIVS records only direct edges, so walk the graph with a hierarchical query. NOCYCLE protects against a mutually-granting role loop, and LEVEL records the depth at which each role was reached:

SELECT /*+ NO_MERGE */
       CONNECT_BY_ROOT rp.GRANTEE AS principal,
       rp.GRANTED_ROLE,
       rp.ADMIN_OPTION,
       LEVEL                      AS hierarchy_depth
FROM   DBA_ROLE_PRIVS rp
START WITH rp.GRANTEE = :grantee
CONNECT BY NOCYCLE PRIOR rp.GRANTED_ROLE = rp.GRANTEE
ORDER BY hierarchy_depth, rp.GRANTED_ROLE;

Cap the traversal in the driver (step 4) so a pathological hierarchy cannot expand combinatorially. Verify the walk terminates and the depths look sane:

-- Expect finite output; hierarchy_depth should not exceed your role-nesting policy.
SELECT MAX(LEVEL) AS max_depth
FROM   DBA_ROLE_PRIVS
START WITH GRANTEE = :grantee
CONNECT BY NOCYCLE PRIOR GRANTED_ROLE = GRANTEE;

4. Stream rows with bounded fetchmany() and tag the SCN

Wrap the queries in python-oracledb and fetch in bounded batches so a large grant set never exhausts PGA. Setting cursor.arraysize equal to the fetch size lets each fetchmany() be satisfied from the client-side buffer without an extra round trip. Capture the system change number once, up front, so the entire snapshot reconciles to a single point in time:

import oracledb

def extract_tab_privs_batched(
    pool: oracledb.ConnectionPool,
    grantee: str,
    batch_size: int = 5000,
) -> dict:
    """Extract DBA_TAB_PRIVS for one grantee via bounded fetchmany().
    Returns the SCN the snapshot was taken at plus normalized grant rows."""
    sql = """
        SELECT GRANTEE, OWNER, TABLE_NAME, PRIVILEGE, GRANTABLE, HIERARCHY
        FROM   DBA_TAB_PRIVS
        WHERE  GRANTEE = :grantee
        ORDER BY OWNER, TABLE_NAME, PRIVILEGE
    """
    grants: list[dict] = []
    with pool.acquire() as conn:
        with conn.cursor() as cur:
            # single point-in-time anchor for the whole extraction
            cur.execute("SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM dual")
            scn = cur.fetchone()[0]

            cur.arraysize = batch_size
            cur.execute(sql, grantee=grantee)
            while True:
                rows = cur.fetchmany(batch_size)
                if not rows:
                    break
                for row in rows:
                    grants.append({
                        "grantee":       row[0],
                        "object_schema": row[1],
                        "object_name":   row[2],
                        "privilege":     row[3],
                        "is_grantable":  row[4] == "YES",
                        "hierarchy":     row[5] == "YES",
                    })
    return {"scn": scn, "grantee": grantee, "grants": grants}

Run it and confirm two consecutive extractions of an unchanged grantee return identical grant lists — the determinism contract the diff engine depends on:

a = extract_tab_privs_batched(pool, "REPORT_SVC")["grants"]
b = extract_tab_privs_batched(pool, "REPORT_SVC")["grants"]
assert a == b, "non-deterministic extraction — check ORDER BY and the fetch loop"

Connection pooling and cursor lifecycle are covered in the python-oracledb documentation.

Worked example: Oracle 19c multitenant, one PDB, a reporting service account

Consider an Oracle 19c CDB named ORCLCDB with a single application PDB SALESPDB, extracted nightly by the c##drift_extract account from step 1. The compliance question is: what can the REPORT_SVC account actually do inside SALESPDB? The orchestration layer connects to the root, switches container, and calls the extractor:

import oracledb

pool = oracledb.create_pool(
    user="c##drift_extract", password="REDACTED",
    dsn="orcl-cdb-host:1521/ORCLCDB", min=1, max=4,
)
with pool.acquire() as conn:
    conn.cursor().execute("ALTER SESSION SET CONTAINER = SALESPDB")
    snapshot = extract_tab_privs_batched(pool, "REPORT_SVC", batch_size=5000)

A representative snapshot payload — the SCN anchors the capture, and the sorted grant rows are the diffable body:

{
  "scn": 5721904663,
  "grantee": "REPORT_SVC",
  "grants": [
    {"grantee": "REPORT_SVC", "object_schema": "SALES", "object_name": "INVOICES",
     "privilege": "SELECT", "is_grantable": false, "hierarchy": false},
    {"grantee": "REPORT_SVC", "object_schema": "SALES", "object_name": "INVOICE_LINES",
     "privilege": "SELECT", "is_grantable": false, "hierarchy": false}
  ]
}

The nightly job persists the payload keyed by (pdb, grantee). When the diff engine compares tonight’s grant set for SALESPDB/REPORT_SVC against last night’s, an unchanged dictionary produces an identical sorted body and zero drift; the appearance of an UPDATE grant on SALES.INVOICES — or a new SALES_ADMIN role edge surfaced by the step 3 query — flips the diff and surfaces exactly that one added privilege, the granularity auditors expect from an evidence trail.

Gotchas and engine-specific notes

Oracle vs PostgreSQL/MySQL catalog surface. PostgreSQL resolves object grants through information_schema.role_table_grants and membership through pg_catalog.pg_auth_members; MySQL 8.0 keeps role edges in mysql.role_edges. Oracle splits the same information across three views — DBA_TAB_PRIVS (object grants), DBA_SYS_PRIVS (system grants), and DBA_ROLE_PRIVS (role membership) — so a complete Oracle principal snapshot is the union of all three, not a single query. Reconciling those dialect differences into one shape is the job of the Cross-DB Parser Adapters.

HIERARCHY is not GRANTABLE. In DBA_TAB_PRIVS, GRANTABLE maps to the canonical is_grantable (WITH GRANT OPTION), while HIERARCHY is a separate flag governing whether the privilege extends to objects in an object hierarchy. Collapsing the two loses semantics the diff engine needs; keep them as distinct boolean fields.

Query from the right container. A DBA_* view queried in CDB$ROOT returns only root-scoped rows unless the account’s CONTAINER_DATA is opened. Either ALTER SESSION SET CONTAINER = <pdb> before extracting (as in the worked example) or query the CDB_* views from the root with a CON_ID predicate — mixing the two silently under-reports PDB grants.

Snapshot skew and ORA-01555. A long fetch loop widens the window between first and last row read. DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER anchors the run, but if ORA-01555: snapshot too old still appears on a busy instance, raise UNDO_RETENTION or schedule extraction during a low-DML window. Never replace fetchmany() with an unbounded fetchall() on a large grant table — it invites ORA-04030: out of process memory; for memory-constrained hosts drop arraysize to 2,048–4,096.

Classify errors before retrying. Transient failures — ORA-00054: resource busy, ORA-12514: TNS listener not currently aware — deserve exponential backoff with jitter and a pool reset, scoped to the individual batch cursor so partial progress survives. Permanent failures — ORA-01031: insufficient privileges, ORA-00942: table or view does not exist — must fail fast and emit telemetry rather than loop. Re-execution is idempotent because each run re-anchors its own SCN and the extraction account holds no write path.

Compliance note

Read-only dictionary extraction is the evidence-collection mechanism for access-review controls: SOC 2 CC6.1 and CC6.3 (logical access is provisioned and reviewed against least privilege), PCI-DSS Requirement 7 (need-to-know restriction), and HIPAA §164.312(a)(1) (access control). The artifact it produces for auditors is an SCN-anchored, deterministically ordered privilege snapshot per (pdb, grantee) — the scn field gives each capture a verifiable point-in-time identity, and the diff between two dated snapshots is the periodic access-review record itself. Because the c##drift_extract principal holds only CREATE SESSION and SELECT_CATALOG_ROLE, the collection process cannot alter the state it attests to, which is the property an auditor checks first.

Frequently asked questions

Do I need SELECT ANY DICTIONARY or is SELECT_CATALOG_ROLE enough? SELECT_CATALOG_ROLE is enough and is the safer choice — it grants read on the DBA_* views without the broader reach of SELECT ANY DICTIONARY. Prefer it so the extraction account stays minimal.

Why capture the SCN instead of a wall-clock timestamp? A timestamp records when the job ran; the SCN records the exact committed state the dictionary was read at. Two runs at the same SCN see identical data by definition, which makes the snapshot reconcilable and the diff defensible in a way a clock reading is not.

How do I get a principal’s complete privilege set, not just object grants? Run all three queries — DBA_TAB_PRIVS for object grants, DBA_SYS_PRIVS for system grants, and the recursive DBA_ROLE_PRIVS walk for roles — under the same SCN, then union the results. The role walk is what surfaces privileges inherited through nested roles.

Does the hint break if the index is missing? No. Oracle treats INDEX(...) as advisory; if I_TABPRIVS_GRANTEE is absent the optimizer ignores the hint and scans normally. That is why step 2 includes a verification query — an ignored hint on a large estate is a latency regression, not an error.

Up: System Catalog Query Optimization