Oracle DBMS_PRIVILEGE_CAPTURE
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Provides an API for database privilege analysis.
AUTHID DEFINER
Constants
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_PATH$
CDB_UNUSED_PRIVS DBA_USED_PRIVS PRIV_PROFILE_LIB
CDB_USED_PRIVS PRIV_CAPTURE$ ROLE_NAME_LIST
DBA_PRIV_CAPTURES PRIV_UNUSED$  
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.0
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
 
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 is to 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);
END;
/

desc dba_priv_captures

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

SELECT *
FROM dba_priv_captures;
 
DISABLE_CAPTURE
  dbms_privilege_capture.disable_capture(name IN VARCHAR2);
exec dbms_privilege_capture.disable_capture('UWPrivCapt');
 
DROP_CAPTURE
  dbms_privilege_capture.drop_capture(name IN VARCHAR2);
exec dbms_privilege_capture.drop_capture('UWPrivCapt');
 
ENABLE_CAPTURE
  dbms_privilege_capture.enable_capture(name IN VARCHAR2);
exec dbms_privilege_capture.enable_capture('UWPrivCapt');
 
GENERATE_RESULT
  dbms_privilege_capture.generate_result(name IN VARCHAR2);
exec dbms_privilege_capture.generate_result('UWPrivCapt');

SELECT * FROM dba_used_privs;

SELECT * FROM dba_unused_privs;

Related Topics
DBMS_PRIV_CAPTURE
Object Privileges
Packages
Profiles
Roles
Security
System Privileges

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved