8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Capture Privilege Usage (DBMS_PRIVILEGE_CAPTURE) in Oracle Database 12c Release 1 (12.1)
The concept of "least privilege" is a common topic when discussing database security. It revolves around making sure the minimum level of privileges are granted to a user to allow them to do their job. Any unnecessary privileges represent a possible security loophole.
When starting a new project it can be relatively simple to follow the least privilege idea. It is significantly harder to implement it on existing systems, where excessive privilege has already been granted.
Oracle 12c introduced the DBMS_PRIVILEGE_CAPTURE
package, which allows you to track the privileges being used, making it much simpler to perform privilege analysis, which in turn allows you to revoke unnecessary privileges and attain a least privilege state.
Until 19c this functionality was part of the Oracle Database Vault option, so it should only be used if you have the appropriate license. From 19c onward this feature is part of Enterprise Edition, so you no longer need the Database Vault option, and the documentation has moved to the Security Guide. Update: This licensing change has been backported Oracle 12.1. Please confirm in the licensing manual for your version before using this feature.
- Basic Usage
- CREATE_CAPTURE
- ENABLE_CAPTURE
- DISABLE_CAPTURE
- GENERATE_RESULT
- Privilege Analysis Views
- DROP_CAPTURE
- Example
For more information see:
Basic Usage
In order to use the DBMS_PRIVILEGE_CAPTURE
package you must be granted the CAPTURE_ADMIN
role. Regardless of what you are trying to monitor, the basic usage of the DBMS_PRIVILEGE_CAPTURE
package is the same.
- Create a privilege analysis policy. (
CREATE_CAPTURE
) - Enable it. (
ENABLE_CAPTURE
) - Wait for the required analysis period.
- Disable the privilege analysis policy. (
DISABLE_CAPTURE
) - Analyze the results. (
GENERATE_RESULT
and query dictionary views) - Drop the policy if it, and the recorded data, is no longer needed. (
DROP_CAPTURE
)
The main differences between analysis runs will be based around the call to CREATE_CAPTURE
procedure, which is discussed below.
The time waited between enabling and disabling the capture is a really import part of the process. You must wait for a representative period of time, or you might miss some important activity. For example, some privileges may be associated with tasks which happen infrequently, like year end jobs. If you don't sample during a representative period, you may incorrectly conclude certain privileges are unnecessary.
In a multitenant environment, the policies are container-specific.
CREATE_CAPTURE
The CREATE_CAPTURE
procedure allows you to create privilege analysis policies with varying degrees of granularity.
G_DATABASE
: Analyzes all privilege usage on the database, except the SYS user. TheROLES
andCONDITION
parameters are not needed.G_ROLE
: Analyzes all privilege usage by the roles specified in theROLES
parameter. Use theROLE_NAME_LIST
function to specify the roles.G_CONTEXT
: Analyzes all privilege usage when the boolean expression specified in theCONDITION
parameter evaluates to TRUE. Conditions can include combinations of calls to theSYS_CONTEXT
.G_ROLE_AND_CONTEXT
: Analyzes all privilege usage when both theROLES
andCONDITION
criteria are true.
All policies are created in a disabled state. The following code gives a simple example of each.
-- Connect to a privileged using in a PDB. conn / as sysdba alter session set container = pdb1; -- Whole database (type = G_DATABASE). begin dbms_privilege_capture.create_capture( name => 'db_pol', type => dbms_privilege_capture.g_database ); end; / -- One or more roles (type = G_ROLE). begin dbms_privilege_capture.create_capture( name => 'role_pol', type => dbms_privilege_capture.g_role, roles => role_name_list('DBA', 'RESOURCE') ); end; / -- A user defined condition, when user is TEST (type = G_CONTEXT). begin dbms_privilege_capture.create_capture( name => 'cond_pol', type => dbms_privilege_capture.g_context, condition => 'sys_context(''userenv'', ''session_user'') = ''TEST''' ); end; / -- Combination of roles and conditions (type = G_ROLE_AND_CONTEXT). begin dbms_privilege_capture.create_capture( name => 'role_cond_pol', type => dbms_privilege_capture.g_role_and_context, roles => role_name_list('dba', 'resource'), condition => 'sys_context(''userenv'', ''session_user'') in (''TEST'',''EMP'')' ); end; /
The DBA_PRIV_CAPTURES
view displays information on the existing privilege capture policies.
column name format a15 column roles format a20 column context format a30 set linesize 100 select name, type, enabled, roles, context from dba_priv_captures order by name; NAME TYPE E ROLES CONTEXT --------------- ---------------- - -------------------- ------------------------------ cond_pol CONTEXT N SYS_CONTEXT('USERENV', 'SESSIO N_USER') = 'TEST' db_pol DATABASE N role_cond_pol ROLE_AND_CONTEXT N ROLE_ID_LIST(4, 3) SYS_CONTEXT('USERENV', 'SESSIO N_USER') IN ('TEST','EMP') role_pol ROLE N ROLE_ID_LIST(4, 3) 4 rows selected. SQL>
ENABLE_CAPTURE
The ENABLE_CAPTURE
procedure is used to enable a capture policy. Typically, only one analysis policy can be enabled at a time. The exception to this is one G_DATABASE
and one none G_DATABASE
policy can be enabled at the same time.
begin dbms_privilege_capture.enable_capture('db_pol'); dbms_privilege_capture.enable_capture('cond_pol'); end; /
DISABLE_CAPTURE
As soon as you have waited a representative amount of time, the capture can be disabled using the DISABLE_CAPTURE
procedure.
begin dbms_privilege_capture.disable_capture('db_pol'); dbms_privilege_capture.disable_capture('cond_pol'); end; /
GENERATE_RESULTS
Once a capture is complete, the GENERATE_RESULT
procedure should be used to push the captured information to the data dictionary views.
begin dbms_privilege_capture.generate_result('db_pol'); end; /
Privilege Analysis Views
The following views have been added in Oracle 12c to allow you to query the results of privilege analysis runs.
- DBA_PRIV_CAPTURES
- DBA_USED_OBJPRIVS
- DBA_USED_OBJPRIVS_PATH
- DBA_USED_PRIVS
- DBA_USED_PUBPRIVS
- DBA_USED_SYSPRIVS
- DBA_USED_SYSPRIVS_PATH
- DBA_USED_USERPRIVS
- DBA_USED_USERPRIVS_PATH
- DBA_UNUSED_OBJPRIVS
- DBA_UNUSED_OBJPRIVS_PATH
- DBA_UNUSED_PRIVS
- DBA_UNUSED_SYSPRIVS
- DBA_UNUSED_SYSPRIVS_PATH
- DBA_UNUSED_USERPRIVS
- DBA_UNUSED_USERPRIVS_PATH
The information displayed by these views will help you decide which grants and roles should be amended.
DROP_CAPTURE
Once your analysis is complete, you can optionally choose to drop the captured information. Only disabled policies can be dropped.
begin dbms_privilege_capture.drop_capture('cond_pol'); dbms_privilege_capture.drop_capture('db_pol'); dbms_privilege_capture.drop_capture('role_cond_pol'); dbms_privilege_capture.drop_capture('role_pol'); end; /
Example
In this section we will look at an example of privilege analysis. Create a user with a high degree of privilege by giving it the DBA
and RESOURCE
roles.
conn / as sysdba alter session set container = pdb1; create user priv_test_user identified by priv_test_user; grant dba, resource to priv_test_user;
Start capturing the privilege usage for these roles against this user.
begin dbms_privilege_capture.create_capture( name => 'dba_res_user_pol', type => dbms_privilege_capture.g_role_and_context, roles => role_name_list('DBA', 'RESOURCE'), condition => 'sys_context(''userenv'', ''session_user'') = ''PRIV_TEST_USER''' ); dbms_privilege_capture.enable_capture( name => 'dba_res_user_pol' ); end; /
Perform some actions as the PRIV_TEST_USER
user.
conn priv_test_user/priv_test_user@pdb1 create table tab1 ( id number, description varchar2(50), constraint tab1_px primary key (id) ); create sequence tab1_seq; create view tab1_view as select * from tab1; insert into tab1 select level, 'Description of ' || to_char(level) from dual connect by level <= 5; commit; select name from v$database;
Disable the capture process and push the results to the data dictionary.
conn / as sysdba alter session set container = pdb1; begin dbms_privilege_capture.disable_capture( name => 'dba_res_user_pol' ); dbms_privilege_capture.generate_result( name => 'dba_res_user_pol' ); end; /
Check the privileges that were used during the capture period by querying the data dictionary.
What system privileges were used during the capture period? We can get that information from the DBA_USED_PRIVS
, DBA_USED_SYSPRIVS
or DBA_USED_SYSPRIVS_PATH
views.
column username format a20 column sys_priv format a20 select username, sys_priv from dba_used_sysprivs where capture = 'dba_res_user_pol' order by username, sys_priv; USERNAME SYS_PRIV -------------------- -------------------- PRIV_TEST_USER CREATE ANY INDEX PRIV_TEST_USER CREATE SEQUENCE PRIV_TEST_USER CREATE SESSION PRIV_TEST_USER CREATE TABLE PRIV_TEST_USER CREATE VIEW 5 rows selected. SQL>
These look straight forward, with the exception of the CREATE ANY INDEX
privilege. This would need to be investigated further, but in many cases, this would just be one of those eccentricities of Oracle that can be ignored. If you have the ability to create tables, you also have the ability to index those tables. As a result, the use of the CREATE ANY INDEX
privilege is not really necessary in most cases.
How were these privileges granted to the user? We can get that information from the DBA_USED_SYSPRIVS_PATH
view.
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 = 'dba_res_user_pol' order by username, sys_priv; USERNAME SYS_PRIV USED_ROLE PATH -------------------- -------------------- ------------------------------ -------------------------------------------------- PRIV_TEST_USER CREATE ANY INDEX IMP_FULL_DATABASE GRANT_PATH('PRIV_TEST_USER', 'DBA', 'IMP_FULL_DATA BASE') PRIV_TEST_USER CREATE ANY INDEX IMP_FULL_DATABASE GRANT_PATH('PRIV_TEST_USER', 'DBA', 'DATAPUMP_IMP_ FULL_DATABASE', 'IMP_FULL_DATABASE') PRIV_TEST_USER CREATE SEQUENCE OLAP_DBA GRANT_PATH('PRIV_TEST_USER', 'DBA', 'OLAP_DBA') PRIV_TEST_USER CREATE SESSION EM_EXPRESS_BASIC GRANT_PATH('PRIV_TEST_USER', 'DBA', 'EM_EXPRESS_AL L', 'EM_EXPRESS_BASIC') PRIV_TEST_USER CREATE TABLE DATAPUMP_EXP_FULL_DATABASE GRANT_PATH('PRIV_TEST_USER', 'DBA', 'DATAPUMP_EXP_ FULL_DATABASE', 'EXP_FULL_DATABASE') PRIV_TEST_USER CREATE TABLE DATAPUMP_EXP_FULL_DATABASE GRANT_PATH('PRIV_TEST_USER', 'DBA', 'DATAPUMP_EXP_ FULL_DATABASE') PRIV_TEST_USER CREATE VIEW DBA GRANT_PATH('PRIV_TEST_USER', 'DBA') 7 rows selected. SQL>
So the privileges came from a variety of roles, but looking at the output from the PATH
column, all of them stem from the grant of the DBA role.
What object privileges were necessary? We can get this information from the DBA_USED_PRIVS
, DBA_USED_OBJPRIVS
or DBA_USED_OBJPRIVS_PATH
views.
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 = 'dba_res_user_pol'; USERNAME OBJ_PRIV OBJECT_OWNER OBJECT_NAME OBJECT_TYPE -------------------- -------- --------------- -------------------- ----------- PRIV_TEST_USER SELECT SYS V_$DATABASE VIEW 1 row selected. SQL>
How were these privileges granted to the user? We can get that information from the DBA_USED_OBJPRIVS_PATH
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 = 'dba_res_user_pol'; USERNAME OBJ_PRIV OBJECT_OWNER OBJECT_NAME USED_ROLE PATH -------------------- -------- --------------- -------------------- -------------------- ------------------------------ PRIV_TEST_USER SELECT SYS V_$DATABASE SELECT_CATALOG_ROLE GRANT_PATH('PRIV_TEST_USER', ' DBA', 'SELECT_CATALOG_ROLE') PRIV_TEST_USER SELECT SYS V_$DATABASE SELECT_CATALOG_ROLE GRANT_PATH('PRIV_TEST_USER', ' DBA', 'EXP_FULL_DATABASE', 'SE LECT_CATALOG_ROLE') PRIV_TEST_USER SELECT SYS V_$DATABASE SELECT_CATALOG_ROLE GRANT_PATH('PRIV_TEST_USER', ' DBA', 'IMP_FULL_DATABASE', 'SE LECT_CATALOG_ROLE') PRIV_TEST_USER SELECT SYS V_$DATABASE SELECT_CATALOG_ROLE GRANT_PATH('PRIV_TEST_USER', ' DBA', 'DATAPUMP_EXP_FULL_DATAB ASE', 'EXP_FULL_DATABASE', 'SE LECT_CATALOG_ROLE') PRIV_TEST_USER SELECT SYS V_$DATABASE SELECT_CATALOG_ROLE GRANT_PATH('PRIV_TEST_USER', ' DBA', 'DATAPUMP_IMP_FULL_DATAB ASE', 'EXP_FULL_DATABASE', 'SE LECT_CATALOG_ROLE') PRIV_TEST_USER SELECT SYS V_$DATABASE SELECT_CATALOG_ROLE GRANT_PATH('PRIV_TEST_USER', ' DBA', 'DATAPUMP_IMP_FULL_DATAB ASE', 'IMP_FULL_DATABASE', 'SE LECT_CATALOG_ROLE') PRIV_TEST_USER SELECT SYS V_$DATABASE SELECT_CATALOG_ROLE GRANT_PATH('PRIV_TEST_USER', ' DBA', 'EM_EXPRESS_ALL', 'EM_EX PRESS_BASIC', 'SELECT_CATALOG_ ROLE') 7 rows selected. SQL>
Once again, the privileges came from a variety of roles, but looking at the output from the PATH
column, all of them stem from the grant of the DBA role.
What can we conclude from this?
- All privileges used were granted via the DBA role, so no direct privileges are necessary.
- With the exception of the
CREATE ANY INDEX
privilege, which would need further investigation in a real situation, all the privileges used are quite basic, so this user really doesn't need theDBA
andRESOURCE
roles.
So the solution here seems quite simple. Create a custom role to apply any necessary privileges, then revoke the DBA
and RESOURCE
roles.
conn / as sysdba alter session set container = pdb1; create role custom_role; grant create sequence to custom_role; grant create session to custom_role; grant create table to custom_role; grant create view to custom_role; grant select on sys.v_$database to custom_role; grant custom_role to priv_test_user; revoke dba, resource from priv_test_user;
With the analysis complete, we can optionally remove the captured information from the data dictionary.
begin dbms_privilege_capture.drop_capture( name => 'dba_res_user_pol' ); end; /
For more information see:
- Performing Privilege Analysis to Find Privilege Use
- DBMS_PRIVILEGE_CAPTURE
- Performing Privilege Analysis to Find Privilege Use (19c)
- Capture Privilege Usage (DBMS_PRIVILEGE_CAPTURE)
Hope this helps. Regards Tim...