DBSecWorx: DBMS_PRIVILEGE

Oracle DBMS_PRIVILEGE_CAPTURE Built-In Package Versions 12.1 - 19.3

Security Advisory

DBMS_PRIVILEGE is included in Enterprise Edition and, for use, additionally requires licensing for Database Vault as of this writing. The package intended for use by database administrators and to provide a tool for those interested that need to understand how and when escalated privileges are used. The demo below makes a single query of DBA_USED_OBJPRIVS using limited columns: You should take a far more thorough look at the information collected there and in all of the views listed below as "Dependent Objects."

This package should be used to make privilege use tracking easier and to provide the metadata required to perform privilege analysis with the target being to allow for revocation of unnecessary privileges and attain a least privilege state. Keep in mind that when Oracle users the phrase "least privilege state" their concept is far different from ours. We remain appalled that a view named ALL_SOURCE was ever created and continues to exist.

Recommended Security Rules

NEVER

Grant EXECUTE on this package to any user not specifically tasked with a project that requires privilege analysis and how it is being used.
Grant the CAPTURE_ADMIN role to any user not specifically tasked with a project that requires privilege analysis and how it is being used.

WITH GREAT CARE

CAUTIONS

If you are not using Enterprise Edition to not call this package or any of its components.

How Oracle Works

Putting the package's objects together in a usable sequence.

The following demo should be performed in two different SQL*Plus sessions. The session in the out text area starts with a login by SYS and should remain there.
      A small inner text area, darker, uses a login by a user with no privilege other than CREATE SESSION to which DBA and SELECT_CATALOG_ROLE are granted solely for purposes of this demonstration. The escalated privileges are dropped at the end of the demo.

      

      The demo intentionally generates an exception, highlighted in red to show what you cannot do. Result generation must following disabling privilege capture. Disabling privilege capture must also precede dropping a capture.

      

      conn sys@pdbdev as sysdba

      

      GRANT create session TO uwclass;

      GRANT dba TO uwclass;

      GRANT select_catalog_role TO uwclass;

      

      col name format a15

      col description format a14

      col roles format a30

      col context format a55

      

      

      SQL> SELECT * FROM dba_priv_captures;

      

      NAME            DESCRIPTION     TYPE             E  ROLES  CONTEXT  RUN_NAME

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

      ORA$DEPENDENCY                 DATABASE          N

      

      

      DECLARE

       rlist role_name_list;

      BEGIN

        rlist := sys.role_name_list(NULL);

        rlist(1) := 'DBA';

        rlist.extend;

        rlist(2) := 'SELECT_CATALOG_ROLE';

      

        dbms_privilege_capture.create_capture(

         name => 'DBSWPC',

         description => 'PrivCapt. Demo',

         type => dbms_privilege_capture.g_role_and_context,

         roles => rlist,

         condition => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') <> ''SYSTEM''');

      END;

      /

      

      PL/SQL procedure successfully completed.

      

      SQL> SELECT name, description, type, enabled, roles

        2  FROM dba_priv_captures;

      

      NAME            DESCRIPTION    TYPE              E  ROLES

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

      DBSWPC          PrivCapt.Demo  ROLE_AND_CONTEXT  N  ROLE_ID_LIST(4, 11)

      ORA$DEPENDENCY                 DATABASE          N

      

      SQL> SELECT name, context

        2  FROM dba_priv_captures;

      

      NAME            CONTEXT

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

      DBSWPC          SYS_CONTEXT('USERENV', 'SESSION_USER') <> 'SYSTEM'

      ORA$DEPENDENCY

      

      SQL> exec dbms_privilege_capture.capture_dependency_privs;

      

      PL/SQL procedure successfully completed.

      

      SQL> exec dbms_privilege_capture.enable_capture('DBSWPC');

      

      PL/SQL procedure successfully completed.

      

      SQL> SELECT * FROM dba_priv_captures;

      

      NAME            DESCRIPTION     TYPE             E 

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

      DBSWPC          PrivCapt. Demo  DATABASE         Y

      ORA$DEPENDENCY                  DATABASE         N

      

      
        
          SQL> conn uwclass/uwclass@pdbdev

          

          SQL> SELECT COUNT(*) FROM sys.obj$

          SQL> SELECT view_name FROM dba_views;

          SQL> CREATE TABLE t AS SELECT * FROM dba_tables;

          SQL> exec dbms_stats.gather_fixed_objects_stats;
        
      


      SQL> exec dbms_privilege_capture.generate_result('DBSWPC');

      BEGIN dbms_privilege_capture.generate_result('DBSWPC'); END;

      *

      ERROR at line 1:

      ORA-47932: Privilege capture DBSWPC is still enabled.

      ORA-06512: at "SYS.DBMS_PRIVILEGE_CAPTURE", line 61

      ORA-06512: at line 1

      

      SQL> exec dbms_privilege_capture.disable_capture('DBSWPC');

      

      PL/SQL procedure successfully completed.

      

      SQL> set timing on

      SQL> exec dbms_privilege_capture.generate_result('DBSWPC');

      

      PL/SQL procedure successfully completed.

      

      Elapsed: 00:00:03.55

      SQL> set timing off

      

      col object_owner format a15

      col obj_priv format a15

      

      SELECT username, object_owner, object_name, obj_priv

      FROM dba_used_objprivs

      WHERE  capture = 'DBSWPC';

      

      USERNAME  OBJECT_OWNER  OBJECT_NAME                OBJ_PRIV

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

      UWCLASS   SYS           DBA_VIEWS                  SELECT

      UWCLASS   SYS           AUX_STATS$                 DELETE

      UWCLASS   SYS           AUX_STATS$                 SELECT

      UWCLASS   SYS           WRI$_OPTSTAT_AUX_HISTORY   UPDATE

      UWCLASS   SYS           AUX_STATS$                 INSERT

      UWCLASS   SYS           WRI$_OPTSTAT_AUX_HISTORY   SELECT

      UWCLASS   SYS           AUX_STATS$                 UPDATE

      

      SQL>
      exec dbms_privilege_capture.drop_capture('DBSWPC');

-- the rows in dba_used_objprivs are deleted when the Capture is dropped.



      

      SQL> DROP TABLE uwclass.t PURGE;

      SQL> REVOKE dba FROM uwclass;

      SQL> REVOKE select_any_catalog FROM uwclass;

      

      
      Any security considerations jump out based on this exercise?

      

      First, and obviously the person that can create a privilege capture can get a good look at what other people are doing and with which privileges.

      

      Second, EXECUTE on the package is granted to two different roles. You likely have good reason to know why you should not grant DBA to anyone that asks but are you familiar with the CAPTURE_ADMIN role?

DBMS_PRIVILEGE_CAPTURE Package Information

AUTHID DEFINER

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_SYSPRIVS_PATH	DBA_USED_USERPRIVS
CDB_UNUSED_PRIVS	DBA_UNUSED_USERPRIVS	DBA_USED_USERPRIVS_PATH
CDB_USED_PRIVS	DBA_UNUSED_USERPRIVS_PATH	PRIV_CAPTURE$
DBA_PRIV_CAPTURES	DBA_USED_OBJPRIVS_PATH	PRIV_PROFILE_LIB
DBA_UNUSED_OBJPRIVS	DBA_USED_PRIVS	PRIV_UNUSED$
DBA_UNUSED_OBJPRIVS_PATH	DBA_USED_PUBPRIVS	PRIV_UNUSED_PATH$
DBA_UNUSED_PRIVS	DBA_USED_SYSPRIVS	PRIV_PROFILE_LIB
DBA_UNUSED_SYSPRIVS	DBA_USED_SYSPRIVS_PATH	ROLE_NAME_LIST

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

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

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

conn sys@pdbdev as sysdba

      

      SELECT * FROM dba_role_privs WHERE grantee = 'SCOTT';

      

      no rows selected

      

      GRANT dba TO scott;

      

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

                                              'Test policy',

                                              dbms_privilege_capture.g_role,

                                              rlist,

                                              NULL);

        dbms_privilege_capture.enable_capture('UWPrivCapt2');

        dbms_privilege_capture.disable_capture('UWPrivCapt2');

        dbms_lock.sleep(90);

        dbms_privilege_capture.generate_result('UWPrivCapt2');

      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 a20

      

      SELECT *

      FROM dba_priv_captures

      WHERE name = 'UWPrivCapt';

      

      NAME        DESCRIPTION          
      TYPE     E ROLES 

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

      UWPrivCapt  Test policy          
      ROLE     N ROLE_ID_LIST(2, 11)

      

      SELECT username, object_owner, object_name, obj_priv

      FROM dba_used_objprivs

      WHERE username = 'SCOTT';

      

      no rows selected

      

      REVOKE dba FROM scott;

      

      GRANT select ON tab$ TO scott;

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;