What is privilege analysis?
To determine the least privilege access, you need to find information about the actual privilege usage in a database. For example, you might need to know the privileges required to run an application module or the 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 (one analysis policy at a time). It captures direct and indirect role grants.
- Afterwards, 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.”
Types of Privilege Analysis
- Role: You must provide a list of roles. If a used privilege is from one of the provided roles, Oracle Database analyzes the privilege use.
- Context: 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: 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, the privilege is analyzed.
- Database: If you do not specify conditions in your privilege analysis policy, 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.)
– 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 an analysis policy driven by a role or context.
– You cannot analyze the privileges of the SYS user.
What Are Your Tools and Prerequisites?
To analyze privileges, use Enterprise Manager Cloud Control or the DBMS_PRIVILEGE_CAPTURE package. You can perform privilege analysis with or without having Database Vault configured and enabled. But 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 to view the generated report.
Managing Privilege Analysis Policies
General Steps for Managing Privilege Analysis
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.
5. Optionally, disable and then drop the privilege analysis policy. Dropping a privilege analysis policy deletes the analyzed privilege reports associated with the policy.
Analyzing ANY Privilege Use
1. Create User Accounts
2. Create and Enable a Privilege Analysis Policy: You define the privilege analysis policy by
using the CREATE_CAPTURE procedure of the DBMS_PRIVILEGE_CAPTURE package.
3. Use the READ ANY TABLE System Privilege
4. Disable the Privilege Analysis Policy
5. Generate and View a Privilege Analysis Report
6. Remove the Components for This Tutorial
SELECT USERNAME, SYS_PRIV, OBJECT_OWNER, OBJECT_NAME FROM DBA_USED_PRIVS WHERE USERNAME = 'APP_USER';
Analyzing Privilege Use by a User Who Has the DBA Role
1. Create User Accounts.
2. Create and Enable a Privilege Analysis Policy.
3. Perform the Database Tuning Operations.
4. Disable the Privilege Analysis Policy.
5. Generate and View a Privilege Analysis Report.
6. Remove the Components for This Tutorial.
SELECT USERNAME, SYS_PRIV, USED_ROLE, PATH FROM DBA_USED_SYSPRIVS_PATH WHERE USERNAME = 'TJONES' ORDER BY USERNAME, SYS_PRIV, USED_ROLE;