Privilege Analysis

In Oracle 12c, we can do an analysis of privileges used by the users. This analysis can actually help to know if there are some privileges granted to some user which were never used by that user and hence can be revoked. This can be even more useful in a situation where DBA realizes that someone (probably previous DBA) has granted a lot of privileges to some users which are
apparently not required but may be a threat to the security of the data and he can do a privilege analysis to decide which privileges were never used and can be revoked.

There are three steps involved in privilege analysis

Create capture (DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE)

Enable capture (DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE)

Disable capture (DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE)

Generate result (DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT)

Following is the list of data dictionary views which would contain the analysis information

DBA_USED_PUBPRIVS

DBA_USED_OBJPRIVS

DBA_USED_SYSPRIVS

DBA_USED_PRIVS

DBA_USED_OBJPRIVS_PATH

DBA_USED_SYSPRIVS_PATH

DBA_UNUSED_OBJPRIVS

DBA_UNUSED_SYSPRIVS

DBA_UNUSED_PRIVS

DBA_UNUSED_OBJPRIVS_PATH

DBA_UNUSED_SYSPRIVS_PATH

Privilege analysis can be done at 1) Database Level, 2) Role level, 3) Context level and 4) Role + Context level.

Here I will be doing Context level (Based on logged in USER) privilege analyses to check the privileges usage by a single database user.

Creating a new user (in a PDB) and grant privileges

###########################################

SQL> create user salman identified by salman;

User created.

SQL> alter user salman default tablespace users quota unlimited on users;

User altered

SQL> grant connect to salman;

Grant succeeded.

SQL> grant resource to salman;

Grant succeeded.

SQL> grant create table to salman;

Grant succeeded.

SQL> grant select any table to salman;

Grant succeeded.

 ###########################################

Create capture

exec dbms_privilege_capture.create_capture(name=>'CHECK_PRIVILEGES', Description=>' Capture privileges for user SALMAN', type=>dbms_privilege_capture.g_context, condition=> 'SYS_CONTEXT(''USERENV'',''SESSION_USER'')=''SALMAN''');

Database level capture can be created as follows.

exec dbms_privilege_capture.create_capture(name=>'CHECK_PRIVILEGES', Description=>' Capture privileges for whole database', type=>dbms_privilege_capture.g_database);

For capture based on Module from the session context, following method would be used

Exec SYS.DBMS_PRIVILEGES_CAPTURE(- NAME=>’Privs_AcctPayable_capture’, - Description=> ‘All privileges used by module’, - Type=> ‘SYS_CONTEXT(“USERENV”, “MODULE”)= “SQLPLUS’)

Enable Capture

exec  dbms_privilege_capture.enable_capture('CHECK_PRIVILEGES')

Log in as user SALMAN and utilize a couple of privileges granted to it.

SQL> conn salman/salman@pdb1

Connected.

SQL> create table test (id number);

Table created.

SQL> insert into test values (1);

1 row created.

SQL> commit;

Commit complete.

Disable the capture

SQL> exec dbms_privilege_capture.disable_capture('CHECK_PRIVILEGES')

PL/SQL procedure successfully completed.

Generate Results

SQL> exec dbms_privilege_capture.generate_result('CHECK_PRIVILEGES')

PL/SQL procedure successfully completed.

SQL> desc dba_used_sysprivs

 Name                                      Null?    Type

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

 CAPTURE                                   NOT NULL VARCHAR2(128)

 SEQUENCE                                  NOT NULL NUMBER

 OS_USER                                            VARCHAR2(128)

 USERHOST                                           VARCHAR2(128)

 MODULE                                             VARCHAR2(64)

 USERNAME                                  NOT NULL VARCHAR2(128)

 USED_ROLE                                          VARCHAR2(128)

 SYS_PRIV                                           VARCHAR2(40)

 ADMIN_OPTION                                       NUMBER

SQL>  select module,used_role,sys_priv from dba_used_privs where capture='CHECK_PRIVILEGES' and username='SALMAN';

MODULE                                   USED_ROLE  SYS_PRIV

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

sqlplus@salman1.salman.com (TNS V1-V3)   CONNECT    CREATE SESSION

sqlplus@salman1.salman.com (TNS V1-V3)   PUBLIC

sqlplus@salman1.salman.com (TNS V1-V3)   PUBLIC

sqlplus@salman1.salman.com (TNS V1-V3)   PUBLIC

sqlplus@salman1.salman.com (TNS V1-V3)   PUBLIC

SQL*Plus                                 PUBLIC

SQL*Plus                                 RESOURCE   CREATE TABLE

SQL*Plus                                 RESOURCE   CREATE TABLE

SQL> desc dba_unused_sysprivs

 Name                                      Null?    Type

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

 CAPTURE                                   NOT NULL VARCHAR2(128)

 USERNAME                                           VARCHAR2(128)

 ROLENAME                                           VARCHAR2(128)

 SYS_PRIV                                           VARCHAR2(40)

 ADMIN_OPTION                                       NUMBER

SQL>  select sys_priv from dba_unused_sysprivs where capture='CHECK_PRIVILEGES' and username='SALMAN';

SYS_PRIV

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

SET CONTAINER

CREATE PROCEDURE

CREATE CLUSTER

CREATE INDEXTYPE

CREATE OPERATOR

CREATE TRIGGER

CREATE SEQUENCE

SELECT ANY TABLE

CREATE TYPE

Drop Capture

exec dbms_privilege_capture.drop_capture('CHECK_PRIVILEGES') – Name is case sensitive

So after all this exercise, we can easily identify which privileges are not in use. DBAs can enable the capture for a longer period of time (may be for a few weeks) to avoid any chance of mistake as there could be a possibility that after revoking an “unnecessary” privilege, suddenly a bi-weekly report stops working because privilege(s) needed by this report was listed under “dba_unused_sysprivs “ as this privilege was not in use during capture was enabled.