What is Privilege Analysis in Oracle Database

What is privilege analysis?

- Privilege Analysis captures the privileges used by database users, including database administrators, at runtime. - Privilege analysis allows you to accurately identify privileges a database account has that are not being used, supporting better decision making about which privileges to remove to create a least-privileged user. - Privilege Analysis compliments the Database Security Assessment Tool, which tells you which users have been granted sensitive database privileges.

Note: You cannot analyze the privileges of the SYS user.

Does Privilege Analysis require additional license?

No, Privilege Analysis is included with the Enterprise Edition of Oracle Database at no additional cost. Privilege Analysis is also available for most Database as a Service (DBaaS) offerings with the exception of Standard Edition.

Privileges required to perform privilege analysis

A user with CAPTURE_ADMIN role or SYSDBA privilege can perform privilege analysis.

How can the Privilege Analysis Policies be created?

They can be created via command line using DBMS_PRIVILEGE_CAPTURE, or in Enterprise Manager Cloud Control.

Steps for managing Privilege Analysis

Create/Define privilege analysis policy.
Enable the policy.This starts recording the privilege usage.
Disable the policy.This stops capturing the privilege usage.
Generate privilege analysis results.This writes the results to the data dictionary views.
Analyze the results to identify candidate privileges to be revoked. You may want to audit the use of a candidate privilege for a period of time before revoking it to ensure that the account does not legitimately use the privilege infrequently.

Types of Privilege Analysis

Role Based privilege use capture. This captures the privileges of the session in which the roles are enabled.
Context Based privilege use capture. This captures the privileges of the sessions with certain specified condition.
Role and context based privilege use capture.This has the functionality of both role based and context based privilege use capture.
Database wide privilege use capture.This captures all the privileges used in the entire database, except the privileges of SYS.

Simple example of privilege analysis

1. User A has CAPTURE_ADMIN role and it performs the privilege analysis of user B. 2. We are creating a context based policy here with the condition of capturing the privileges of user B. 3. User B had been granted SELECT ANY TABLE system privilege sometime earlier for some specific operation/job and it has not been revoked after the operation. 4. Due to this privilege,the user can as well read all the tables of the users who are created later.This is a security threat. 5. Privilege Analysis captures the use of such privileges during run time and helps to keep the database secure. 6. Based on the results,you can decide about granting/revoking the respective privilege.

1. Create a privilege analysis policy by logging in as user A:

BEGIN
 DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
  name           => 'Context Based Policy',
  description    => 'Context Based Policy test',
  type           => DBMS_PRIVILEGE_CAPTURE.G_CONTEXT,
  condition      => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'')=''B''');
END;
/

2. Enable the policy:

BEGIN
  DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE (
   name       => 'Context Based Policy',
   run_name   => 'Context Based Policy first run');
END;
/

3. Open a new session as user B and perform a “select” operation on a table called X.Y and exit the session.

4. In the session that is opened as A,disable the policy:

EXEC DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE ('Context Based Policy');

5.Generate the results to check the usage of SELECT ANY TABLE privilege.

BEGIN
  DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (
    name      => 'Context Based Policy',
    run_name  => 'Context Based Policy first run');
END;
/

6. View the result in the data dictionary table:

SQL> SELECT SYS_PRIV, OBJECT_OWNER, OBJECT_NAME, RUN_NAME FROM DBA_USED_PRIVS WHERE USERNAME = 'B';
     SYS_PRIV         OBJECT_OWNER  OBJECT_NAME             RUN_NAME
     -------------- ------------- ----------------------- ----------------------
     SELECT ANY TABLE   X              Y                     Context Based Policy first run

User B has used SELECT ANY TABLE privilege to access the table X.Y.If this user is not intended to read this table or all the tables in the database,then you can decide on revoking this powerful system privilege from the user.

What is Privilege Analysis in Oracle Database