4 Performing Privilege Analysis to Find Privilege Use

Privilege analysis dynamically analyzes the privileges and roles that users use and do not use.

Topics:

What Is Privilege Analysis?

Oracle Database Vault with Oracle Database Release 12c includes a feature called privilege analysis to help you increase the security of your applications and database operations.

Topics:

About Privilege Analysis

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

How Privilege Analysis Works with Pre-Compiled Database Objects

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.

Who Can Perform Privilege Analysis?

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.

Types of Privilege Analysis

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.

Benefits and Use Cases of Privilege Analysis

Analyzing privilege use is beneficial in finding unnecessarily granted privileges.

Topics:

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.

But when an application is developed, especially by a third party, more privileges than necessary may be granted to the application connection pool accounts for convenience. In addition, some developers grant system and application object privileges to the PUBLIC role.

For example, to select from application data and run application procedures, the system privileges SELECT ANY TABLE and EXECUTE ANY PROCEDURE are granted to an application account appsys. The account appsys now can access non-application data even if he or she does not intend to. In this situation, you can analyze the privilege usage by user appsys, and then based on the results, revoke and grant privileges as necessary.

Development of Secure Applications

During the application development phase, some administrators may grant many powerful system privileges and roles to application developers.

The administrators may do this because at that stage they may not know what privileges the application developer needs.

Once the application is developed and working, the privileges that the application developer needs — and does not need — become more apparent. At that time, the security administrator can begin to revoke unnecessary privileges. However, the application developer may resist this idea on the basis that the application is currently working without problems. The administrator can use privilege analysis to examine each privilege that the application uses, to ensure that when he or she does revoke any privileges, the application will continue to work.

For example, app_owner is an application database user through whom the application connects to a database. User app_owner must query tables in the OE, SH, and PM schemas. Instead of granting the SELECT object privilege on each of the tables in these schemas, a security administrator grants the SELECT ANY TABLE privilege to app_owner. After a while, a new schema, HR, is created and sensitive data are inserted into HR.EMPLOYEES table. Because user app_owner has the SELECT ANY TABLE privilege, he can query this table to access its sensitive data, which is a security issue. Instead of granting system privileges (particularly the ANY privileges), it is far better to grant object privileges for specific tables.

How Does a Multitenant Environment Affect Privilege Analysis?

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.

See Also:

Oracle Database Administrator's Guide for more information about multitenant container databases (CDBs)

Creating and Managing Privilege Analysis Policies

You can create and manage privilege analysis policies in either SQL*Plus or in Enterprise Manager Cloud Control.

Topics:

About Creating and Managing Privilege Analysis Policies

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.

See Also:

Oracle Database PL/SQL Packages and Types Reference for detailed information about the DBMS_PRIVILEGE_CAPTURE PL/SQL package

General Steps for Managing Privilege Analysis

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.

Creating a Privilege Analysis 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

When a policy is created, it resides in the Oracle data dictionary and the SYS schema.

However, the user who created the policy can drop it, as well as user SYS. After you create the policy, you must manually enable it so that it can begin to analyze privilege use. If you want to use Oracle Enterprise Manager Cloud Control, then you must use Enterprise Manager Release 12.1.0.3 or later.

Creating a Privilege Analysis Policy in Enterprise Manager Cloud Control

You can create a privilege analysis policy in Cloud Control.

  1. In Enterprise Manager, access the target Database home page as a user who has been granted the CAPTURE_ADMIN role and the SELECT ANY DICTIONARY privilege.

    See Oracle Database 2 Day DBA for more information.

  2. From the Security menu, select Privilege Analysis.
  3. In the Privilege Analysis page, under Policies, select Create.
  4. Enter the following information:
    • Policy: Enter a unique name for the privilege analysis policy. You can find the names of existing policies by querying the NAME column of the DBA_PRIV_CAPTURES view. You can include spaces in the name and have a maximum of 128 characters in this name.

    • Description: Optionally, enter a description for the policy, in up to 1024 characters.

    • Scope: Select from the following types:

      • Database captures all privileges that were used in the entire database, except privileges from user SYS.

      • Role captures privileges from one or more roles that you specify. If the roles in the list are enabled in the database session, then the used privileges for the session will be captured. If you select this option, then the Create Policy page displays the Available Roles list.

      • Context captures privileges when the condition that you specify evaluates to TRUE. If you select this option, then the Capture Policy page displays a Condition field. To build the condition, select the edit icon on the right of this field to display the Policy Expression Builder dialog box.

      • Role and Context captures privileges from one of the specified roles when the context condition evaluates to TRUE. If you select this option, then both the list of available roles and Condition field appear.

  5. Click OK.

    The new policy appears in the Policies area of the Privilege Analysis page.

  6. To enable the policy so that it can begin to capture privilege use, return to the main Privilege Analysis policy page, select the policy under Policies, and then click Start Capture.

Creating a Privilege Analysis Policy Using DBMS_PRIVILEGE_CAPTURE

The DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE procedure creates a privilege analysis policy.

After you create the privilege analysis policy, you can find it listed in the DBA_PRIV_CAPTURES data dictionary view.

  • Use the following syntax for the DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE procedure:

    DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
       name              VARCHAR2, 
       description       VARCHAR2 DEFAULT NULL, 
       type              NUMBER DEFAULT DBMS_PRIVILEGE_CAPTURE.G_DATABASE, 
       roles             ROLE_NAME_LIST DEFAULT ROLE_NAME_LIST(), 
       condition         VARCHAR2 DEFAULT NULL);
    

In this specification:

  • name: Specifies the name of the privilege analysis policy to be created. Ensure that this name is unique and no more than 128 characters. You can include spaces in the name, but you must enclose the name in single quotation marks whenever you refer to it. To find the names of existing policies, query the NAME column of the DBA_PRIV_CAPTURES view.

  • description: Describes the purpose of the privilege analysis policy, up to 1024 characters in mixed-case letters. Optional.

  • type: Specifies the type of capture condition. If you omit the type parameter, then the default is DBMS_PRIVILEGE_CAPTURE.G_DATABASE. Optional.

    Enter one of the following types:

    • DBMS_PRIVILEGE_CAPTURE.G_DATABASE: Captures all privileges used in the entire database, except privileges from user SYS.

    • DBMS_PRIVILEGE_CAPTURE.G_ROLE: Captures privileges for the sessions that have the roles enabled. If you enter DBMS_PRIVILEGE_CAPTURE.G_ROLE for the type parameter, then you must also specify the roles parameter. For multiple roles, separate each role name with a comma.

    • DBMS_PRIVILEGE_CAPTURE.G_CONTEXT: Captures privileges for the sessions that have the condition specified by the condition parameter evaluating to TRUE. If you enter DBMS_PRIVILEGE_CAPTURE.G_CONTEXT for the type parameter, then you must also specify the condition parameter.

    • DBMS_PRIVILEGE_CAPTURE.G_ROLE_AND_CONTEXT: Captures privileges for the sessions that have the role enabled and the context condition evaluating to TRUE. If you enter DBMS_PRIVILEGE_CAPTURE.G_ROLE_AND_CONTEXT for the type parameter, then you must also specify both the roles and condition parameters.

  • roles: Specifies the roles whose used privileges will be analyzed. That is, if a privilege from one of the given roles is used, then the privilege will be analyzed. You must specify this argument if you specify DBMS_PRIVILEGE_CAPTURE.G_ROLE or DBMS_PRIVILEGE_CAPTURE.G_ROLE_AND_CONTEXT for the type argument. Each role you enter must exist in the database. (You can find existing roles by querying the DBA_ROLES data dictionary view.) For multiple roles, use varray type role_name_list to enter the role names. You can specify up to 10 roles.

    For example, to specify two roles:

    roles => role_name_list('role1', 'role2'),
    
  • condition: Specifies a Boolean expression up to 4000 characters. You must specify this argument if you specify DBMS_PRIVILEGE_CAPTURE.G_CONTEXT or DBMS_PRIVILEGE_CAPTURE.G_ROLE_AND_CONTEXT for the type argument. Only SYS_CONTEXT expressions with relational operators(==, >, >=, <, <=, <>, BETWEEN, and IN) are permitted in this Boolean expression.

    The condition expression syntax is as follows:

    predicate::= SYS_CONTEXT(namespace, attribute) relop constant_value |
                 SYS_CONTEXT(namespace, attribute)
                 BETWEEN 
                 constant_value 
                 AND constant_value | SYS_CONTEXT(namespace, attribute) 
                 IN {constant_value (,constant_value)* }
    
    relop::= = | < | <= | > | >= | <>
    
    context_expression::= predicate | (context_expression) 
                 AND (context_expression) | (context_expression) 
                 OR (context_expression )
    

    For example, to use a condition to specify the IP address 192.0.2.1:

    condition => 'SYS_CONTEXT(''USERENV'', ''IP_ADDRESS'')=''192.0.2.1''';
    

* You can add as many constant values as you need (for example, IN {constant_value1}, or IN {constant_value1, constant_value2, constant_value3}).

Remember that after you create the privilege analysis policy, you must enable it, as described in Enabling a Privilege Analysis Policy.

Examples of Privilege Analysis Policies

You can create a variety of privilege analysis policies.

Topics:

Example: Privilege Analysis of Database-Wide Privileges

The DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE can be used to analyze database-wide privileges.

Example 4-1 shows how to use the DBMS_PRIVILEGE_CAPTURE package to create and enable a privilege analysis policy to record all privilege use in the database.

Example 4-1 Privilege Analysis of Database-Wide Privileges

BEGIN
 DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
  name          => 'db_wide_capture_pol',
  description   => 'Captures database-wide privileges',
  type          => DBMS_PRIVILEGE_CAPTURE.G_DATABASE);
END;
/
EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ('db_wide_capture_pol');

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 4-2 shows how to analyze the privilege usage of two roles.

Example 4-2 Privilege Analysis of Privilege Usage of Two Roles

BEGIN
 DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
  name          => 'dba_roles_capture_pol',
  description   => 'Captures DBA and LBAC_DBA role use',
  type          => DBMS_PRIVILEGE_CAPTURE.G_ROLE,
  roles         => role_name_list('dba', 'lbac_dba'));
END;
/
EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ('dba_roles_capture_pol');

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 4-3 shows how to analyze privileges used to run SQL*Plus.

Example 4-3 Privilege Analysis of Privileges During SQL*Plus Use

BEGIN
 DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
  name             => 'sqlplus_capture_pol',
  description      => 'Captures privilege use during SQL*Plus use',
  type             => DBMS_PRIVILEGE_CAPTURE.G_CONTEXT,
  condition        => 'SYS_CONTEXT(''USERENV'', ''MODULE'')=''sqlplus''');
END;
/
EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ('sqlplus_capture_pol');

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.

Example 4-4 shows how to analyze the privileges used by session user PSMITH when running SQL*Plus.

Example 4-4 Privilege Analysis of PSMITH Privileges During SQL*Plus Access

BEGIN
 DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
  name         => 'psmith_sqlplus_analysis_pol',
  description  => 'Analyzes PSMITH role priv use for SQL*Plus module',
  type         => DBMS_PRIVILEGE_CAPTURE.G_CONTEXT,
  condition    => 'SYS_CONTEXT(''USERENV'', ''MODULE'')=''sqlplus'' 
                  AND SYS_CONTEXT(''USERENV'', ''SESSION_USER'')=''PSMITH''');
END;
/
EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ('psmith_sqlplus_analysis_pol');

Enabling a Privilege Analysis Policy

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

After you create a privilege analysis policy, you must enable it.

Once enabled, the privilege analysis policy will begin to record the privilege usage when the condition is satisfied. At any given time, only one privilege analysis policy in the database can be enabled. The only exception is that a privilege analysis policy of type DBMS_PRIVILEGE_CAPTURE.G_DATABASE can be enabled at the same time with a privilege analysis of a different type.

Restarting a database does not change the status of a privilege analysis. For example, if a privilege analysis policy is enabled before a database shutdown, then the policy is still enabled after the database shutdown and restart.

Enabling a Privilege Analysis Policy Using Cloud Control

You can enable a privilege analysis policy using Cloud Control.

  1. In Enterprise Manager, access the target Database home page as a user who has been granted the CAPTURE_ADMIN role and the SELECT ANY DICTIONARY privilege.

    See Oracle Database 2 Day DBA for more information.

  2. From the Security menu, select Privilege Analysis.
  3. Under Policies, select the policy that you want to enable.
  4. Select the Start Capture button.
  5. In the Privilege Analysis: Start Capture dialog box, specify a time to begin the privilege analysis policy.

    To run the policy now, select Immediate. To run the policy later, select Later, and then specify the hour, minute, second, and the time zone for the policy to begin.

  6. Click OK.

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.

The run name defines the period of time that the capture took place.
  1. Query the NAME and ENABLED columns of the DBA_PRIV_CAPTURES data dictionary view to find the existing privilege analysis policies and whether they are currently enabled.
  2. Run the DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE procedure to enable the policy and optionally create a name for a capture run.

    For example, to enable the privilege analysis policy logon_users_analysis:

    BEGIN
      DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE (
        name     => 'logon_users_analysis_pol',
        run_name => 'logon_users_04092016');
    END;
    /
    

Disabling a Privilege Analysis Policy

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

You must disable the privilege analysis policy before you can generate a privilege analysis report.

After you disable the policy, then the privileges are no longer recorded. Disabling a privilege analysis policy takes effect immediately for user sessions logged on both before and after the privilege analysis policy is disabled.

Disabling a Privilege Analysis Policy Using Cloud Control

You can disable a privilege analysis policy using Cloud Control.

  1. In Enterprise Manager, access the target Database home page as a user who has been granted the CAPTURE_ADMIN role and the SELECT ANY DICTIONARY privilege.

    See Oracle Database 2 Day DBA for more information.

  2. From the Security menu, select Privilege Analysis.
  3. Under Policies, select the policy that you want to disable.
  4. Select Stop Capture.
  5. In the Privilege Analysis: Stop Capture dialog box, do the following: specify a time to stop the privilege analysis policy.

    To stop the policy now, select Immediate. To stop the policy later, select Later, and then specify the hour, minute, second, and the time zone for the policy to stop.

  6. Click OK.

Disabling a Privilege Analysis Policy Using DBMS_PRIVILEGE_CAPTURE

The DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE procedure disables a privilege analysis policy.

  1. Query the NAME and ENABLED columns of the DBA_PRIV_CAPTURES data dictionary view to find the existing privilege analysis policies and whether they are currently disabled.
  2. Run the DBMS_PRIVILEGE_CAPTURE.DISBLE_CAPTURE procedure to enable the policy.

    For example, to disable the privilege analysis policy logon_users_analysis:

    EXEC DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE ('logon_users_analysis_pol');
    

Generating a Privilege Analysis Report

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

After the privilege analysis policy has been disabled, you can generate a report.

In Enterprise Manager Cloud Control, you can view the reports from the Privilege Analysis page Actions menu, and from there, revoke and regrant roles and privileges as necessary. To view the report results in SQL*Plus, query the data dictionary views in Privilege Analysis Policy and Report Data Dictionary Views. If a privilege is used during the privilege analysis process and then revoked before you generate the report, then the privilege is still reported as a used privilege, but without the privilege grant path.

Generating a Privilege Analysis Report Using Cloud Control

You can generate a privilege analysis report using Cloud Control.

  1. In Enterprise Manager, access the target Database home page as a user who has been granted the CAPTURE_ADMIN role and the SELECT ANY DICTIONARY privilege.

    See Oracle Database 2 Day DBA for more information.

  2. From the Security menu, select Privilege Analysis.
  3. Under Policies, select the policy whose report you want to generate.
  4. Select Generate Report.
  5. In the Privilege Analysis: Generate Report dialog box, specify a time to generate the report.

    To generate the report now, select Immediate. To generate the report later, select Later, and then specify the hour, minute, second, and the time zone for the report to generate.

  6. Click OK.

    In the Privilege Analysis page, a Confirmation message notifies you that a report has been submitted. You can refresh the page until the job is complete. To view the report, select the policy name and then click View Reports.

Accessing Privilege Analysis Reports Using Cloud Control

A privilege analysis report provides information about both used and unused privileges.

  1. Generate the privilege analysis report.
  2. In the Privilege Analysis page, select the policy on which you generated a report.
  3. From the Actions menu, select Reports.

    The Privilege Analysis Reports page appears. The following image shows the Usage Summary tab, with the Search field expanded. It also shows how many system privileges not used.

  4. Select from the Usage Summary, Unused, and Used tabs to find detailed information about the privilege use that was found by the policy.

    From here, you can select roles to revoke or regrant to users as necessary. To do so, select the role nad then click Revoke or Regrant.

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.

  1. Query the NAME and ENABLED columns of the DBA_PRIV_CAPTURES data dictionary view to find the existing privilege analysis policies and whether they are currently disabled.

    The privilege analysis policy must be disabled before you can generate a privilege analysis report on it.

  2. Run the DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT procedure.

    For example, to generate a report for the privilege analysis policy logon_users_analysis:

    EXEC DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT ('logon_users_analysis');
    
  3. Query the used privileges from DBA_USED_* data dictionary views with privilege grant paths.

Dropping a Privilege Analysis Policy

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

Before you can drop a privilege analysis policy, you must first disable it.

Dropping a privilege analysis policy also drops all the used and unused privilege records associated with this privilege analysis.

Dropping a Privilege Analysis Policy Using Cloud Control

You can drop a privilege analysis policy by using Cloud Control.

  1. In Enterprise Manager, access the target Database home page as a user who has been granted the CAPTURE_ADMIN role and the SELECT ANY DICTIONARY privilege.

    See Oracle Database 2 Day DBA for more information.

  2. From the Security menu, select Privilege Analysis.
  3. Under Policies, select the policy that you want to drop.
  4. Select Delete.
  5. In the Confirmation dialog box, select Yes.

Dropping a Privilege Analysis Policy Using the DBMS_PRIVILEGE_CAPTURE Package

The DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE procedure drops a privilege analysis policy.

  1. Query the NAME and ENABLE columns of the DBA_PRIV_CAPTURES data dictionary view to find the policy and to check if it is enabled or disabled.
  2. If the policy is enabled, then disable it.

    For example:

    EXEC DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE ('logon_users_analysis_pol');
    
  3. Run the DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE procedure to drop the policy.

    For example:

    EXEC DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE ('logon_users_analysis_pol');
    

Creating Roles and Managing Privileges Using Cloud Control

You can create new roles using privileges found in a privilege analysis report and then grant this role to users.

Topics:

Creating a Role from a Privilege Analysis Report in Cloud Control

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

Revoking and Regranting Roles and Privileges Using Cloud Control

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.

  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.

Generating a Revoke or Regrant Script Using Cloud Control

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

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.

Later on, if you want to regrant these privileges back to the user, you can generate a regrant script. In order to generate the regrant script, you must have a corresponding revoke script.

Execute the revoke scripts in a development or test environment. Be aware that you cannot revoke privileges and roles from Oracle-supplied accounts and roles.

Generating a Revoke Script

You can use Enterprise Manager Cloud Control to generate a script that revokes privileges from 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. In Enterprise Manager, access the target Database home page as a user who has been granted the CAPTURE_ADMIN role and the SELECT ANY DICTIONARY privilege.

    See Oracle Database 2 Day DBA for more information.

  3. From the Security menu, select Privilege Analysis.
  4. Ensure that the reports you want have been generated.
  5. In the Privilege Analysis page, from the Actions menu, select Revoke Scripts.
  6. On the Revoke Scripts page, click Generate.

    The generate revoke script details wizard is displayed.

  7. In the Script Details page, select a policy name from the menu against which the revoke script needs to be prepared.
  8. Enter a unique name and description for the script.

    For example, if you want to revoke all the unused privileges, select the All option for all the privileges and roles, and click Next.

    Based on your selection, and the available privileges, all the unused system privileges, object privileges, and roles that are going to be revoked are displayed on the respective pages.

  9. Click Next.

    On the Review page, you can see a list of all the privileges that are going to be included in the revoke script.

  10. Click Save.

    A Confirmation window appears.

  11. In the Revoke Scripts page, find the newly created SQL script, and then click Revoke Script to download this script.

Generating a Regrant Script

You can use Enterprise Manager Cloud Control to generate a script that regrants privileges that have been revoked from 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. In Enterprise Manager, access the target Database home page as a user who has been granted the CAPTURE_ADMIN role and the SELECT ANY DICTIONARY privilege.

    See Oracle Database 2 Day DBA for more information.

  3. From the Security menu, select Privilege Analysis.
  4. Ensure that the reports you want have been generated.
  5. In the Privilege Analysis page, from the Actions menu, select Revoke Scripts.
  6. In the Revoke Scripts page, find the regrant script that corresponds to the revoke script that you had generated earlier, and then click Regrant Script to download this script.

Tutorial: Analyzing ANY Privilege Use

This tutorial demonstrates how to use privilege analysis to analyze the use of the READ ANY TABLE system privilege.

Topics:

Step 1: Create User Accounts

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.

Step 2: Create and Enable a Privilege Analysis Policy

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.

Step 3: Use the READ ANY TABLE System Privilege

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
    

Step 4: Disable the Privilege Analysis Policy

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

Step 5: Generate and View a Privilege Analysis Report

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
    

Step 6: Remove the Components for This Tutorial

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;
    

Tutorial: Analyzing Privilege Use by a User Who Has the DBA Role

This tutorial demonstrates how to analyze the privilege use of a user who has the DBA role and performs database tuning operations.

Topics:

Step 1: Create User Accounts

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.

Step 2: Create and Enable a Privilege Analysis Policy

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.

Step 3: Perform the Database Tuning Operations

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;
    

Step 4: Disable the Privilege Analysis Policy

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

Step 5: Generate and View Privilege Analysis Reports

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

Step 6: Remove the Components for This Tutorial

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;
    

Privilege Analysis Policy and Report Data Dictionary Views

Oracle Database provides data dictionary views that show information about analyzed privileges.

Table 4-1 lists these data dictionary views.

Table 4-1 Data Dictionary Views That Display Privilege Analysis Information

View Description

DBA_PRIV_CAPTURES

Lists information about existing privilege analysis policies

DBA_USED_PRIVS

Lists the privileges that have been used for reported privilege analysis policies

DBA_UNUSED_PRIVS

Lists the privileges that have not been used for reported privilege analysis policies

DBA_USED_OBJPRIVS

Lists the object privileges that have been used for reported privilege analysis policies. It does not include the object grant paths.

DBA_UNUSED_OBJPRIVS

Lists the object privileges that have not been used for reported privilege analysis policies. It does not include the object privilege grant paths.

DBA_USED_OBJPRIVS_PATH

Lists the object privileges that have been used for reported privilege analysis policies. It includes the object privilege grant paths.

DBA_UNUSED_OBJPRIVS_PATH

Lists the object privileges that have not been used for reported privilege analysis policies. It includes the object privilege grant paths.

DBA_USED_SYSPRIVS

Lists the system privileges that have been used for reported privilege analysis policies. It does not include the system privilege grant paths.

DBA_UNUSED_SYSPRIVS

Lists the system privileges that have not been used for reported privilege analysis policies. It does not include the system privilege grant paths.

DBA_USED_SYSPRIVS_PATH

Lists the system privileges that have been used for reported privilege analysis policies. It includes the system privilege grant paths.

DBA_UNUSED_SYSPRIVS_PATH

Lists the system privileges that have not been used for reported privilege analysis policies. It includes system privilege grant paths

DBA_USED_PUBPRIVS

Lists all the privileges for the PUBLIC role that have been used for reported privilege analysis policies

DBA_USED_USERPRIVS

Lists the user privileges that have been used for reported privilege analysis policies. It does not include the user privilege grant paths.

DBA_UNUSED_USERPRIVS

Lists the user privileges that have not been used for reported privilege analysis policies. It does not include the user privilege grant paths.

DBA_USED_USERPRIVS_PATH

Lists the user privileges that have been used for reported privilege analysis policies. It includes the user privilege grant paths.

DBA_UNUSED_USERPRIVS_PATH

Lists the privileges that have not been used for reported privilege analysis policies. It includes the user privilege grant paths.

See Also:

Oracle Database Reference for a detailed description of these data dictionary views