Tuesday, December 31, 2019

Oracle Privilege Analysis through DBMS_PRIVILEGE_CAPTURE


Oracle offers a tool to capture database privileges as part of Oracle Enterprise Database license. This tool is comprised of DBMS_PRIVILEGE_CAPTURE PL/SQL package and DBA_USED% and DBA_UNUSED% data dictionary views. This tool required Oracle Database Vault license but not any more! 

Let's give this tool a run in Oracle 18c.

Database Privilege Analysis Setup

Let's create a user USER1, and grant dba role to it. In addition, direct privileges on dba_tables and dba_objects are granted as well so that the user can create stored objects based on these two objects.

SYS@cdb218c> create user USER1
  2  identified by oracle
  3  default tablespace users ;
User created.
SYS@cdb218c> grant unlimited tablespace to USER1;
Grant succeeded.
SYS@cdb218c> grant dba to USER1;
Grant succeeded.
SYS@cdb218c> grant select on dba_tables to USER1 ;
Grant succeeded.
SYS@cdb218c> grant select on dba_objects to USER1 ;
Grant succeeded.
SYS@cdb218c>

System Objects

We are also going to create objects under SYSTEM account and grant access to these objects to our test user USER1 to see how privilege analysis works for objects owned by other users. Let's create system.system_dba_views and system.fn_no_of_indexes under SYSTEM

SYS@cdb218c> grant select on dba_views to system ;
Grant succeeded.
SYS@cdb218c> create or replace view system.system_dba_views
  2  as
  3  select *
  4  from dba_views ;
View created.
SYS@cdb218c> grant select on system.system_dba_views to  USER1 ;
Grant succeeded.
SYS@cdb218c> grant select on dba_indexes to system ;
Grant succeeded.
SYS@cdb218c> create or replace function system.fn_no_of_indexes
  2  return number
  3  as
  4     cnt number ;
  5  begin
  6     select count(*)
  7     into cnt
  8     from dba_indexes ;
  9  return cnt;
 10
 11  end;
 12  /
Function created.
SYS@cdb218c> grant execute on system.fn_no_of_indexes to  USER1 ;
Grant succeeded.

Test Case 1 - Login

The first step that the user USER1 performs is LOGIN. Let's review the privileges that are used during login

Sys Privileges

SYS@cdb218c> select username, sys_priv, used_role
  2  from dba_used_sysprivs;

USERNAME                  SYS_PRIV             USED_ROLE
------------------------- -------------------- --------------------
USER1    CREATE SESSION       EM_EXPRESS_BASIC

As you could see from the above query, CREATE SESSION sys privilege was used to log into the database. The output above also mentions that EM_EXPRESS_BASIC role was used. Wait a minute, we didn't grant EM_EXPRESS_BASIC role to the user. The only role that we granted to USER1 was DBA role. Let's get the privilege path to understand how CREATE SESSION privilege was availed by the user USER1.


SYS@cdb218c> select rownum, sys_priv, path
  2  from dba_used_sysprivs_path;

 Num SYS_PRIV             PATH
---- -------------------- ----------------------------------------------------------------------------------
   1 CREATE SESSION       GRANT_PATH('USER1', 'DBA', 'DATAPUMP_EXP_FULL_DATABASE', 'EXP_FULL_DATABASE')
   2 CREATE SESSION       GRANT_PATH('USER1', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE', 'IMP_FULL_DATABASE')
   3 CREATE SESSION       GRANT_PATH('USER1', 'DBA')
   4 CREATE SESSION       GRANT_PATH('USER1', 'DBA', 'IMP_FULL_DATABASE')
   5 CREATE SESSION       GRANT_PATH('USER1', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE', 'EXP_FULL_DATABASE')
   6 CREATE SESSION       GRANT_PATH('USER1', 'DBA', 'EXP_FULL_DATABASE')
   7 CREATE SESSION       GRANT_PATH('USER1', 'DBA', 'DATAPUMP_EXP_FULL_DATABASE')
   8 CREATE SESSION       GRANT_PATH('USER1', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE')
   9 CREATE SESSION       GRANT_PATH('USER1', 'DBA', 'EM_EXPRESS_ALL', 'EM_EXPRESS_BASIC')

Privilege path is an hierarchical representation of the privilege path. The output above shows that there are 9 different ways this user could avail CREATE SESSION privilege. Let's review Row #9. User USER1 has been granted DBA role which in turn has been granted EM_EXPRESS_ALL role which in turn has been granted EM_EXPRESS_BASIC role. So the path to CREATE SESSION is: DBA->EM_EXPRESS_ALL->EM_EXPRESS_BASIC.

Does EM_EXPRESS_BASIC role have CREATE SESSION privilege? Yes, indeed as shown below.

SYS@cdb218c> select grantee,privilege
  2  from dba_sys_privs
  3  where grantee = 'EM_EXPRESS_BASIC' ;
GRANTEE                   PRIVILEGE
------------------------- --------------------
EM_EXPRESS_BASIC          EM EXPRESS CONNECT
EM_EXPRESS_BASIC          CREATE SESSION

Row #1-8 shows the hierarchy in similar format to show that CREATE SESSION privilege also can be availed through other paths.

PUBLIC Privileges

Let's check out whether any public privileges were used during login by querying dba_used_pubprivs view. As the output below shows, the login process accessed four objects which are granted through PUBLIC role. This is exactly what SQL*Plus does during login to set module information and check product privileges.

SYS@cdb218c> select object_owner, object_name, object_type, obj_priv, sys_priv
  2  from dba_used_pubprivs;
OBJECT_OWN OBJECT_NAME                    OBJECT_TYPE          OBJ_PRIV             SYS_PRIV
---------- ------------------------------ -------------------- -------------------- --------------------
SYS        DBMS_APPLICATION_INFO          PACKAGE              EXECUTE
SYS        DBMS_OUTPUT                    PACKAGE              EXECUTE
SYSTEM     PRODUCT_PRIVS                  VIEW                 READ
SYS        DUAL                           TABLE                SELECT

Object Privileges

The output below shows all the objects that were accessed during login. These are the same objects as reported by dba_used_pubprivs view above.

SYS@cdb218c> select  username, object_owner, object_name, object_type, obj_priv, used_role
  2  from dba_used_objprivs;
USERNAME       OBJECT_OWN OBJECT_NAME                    OBJECT_TYPE          OBJ_PRIV             USED_ROLE
------------------------- ---------- ------------------------------ -------------------- ------------------
USER1    SYS        DBMS_APPLICATION_INFO          PACKAGE              EXECUTE              PUBLIC
USER1    SYSTEM     PRODUCT_PRIVS                  VIEW                 READ                 PUBLIC
USER1    SYS        DUAL                           TABLE                SELECT               PUBLIC
USER1    SYS        DUAL                           TABLE                SELECT               PUBLIC

The output below confirms that all objects that were accessed via public role.

SYS@cdb218c> select  object_owner, object_name, path
  2  from dba_used_objprivs_path;
OBJECT_OWN OBJECT_NAME                    PATH
---------- ------------------------------ ------------------------
SYS        DUAL                           GRANT_PATH('PUBLIC')
SYS        DBMS_OUTPUT                    GRANT_PATH('PUBLIC')
SYS        DBMS_APPLICATION_INFO          GRANT_PATH('PUBLIC')
SYSTEM     PRODUCT_PRIVS                  GRANT_PATH('PUBLIC')
SYS        DUAL                           GRANT_PATH('PUBLIC')


Test Case 2 - Query DBA_TABLES

The next test case queries dba_tables data dicitonary view, as shown below.

USER1@cdb218c>  select count(*) from dba_tables ;
  COUNT(*)                                                                     
----------                                                                     
      1793                                                                     

Sys Privileges

SYS@cdb218c> select rownum, username, sys_priv, used_role
  2  from dba_used_sysprivs;
no rows selected

SYS@cdb218c> select rownum, sys_priv, path
  2  from dba_used_sysprivs_path;
no rows selected

No Sys privileges were used.

Public Privileges

SYS@cdb218c> select rownum, object_owner, object_name, object_type, obj_priv, sys_priv
  2  from dba_used_pubprivs;
no rows selected

No PUBLIC privileges were used.

Object Privileges

SYS@cdb218c> select rownum, username, object_owner, object_name, object_type, obj_priv, used_role
  2  from dba_used_objprivs
  3  order by 2,3,4;

Num USERNAME  OBJECT_OWN OBJECT_NAME                    OBJECT_TYPE          OBJ_PRIV             USED_ROLE
---- ------------------------- ---------- ------------------------------ -------------------- -------------------- --------------------
   1 USER1    SYS        DBA_TABLES                     VIEW                 SELECT               SELECT_CATALOG_ROLE

SYS@cdb218c> select rownum, object_owner, object_name, path
  2  from dba_used_objprivs_path
  3  order by 2,3;

 Num OBJECT_OWN OBJECT_NAME                    PATH
---- ---------- ------------------------------ ------------------------------------------------------------------------------------------------------------------------
   1 SYS        DBA_TABLES                     GRANT_PATH('USER1', 'DBA', 'EM_EXPRESS_ALL', 'EM_EXPRESS_BASIC', 'SELECT_CATALOG_ROLE')
   2 SYS        DBA_TABLES                     GRANT_PATH('USER1', 'DBA', 'SELECT_CATALOG_ROLE')
   3 SYS        DBA_TABLES                     GRANT_PATH('USER1', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE', 'IMP_FULL_DATABASE', 'SELECT_CATALOG_ROLE')
   4 SYS        DBA_TABLES                     GRANT_PATH('USER1')
   5 SYS        DBA_TABLES                     GRANT_PATH('USER1', 'DBA', 'EXP_FULL_DATABASE', 'SELECT_CATALOG_ROLE')
   6 SYS        DBA_TABLES                     GRANT_PATH('USER1', 'DBA', 'IMP_FULL_DATABASE', 'SELECT_CATALOG_ROLE')
   7 SYS        DBA_TABLES                     GRANT_PATH('USER1', 'DBA', 'DATAPUMP_EXP_FULL_DATABASE', 'EXP_FULL_DATABASE', 'SELECT_CATALOG_ROLE')
   8 SYS        DBA_TABLES                     GRANT_PATH('USER1', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE', 'EXP_FULL_DATABASE', 'SELECT_CATALOG_ROLE')

8 rows selected.


The following are the observations derived from the above output:
• There are 8 different privilege paths to SELECT against DBA_TABLES view.
• Oracle makes use of SELECT_CATALOG_ROLE to ascertain that the user can query DBA_TABLES
• Note that Oracle didn't use direct privilege on DBA_TABLES that were granted to USER1 (Row #4)
• SELECT_CATALPG_ROLE can be availed through 7 different ways (Rows 1-3,5-7)

Test Case 3 - Query DBA_TABLES via a view

The next case involves creating a view on DBA_TABLES. As you may remember, we have granted direct SELECT privilege on DBA_TABLES to the user USER1 for this purpose.


USER1@cdb218c>> -- create a view on dba_tables

USER1@cdb218c>> create or replace view USER1.my_dba_tables
  2  as
  3  select *
  4  from dba_tables ;

View created.

USER1@cdb218c>> select count(*) from my_dba_tables ;

  COUNT(*)
----------
      1793


Sys Privileges

SYS@cdb218c> select rownum, username, sys_priv, used_role
  2  from dba_used_sysprivs;

 Num USERNAME                  SYS_PRIV             USED_ROLE
---- ------------------------- -------------------- ------------------------------
   1 USER1    CREATE VIEW          DBA

SYS@cdb218c> select rownum, sys_priv, path
  2  from dba_used_sysprivs_path;

 Num SYS_PRIV             PATH
---- -------------------- ------------------------------------------------------------------------------------------------------------------------
   1 CREATE VIEW          GRANT_PATH('USER1', 'DBA')

CREATE VIEW privilege, which is required to create a view, is derived from DBA role. There is only one privilege path for this privilege. There are no other paths to CREATE VIEW privilege.

Public Privileges

SYS@cdb218c> select rownum, object_owner, object_name, object_type, obj_priv, sys_priv
  2  from dba_used_pubprivs;

no rows selected

Object Privileges

SYS@cdb218c> select rownum, username, object_owner, object_name, object_type, obj_priv, used_role
  2  from dba_used_objprivs
  3  order by 2,3,4;

 Num USERNAME                  OBJECT_OWN OBJECT_NAME                    OBJECT_TYPE          OBJ_PRIV             USED_ROLE
---- ------------------------- ---------- ------------------------------ -------------------- -------------------- ------------------------------
   1 USER1    SYS        DBA_TABLES                     VIEW                 SELECT               USER1

SYS@cdb218c> select rownum, object_owner, object_name, path
  2  from dba_used_objprivs_path
  3  order by 2,3;

 Num OBJECT_OWN OBJECT_NAME                    PATH
---- ---------- ------------------------------ ------------------------------------------------------------------------------------------------------------------------
   1 SYS        DBA_TABLES                     GRANT_PATH('USER1')


The following observations can be derived from the above output:
• Above query against dba_used_objprivs shows that SELECT privilege against SYS.DBA_TABLES was used, which is a view. USED_ROLE for this privilege is USER1 which is a username itself, indicating that the this privilege, a SELECT on SYS.DBA_TABLES view, was granted directly to the user.
• A query against dba_used_objprivs_path shows that there is only one path to this privilege.

Test Case 4 - Query DBA_OBJECTS via a function

This test case is very similar to the previous one except that this test creates a function to return the count of records in dba_objects. The observations similar to the test case above can be derived from this test case.


USER1@cdb218c>> -- create a function
USER1@cdb218c>> create or replace function USER1.fn_no_of_objects
  2  return number
  3  as
  4     cnt number ;
  5  begin
  6
  7     select count(*)
  8     into cnt
  9     from dba_objects ;
 10
 11  return cnt;
 12
 13  end;
 14  /

Function created.

USER1@cdb218c>> select USER1.fn_no_of_objects from dual ;

FN_NO_OF_OBJECTS
----------------
           62196


SYS@cdb218c> select rownum, username, sys_priv, used_role
  2  from dba_used_sysprivs;

 Num USERNAME                  SYS_PRIV             USED_ROLE
---- ------------------------- -------------------- ------------------------------
   1 USER1    CREATE PROCEDURE     DBA

SYS@cdb218c> select rownum, sys_priv, path
  2  from dba_used_sysprivs_path;

 Num SYS_PRIV             PATH
---- -------------------- ------------------------------------------------------------------------------------------------------------------------
   1 CREATE PROCEDURE     GRANT_PATH('USER1', 'DBA')

SYS@cdb218c> select rownum, object_owner, object_name, object_type, obj_priv, sys_priv
  2  from dba_used_pubprivs;

 Num OBJECT_OWN OBJECT_NAME                    OBJECT_TYPE          OBJ_PRIV             SYS_PRIV
---- ---------- ------------------------------ -------------------- -------------------- --------------------
   1 SYS        DUAL                           TABLE                SELECT

SYS@cdb218c> select rownum, username, object_owner, object_name, object_type, obj_priv, used_role
  2  from dba_used_objprivs
  3  order by 2,3,4;

 Num USERNAME                  OBJECT_OWN OBJECT_NAME                    OBJECT_TYPE          OBJ_PRIV             USED_ROLE
---- ------------------------- ---------- ------------------------------ -------------------- -------------------- ------------------------------
   1 USER1    SYS        DBA_OBJECTS                    VIEW                 SELECT               USER1
   2 USER1    SYS        DUAL                           TABLE                SELECT               PUBLIC

SYS@cdb218c> select rownum, object_owner, object_name, path
  2  from dba_used_objprivs_path
  3  order by 2,3;

 Num OBJECT_OWN OBJECT_NAME                    PATH
---- ---------- ------------------------------ ------------------------------------------------------------------------------------------------------------------------
   1 SYS        DBA_OBJECTS                    GRANT_PATH('USER1')
   2 SYS        DUAL                           GRANT_PATH('PUBLIC')


Test Case 5 - Query DBA_OBJECTS via a function

The purpose of this test case is to show the privileges that are required to access an object owned by an another user. This test case involves querying a view owned by SYSTEM schema. This view, in turn, queries DBA_VIEWS.

USER1@cdb218c>> select count(*) from system.system_dba_views ;

  COUNT(*)
----------
      6872

SYS Privileges

USER1@cdb218c> select rownum, username, sys_priv, used_role
  2  from dba_used_sysprivs;

 Num USERNAME                  SYS_PRIV             USED_ROLE
---- ------------------------- -------------------- ------------------------------
   1 USER1    SELECT ANY TABLE     DATAPUMP_IMP_FULL_DATABASE

SYS@cdb218c> select rownum, sys_priv, path
  2  from dba_used_sysprivs_path;

 Num SYS_PRIV             PATH
---- -------------------- ------------------------------------------------------------------------------------------------------------------------
   1 SELECT ANY TABLE     GRANT_PATH('USER1', 'DBA', 'EXP_FULL_DATABASE')
   2 SELECT ANY TABLE     GRANT_PATH('USER1', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE', 'IMP_FULL_DATABASE')
   3 SELECT ANY TABLE     GRANT_PATH('USER1', 'DBA')
   4 SELECT ANY TABLE     GRANT_PATH('USER1', 'DBA', 'DATAPUMP_EXP_FULL_DATABASE', 'EXP_FULL_DATABASE')
   5 SELECT ANY TABLE     GRANT_PATH('USER1', 'DBA', 'IMP_FULL_DATABASE')
   6 SELECT ANY TABLE     GRANT_PATH('USER1', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE', 'EXP_FULL_DATABASE')
   7 SELECT ANY TABLE     GRANT_PATH('USER1', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE')

8 rows selected.

As shown above through the query against dba_used_sysprivs, the user USER1 uses SELECT ANY TABLE privilege. I am not sure why Oracle needed this privilege. The only query that we executed was against SYSTEM.SYSTEM_DBA_VIEWS. We have granted SELECT on SYSTEM.SYSTEM_DBA_VIEWS to USER1, and the query on dba_used_objprivs confirms that Oracle availed direct privilege on SYSTEM.SYSTEM_DBA_VIEWS. Why did Oracle need SELECT ANY TABLE privilege?

As you could see, SELECT ANY TABLE privilege is availed through DATAPUMP_IMP_FULL_DATABASE role. Of course, we didn't grant DATAPUMP_IMP_FULL_DATABASE role to this user. Let's review privilege path to understand the flow of this role.

A query against dba_used_sysprivs_path shows that DATAPUMP_IMP_FULL_DATABASE role was granted to the DBA role which we have granted to this user. As you could see from the privilege path output, there are 7 different ways, or through 7 different roles, SELECT ANY TABLE privilege can be availed.

PUBLIC Privileges

SYS@cdb218c> select rownum, object_owner, object_name, object_type, obj_priv, sys_priv
  2  from dba_used_pubprivs;

no rows selected

Object Privileges

SYS@cdb218c> select rownum, username, object_owner, object_name, object_type, obj_priv, used_role
  2  from dba_used_objprivs
  3  order by 2,3,4;

 Num USERNAME                  OBJECT_OWN OBJECT_NAME                    OBJECT_TYPE          OBJ_PRIV             USED_ROLE
---- ------------------------- ---------- ------------------------------ -------------------- -------------------- ------------------------------
   2 USER1    SYSTEM     SYSTEM_DBA_VIEWS               VIEW                 SELECT               USER1
   1 SYSTEM                    SYS        DBA_VIEWS                      VIEW                 SELECT               SYSTEM

As you could see from the above output Row #2, USER1 requires SELECT on SYSTEM.SYSTEM_DBA_VIEWS. Value of USER1 in USED_ROLE column confirms that this privilege was directly granted to the user.

A interesting observation can be derived from Row #1. SYSTEM user required SELECT on DBA_VIEWS. USER1 user executes "select count(*) from system.system_dba_views" The view system_dba_view in turn queries DBA_VIEWS. This is recorded in Row #1.


SYS@cdb218c> select rownum, object_owner, object_name, path
  2  from dba_used_objprivs_path
  3  order by 2,3;

 Num OBJECT_OWN OBJECT_NAME                    PATH
---- ---------- ------------------------------ ------------------------------------------------------------------------------------------------------------------------
   2 SYS        DBA_VIEWS                      GRANT_PATH('SYSTEM')
   1 SYSTEM     SYSTEM_DBA_VIEWS               GRANT_PATH('USER1')


Test Case 6

USER1@cdb218c> select system.fn_no_of_indexes from dual ;

FN_NO_OF_INDEXES
----------------
            2331

SYS@cdb218c> select rownum, username, sys_priv, used_role
  2  from dba_used_sysprivs;

no rows selected

SYS@cdb218c>
SYS@cdb218c> select rownum, sys_priv, path
  2  from dba_used_sysprivs_path;

no rows selected

SYS@cdb218c> select rownum, object_owner, object_name, object_type, obj_priv, sys_priv
  2  from dba_used_pubprivs;

 Num OBJECT_OWN OBJECT_NAME                    OBJECT_TYPE          OBJ_PRIV             SYS_PRIV
---- ---------- ------------------------------ -------------------- -------------------- --------------------
   1 SYS        DUAL                           TABLE                SELECT

SYS@cdb218c>
SYS@cdb218c> select rownum, username, object_owner, object_name, object_type, obj_priv, used_role
  2  from dba_used_objprivs
  3  order by 2,3,4;

 Num USERNAME                  OBJECT_OWN OBJECT_NAME                    OBJECT_TYPE          OBJ_PRIV             USED_ROLE
---- ------------------------- ---------- ------------------------------ -------------------- -------------------- ------------------------------
   3 USER1    SYS        DUAL                           TABLE                SELECT               PUBLIC
   2 USER1    SYSTEM     FN_NO_OF_INDEXES               FUNCTION             EXECUTE              USER1
   1 SYSTEM                    SYS        DBA_INDEXES                    VIEW                 SELECT               SYSTEM

SYS@cdb218c> select rownum, object_owner, object_name, path
  2  from dba_used_objprivs_path
  3  order by 2,3;

 Num OBJECT_OWN OBJECT_NAME                    PATH
---- ---------- ------------------------------ ------------------------------------------------------------------------------------------------------------------------
   1 SYS        DBA_INDEXES                    GRANT_PATH('SYSTEM')
   3 SYS        DUAL                           GRANT_PATH('PUBLIC')
   2 SYSTEM     FN_NO_OF_INDEXES               GRANT_PATH('USER1')