Oracle 12c : New Feature – Privilege Analysis

In a common situation present in many database we find that some users have been granted more
privileges than what was needed and these privileges , over time , have remained .
This could be dangerous to the security of the data and the database itself .
In Oracle 12c we have a package called DBMS_PRIVLEGE_CAPTURE through which we are
able to identify the object privileges and unnecessary system that have been granted and revoke
privileges that have not been used .
The analysis of the privileges can be done at the database level , on a single role or to a particular user in the database .
These are the main steps involved:

1. Create the Database, Role or Context privilege analysis policy via DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE
2. Start the analysis of used privileges via DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE
3. Stop the analysis when required via DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE
4. Generate the report via DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT
5. Examine the views like DBA_USED_SYSPRIVS to see which privilege were used during our monitoring.

SQL> exec SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(-
> name => ‘AUDIT_DBA_SCOTT’,-
> type => dbms_privilege_capture.g_role_and_context,-
> roles => role_name_list (‘DBA’),-
> condition => ‘SYS_CONTEXT (”USERENV”,”SESSION_USER”)=”SCOTT”’);
PL/SQL procedure successfully completed.
SQL> exec SYS.DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE(-
> name => ‘AUDIT_DBA_SCOTT’);
PL/SQL procedure successfully completed.

— we monitor the user for a significant period —

SQL> exec SYS.DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE(-
> name => ‘AUDIT_DBA_SCOTT’);
PL/SQL procedure successfully completed.
SQL> exec SYS.DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT(-
> name => ‘AUDIT_DBA_SCOTT’);
PL/SQL procedure successfully completed.
SQL> select username, sys_priv from dba_used_sysprivs;
USERNAME SYS_PRIV
——————– —————————————-
SCOTT CREATE SESSION
SCOTT CREATE TABLE
SHCOTT ALTER TABLESPACE