Oracle Privilege Analysis: You Granted DBA. Here's What They Actually Used.

Someone needed to read one table. The ticket said “just give the app the standard role so we can ship,” the role already carried half a dozen ANY privileges, and it got granted. Three years later that account can DROP ANY TABLE in production and nobody alive remembers whether it needs to. Multiply by every service account, every “temporary” grant that became permanent, every role that grew by accretion, and you get the database most shops actually run: a pile of privilege nobody can safely revoke, because nobody can prove what’s in use.

You don’t have to guess. Privilege Analysis — the DBMS_PRIVILEGE_CAPTURE package, built into Enterprise Edition — records the privileges a session actually exercises over a window of real activity. Afterward you compare used against granted, and the difference is your revoke list: privileges held but never exercised, removable with evidence instead of a nervous guess. This post is what a capture is, how to run one, the one finding it produces almost every time (SELECT ANY TABLE, unused), and a lab that grants a user a dozen privileges, watches it use a handful, and prints the rest.

What Privilege Analysis actually records

A privilege capture watches sessions and notes every privilege that was needed to authorize an operation — the CREATE TABLE behind a CREATE TABLE statement, the SELECT object grant behind a query, the system privilege behind a DDL. It does not record what was granted; the data dictionary already knows that. It records what was exercised. Run a representative workload — a month-end, a full release cycle, a busy week — and you have an evidence-based map of the privileges an account can’t do its job without.

Then you subtract. Everything granted but not captured is, by definition, unused over that window. That set is the least-privilege candidate list: revoke it and the account keeps working, minus the blast radius you were carrying for no reason. The whole discipline is “grant what’s used, revoke what isn’t” — and Privilege Analysis is what turns that from a slogan into a query.

How a capture works

Four steps, all through DBMS_PRIVILEGE_CAPTURE:

-- 1. define a capture. G_CONTEXT scopes it to just the sessions you care about.
BEGIN
  DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
    name        => 'APPUSER_CAP',
    description => 'What APPUSER actually uses vs what it was granted',
    type        => DBMS_PRIVILEGE_CAPTURE.G_CONTEXT,
    condition   => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''APPUSER''');

-- 2. turn it on — BEFORE the workload runs
  DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE(name => 'APPUSER_CAP');
END;
/

-- 3. ... the application runs its real workload as APPUSER ...

-- 4. stop capturing and compute the result
BEGIN
  DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE(name => 'APPUSER_CAP');
  DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT(name => 'APPUSER_CAP');
END;
/

The step people miss is GENERATE_RESULT: until you call it, the DBA_USED_PRIVS / DBA_UNUSED_PRIVS views (and their *_SYSPRIVS / *_OBJPRIVS siblings) are empty. Enable, run the workload, disable, generate — then read the views. And there are four capture types for scoping:

• G_DATABASE — every privilege use in the whole database. The broad sweep; only one can run at a time.
• G_ROLE — only privileges that come from a named set of roles. Perfect for “is anyone actually using what DBA grants?”
• G_CONTEXT — a SQL condition you supply, evaluated per session (the lab keys off SESSION_USER so it captures exactly one account).
• G_ROLE_AND_CONTEXT — both, ANDed together.

flowchart TD
A["Grant accretes<br/>role PA_ROLE = 12 privileges"] --> B["CREATE_CAPTURE + ENABLE_CAPTURE<br/>scoped to APPUSER"]
B --> C["APPUSER runs a real workload<br/>log in · read CUSTOMERS · create own table"]
C --> D["DISABLE_CAPTURE + GENERATE_RESULT"]
D --> E{"For each granted privilege:<br/>was it exercised?"}
E -->|yes| U["DBA_USED_PRIVS<br/>CREATE SESSION · CREATE TABLE · SELECT on CUSTOMERS"]
E -->|no| N["DBA_UNUSED_PRIVS<br/>SELECT ANY TABLE · DROP ANY TABLE · CREATE ANY TABLE · …"]
N --> R["The revoke list — least privilege, from evidence not guesswork"]

The finding you’ll get almost every time: SELECT ANY TABLE, unused

There’s one result Privilege Analysis surfaces so reliably it’s worth calling out. When an account holds both a specific object grant (SELECT on one table) and the system privilege SELECT ANY TABLE, and it queries that table, Oracle authorizes the read with the object grant. The ANY privilege is never touched — so it lands in DBA_UNUSED_PRIVS.

That is the single most common over-grant in the wild: someone was handed the god-mode “read every table in the database” privilege when all they ever read was the two tables they were explicitly granted. Privilege Analysis catches it in black and white, which is exactly the ammunition you need to revoke it over the inevitable “but what if something breaks” objection. Nothing was using it. The evidence says so.

Now prove it

Grant a user a role that carries twelve privileges — ten system privileges including SELECT ANY TABLE, DROP ANY TABLE, CREATE ANY TABLE, and ALTER ANY TABLE, plus SELECT on two tables. Start a capture scoped to that user. Then have it do three ordinary things: log in, read one table it has an explicit grant on, and create one table in its own schema. Disable, generate, and read the result:

Three privileges did the work. Nine sat unused — every ANY privilege among them. SELECT ANY TABLE is unused because the one table the account read, it was explicitly granted; the read never needed the god-mode version. DROP ANY TABLE and friends are unused because the account never dropped or altered anything outside its own schema. SELECT on PAOWN.ORDERS is unused because the workload simply never touched that table. The least-privilege regrant writes itself: keep the three, revoke the nine.

Don’t take my word for it — run it. The Privilege Analysis lab stands up an Oracle Database Free container, grants APPUSER the 12-privilege role, enables a DBMS_PRIVILEGE_CAPTURE run scoped to it, executes the small workload, then generates the result and asserts the split: CREATE SESSION / CREATE TABLE / SELECT on CUSTOMERS come back used; SELECT ANY TABLE, DROP ANY TABLE, CREATE ANY TABLE, and the rest come back unused; and there are more unused than used. If the capture stops telling them apart, the run fails. It’s proven on every CI push.

What teams get wrong

• Guessing the revoke list instead of measuring it. The reason over-grants never get cleaned up is fear: nobody will revoke a privilege they can’t prove is unused. Privilege Analysis removes the fear — you revoke from a captured-usage report, not a hunch. Measure first, then cut.
• Too short a capture window. If you capture for an afternoon, month-end batch jobs, quarterly reports, and the annual archive purge never run — and their privileges look “unused” right up until you revoke them and break the quarter close. Capture across a representative period, and think about the rare-but-real jobs before you cut.
• Forgetting GENERATE_RESULT. Reading DBA_UNUSED_PRIVS and seeing nothing, then concluding the account used everything. The views are empty until you generate the result for the capture. Disable → generate → read.
• Revoking the privilege but leaving the role. If the unused privilege comes through a role, revoking it from the account does nothing — it’s still in the role. Privilege Analysis tells you how each privilege was granted (directly, or via which role); fix it at the grant path the report names, and remember the role is shared, so trimming it affects everyone who holds it.
• Treating one capture as forever. Usage changes: a new feature ships, a new report is added, and yesterday’s “unused” becomes today’s needed. Privilege Analysis is a periodic hygiene practice, not a one-time audit — recapture after significant change, and before you trust a stale result.
• Confusing it with auditing. Privilege Analysis tells you which privileges could be revoked; it is not a record of who did what — that’s unified auditing. Use analysis to shrink the grants, auditing to watch how the remaining ones get used. Different jobs.

Privilege Analysis is the layer of database security most shops skip because it feels impossible to do safely — so the grants only ever grow. It is the natural companion to the rest of the discipline: the hardening checklist sets the baseline for who can connect and how, VPD decides which rows each session sees, redaction narrows which values reach a screen, TDE protects the files, and unified auditing records who looked. Privilege Analysis works on the grants underneath all of them — shrinking what every account can do down to what it demonstrably needs to. Capture a representative window, generate the result, and revoke the unused pile with the evidence in hand. Then prove it the way that ends the argument: with the Privilege Analysis lab, where an account granted a dozen privileges is shown using just three.