Oracle | DBMS_PRIV

Oracle DBMS_PRIV_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 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 original check. For queries that cannot be replaced, privileges are collected directly by calling dbms_priv_capture.capture_privilege_use.

AUTHID CURRENT_USER

Dependencies

DBMS_AQADM_SYS	DBMS_PARALLEL_EXECUTE	ISXMLTYPETABLE
DBMS_AQJMS	DBMS_RULE_EXP_UTLI	KUPF$FILE
DBMS_CMP_INT	DBMS_SCHED_ARGUMENT_IMPORT	KUPP$PROC
DBMS_COMPARISON	DBMS_SCHED_JOB_EXPORT	KUPV$FT
DBMS_COMPRESSION	DBMS_SCHED_MAIN_EXPORT	KUPW$WORKER
DBMS_CSX_ADMIN	DBMS_SMB	LBAC_EXP
DBMS_CUBE	DBMS_SNAPSHOT_COMMON	LBAC_SERVICES
DBMS_CUBE_ADVISE	DBMS_SQLTUNE	LBAC_SYSDBA
DBMS_DATAPUMP	DBMS_STATS	LOGMNR_EM_SUPPORT
DBMS_DATA_MINING	DBMS_STATS_ADVISOR	LOGSTDBY_INTERNAL
DBMS_DDL	DBMS_STREAMS_ADM_UTL_INVOK	OLS_ENFORCEMENT
DBMS_EDITIONS_UTILITIES	DBMS_STREAMS_PUB_RPC	PRIV_CAPTURE$
DBMS_FILE_GROUP	DBMS_TRANSFORM	PRIV_PROFILE_LIB
DBMS_FILE_GROUP_IMP	DBMS_WRR_PROTECTED	RDF_APIS
DBMS_HADOOP	DBMS_XDB_CONFIG	ROLENAME_ARRAY
DBMS_HEAT_MAP	DRIACC	ROLE_ARRAY
DBMS_HPROF	DRIIMP	SDO_RDF
DBMS_ILM	DRIXMD	SDO_RDF_INTERNAL
DBMS_LOGREP_IMP	DRVDDL	SEM_RDFSA_DR
DBMS_LOGREP_UTIL_INVOK	DRVXMD	XS_DATA_SECURITY_UTIL
DBMS_METADATA

Documented No

First Available 12.1

Security Model Owned by SYS with EXECUTE granted to CTXSYS, LBACSYS, MDSYS, and XDB

Source {ORACLE_HOME}/rdbms/admin/catprofp.sql

Subprograms

CAPTURE_PRIVILEGE_USE	SES_HAS_ROLE_PRIV	SES_HAS_SYS_PRIV
SES_HAS_OBJ_PRIV

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

SES_HAS_OBJ_PRIV

Checks whether the current user has a given object privilege dbms_priv_capture.ses_has_obj_priv( objpriv IN VARCHAR2, objowner IN VARCHAR2, objname IN VARCHAR2, nmspace IN PLS_INTEGER DEFAULT 1) RETURN BOOLEAN;

conn sys@pdbdev as sysdba

      

      CREATE TABLE uwclass.test as select * from user_objects;

      

      Table created.

      

      BEGIN

        IF dbms_priv_capture.ses_has_obj_priv('SELECT', 'UWCLASS', 'TEST') THEN

          dbms_output.put_line('T');

        ELSE

          dbms_output.put_line('F');

        END IF;

      END;

      /

      F

      

      PL/SQL procedure successfully completed.

      

      conn uwclass/uwclass@pdbdev

      

      GRANT select ON uwclass.test TO sys;

      

      Grant succeeded.

      

      conn sys@pdbdev as sysdba

      

      BEGIN

        IF dbms_priv_capture.ses_has_obj_priv('SELECT', 'UWCLASS', 'TEST') THEN

          dbms_output.put_line('T');

        ELSE

          dbms_output.put_line('F');

        END IF;

      END;

      /

      T

SES_HAS_ROLE_PRIV

Determines whether the current user has a given role dbms_priv_capture.ses_has_role_priv(rolename IN VARCHAR2) RETURN PLS_INTEGER;

SELECT dbms_priv_capture.ses_has_role_priv('DBHADOOP')

      FROM dual;

      

      DBMS_PRIV_CAPTURE.SES_HAS_ROLE_PRIV('DBHADOOP')

      -----------------------------------------------

                                                    
      0

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 PLS_INTEGER;

SELECT dbms_priv_capture.ses_has_sys_priv('CREATE TABLE')

      FROM dual;

      

      DBMS_PRIV_CAPTURE.SES_HAS_SYS_PRIV('CREATETABLE')

      -------------------------------------------------

                                                      
      1