4 Performing Privilege Analysis to Find Privilege Use

Privilege analysis performs a dynamic (not static) analysis of privileges and roles that a user account or database uses over time.

Because it is a dynamic analysis, it captures real privileges and roles that were actually used.

Privilege analysis captures privileges used by database users and applications at runtime. You then can revoke unused grants and other changes to better reflect the access a user requires. This simplifies the work required to implement least privilege practice.

Running inside the Oracle Database kernel, privilege analysis helps reduce the attack surface of applications and increase operational security by identifying used and unused privileges. Privilege analysis can be used after you install Oracle Database Release 12c without any additional configuration steps.

Privilege analysis support is available from Oracle Enterprise Manager Cloud Control 12c Release 3 Plug-in Update 1 (12.1.0.3).

Privilege analysis can be used to capture the privileges that have been exercised on pre-compiled database objects.

Examples of these objects are PL/SQL packages, procedures, functions, views, triggers, and Java classes and data.

Because these privileges may not be exercised during run time when a stored procedure is called, these privileges are collected when you generate the results for any database-wide capture, along with run-time captured privileges. A privilege is treated as an unused privilege when it is not used in either pre-compiled database objects or run-time capture, and it is saved under the run-time capture name. If a privilege is used for pre-compiled database objects, then it is saved under the capture name ORA$DEPENDENCY. If a privilege is captured during run time, then it is saved under the run-time capture name. If you want to know what the used privileges are for both pre-compiled database objects and run-time usage, then you must query both the ORA$DEPENDENCY and run-time captures. For unused privileges, you only need to query with the run-time capture name.

To find a full list of the pre-compiled objects on which privilege analysis can be used, query the TYPE column of the ALL_DEPENDENCIES data dictionary view.

To use privilege analysis, you must be granted the CAPTURE_ADMIN role.

You use the DBMS_PRIVILEGE_CAPTURE PL/SQL package to manage privilege capture. You use the data dictionary views provided by privilege analysis to analyze your privilege use.

You can create different types of privilege analysis policies to achieve specific goals.

• Role-based privilege use capture. You must provide a list of roles. If the roles in the list are enabled in the database session, then the used privileges for the session will be captured.

• Context-based privilege use capture. You must specify a Boolean expression only with the SYS_CONTEXT function. The used privileges will be captured if the condition evaluates to TRUE.

• Role- and context-based privilege use capture. You must provide both a list of roles that are enabled and a SYS_CONTEXT Boolean expression for the condition. When any of these roles is enabled in a session and the given context condition is satisfied, then privilege analysis starts capturing the privilege use.

• Database-wide privilege capture. If you do not specify any type in your privilege analysis policy, then the used privileges in the database will be captured, except those for the user SYS. (This is also referred to as unconditional analysis, because it is turned on without any conditions.)

Note the following restrictions:

• You can enable only one privilege analysis policy at a time. The only exception is that you can enable a database-wide privilege analysis policy at the same time as a non-database-wide privilege analysis policy, such as a role or context attribute-driven analysis policy.

• You cannot analyze the privileges of the SYS user.

• Privilege analysis shows the grant paths to the privilege but it does not suggest which grant path to keep.

• If the role, user, or object has been dropped, then the values that reflect the privilege captures for these in the privilege analysis data dictionary views are dropped as well.

Analyzing privilege use is beneficial in finding unnecessarily granted privileges.

Topics:

• Unnecessarily Granted Privileges of Applications

• Development of Secure Applications

You can create and use privilege analysis policies in a multitenant environment.

If you are using a multitenant environment, then you can create privilege analysis policies in either the root or in individual PDBs. The privilege analysis policy applies only to the container in which it is created, either to the privileges used within the root or to the privileges used within a PDB. It cannot be applied globally throughout the multitenant environment.

You can use Oracle Enterprise Manager Cloud Control or the DBMS_PRIVILEGE_CAPTURE PL/SQL package to analyze privileges.

Before you can do so, you must be granted the CAPTURE_ADMIN role. The DBMS_PRIVILEGE_CAPTURE package enables you to create, enable, disable, and drop privilege analysis policies. It also generates reports that show the privilege usage, which you can view in DBA_* views.

You must follow a general set of steps to analyze privileges.

1. Define the privilege analysis policy.
2. Enable the privilege analysis policy.

   This step begins recording the privilege use that the policy defined.

3. After a sufficient period of time to gather data, disable the privilege analysis policy's recording of privilege use.

   This step stops capturing the privilege use for the policy.

4. Generate privilege analysis results.

   This step writes the results to the data dictionary views described in Privilege Analysis Policy and Report Data Dictionary Views.

5. Optionally, disable and then drop the privilege analysis policy.

   Dropping a privilege analysis policy deletes the data captured by the policy.

You can create a privilege analysis policy in either Enterprise Manager Cloud Control or from SQL*Plus, using the DBMS_PRIVILEGE_CAPTURE PL/SQL package.

Topics:

• About Creating a Privilege Analysis Policy

• Creating a Privilege Analysis Policy in Enterprise Manager Cloud Control

• Creating a Privilege Analysis Policy Using DBMS_PRIVILEGE_CAPTURE

You can create a variety of privilege analysis policies.

Topics:

• Example: Privilege Analysis of Database-Wide Privileges

• Example: Privilege Analysis of Privilege Usage of Two Roles

• Example: Privilege Analysis of Privileges During SQL*Plus Use

• Example: Privilege Analysis of PSMITH Privileges During SQL*Plus Access

You can enable a privilege analysis policy using either Enterprise Manager Cloud Control or from SQL*Plus, using the DBMS_PRIVILEGE_CAPTURE PL/SQL package.

Topics:

• About Enabling a Privilege Analysis Policy

• Enabling a Privilege Analysis Policy Using Cloud Control

• Enabling a Privilege Analysis Policy Using DBMS_PRIVILEGE_CAPTURE

You can disable a privilege analysis policy using either Enterprise Manager Cloud Control or from SQL*Plus, using the DBMS_PRIVILEGE_CAPTURE PL/SQL package.

Topics:

• About Disabling a Privilege Analysis Policy

• Disabling a Privilege Analysis Policy Using Cloud Control

• Disabling a Privilege Analysis Policy Using DBMS_PRIVILEGE_CAPTURE

You can generate a privilege analysis policy report using either Enterprise Manager Cloud Control or from SQL*Plus, using the DBMS_PRIVILEGE_CAPTURE PL/SQL package.

Topics:

• About Generating a Privilege Analysis Report

• Generating a Privilege Analysis Report Using Cloud Control

• Accessing Privilege Analysis Reports Using Cloud Control

• Generating a Privilege Analysis Report Using DBMS_PRIVILEGE_CAPTURE

You can drop a privilege analysis policy report using either Enterprise Manager Cloud Control or from SQL*Plus, using the DBMS_PRIVILEGE_CAPTURE PL/SQL package.

Topics:

• About Dropping a Privilege Analysis Policy

• Dropping a Privilege Analysis Policy Using Cloud Control

• Dropping a Privilege Analysis Policy Using the DBMS_PRIVILEGE_CAPTURE Package

You can use the report summary to find the least number of privileges an application needs, and encapsulate these privileges into a role.

1. Access the Privilege Analysis page.

   See Accessing Privilege Analysis Reports Using Cloud Control for more information.

2. On the Privilege Analysis page, select the policy name, and then from Actions menu, click Create Role.
3. On the Create Role page, provide the following details, and then click OK:

   • Select the policy from which you would like to create a new role.

   • Enter a unique name for the new role that you want to create.

   • Select the Used or Unused check box, depending on what your role must encapsulate. The role can have used or unused system and object privileges and roles.

   • Select the corresponding radio buttons for Directly Granted System Privileges, Directly Granted Object Privileges, and Directly Granted Roles.

     For example, if you select the Used check box, and select:

     • All system privileges, then all the used system privileges captured are included in the new role that you are creating.

     • None for role, then no role that is captured in the policy will be used in the new role.

     • Customize object privileges, then a list of available used objects privileges captured are displayed, you need to select the privileges from the list to assign to the role.

You can use Enterprise Manager Cloud Control to revoke and regrant roles and privileges to users.

1. If Oracle Database Vault is enabled, then ensure that you are authorized as an owner of the Oracle System Privilege and Role Management realm.

   In SQL*Plus, a user who has been granted the DV_OWNER role can check the authorization by querying the DVSYS.DBA_DV_REALM_AUTH data dictionary view. To grant the user authorization, use the DBMS_MACADM.ADD_AUTH_TO_REALM procedure.

2. Access the Privilege Analysis Reports page.

   In the Privilege Analysis page, select the policy that is associated with the report, and then from the Actions menu, select Reports.

3. In the Privilege Analysis Reports page, expand the grantee whose privileges you want to revoke or regrant.

   The following image shows the EXP_FULL_DATABASE role, which can be revoked.

   
   Description of the illustration GUID-4EE55F7B-836E-438A-85B3-EA710FCA0008-default.gif
4. Select a category under this user name, such as a specific role, or the privileges listed in the System Privileges or Object Privileges folders.

   The Revoke button is enabled if the role or privilege is currently granted to the user. If it is not, then the Regrant button is enabled.

5. To revoke the role or privilege, select Revoke; to regrant it, select Regrant.
6. In the Confirmation window, select Yes.

You can generate a script that revokes or regrants privileges from and to users, based on the results of privilege analysis reports.

Topics:

• About Generating Revoke and Regrant Scripts

• Generating a Revoke Script

• Generating a Regrant Script

You must create two users, one user to create the policy and a second user whose privilege use will be analyzed.

1. Log into the database instance as a user who has been granted the DV_ACCTMGR role.

   For example:

   sqlplus bea_dvacctmgr
   Enter password: password
   

   In a multitenant environment, you must connect to the appropriate pluggable database (PDB).

   For example:

   sqlplus bea_dvacctmgr@hrpdb
   Enter password: password
   

   To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

   If Oracle Database Vault is not enabled, then log into the database instance as a user who has the CREATE USER system privilege.

2. Create the following users:

   CREATE USER pa_admin IDENTIFIED BY password;
   CREATE USER app_user IDENTIFIED BY password;
   

3. Connect as a user who has the privileges to grant roles and system privileges to other users, and who has been granted the owner authorization for the Oracle System Privilege and Role Management realm. (User SYS has these privileges by default.)

   For example:

   CONNECT dba_psmith -- Or, CONNECT dba_psmith@hrpdb
   Enter password: password
   

   In SQL*Plus, a user who has been granted the DV_OWNER role can check the authorization by querying the DVSYS.DBA_DV_REALM_AUTH data dictionary view. To grant the user authorization, use the DBMS_MACADM.ADD_AUTH_TO_REALM procedure.

4. Grant the following role and privilege to the users.

   GRANT CREATE SESSION, CAPTURE_ADMIN TO pa_admin;
   GRANT CREATE SESSION, READ ANY TABLE TO app_user;
   

   User pa_admin will create the privilege analysis policy that will analyze the READ ANY TABLE query that user app_user will perform.

The user pa_admin must create and enable the privilege analysis policy.

1. Connect as user pa_admin.

   CONNECT pa_admin -- Or, CONNECT pa_admin@hrpdb
   Enter password: password
   

2. Create the following privilege analysis policy:

   BEGIN
    DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
     name           => 'ANY_priv_analysis_pol',
     description    => 'Analyzes system privilege use',
     type           => DBMS_PRIVILEGE_CAPTURE.G_CONTEXT,
     condition      => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'')=''APP_USER''');
   END;
   /
   

   In this example:

   • type specifies the type of capture condition that is defined by the condition parameter, described next. In this policy, the type is a context-based condition.

   • condition specifies condition using a Boolean expression that must evaluate to TRUE for the policy to take effect. In this case, the condition checks if the session user is app_user.

3. Enable the policy.

   EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ('ANY_priv_analysis_pol');
   

   At this point, the policy is ready to start recording the actions of user app_user.

User app_user uses the READ ANY TABLE system privilege.

1. Connect as user app_user.

   CONNECT app_user -- Or, CONNECT app_user@hrpdb
   Enter password: password
   

2. Query the HR.EMPLOYEES table.

   SELECT FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE SALARY > 12000 ORDER BY SALARY DESC;
   
   FIRST_NAME           LAST_NAME                     SALARY
   -------------------- ------------------------- ----------
   Steven               King                           24000
   Neena                Kochhar                        17000
   Lex                  De Haan                        17000
   John                 Russell                        14000
   Karen                Partners                       13500
   Michael              Hartstein                      13000
   Shelley              Higgins                        12008
   Nancy                Greenberg                      12008

You must disable the policy before you can generate a report that captures the actions of user app_user.

1. Connect as user pa_admin.

   CONNECT pa_admin -- Or, CONNECT pa_admin@hrpdb
   Enter password: password
   

2. Disable the ANY_priv_analysis_pol privilege policy.

   EXEC DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE ('ANY_priv_analysis_pol');

With the privilege analysis policy disabled, user pa_admin then can generate and view a privilege analysis report.

1. As user pa_admin, generate the privilege analysis results.

   EXEC DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT ('ANY_priv_analysis_pol');
   

   The generated results are stored in the privilege analysis data dictionary views, which are described in "Privilege Analysis Policy and Report Data Dictionary Views."

2. Enter the following commands to format the data dictionary view output:

   col username format a10
   col sys_priv format a16
   col object_owner format a13
   col object_name format a23
   

3. Find the system privileges that app_user used and the objects on which he used them during the privilege analysis period.

   SELECT USERNAME, SYS_PRIV, OBJECT_OWNER, OBJECT_NAME FROM DBA_USED_PRIVS WHERE USERNAME = 'APP_USER';
   

   Output similar to the following appears. The first row shows that app_user used the READ ANY TABLE privilege on the HR.EMPLOYEES table.

   USERNAME   SYS_PRIV             OBJECT_OWNER  OBJECT_NAME
   ---------- ---------------- ------------- -----------------------
   APP_USER                    SYSTEM        PRODUCT_PRIVS
   APP_USER                    SYS           DUAL
   APP_USER                    SYS           DUAL
   APP_USER   CREATE SESSION
   APP_USER                    SYS           DBMS_APPLICATION_INFO
   APP_USER   READ ANY TABLE   HR            EMPLOYEES
   

You can remove the components that you created for this tutorial if you no longer need them.

1. As user pa_admin, drop the ANY_priv_analysis_pol privilege analysis policy.

   EXEC DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE ('ANY_priv_analysis_pol');
   

   Even though in the next steps you will drop the pa_admin user, including any objects created in this user's schema, you must manually drop the ANY_priv_analysis_pol privilege analysis policy because this object resides in the SYS schema.

2. Connect as the user who created the user accounts. If Oracle Database Vault is enabled, then connect as the Oracle Database Vault Account Manager.

   For example:

   CONNECT bea_dvacctmgr -- Or, CONNECT bea_dvacctmgr@hrpdb
   Enter password: password
   

3. Drop the users pa_admin and app_user.

   DROP USER pa_admin;
   DROP USER app_user;

You must create two users, one to create the privilege analysis policy and a second user whose privilege use will be analyzed.

1. Log into the database instance as a user who has been granted the DV_ACCTMGR role.

   For example:

   sqlplus bea_dvacctmgr
   Enter password: password
   

   In a multitenant environment, you must log into the appropriate pluggable database (PDB).

   For example:

   sqlplus bea_dvacctmgr@hrpdb
   Enter password: password
   

   To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

   If Oracle Database Vault is not enabled, then log into the database instance as a user who has the CREATE USER system privilege.

2. Create the following users:

   CREATE USER pa_admin IDENTIFIED BY password;
   CREATE USER tjones IDENTIFIED BY password;
   

3. Connect as a user who has the privileges to grant roles and system privileges to other users, and who has been granted the owner authorization for the Oracle System Privilege and Role Management realm. (User SYS has these privileges by default.)

   For example:

   CONNECT dba_psmith -- Or, CONNECT dba_psmith@hrpdb
   Enter password: password
   

   In SQL*Plus, a user who has been granted the DV_OWNER role can check the authorization by querying the DVSYS.DBA_DV_REALM_AUTH data dictionary view. To grant the user authorization, use the DBMS_MACADM.ADD_AUTH_TO_REALM procedure.

4. Grant the following roles and privileges to the users.

   GRANT CREATE SESSION, CAPTURE_ADMIN TO pa_admin;
   GRANT CREATE SESSION, DBA TO tjones;
   

   User pa_admin will create the privilege analysis policy that will analyze the database tuning operations that user tjones will perform.

User pa_admin must create the and enable the privilege analysis policy.

1. Connect as user pa_admin.

   CONNECT pa_admin -- Or, CONNECT pa_admin@hrpdb
   Enter password: password
   

   If Oracle Database Vault is enabled, then log in as the Database Vault Account Manager, who has the DV_ACCTMGR role. Ensure that you are the owner of the Oracle System Privilege and Role Management realm.

2. Create the following privilege analysis policy:

   BEGIN
    DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
     name             => 'dba_tuning_priv_analysis_pol',
     description      => 'Analyzes DBA tuning privilege use',
     type             => DBMS_PRIVILEGE_CAPTURE.G_CONTEXT,
     condition        => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'')=''TJONES''');
   END;
   /
   

   In this example:

   • type specifies the type of capture condition that is defined by the condition parameter, described next. In this policy, the type is a context-based condition.

   • condition specifies condition using a Boolean expression that must evaluate to TRUE for the policy to take effect. In this case, the condition checks if the session user is tjones.

3. Enable the policy.

   EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ('dba_tuning_priv_analysis_pol');
   

   At this point, the policy is ready to start recording the actions of user tjones.

User tjones uses the DBA role to perform database tuning operations.

1. Connect as user tjones.

   CONNECT tjones -- Or, CONNECT tjones@hrpdb
   Enter password: password
   

2. Run the following script to create the PLAN_TABLE table.

   @$ORACLE_HOME/rdbms/admin/utlxplan.sql
   

   The ___location of this script may vary depending on your operating system. This script creates the PLAN_TABLE table in the tjones schema.

3. Run the following EXPLAIN PLAN SQL statement on the HR.EMPLOYEES table:

   EXPLAIN PLAN
    SET STATEMENT_ID = 'Raise in Tokyo' 
    INTO PLAN_TABLE
    FOR UPDATE HR.EMPLOYEES
    SET SALARY = SALARY * 1.10
    WHERE DEPARTMENT_ID = 
     (SELECT DEPARTMENT_ID FROM HR.DEPARTMENTS WHERE LOCATION_ID = 110);
   

   Next, user tjones will analyze the HR.EMPLOYEES table.

4. Run either of the following scripts to create the CHAINED_ROWS table

   @$ORACLE_HOME/rdbms/admin/utlchain.sql
   

   Or

   @$ORACLE_HOME/rdbms/admin/utlchn1.sql
   

5. Run the ANALYZE TABLE statement on the HR.EMPLOYEES table.

   ANALYZE TABLE HR.EMPLOYEES LIST CHAINED ROWS INTO CHAINED_ROWS;

You must disable the policy before you can generate a report that captures the actions of user tjones.

1. Connect as user pa_admin.

   CONNECT pa_admin -- Or, CONNECT pa_admin@hrpdb
   Enter password: password
   

2. Disable the dba_tuning_priv_analysis_pol privilege policy.

   EXEC DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE ('dba_tuning_priv_analysis_pol');

With the privilege analysis policy disabled, user pa_admin can generate and view privilege analysis reports.

1. As user pa_admin, generate the privilege analysis results.

   EXEC DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT ('dba_tuning_priv_analysis_pol');
   

   The generated results are stored in the privilege analysis data dictionary views, which are described in Privilege Analysis Policy and Report Data Dictionary Views.

2. Enter the following commands to format the data dictionary view output:

   col username format a8
   col sys_priv format a18
   col used_role format a20
   col path format a150
   col obj_priv format a10
   col object_owner format a10
   col object_name format a10
   col object_type format a10
   

3. Find the system privileges and roles that user tjones used during the privilege analysis period.

   SELECT USERNAME, SYS_PRIV, USED_ROLE, PATH
    FROM DBA_USED_SYSPRIVS_PATH
    WHERE USERNAME = 'TJONES'
    ORDER BY 1, 2, 3;
   

   Output similar to the following appears:

   USERNAME SYS_PRIV           USED_ROLE
   -------- ------------------ --------------------
   PATH
   -------------------------------------------------------------------------------
   TJONES   ANALYZE ANY        IMP_FULL_DATABASE
   GRANT_PATH('TJONES', 'DBA')
   
   TJONES   ANALYZE ANY        IMP_FULL_DATABASE
   GRANT_PATH('TJONES', 'DBA', 'IMP_FULL_DATABASE')
   
   TJONES   ANALYZE ANY        IMP_FULL_DATABASE
   GRANT_PATH('TJONES', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE', 'IMP_FULL_DATABASE')
   ...

4. Find the object privileges and roles that user tjones used during the privilege analysis period.

   col username format a9
   col used_role format a10
   col object_name format a22
   col object_type format a12
   
   SELECT USERNAME, OBJ_PRIV, USED_ROLE,
    OBJECT_OWNER, OBJECT_NAME, OBJECT_TYPE
    FROM DBA_USED_OBJPRIVS 
    WHERE USERNAME = 'TJONES'
    ORDER BY 1, 2, 3, 4, 5, 6;
   

   Output similar to the following appears:

   USERNAME  OBJ_PRIV   USED_ROLE  OBJECT_OWN OBJECT_NAME            OBJECT_TYPE
   --------- ---------- ---------- ---------- ---------------------- ------------
   TJONES    EXECUTE    PUBLIC     SYS        DBMS_APPLICATION_INFO  PACKAGE
   TJONES    SELECT     PUBLIC     SYS        DUAL                   TABLE
   TJONES    SELECT     PUBLIC     SYS        DUAL                   TABLE
   TJONES    SELECT     PUBLIC     SYSTEM     PRODUCT_PRIVS          VIEW
   ...
   

5. Find the unused system privileges for user tjones.

   col username format a9
   col sys_priv format a35
   
   SELECT USERNAME, SYS_PRIV
    FROM DBA_UNUSED_SYSPRIVS
    WHERE USERNAME = 'TJONES'
    ORDER BY 1, 2;
   
   USERNAME SYS_PRIV
   -------- ------------------------------
   TJONES   ADMINISTER ANY SQL TUNING SET
   TJONES   ADMINISTER DATABASE TRIGGER
   TJONES   ADMINISTER RESOURCE MANAGER
   TJONES   ADMINISTER SQL TUNING SET
   TJONES   ALTER ANY ASSEMBLY
   TJONES   ON COMMIT REFRESH
   ...

You can remove the components that you created for this tutorial if you no longer need them.

1. As user pa_admin, drop the dba_tuning_priv_analysis_pol privilege analysis policy.

   EXEC DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE ('dba_tuning_priv_analysis_pol');
   

   Even though in the next steps you will drop the pa_admin user, including any objects created in this user's schema, you must manually drop the dba_tuning_priv_analysis_pol privilege analysis policy because this object resides in the SYS schema.

2. Connect as the user who created the user accounts. If Oracle Database Vault is enabled, then connect as the Oracle Database Vault Account Manager.

   For example:

   CONNECT bea_dvacctmgr -- Or, CONNECT bea_dvacctmgr@hrpdb
   Enter password: password
   

3. Drop the users pa_admin and tjones.

   DROP USER pa_admin;
   DROP USER tjones CASCADE;