Oracle DBMS_PRIV_CAPTURE
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. 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.
Purpose Capture privileges used in Oracle defined PL/SQL packages.

The purpose of this project, #32973, is to capture privileges used for an operation. Privileges checked in the kernel(e.g, through KZP layer) have been collected. However, many Oracle defined PL/SQL packages query privilege related dictionary tables/views(for example, session_privs, session_roles, sysauth$, objauth$, etc.) to check whether a user has a given privilege. For such cases, APIs in this package have been used to replace orginal check. For queries that cannot be replaced, privileges are collected directly by calling dbms_priv_capture.capture_privilege_use.
AUTHID CURRENT_USER
Dependencies
DBMSOBJG DBMS_REPCAT_RGT_CHK LBAC_SYSDBA
DBMS_AQADM_SYS DBMS_RULE_EXP_UTLI LBAC_UTL
DBMS_CDC_IPUBLISH DBMS_SCHED_JOB_EXPORT LOGMNR_EM_SUPPORT
DBMS_CSX_ADMIN DBMS_SCHED_MAIN_EXPORT LOGSTDBY_INTERNAL
DBMS_CUBE_ADVISE DBMS_SMB OBJ$
DBMS_CUBE_ADVISE_SEC DBMS_STATS OBJAUTH$
DBMS_DDL_INTERNAL DBMS_STREAMS_ADM_UTL_INVOK OLS_ENFORCEMENT
DBMS_EDITIONS_UTILITIES DBMS_STREAMS_PUB_RPC PRIV_CAPTURE$
DBMS_FILE_GROUP_IMP DBMS_UTILITY PRIV_PROFILE_LIB
DBMS_IREFRESH ISXMLTYPETABLE SESSION_PRIVS
DBMS_LOGREP_IMP KUPF$FILE SESSION_ROLES
DBMS_LOGREP_UTIL_INVOK KUPP$PROC SYSAUTH$
DBMS_METADATA KUPV$FT SYSTEM_PRIVILEGE_MAP
DBMS_PARALLEL_EXECUTE KUPV$FT_INT USER$
Documented No
First Available 12.1.0
Security Model Owned by SYS with EXECUTE granted to EXFSYS, LBACSYS, OLAPSYS, and XDB
Source {ORACLE_HOME}/rdbms/admin/catprofp.sql
Subprograms
 
CAPTURE_PRIVILEGE_USE
Capture a privilege usage, if a privilege capture condition is met. This procedure is called when a privilege is used in PL/SQL or JAVA.

Overload 1
dbms_priv_capture.capture_privilege_use(
userid  IN NUMBER,
syspriv IN NUMBER   DEFAULT NULL,
role    IN VARCHAR2 DEFAULT NULL,
objpriv IN NUMBER   DEFAULT NULL,
obj     IN NUMBER   DEFAULT NULL);
TBD
Overload 2 dbms_priv_capture.capture_privilege_use(
username IN VARCHAR2,
syspriv  IN VARCHAR2 DEFAULT NULL,
role     IN VARCHAR2 DEFAULT NULL,
objpriv  IN VARCHAR2 DEFAULT NULL,
owner    IN VARCHAR2 DEFAULT NULL,
object   IN VARCHAR2 DEFAULT NULL);
TBD
 
HAS_OBJ_PRIV
Determines whether the user has a given object privilege dbms_priv.capture.has_obj_priv(
username IN VARCHAR2,
objpriv  IN VARCHAR2,
objowner IN VARCHAR2,
objname  IN VARCHAR2)
RETURN NUMBER;
conn / as sysdba

SELECT user_id, username
FROM dba_users
WHERE username IN ('SYS', 'WMSYS');

SELECT *
FROM table_privilege_map;

SELECT object_id
FROM dba_objects
WHERE object_name = 'TAB$';

SELECT sys.dbms_priv_capture.has_obj_priv(59, 9, 0, 4)
FROM dual;
 
HAS_OBJ_PRIV_ID
  dbms_priv.capture.has_obj_priv_id(
l_user IN NUMBER,
l_priv IN NUMBER,
l_obj  IN NUMBER)
RETURN NUMBER;
conn / as sysdba

SELECT user_id
FROM dba_users
WHERE username = 'WMSYS';

SELECT *
FROM table_privilege_map;

SELECT object_id
FROM dba_objects
WHERE object_name = 'TAB$';

SELECT sys.dbms_priv_capture.has_obj_priv_id(59, 9, 4)
FROM dual;
 
HAS_SYS_PRIV
Determines whether the user as a given system privilege dbms_priv.capture.has_sys_priv_id(
username IN VARCHAR2,
syspriv  IN VARCHAR2)
RETURN NUMBER;
SELECT dbms_priv_capture.has_sys_priv('WMSYS', 'DROP ANY TRIGGER')
FROM dual;
 
HAS_SYS_PRIV_DIRECT
Determines whether the user has a directly granted system privilege dbms_priv_capture.has_sys_priv_direct(
username IN VARCHAR2,
syspriv  IN VARCHAR2)
RETURN NUMBER;
SELECT dbms_priv_capture.has_sys_priv_direct('WMSYS', 'DROP ANY TRIGGER')
FROM dual;
 
HAS_SYS_PRIV_DIRECT_ID
Determines whether the user has a directly granted system privilege dbms_priv_capture.has_sys_priv_direct_id(
userid  IN NUMBER,
syspriv IN NUMBER)
RETURN NUMBER;
SELECT user#
FROM user$
WHERE name = 'WMSYS';

SELECT privilege
FROM system_privilege_map
WHERE name = 'DROP ANY TRIGGER';

SELECT dbms_priv_capture.has_sys_priv_direct_id(59, -154)
FROM dual;
 
HAS_SYS_PRIV_ID
Deermines whether the user as a given system privilege dbms_priv_capture.has_sys_priv_id(
userid  IN NUMBER,
syspriv IN NUMBER)
RETURN NUMBER;
SELECT privilege
FROM dba_sys_privs
WHERE grantee = 'WMSYS';

SELECT user#
FROM user$
WHERE name = 'WMSYS';

SELECT privilege
FROM system_privilege_map
WHERE name = 'DROP ANY TRIGGER';

SELECT dbms_priv_capture.has_sys_priv_id(59, -154)
FROM dual;
 
SES_HAS_ROLE_PRIV
Determines whether the current user has a given role dbms_priv_capture.ses_has_role_priv(rolename IN VARCHAR2) RETURN NUMBER;
SELECT dbms_priv_capture.ses_has_role_priv('DBHADOOP')
FROM dual;
 
SES_HAS_SYS_PRIV
Determines whether the current user has a given system privilege dbms_priv_capture.ses_has_sys_priv(syspriv IN VARCHAR2) RETURN NUMBER;
SELECT dbms_priv_capture.ses_has_sys_priv('CREATE TABLE')
FROM dual;

Related Topics
DBMS_PRIVILEGE_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