Oracle 12c Privilege Analysis

Introduction

In this post I'd like to demonstrate another new Oracle 12c security feature - Privilege Analysis. Using this feature, the DBA can easily understand which privileges are actually being used by users. Once the analysis has been completed, the DBA can revoke all of the unnecessary privileges/roles. This feature is only available for Enterprise Edition with Oracle Database Vault (extra cost option).

How does it work?

Oracle introduced a new package, DBMS_PRIVILEGE_CAPTURE which allows you to create a new privilege analysis policy, enable/disable it, generate the results of the analysis and drop the policy once the analysis is over and the policy is not needed anymore. In order to use the DBMS_PRIVILEGE_CAPTURE package you need to be grnted the CAPTURE_ADMIN role.

Demonstration

In order to create a new privilege analysis policy, use the following syntax:

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

Let's start by creating a user named "pini" and grant him the DBA role. Afterwards, we'll create and enable a simple policy named "capture_pini_privs" that simply captures all the privileges used by user pini:

SQL> create user pini identified by pini default tablespace users;
User created.

SQL> grant dba to pini;
Grant succeeded.

SQL> BEGIN
  2    DBMS_PRIVILEGE_CAPTURE.create_capture(
  3      name        => 'capture_pini_privs',
  4      type        => DBMS_PRIVILEGE_CAPTURE.g_context,
  5      condition   => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''PINI'''
  6    );
  7  END;
  8  /
PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_PRIVILEGE_CAPTURE.enable_capture('capture_pini_privs');
PL/SQL procedure successfully completed.

Explanation of the above code:

- In line 3 I set the name of the policy to be "capture_pini_privs"
- In line 4 I set the type to be DBMS_PRIVILEGE_CAPTURE.g_context to captrue privileges of the sessions defined in line 5
- In line 5 I used SYS_CONTEXT to specify capture privileges for user "PINI" only

After we have created the new policy we can verify it via DBA_PRIV_CAPTURES dictionary view that lists all the policies as follows:

SQL> SELECT TYPE, enabled, context FROM DBA_PRIV_CAPTURES;

TYPE             ENABLED    CONTEXT
---------------- ---------- --------------------------------------------------
CONTEXT          Y          SYS_CONTEXT('USERENV', 'SESSION_USER') = 'PINI'

Note that there are several other types that you can use (in line 4) like capturing privileges of the entire database users except of use SYS (using DBMS_PRIVILEGE_CAPTURE.G_DATABASE), or capturing the privileges of specific sessions with specific roles (DBMS_PRIVILEGE_CAPTURE.G_ROLE_AND_CONTEXT) which requires also to specify the "roles" parameter. You can read more about these options in the official documentation for the DBMS_PRIVILEGE_CAPTURE package.

Now, after we have created and enabled to policy, the analysis period has been started.
Next, let's connect with user pini and execute a few commands to test this feature:

SQL> connect pini/pini
Connected.

SQL> create table EMP (id number, name varchar2(20), constraint id_pk PRIMARY KEY (id));
Table created.

SQL> insert into EMP values (1, 'DAVID');
1 row created.

SQL> commit;
Commit complete.

SQL> CREATE OR REPLACE FUNCTION return_num_of_emps
  2     RETURN NUMBER IS
  3     num_of_emps NUMBER;
  4     BEGIN
  5        select count(*)
  6        into num_of_emps
  7        from EMP;
  8        RETURN(num_of_emps);
  9      END;
 10  /
Function created.

SQL> select return_num_of_emps from dual;
RETURN_NUM_OF_EMPS
------------------
                 1
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
o1264np

SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG

Now, I will disable the capture policy and generate the report as follows:

SQL> execute DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE('capture_pini_privs');
PL/SQL procedure successfully completed.

SQL> execute DBMS_PRIVILEGE_CAPTURE.generate_result('capture_pini_privs');
PL/SQL procedure successfully completed.

The output of the report will be printed to various dictionary views like DBA_USED_SYSPRIVS and DBA_USED_OBJPRIVS as you can see in the following demonstration:

SQL> SELECT SYS_PRIV
  2    FROM DBA_USED_SYSPRIVS
  3   WHERE username = 'PINI';

SYS_PRIV
---------------------------------------
CREATE SESSION
CREATE TABLE
CREATE ANY INDEX
CREATE PROCEDURE
UNLIMITED TABLESPACE

SQL> SELECT obj_priv, object_owner, object_name
  2    FROM DBA_USED_OBJPRIVS
  3   WHERE username = 'PINI';

OBJ_PRIV                                 OBJECT_OWNER         OBJECT_NAME
---------------------------------------- -------------------- ----------------------------------------
SELECT                                   SYS                  V_$INSTANCE
SELECT                                   SYS                  V_$DATABASE

Once the analysis has been completed, you can drop the policy. This step is optional and it's goal is to clean to policy-related information from the data-dictionary views. In order to do that use the following syntax:

SQL> execute DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE ('capture_pini_privs');
PL/SQL procedure successfully completed.

Summary

Granting minimum privileges for users is a security best practice, however, many DBAs are struggling with identifying which exact privileges are actually needed and which privileges are unnecessary - and therefore, should be revoked. This feature comes to solve this challenge. I would expect this useful security feature to be available for all Oracle Editions but unfortunately, it's only available for Enterprise Edition with Oracle Database Vault (extra cost option).