Oracle | DBMS_PRIVILEGE

Oracle DBMS_PRIVILEGE_CAPTURE Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Provides an API for database privilege analysis. Requires Oracle Enterprise Edition and the Data Vault license.

AUTHID CURRENT_USER

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_PRIVS	PRIV_UNUSED$
CDB_UNUSED_PRIVS	DBA_USED_PRIVS	PRIV_UNUSED_PATH$
CDB_USED_PRIVS	PRIV_CAPTURE$	PRIV_PROFILE_LIB
DBA_PRIV_CAPTURES	PRIV_PROFILE_LIB	ROLE_NAME_LIST

Documented Yes: Packages and Types Reference

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	DISABLE_CAPTURE	GENERATE_RESULT
CREATE_CAPTURE	DROP_CAPTURE	Demo
DELETE_RUN	ENABLE_CAPTURE

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 parameter evaluates to TRUE

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('UWPrivCapt',

                                              'Test policy',

                                              dbms_privilege_capture.g_role,

                                              rlist,

                                              NULL);

        dbms_privilege_capture.enable_capture('UWPrivCapt');

        dbms_privilege_capture.disable_capture('UWPrivCapt');

        dbms_privilege_capture.generate_result('UWPrivCapt');

      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 a15

      

      SELECT *

      FROM dba_priv_captures

      WHERE name = 'UWPrivCapt';

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;

Demo

This functional demonstration shows how to put the pieces together conn sys@pdbdev as sysdba BEGIN dbms_privilege_capture.create_capture('UWPrivCapt','Morgan''s Library Privilege Capture Demo', dbms_privilege_capture.g_database); dbms_privilege_capture.enable_capture('UWPrivCapt'); dbms_privilege_capture.disable_capture('UWPrivCapt'); dbms_privilege_capture.generate_result('UWPrivCapt'); END; / col object_owner format a15 col obj_priv format a15 SELECT username, object_owner, object_name, obj_priv FROM dba_used_objprivs WHERE capture = 'UWPrivCapt'; exec dbms_privilege_capture.drop_capture('UWPrivCapt'); -- the rows in dba_used_objprivs are deleted when the Capture is dropped.