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.
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')
