DBMS_PRIVILEGE_CAPTURE

The DBMS_PRIVILEGE_CAPTURE package provides an interface 
to database privilege analysis.

References:
http://docs.oracle.com/database/121/DVADM/priv_analysis.htm#DVADM591
http://docs.oracle.com/database/121/ARPLS/d_priv_prof.htm#ARPLS74328
https://oracle-base.com/articles/12c/capture-privilege-usage-12cr1

Tested on:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit

1.) Create user to be analyzed:

create user checkprivs identified by test;
grant dba to checkprivs;
grant resource to checkprivs;
grant connect to checkprivs;

2.) Creating the capture:

BEGIN
  DBMS_PRIVILEGE_CAPTURE.create_capture(
    name        => 'CHECK_PRIVS',
    type        => DBMS_PRIVILEGE_CAPTURE.g_role_and_context,
    roles       => role_name_list('DBA', 'RESOURCE'),
    condition   => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''CHECKPRIVS'''
  );

  DBMS_PRIVILEGE_CAPTURE.enable_capture(
    name        => 'CHECK_PRIVS'
  );
END;
/

3.) Some commands:

conn checkprivs/test;

create table check1 
(num number);

insert into check1 values (1);
commit;

select * from v$database;

truncate table check1;

insert into check1 values (2);
commit;

alter table check1 add (dt date);

update check1 set dt=sysdate;

commit;

4.) Stopping the capture:

BEGIN
  DBMS_PRIVILEGE_CAPTURE.disable_capture(
    name        => 'CHECK_PRIVS'
  );

  DBMS_PRIVILEGE_CAPTURE.generate_result(
    name        => 'CHECK_PRIVS'
  );
END;
/


5.) Checking the Results:
-- Thanks to ORACLE-BASE for the queries:

COLUMN username FORMAT A20
COLUMN sys_priv FORMAT A20

SELECT username, sys_priv
FROM   dba_used_sysprivs
WHERE  capture = 'CHECK_PRIVS'
ORDER BY username, sys_priv;

USERNAME             SYS_PRIV
-------------------- --------------
CHECKPRIVS           CREATE SESSION
CHECKPRIVS           CREATE TABLE

COLUMN username FORMAT A20
COLUMN used_role FORMAT A30
COLUMN sys_priv FORMAT A20
COLUMN path FORMAT A50
SET LINESIZE 200

SELECT username, sys_priv, used_role, path
FROM   dba_used_sysprivs_path
WHERE  capture = 'CHECK_PRIVS'
ORDER BY username, sys_priv;

USERNAME             SYS_PRIV             USED_ROLE                      PATH
-------------------- -------------------- ------------------------------ --------------------------------------------------
CHECKPRIVS           CREATE SESSION       EM_EXPRESS_BASIC               GRANT_PATH('CHECKPRIVS', 'DBA', 'EM_EXPRESS_ALL',
                                                                         'EM_EXPRESS_BASIC')

CHECKPRIVS           CREATE TABLE         DATAPUMP_EXP_FULL_DATABASE     GRANT_PATH('CHECKPRIVS', 'DBA', 'DATAPUMP_EXP_FULL
                                                                         _DATABASE')

CHECKPRIVS           CREATE TABLE         DATAPUMP_EXP_FULL_DATABASE     GRANT_PATH('CHECKPRIVS', 'DBA', 'DATAPUMP_EXP_FULL
                                                                         _DATABASE', 'EXP_FULL_DATABASE')
                                                                         
                                                                         COLUMN username FORMAT A20
COLUMN obj_priv FORMAT A8
COLUMN object_owner FORMAT A15
COLUMN object_name FORMAT A20
COLUMN object_type FORMAT A11

SELECT username, obj_priv, object_owner, object_name, object_type 
FROM   dba_used_objprivs
WHERE  capture = 'CHECK_PRIVS';

USERNAME             OBJ_PRIV OBJECT_OWNER    OBJECT_NAME          OBJECT_TYPE
-------------------- -------- --------------- -------------------- -----------
CHECKPRIVS           SELECT   SYS             V_$DATABASE          VIEW


COLUMN username FORMAT A20
COLUMN obj_priv FORMAT A8
COLUMN object_owner FORMAT A15
COLUMN object_name FORMAT A20
COLUMN used_role FORMAT A20
COLUMN path FORMAT A30
SET LINESIZE 200

SELECT username, obj_priv, object_owner, object_name, used_role, path 
FROM   dba_used_objprivs_path
WHERE  capture = 'CHECK_PRIVS';

USERNAME             OBJ_PRIV OBJECT_OWNER    OBJECT_NAME          USED_ROLE            PATH
-------------------- -------- --------------- -------------------- -------------------- ------------------------------
CHECKPRIVS           SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('CHECKPRIVS', 'DBA'
                                                                                        , 'IMP_FULL_DATABASE', 'SELECT
                                                                                        _CATALOG_ROLE')

CHECKPRIVS           SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('CHECKPRIVS', 'DBA'
                                                                                        , 'DATAPUMP_IMP_FULL_DATABASE'
                                                                                        , 'EXP_FULL_DATABASE', 'SELECT
                                                                                        _CATALOG_ROLE')

CHECKPRIVS           SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('CHECKPRIVS', 'DBA'
                                                                                        , 'EM_EXPRESS_ALL', 'EM_EXPRES
                                                                                        S_BASIC', 'SELECT_CATALOG_ROLE
                                                                                        ')

CHECKPRIVS           SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('CHECKPRIVS', 'DBA'
                                                                                        , 'DATAPUMP_EXP_FULL_DATABASE'
                                                                                        , 'EXP_FULL_DATABASE', 'SELECT
                                                                                        _CATALOG_ROLE')

CHECKPRIVS           SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('CHECKPRIVS', 'DBA'
                                                                                        , 'SELECT_CATALOG_ROLE')

CHECKPRIVS           SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('CHECKPRIVS', 'DBA'
                                                                                        , 'EXP_FULL_DATABASE', 'SELECT
                                                                                        _CATALOG_ROLE')

CHECKPRIVS           SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('CHECKPRIVS', 'DBA'
                                                                                        , 'DATAPUMP_IMP_FULL_DATABASE'
                                                                                        , 'IMP_FULL_DATABASE', 'SELECT
                                                                                        _CATALOG_ROLE')                                                                       

6.) Drop Capture:

BEGIN
  DBMS_PRIVILEGE_CAPTURE.drop_capture(
    name        => 'CHECK_PRIVS'
  );
END;
/
 

7.) Change:

According with the results we can create a ROLE with the GRANTS below:

CREATE SESSION
CREATE TABLE  
SELECT ON SYS.V_$DATABASE

We can REVOKE DBA from user CHECKPRIVS;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s