Skip Headers
Oracle® Database Vault Administrator's Guide
12c Release 1 (12.1)

E17608-18
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

4 Performing Privilege Analysis to Find Privilege Use

This section contains:

What Is Privilege Analysis?

This section contains:

About Privilege Analysis

Before you create an Oracle Database Vault policy, you can perform privilege analysis to find information about privilege usage for a database according to a specified condition, such as privileges to run an application module or privileges used in a given user session. The privilege analysis includes both system privileges and object privileges. When a user performs an action and you want to monitor the privileges that are used for this action, you can create and enable a privilege analysis policy. Afterward, you can generate a report that describes the used privileges. To view this report, query the data dictionary views under "Privilege Analysis Policy and Report Data Dictionary Views". Using this information, you can design policies based on the privileges that users exercise.

You can perform privilege analysis with or without having Database Vault configured and enabled.

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

Who Can Perform Privilege Analysis?

To analyze privilege use, you use the DBMS_PRIVILEGE_CAPTURE PL/SQL package, and you must be granted the CAPTURE_ADMIN role to use the DBMS_PRIVILEGE_CAPTURE package.

Types of Privilege Analysis

You can manage privilege analysis in the following conditions:

  • Role analysis. You must provide a list of roles. If a used privilege is from one of the provided roles, then Oracle Database analyzes the privilege use.

  • Context analysis. You must specify a Boolean expression only with the SYS_CONTEXT function. The used privileges are analyzed if the condition evaluates to TRUE.

  • Role and context analysis. You must provide both a list of roles to analyze and a SYS_CONTEXT Boolean expression for the condition. When a used privilege is from one of the analyzed roles and the given context condition is satisfied, then the privilege is analyzed.

  • Database analysis. If you do not specify conditions in your privilege analysis policy, then the all privilege use in the database is analyzed, except for privileges that user SYS exercises. (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.

Benefits and Use Cases of Privilege Analysis

Analyzing privilege use is beneficial in the following scenarios:

Finding Over-Privileged Users

When applications are being developed, some security administrators initially grant many powerful system privileges and roles to application developers because at that stage they (the administrators) 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.

Finding Unnecessarily Granted Privileges of Application Database Users

When an application accesses a database, the privileges of the account that is used to access the 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.

How Does a Multitenant Environment Affect Privilege Analysis?

If you are using a multitenant environment, each privilege analysis policy only analyzes and reports privileges exercised within the pluggable database (PDB) where the privilege analysis resides.

See Also:

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

Creating and Managing Privilege Analysis Policies

This section contains:

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, which provides the EXECUTE privilege for the DBMS_PRIVILEGE_CAPTURE package and the SELECT privilege on the DBA_* report views. 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

The general steps that you use to analyze privileges are as follows:

  1. Define the privilege analysis policy.

  2. Enable the privilege analysis policy.

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

  3. Disable the privilege analysis policy's recording of privilege use.

    This step enables you to define a snapshot of the privilege based on an ending time.

  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 analyzed privilege reports associated with the policy.

Creating a Privilege Analysis Policy

This section contains:

About Creating a Privilege Analysis Policy

When a policy is created, it resides not in the schema of the user who created it, but in the SYS schema. However, the user who created it 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.

Creating a Privilege Analysis Policy in Enterprise Manager Cloud Control

  1. In Enterprise Manager, access the Database home page as a user who has been granted the CAPTURE_ADMIN role.

    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.

    The Privilege Analysis: Create Policy page appears.

    Description of priv_analysis_create.gif follows
    Description of the illustration priv_analysis_create.gif

  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.

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

    • Scope: Select from the following types:

      • Database analyses all privileges used in the entire database, except privileges from user SYS.

      • Role analyses privileges from one or more of roles that you will specify. If you select this option, then the Create Policy page displays the Available Roles list.

        Description of priv_analysis_role.gif follows
        Description of the illustration priv_analysis_role.gif

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

        Description of priv_analysis_context.gif follows
        Description of the illustration priv_analysis_context.gif

      • Role and Context analyses 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 analyze privilege use, see "Enabling a Privilege Analysis Policy Using DBMS_PRIVILEGE_CAPTURE".

Creating a Privilege Analysis Policy Using DBMS_PRIVILEGE_CAPTURE

To create a privilege analysis policy using the DBMS_PRIVILEGE_CAPTURE package, use the DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE procedure. After you create the privilege analysis policy, you can find it listed in the DBA_PRIV_CAPTURES data dictionary view.

The syntax for the CREATE_CAPTURE procedure is:

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. (You can find the names of existing policies by querying the NAME column of the DBA_PRIV_CAPTURES view.)

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

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

    Enter one of the following types:

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

    • DBMS_PRIVILEGE_CAPTURE.G_ROLE: Analyses privileges from one of the specified roles. If you enter DBMS_PRIVILEGE_CAPTURE.G_ROLE for the type parameter, then you must also specify the roles parameter.

    • DBMS_PRIVILEGE_CAPTURE.G_CONTEXT: Analyses privileges when the condition specified by the condition parameter is 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: Analyses privileges from one of the specified roles when the context condition is 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 )
    

* 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

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 All Privilege Use in a Database

BEGIN
 DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
  name          => 'all_privs_capture',
  description   => 'Captures all privilege use',
  type          => DBMS_PRIVILEGE_CAPTURE.G_DATABASE);
END;
/
EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ('all_privs_capture');

Example 4-2 shows how to analyze used privileges from the PUBLIC role.

Example 4-2 Privilege Analysis of PUBLIC Role Privilege Usage

BEGIN
 DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
  name          => 'public_privs_capture',
  description   => 'Captures privilege use by PUBLIC',
  type          => DBMS_PRIVILEGE_CAPTURE.G_ROLE,
  roles         => role_name_list('public');
END;
/
EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ('public_privs_capture');

Example 4-3 shows how to analyze the privilege usage of two roles.

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

BEGIN
 DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
  name          => 'audit_roles_capture',
  description   => 'Captures AUDITOR and AUDIT_ADMIN role use',
  type          => DBMS_PRIVILEGE_CAPTURE.G_ROLE,
  roles         => role_name_list('auditor', 'audit_admin');
END;
/
EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ('audit_roles_capture');

Example 4-4 shows how to analyze privileges used to run the Accounts Payable module.

Example 4-4 Privilege Analysis of Privileges During Application Use

BEGIN
 DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
  name             => 'acct_payable_capture',
  description      => 'Captures privilege use during Accounts Payable app use',
  type             => DBMS_PRIVILEGE_CAPTURE.G_CONTEXT,
  condition        => 'SYS_CONTEXT(''USERENV'', ''MODULE'')=''Account Payable''');
END;
/
EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ('acct_payable_capture');

Example 4-5 shows how to analyze the privileges used from the PUBLIC role while the Accounts Payable module is running.

Example 4-5 Privilege Analysis of PUBLIC Role Privileges During Application Use

BEGIN
 DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
  name            => 'acct_payable_public_role_analysis',
  description     => 'Analyzes PUBLIC role use during Accts Payable app use',
  type            => DBMS_PRIVILEGE_CAPTURE.G_ROLE_AND_CONTEXT,
  roles           => role_name_list('PUBLIC'),
  condition       => 'SYS_CONTEXT(''USERENV'', ''MODULE'')=''Accounts Payable''');
END;
/
EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ('acct_payable_public_role_analysis');

Example 4-6 shows how to analyze the privileges used by session user APPSYS when running the Account Payable application module.

Example 4-6 Privilege Analysis of APPSYS Privileges During Application Access

BEGIN
 DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
  name         => 'appsys_accts_payable_analysis',
  description  => 'Analyzes APPSYS role priv use for Accts Payable module',
  type         => DBMS_PRIVILEGE_CAPTURE.G_CONTEXT,
  condition    => 'SYS_CONTEXT(''USERENV'', ''MODULE'')=''Accounts Payable'' 
                   AND SYS_CONTEXT(''USERENV'', ''SESSION_USER'')=''APPSYS''');
END;
/
EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ('appsys_accts_payable_analysis');

Enabling a Privilege Analysis Policy

This section contains:

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

  1. In Enterprise Manager, access the Database home page as a user who has been granted the CAPTURE_ADMIN role.

    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.

    Description of priv_analysis_enable.gif follows
    Description of the illustration priv_analysis_enable.gif

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

You can enable a privilege policy by using the DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE procedure. To find a list of existing privilege analysis policies, query the DBA_PRIV_CAPTURES data dictionary view.

Example 4-7 shows how to enable the privilege analysis policy logon_users_analysis.

Example 4-7 Enabling a Privilege Analysis Policy

EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ('logon_users_analysis');

Disabling a Privilege Analysis Policy

This section contains:

About Disabling a Privilege Analysis Policy

You must disable the privilege analysis policy before you can generate a privilege analysis report. Once 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

  1. In Enterprise Manager, access the Database home page as a user who has been granted the CAPTURE_ADMIN role.

    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, specify a time to stop the privilege analysis policy.

    To stop the policy now, select Now. 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

You can use the DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE procedure to disable a privilege policy. To find a list of privilege analysis policies, query the DBA_PRIV_CAPTURES data dictionary view.

Example 4-8 shows how to disable the privilege analysis policy logon_users_analysis.

Example 4-8 Disabling a Privilege Analysis Policy

EXEC DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE ('logon_users_analysis');

Generating a Privilege Analysis Report

This section contains:

About Generating a Privilege Analysis Report

After the privilege analysis policy has been enabled and you are ready to see the results, 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 re-grant 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

  1. In Enterprise Manager, access the Database home page as a user who has been granted the CAPTURE_ADMIN role.

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

Accessing Privilege Analysis Reports Using Cloud Control

  1. Generate the privilege analysis report.

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

  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 which roles have been used or not used.

    Description of priv_analysis_report.gif follows
    Description of the illustration priv_analysis_report.gif

  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 re-grant to users as necessary.

Generating a Privilege Analysis Report Using the DBMS_PRIVILEGE_CAPTURE Package

You can run the DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT procedure to generate a report showing the results of the privilege capture.

Example 4-9 shows how to generate a report for the privilege analysis policy logon_users_analysis.

Example 4-9 Generating a Privilege Analysis Report

EXEC DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT ('logon_users_analysis');

You can query the used privileges from DBA_USED_* data dictionary views with privilege grant paths.

Dropping a Privilege Analysis Policy

This section contains:

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

  1. In Enterprise Manager, access the Database home page as a user who has been granted the CAPTURE_ADMIN role.

    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

To drop a privilege analysis policy, run the DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE procedure. You must first disable the policy before you can drop it. You can find a list of privilege analysis policies by querying the DBA_PRIV_CAPTURES data dictionary view.

Example 4-10 shows how to drop a disabled privilege analysis policy.

Example 4-10 Dropping a Privilege Analysis Policy

EXEC DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE ('logon_users_analysis');

Creating Roles and Managing Privileges Using Cloud Control

This section contains:

About Creating Roles and Managing Privileges Using Cloud Control

In Enterprise Manager Cloud Control, after you have created a policy and generated a report, you then can create roles and revoke or regrant privileges and roles based on the findings of the generated reports.

Creating a Role from a Privilege Analysis Report Using Cloud Control

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

  1. Access the privilege analysis report.

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

  2. On the privilege analysis page, select the policy name, then from Actions menu, click Create Role.

  3. On the Create Role page, provide the following details, and then click OK:

    • Select the policy report using 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.

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

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

Revoking and Regranting Roles and Privileges Using Cloud Control

  1. Access the privilege analysis report.

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

  2. In the Privilege Analysis Reports page, expand the grantee whose privileges you want to revoke or re-grant.

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

    Description of priv_analysis_rev.gif follows
    Description of the illustration priv_analysis_rev.gif

  3. 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 Re-grant button is enabled.

  4. To revoke the role or privilege, select Revoke; to re-grant it, select Re-grant.

  5. In the Confirmation window, select Yes.

Generating a Revoke or Regrant Script Using Cloud Control

This section contains:

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.

Generating a Revoke Script

  1. In Enterprise Manager, access the Database home page as a user who has been granted the CAPTURE_ADMIN role.

    See Oracle Database 2 Day DBA for more information.

  2. From the Security menu, select Privilege Analysis.

  3. Ensure that the reports you want have been generated.

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

  4. In the Privilege Analysis page, from the Actions menu, select Revoke Scripts.

  5. On the Revoke Scripts page, click Generate.

    The generate revoke script details wizard is displayed.

  6. In the Script Details page, select a policy name from the menu against which the revoke script needs to be prepared.

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

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

  9. Click Save.

    A Confirmation window appears.

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

Generating a Regrant Script

  1. In Enterprise Manager, access the Database home page as a user who has been granted the CAPTURE_ADMIN role.

    See Oracle Database 2 Day DBA for more information.

  2. From the Security menu, select Privilege Analysis.

  3. Ensure that the reports you want have been generated.

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

  4. In the Privilege Analysis page, from the Actions menu, select Revoke Scripts.

  5. 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 section contains:

About This Tutorial

In this tutorial, you analyze the use of the SELECT ANY TABLE system privilege.

Step 1: Create User Accounts

  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, connect to the appropriate 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, SELECT ANY TABLE TO app_user;
    

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

Step 2: Create and Enable a 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 SELECT 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

  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 SELECT ANY TABLE privilege on the HR.EMPLOYEES table.

    USERNAME   SYS_PRIV             OBJECT_OWNER  OBJECT_NAME
    ---------- -------------------- ------------- -----------------------
    APP_USER   SELECT ANY TABLE     HR            EMPLOYEES
    APP_USER   CREATE SESSION
    APP_USER                        SYS           ORA$BASE
    APP_USER                        SYS           DUAL
    APP_USER                        SYSTEM        PRODUCT_PRIVS
    APP_USER                        SYS           DBMS_APPLICATION_INFO
    APP_USER                        SYS           DUAL
    

Step 6: Remove the Components for This Tutorial

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

    CONNECT SYSTEM -- Or, CONNECT SYSTEM@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 section contains:

About This Tutorial

In this tutorial, you will analyze the system and object privilege use of a user who has been granted the DBA role and who performs database tuning operations.

Step 1: Create User Accounts

  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, log into the appropriate 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

  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             => '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

  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

  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
    TJONES    USE        PUBLIC     SYS        ORA$BASE               EDITION
    
  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

  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

Table 4-1 lists data dictionary views that you can use to find information about analyzed privileges.

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