Tuesday, September 9, 2014

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.

No comments:

Post a Comment

Popular Posts - All Times