4 Performing Privilege Analysis to Find Privilege Use

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

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. If your applications include definer’s rights and invoker’s rights procedures, then privilege analysis captures the privileges that are required to compile a procedure and execute it, even if the procedure was compiled before the privilege capture was created and enabled.

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 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. You can capture privilege use for the following types of roles: Oracle default roles, user-created roles, Code Based Access Control (CBAC) roles, and secure application roles.

• 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.

• Unnecessarily Granted Privileges of Applications
  The privileges of the account that accesses a database should only be limited to the privileges that are strictly required by the application.
• Development of Secure Applications
  During the application development phase, some administrators may grant many powerful system privileges and roles to application developers.

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 CDB 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 CDB root or the application root, or to the privileges used within a PDB. It cannot be applied globally throughout the multitenant environment. You can grant the CAPTURE_ADMIN role locally to a local user or a common user. You can grant the CAPTURE_ADMIN role commonly to common users.

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. Optionally, specify a name for this capture run. Each time you enable a privilege analysis policy, you can create a different capture run for it. In this way, you can create multiple named capture runs for comparison analysis later on.

3. Optionally, enable the policy to capture dependency privileges if you want to capture the privileges that are used by definer’s rights and invoker’s rights program units.
4. 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.

5. Generate privilege analysis results.

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

6. Optionally, disable and then drop the privilege analysis policy and capture run.

   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.

• About Creating a Privilege Analysis Policy
  When a policy is created, it resides in the Oracle data dictionary and the SYS schema.
• Creating a Privilege Analysis Policy in Enterprise Manager Cloud Control
  You can create a privilege analysis policy in Cloud Control.
• Creating a Privilege Analysis Policy Using DBMS_PRIVILEGE_CAPTURE
  The DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE procedure creates a privilege analysis policy.

You can create a variety of privilege analysis policies.

• Example: Privilege Analysis of Database-Wide Privileges
  The DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE can be used to analyze database-wide privileges.
• Example: Privilege Analysis of Privilege Usage of Two Roles
  The DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE procedure can be used to analyze the privilege usage of multiple roles.
• Example: Privilege Analysis of Privileges During SQL*Plus Use
  The DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE procedure can be used to capture privileges for analysis.
• Example: Privilege Analysis of PSMITH Privileges During SQL*Plus Access
  The DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE can be used to analyze user access when the user is running SQL*Plus.

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.

• About Enabling a Privilege Analysis Policy
  After you create a privilege analysis policy, you must enable it.
• Enabling a Privilege Analysis Policy Using Cloud Control
  You can enable a privilege analysis policy using Cloud Control.
• Enabling a Privilege Analysis Policy Using DBMS_PRIVILEGE_CAPTURE
  The DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE procedure enables a privilege policy and creates a capture run name for it.

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.

• About Disabling a Privilege Analysis Policy
  You must disable the privilege analysis policy before you can generate a privilege analysis report.
• Disabling a Privilege Analysis Policy Using Cloud Control
  You can disable a privilege analysis policy using Cloud Control.
• Disabling a Privilege Analysis Policy Using DBMS_PRIVILEGE_CAPTURE
  The DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE procedure disables a privilege analysis policy.

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.

• About Generating a Privilege Analysis Report
  After the privilege analysis policy has been disabled, you can generate a report.
• Generating a Privilege Analysis Report Using Cloud Control
  You can generate a privilege analysis report using Cloud Control.
• Accessing Privilege Analysis Reports Using Cloud Control
  A privilege analysis report provides information about both used and unused privileges.
• Generating a Privilege Analysis Report Using DBMS_PRIVILEGE_CAPTURE
  The DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT procedure generates a report showing the results of a 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.

• About Dropping a Privilege Analysis Policy
  Before you can drop a privilege analysis policy, you must first disable it.
• Dropping a Privilege Analysis Policy Using Cloud Control
  You can drop a privilege analysis policy by using Cloud Control.
• Dropping a Privilege Analysis Policy Using the DBMS_PRIVILEGE_CAPTURE Package
  The DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE procedure drops a privilege analysis policy.

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

1. Log in to Cloud Control as a user who has been granted the CAPTURE_ADMIN role and the SELECT ANY DICTIONARY privilege. Oracle Database 2 Day DBA explains how to log in.
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 DBA_DV_REALM_AUTH data dictionary view. To grant the user authorization, use the DBMS_MACADM.ADD_AUTH_TO_REALM procedure.

2. Generate the privilege analysis report.

   See Generating a Privilege Analysis Report Using Cloud Control for more information.

3. In the Privilege Analysis page, select the policy on which you generated a report.
4. Select View Reports.
5. In the Privilege Analysis: Reports page, select the Summary tab.
6. Under Search, ensure that the Policy and Grantee menu options are set.
7. Under the Grantee area, expand the grantee options.
   For example, for a role privilege analysis report for a role called HR_ADMIN role, you would expand the HR_ADMIN role to show the privileges that are associated with it.
8. Select each privilege to revoke and then click Revoke, or select Regrant to regrant the privilege to the role.

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

• About Generating Revoke and Regrant Scripts
  You can perform a bulk revoke of unused system and object privileges and roles by using scripts that you can download after you have generated the privilege analysis.
• Generating a Revoke Script
  You can use Enterprise Manager Cloud Control to generate a script that revokes privileges from users.
• Generating a Regrant Script
  You can use Enterprise Manager Cloud Control to generate a script that regrants privileges that have been revoked from users.

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 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 and create a capture run for it.

   BEGIN
     DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE (
      name       => 'ANY_priv_analysis_pol',
      run_name   => 'ANY_priv_pol_run_1');
   END;
   /
   

   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.

   BEGIN
     DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (
       name      => 'ANY_priv_analysis_pol',
       run_name  => 'ANY_priv_pol_run_1');
   END;
   /
   

   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
   col run_name format a27
   

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

   SELECT SYS_PRIV, OBJECT_OWNER, OBJECT_NAME, RUN_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.

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

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 and its associated capture runs.

   EXEC DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE ('ANY_priv_analysis_pol');
   

   Any capture runs that are associated with this policy are dropped automatically when you run the DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE procedure.

   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;
   

   Follow the guidelines in Oracle Database Security Guide to replace password with a password that is secure.

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 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;