Oracle 12c: Track and analyze privilege utilization with DBMS_PRIVILEGE_CAPTURE

郑全

Basic UsageIn 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_CAPTUREpackage 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.
Note. In a multitenant environment, the policies are container-specific.
CREATE_CAPTUREThe 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. The ROLES and CONDITION parameters are not needed.
• G_ROLE : Analyzes all privilege usage by the roles specified in the ROLES parameter. Use the ROLE_NAME_LIST function to specify the roles.
• G_CONTEXT : Analyzes all privilege usage when the boolean expression specified in the CONDITION parameter evaluates to TRUE. Conditions can include combinations of calls to theSYS_CONTEXT.
• G_ROLE_AND_CONTEXT : Analyzes all privilege usage when both the ROLES and CONDITION 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 A15COLUMN 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', 'SESSION_USER') = 'TEST'
db_pol          DATABASE         Nrole_cond_pol   ROLE_AND_CONTEXT N ROLE_ID_LIST(4, 3)   SYS_CONTEXT('USERENV', 'SESSION_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_DATABASEpolicy 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_DATABASE')
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_ALL', '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', 'SELECT_CATALOG_ROLE')
PRIV_TEST_USER       SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('PRIV_TEST_USER', ' DBA', 'IMP_FULL_DATABASE', 'SELECT_CATALOG_ROLE')
PRIV_TEST_USER       SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('PRIV_TEST_USER', ' DBA', 'DATAPUMP_EXP_FULL_DATABASE', 'EXP_FULL_DATABASE', 'SELECT_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', 'SELECT_CATALOG_ROLE')
PRIV_TEST_USER       SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('PRIV_TEST_USER', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE', 'IMP_FULL_DATABASEDBA', 'EM_EXPRESS_ALL', 'EM_EXPRESS_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 the DBA 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;
/