Oracle DBMS_PRIV_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 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_RULE_EXP_UTLI LOGMNR_EM_SUPPORT
DBMS_AQADM_SYS DBMS_SCHED_JOB_EXPORT LOGSTDBY_INTERNAL
DBMS_CDC_IPUBLISH DBMS_SCHED_MAIN_EXPORT OBJ$
DBMS_CSX_ADMIN DBMS_SMB OBJAUTH$
DBMS_CUBE_ADVISE DBMS_STATS OLS_ENFORCEMENT
DBMS_CUBE_ADVISE_SEC DBMS_STREAMS_ADM_UTL_INVOK PLITBLM
DBMS_DDL_INTERNAL DBMS_STREAMS_PUB_RPC PRIV_CAPTURE$
DBMS_EDITIONS_UTILITIES DBMS_UTILITY PRIV_PROFILE_LIB
DBMS_FILE_GROUP_IMP ISXMLTYPETABLE ROLENAME_ARRAY
DBMS_IREFRESH KUPF$FILE SESSION_PRIVS
DBMS_LOGREP_IMP KUPP$PROC SESSION_ROLES
DBMS_LOGREP_UTIL_INVOK KUPV$FT SYSAUTH$
DBMS_METADATA KUPV$FT_INT SYSTEM_PRIVILEGE_MAP
DBMS_PARALLEL_EXECUTE LBAC_SYSDBA TABLE_PRIVILEGE_MAP
DBMS_REPCAT_RGT_CHK LBAC_UTL 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,
domain     IN role_array     DEFAULT NULL,
domain_str IN rolename_array 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,
domain     IN role_array     DEFAULT NULL,
domain_str IN rolename_array 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,
usepublic IN BOOLEAN DEFAULT TRUE,
nmspace   IN NUMBER  DEFAULT 1)
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_DIRECT
Checks only privileges directly granted to a username and returns 1 if the privilege exists, 0 otherwise dbms_priv_capture.has_obj_priv_direct(
username IN VARCHAR2,
objpriv  IN VARCHAR2,
objowner IN VARCHAR2,
obj_name IN VARCHAR2,
nmspacev IN NUMBER DEFAULT 1)
RETURN NUMBER;
DECLARE
 n NUMBER;
BEGIN
  n := dbms_priv_capture.has_obj_priv_direct('SYS', 'SELECT', 'SYSTEM', 'APP_ROLE_MEMBERSHIP');
  dbms_output.put_line(TO_CHAR(n));
END;
/
 
HAS_OBJ_PRIV_DIRECT_ID
Checks privileges directly granted to "l_user" and returns 1 if the privilege exists, 0 otherwise. dbms_priv_capture.has_obj_priv_direct_id(
l_user IN NUMBER,
l_priv IN NUMBER,
l_obj  IN NUMBER)
RETURN NUMBER;
SQL> SELECT user_id
   2 FROM dba_users
   3 WHERE username = 'SYS';

   USER_ID
----------
         0

SQL> SELECT privilege
2 FROM table_privilege_map
3 WHERE name = 'SELECT';

 PRIVILEGE
----------
         9
SQL> SELECT object_id
   2 FROM dba_objects
   3 WHERE object_name = 'APP_ROLE_MEMBERSHIP';

OBJECT_ID
----------
     19260

DECLARE
 n NUMBER;
BEGIN
  n := dbms_priv_capture.has_obj_priv_direct_id(0, 9, 19260);
  dbms_output.put_line(TO_CHAR(n));
END;
/
 
HAS_OBJ_PRIV_ID
Checks both privileges directly granted to "l_user" and privileges indirectly granted to one of the roles "l_user" has. If usepublic=TRUE, privileges directly and indirectly granted to PUBLIC will also be checked. Return 1 if the privilege exists, 0 otherwise. dbms_priv.capture.has_obj_priv_id(
l_user    IN NUMBER,
l_priv    IN NUMBER,
l_obj     IN NUMBER,
usepublic IN BOOLEAN DEFAULT TRUE)
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_ROLE_PRIV
 

Checks both roles directly granted to "userid" and roles indirectly granted to one of the roles "userid" has. If usepublic=TRUE, roles directly and indirectly granted to PUBLIC are also  checked.

dbms_priv.capture.has_role_priv_direct(
userid    IN NUMBER,
roleid    IN NUMBER,
usepublic IN BOOLEAN DEFAULT TRUE)
RETURN NUMBER;
SELECT dbms_priv_capture.has_role_priv_direct(
 
HAS_ROLE_PRIV_DIRECT
Checks for a direct grant of a role to user. Returns 1 if role is the granted to user, 0 otherwise. dbms_priv.capture.has_role_priv_direct(
username IN VARCHAR2,
rolename IN VARCHAR2)
RETURN NUMBER;
SELECT dbms_priv_capture.has_role_priv_direct('SYS', 'DBA')
FROM dual;
 
HAS_ROLE_PRIV_DIRECT_ID
Checks for a direct grant of a role to user. Returns 1 if role is the granted to user, 0 otherwise. dbms_priv.capture.has_role_priv_direct_id(
userid IN NUMBER,
roleid IN NUMBER)
RETURN NUMBER;
SELECT dbms_priv_capture.has_role_priv_direct_id(0, 4)
FROM dual;
 
HAS_ROLE_PRIV_ID
Checks both roles directly granted to "userid"
and roles indirectly granted to one of the roles "userid" has. If usepublic=TRUE, roles directly and indirectly granted to PUBLIC are also checked.
dbms_priv.capture.has_role_priv_id(
userid    IN NUMBER,
roleid    IN NUMBER,
usepublic IN BOOLEAN DEFAULT TRUE)
RETURN NUMBER;
SELECT dbms_priv_capture.has_role_priv_id(8, 24)
FROM dual;

DECLARE
 n NUMBER;
BEGIN
  n := dbms_priv_capture.has_role_priv_id(8, 4, FALSE);
  dbms_output.put_line(TO_CHAR(n));
END;
/
 
HAS_SYS_PRIV
Determines whether the user as a given system privilege dbms_priv.capture.has_sys_priv(
username  IN VARCHAR2,
syspriv   IN VARCHAR2,
usepublic IN BOOLEAN DEFAULT TRUE)
RETURN NUMBER;
SELECT dbms_priv_capture.has_sys_priv('WMSYS', 'DROP ANY TRIGGER')
FROM dual;
 
HAS_SYS_PRIV_DIRECT
Checks for a direct granted system privilege. Returns 1 if the privilege exists, 0 otherwise. 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
Checks for a direct granted system privilege. Returns 1 if the privilege exists, 0 otherwise. 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,
usepublic IN BOOLEAN DEFAULT TRUE)
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(systempriv 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