Oracle DBMS_PRIVILEGE_CAPTURE Built-In Package
Versions 12.1 - 19.3

Security Advisory
DBMS_PRIVILEGE is included in Enterprise Edition and, for use, additionally requires licensing for Database Vault as of this writing. The package intended for use by database administrators and to provide a tool for those interested that need to  understand how and when escalated privileges are used. The demo below makes a single query of DBA_USED_OBJPRIVS using limited columns: You should take a far more thorough look at the information collected there and in all of the views listed below as "Dependent Objects."

This package should be used to make privilege use tracking easier and to provide the metadata required to perform privilege analysis with the target being to allow for revocation of unnecessary privileges and attain a least privilege state. Keep in mind that when Oracle users the phrase "least privilege state" their concept is far different from ours. We remain appalled that a view named ALL_SOURCE was ever created and continues to exist.
 
Recommended Security Rules

 NEVER
  • Grant EXECUTE on this package to any user not specifically tasked with a project that requires privilege analysis and how it is being used.
  • Grant the CAPTURE_ADMIN role to any user not specifically tasked with a project that requires privilege analysis and how it is being used.
 WITH GREAT CARE
  • N/A
 CAUTIONS
  • If you are not using Enterprise Edition to not call this package or any of its components.
 
How Oracle Works
Putting the package's objects together in a usable sequence. The following demo should be performed in two different SQL*Plus sessions. The session in the out text area starts with a login by SYS and should remain there. A small inner text area, darker, uses a login by a user with no privilege other than CREATE SESSION to which DBA and SELECT_CATALOG_ROLE are granted solely for purposes of this demonstration. The escalated privileges are dropped at the end of the demo.

The demo intentionally generates an exception, highlighted in red to show what you cannot do. Result generation must following disabling privilege capture. Disabling privilege capture must also precede dropping a capture.


conn sys@pdbdev as sysdba

GRANT create session TO uwclass;
GRANT dba TO uwclass;
GRANT select_catalog_role TO uwclass;

col name format a15
col description format a14
col roles format a30
col context format a55


SQL> SELECT * FROM dba_priv_captures;

NAME            DESCRIPTION     TYPE             E  ROLES  CONTEXT  RUN_NAME
--------------- -------------- ----------------- -- ------ -------- ----------
ORA$DEPENDENCY                 DATABASE          N


DECLARE
 rlist role_name_list;
BEGIN
  rlist := sys.role_name_list(NULL);
  rlist(1) := 'DBA';
  rlist.extend;
  rlist(2) := 'SELECT_CATALOG_ROLE';

  dbms_privilege_capture.create_capture(
   name => 'DBSWPC',
   description => 'PrivCapt. Demo',
   type => dbms_privilege_capture.g_role_and_context,
   roles => rlist,
   condition => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') <> ''SYSTEM''');
END;
/

PL/SQL procedure successfully completed.

SQL> SELECT name, description, type, enabled, roles
  2  FROM dba_priv_captures;

NAME            DESCRIPTION    TYPE              E  ROLES
--------------- -------------- ----------------- -- --------------------
DBSWPC          PrivCapt.Demo  ROLE_AND_CONTEXT  N  ROLE_ID_LIST(4, 11)
ORA$DEPENDENCY                 DATABASE          N

SQL> SELECT name, context
  2  FROM dba_priv_captures;

NAME            CONTEXT
--------------- ---------------------------------------------------
DBSWPC          SYS_CONTEXT('USERENV', 'SESSION_USER') <> 'SYSTEM'
ORA$DEPENDENCY

SQL> exec dbms_privilege_capture.capture_dependency_privs;

PL/SQL procedure successfully completed.

SQL> exec dbms_privilege_capture.enable_capture('DBSWPC');

PL/SQL procedure successfully completed.

SQL> SELECT * FROM dba_priv_captures;

NAME            DESCRIPTION     TYPE             E
--------------- --------------- ---------------- --
DBSWPC          PrivCapt. Demo  DATABASE         Y
ORA$DEPENDENCY                  DATABASE         N

SQL> conn uwclass/uwclass@pdbdev

SQL> SELECT COUNT(*) FROM sys.obj$
SQL> SELECT view_name FROM dba_views;
SQL> CREATE TABLE t AS SELECT * FROM dba_tables;
SQL> exec dbms_stats.gather_fixed_objects_stats;

SQL> exec dbms_privilege_capture.generate_result('DBSWPC');
BEGIN dbms_privilege_capture.generate_result('DBSWPC'); END;
*
ERROR at line 1:
ORA-47932: Privilege capture DBSWPC is still enabled.
ORA-06512: at "SYS.DBMS_PRIVILEGE_CAPTURE", line 61
ORA-06512: at line 1


SQL> exec dbms_privilege_capture.disable_capture('DBSWPC');

PL/SQL procedure successfully completed.

SQL> set timing on
SQL> exec dbms_privilege_capture.generate_result('DBSWPC');

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.55
SQL> set timing off

col object_owner format a15
col obj_priv format a15

SELECT username, object_owner, object_name, obj_priv
FROM dba_used_objprivs
WHERE  capture = 'DBSWPC';

USERNAME  OBJECT_OWNER  OBJECT_NAME                OBJ_PRIV
--------- ------------- -------------------------- ---------
UWCLASS   SYS           DBA_VIEWS                  SELECT
UWCLASS   SYS           AUX_STATS$                 DELETE
UWCLASS   SYS           AUX_STATS$                 SELECT
UWCLASS   SYS           WRI$_OPTSTAT_AUX_HISTORY   UPDATE
UWCLASS   SYS           AUX_STATS$                 INSERT
UWCLASS   SYS           WRI$_OPTSTAT_AUX_HISTORY   SELECT
UWCLASS   SYS           AUX_STATS$                 UPDATE

SQL> exec dbms_privilege_capture.drop_capture('DBSWPC');

-- the rows in dba_used_objprivs are deleted when the Capture is dropped.

SQL> DROP TABLE uwclass.t PURGE;
SQL> REVOKE dba FROM uwclass;
SQL> REVOKE select_any_catalog FROM uwclass;

Any security considerations jump out based on this exercise?

First, and obviously the person that can create a privilege capture can get a good look at what other people are doing and with which privileges.

Second, EXECUTE on the package is granted to two different roles. You likely have good reason to know why you should not grant DBA to anyone that asks but are you familiar with the CAPTURE_ADMIN role?
 
DBMS_PRIVILEGE_CAPTURE Package Information
AUTHID DEFINER
Constants

For usage see CREATE_CAPTURE below
Name Data Type Value
G_DATABASE NUMBER 1
G_ROLE NUMBER 2
G_CONTEXT NUMBER 3
G_ROLE_AND_CONTEXT NUMBER 4
Data Types SQL> desc sys.role_name_list
sys.role_name_list VARRAY(10) OF VARCHAR2(128)
Dependencies
CDB_PRIV_CAPTURES DBA_UNUSED_SYSPRIVS_PATH DBA_USED_USERPRIVS
CDB_UNUSED_PRIVS DBA_UNUSED_USERPRIVS DBA_USED_USERPRIVS_PATH
CDB_USED_PRIVS DBA_UNUSED_USERPRIVS_PATH PRIV_CAPTURE$
DBA_PRIV_CAPTURES DBA_USED_OBJPRIVS_PATH PRIV_PROFILE_LIB
DBA_UNUSED_OBJPRIVS DBA_USED_PRIVS PRIV_UNUSED$
DBA_UNUSED_OBJPRIVS_PATH DBA_USED_PUBPRIVS PRIV_UNUSED_PATH$
DBA_UNUSED_PRIVS DBA_USED_SYSPRIVS PRIV_PROFILE_LIB
DBA_UNUSED_SYSPRIVS DBA_USED_SYSPRIVS_PATH ROLE_NAME_LIST
Documented Yes
Exceptions
Error Code Reason
ORA-47937 Input condition does not match the given privilege capture type.
ORA-47951 Invalid input value or length for parameter 'condition'.
First Available 12.1
Security Model Owned by SYS with EXECUTE granted to the CAPTURE_ADMIN role. The CAPTURE_ADMIN role is also granted to the DBA role.
Source {ORACLE_HOME}/rdbms/admin/catprofp.sql
Subprograms
 
CAPTURE_DEPENDENCY_PRIVS
Captures the privileges that are used by definer’s rights and invoker’s rights PL/SQL program units for compilation dbms_privilege_capture.dependency_privs;
exec dbms_privilege_capture.capture_dependency_privs;
 
CREATE_CAPTURE
Creates a privilege analysis policy to show privilege use by database users. It also optionally specifies the roles for which privilege use is to be analyzed, and the conditions under which privilege use will be analyzed dbms_privilege_capture.create_capture(
name        IN VARCHAR2,
description IN VARCHAR2       DEFAULT NULL,
type        IN NUMBER         DEFAULT G_DATABASE,
roles       IN role_name_list DEFAULT role_name_list(),
condition   IN VARCHAR2       DEFAULT NULL);


Type Description
g_database Captures all privilege use, except privileges used by SYS
g_role Captures privilege use for the specified roles
g_context Captures privilege use when the condition parameter evaluates to TRUE
g_role_and_context Captures privilege use for the specified roles when the condition evaluates to TRUE
conn sys@pdbdev as sysdba

SELECT * FROM dba_role_privs WHERE grantee = 'SCOTT';

no rows selected

GRANT dba TO scott;

DECLARE
 rlist role_name_list;
BEGIN
  rlist := role_name_list(NULL);
  rlist(1) := 'CONNECT';
  rlist.extend;
  rlist(2) := 'EXECUTE_CATALOG_ROLE';

  dbms_privilege_capture.create_capture('UWPrivCapt2',
                                        'Test policy',
                                        dbms_privilege_capture.g_role,
                                        rlist,
                                        NULL);
  dbms_privilege_capture.enable_capture('UWPrivCapt2');
  dbms_privilege_capture.disable_capture('UWPrivCapt2');
  dbms_lock.sleep(90);
  dbms_privilege_capture.generate_result('UWPrivCapt2');
END;
/

desc dba_priv_captures

col name format a20
col description format a20
col roles format a30
col context format a20
col run_name format a20

SELECT *
FROM dba_priv_captures
WHERE name = 'UWPrivCapt';

NAME        DESCRIPTION          TYPE     E ROLES
----------- -------------------- -------- - -------------------------
UWPrivCapt  Test policy          ROLE     N ROLE_ID_LIST(2, 11)

SELECT username, object_owner, object_name, obj_priv
FROM dba_used_objprivs
WHERE username = 'SCOTT';

no rows selected

REVOKE dba FROM scott;

GRANT select ON tab$ TO scott;
 
 
DELETE_RUN
Deletes a privilege analysis capture run dbms_privilege_capture.delete_run(
name     IN VARCHAR2,
run_name IN VARCHAR2);
SELECT *
FROM dba_privs_captures
ORDER BY 1;

exec dbms_privilege_capture.delete_run('UWPrivCapt', 'ORA$DEPENDENCY');
 
DISABLE_CAPTURE
Disables a capture dbms_privilege_capture.disable_capture(name IN VARCHAR2);
exec dbms_privilege_capture.disable_capture('UWPrivCapt');
 
DROP_CAPTURE
Drops a capture dbms_privilege_capture.drop_capture(name IN VARCHAR2);
exec dbms_privilege_capture.drop_capture('UWPrivCapt');
 
ENABLE_CAPTURE
Enables a capture dbms_privilege_capture.enable_capture(
name     IN VARCHAR2,
run_name IN VARCHAR2 DEFAULT NULL);
exec dbms_privilege_capture.enable_capture('UWPrivCapt');
 
GENERATE_RESULT
Generates capture results dbms_privilege_capture.generate_result(
name       IN VARCHAR2,
run_name   IN VARCHAR2 DEFAULT NULL,
depencency IN BOOLEAN  DEFAULT NULL);
exec dbms_privilege_capture.generate_result('UWPrivCapt');

SELECT * FROM dba_used_privs;

SELECT * FROM dba_unused_privs;

Related Topics
DBMS_PRIV_CAPTURE
Object Privileges
System Privileges