Find unused privileges with privilege analysis

Command

-- 1) Capture privilege usage for a specific user:
BEGIN
  DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
    name        => 'CAP_APP_USER',
    description => 'Actual privilege usage of the application user',
    type        => DBMS_PRIVILEGE_CAPTURE.G_CONTEXT,
    condition   => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''CUSTOMER_APP''');
  DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE(name => 'CAP_APP_USER', run_name => 'RUN_2026_10');
END;
/
-- Other types: G_DATABASE (whole DB), G_ROLE (roles => role_name_list('DBA')), G_ROLE_AND_CONTEXT

-- 2) After a representative workload (including month end), stop and generate results:
BEGIN
  DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE(name => 'CAP_APP_USER');
  DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT(name => 'CAP_APP_USER', run_name => 'RUN_2026_10');
END;
/

-- 3) Used and never-used privileges:
SELECT username, sys_priv, obj_priv, object_owner, object_name, used_role
FROM   dba_used_privs   WHERE capture = 'CAP_APP_USER';
SELECT username, sys_priv, obj_priv, object_owner, object_name
FROM   dba_unused_privs WHERE capture = 'CAP_APP_USER';

-- 4) Through which role chain was the privilege used?
SELECT username, sys_priv, used_role, path
FROM   dba_used_sysprivs_path WHERE capture = 'CAP_APP_USER';

-- 5) Cleanup:
EXEC DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE(name => 'CAP_APP_USER');

What it does

Privilege analysis records at run time which system/object privileges are actually used within the chosen scope (whole database, role, condition); the result lists unused privileges so REVOKEs can be planned under the least-privilege principle.

When to use it

When cleaning up application accounts with DBA/ANY privileges, for privilege reviews before an audit, and to find out what a packaged application really needs.

Caution

Included in Enterprise Edition since 2018 (previously required a Database Vault license); no extra license. There is a small overhead while capturing. The result reflects only the workload in the capture window: if month-end, year-end or emergency jobs did not run, a privilege shown as "unused" may still be needed, so revoke gradually. Only one capture can be enabled at a time; the only exception is that a G_DATABASE capture can be enabled alongside one role or context capture.