Elimnate Unuse privilege by certain users Oracle12c : Define the target objects to analyze

Step A: Define the target objects to analyze

There are three kinds of analysis that can be performed:
Database – analyze used privileges within the entire database (except for those being used by administrative users).
To run a database level analysis:

 exec SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE ( -
 name=>’All_Priv_Analysis’, -
 description=>’Captures everything’, - 
 type => dbms_privilege_capture.g_database);

Role – analyze the privileges used by any specified role.

 exec SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE ( -
 name=>’Role_Priv_Analysis’, -
 description=>’Captures for a role’, -
 type=> dbms_privilege_capture.g_role, -
 roles=> role_name_list(‘ROLE1’,’ROLE2’);

Context Specific – analyze the privileges used through a specified module.

 exec SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE ( -
 name=>’Priv_GL_Analysis’, -
 description=>’Captures for GL App’, -
 type=>dbms_privilege_capture.g.context, -
 condition=> ‘SYS_CONTEXT -
 (‘ ‘USERENV’ ‘,’ ‘MODULE’ ‘)=’ ‘General Ledger’ ‘ ‘);

Step B: Start (and end) the capture

 exec SYS.DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ( - name =>’All_Priv_Analysis’;

After a reasonable period of time:

  exec SYS.DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE ( - name =>’All_Priv_Analysis’;

Step C: Generate the Analysis Report

exec SYS.DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT ( - name=>’All_Priv_Analysis’;

Step D: Review the Results

There are new dictionary views that can be used to examine the results.

• DBA_USED_SYSPRIVS shows which system privileges were used during the analysis period
• DBA_USED_OBJPRIVS shows which object privileges were used during the analysis period
• DBA_USED_OBJPRIVS_PATH shows how the privileges were granted – direct to the user or via a role
• DBA_UNUSED_PRIVS shows privileges that were granted to the users but not used during the analysis period
• DBA_PRIV_CAPTURES shows information about the various captures that have been created

To drop a capture that is no longer required use the following:

exec DBMS_PRIVILEGE_CAPTURE.DROP_CAPUTURE(‘All_Priv_Analysis’);

With these new features to help with Privilege