Did you know 12c now has a mechanism to examine which privileges are actually being used by an user, module or for the database as a whole, as opposed to merely the privileges granted?
This is a great security improvement in order give users the privileges that they need precisely without granting too much.
Here’s how is works?
1. First you have to set up a capture process using the new package DBMS_PRIVILEGE_CAPTURE.
# Example:
BEGIN DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE( name => 'all_priv_analysis_pol', description => 'database-wide policy to analyze all privileges', type => DBMS_PRIVILEGE_CAPTURE.G_DATABASE); END; /
This starts the capture process database wide. Let it run for a bit.
2. Now you can either generate a report:
# Example
BEGIN DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT( name => 'all_priv_analysis_pol'); END; /
Or examine the new views:
DBA_USED_PRIVS DBA_UNUSED_PRIVS DBA_USED_OBJPRIVS DBA_UNUSED_OBJPRIVS
3. To turn off you proceed with the following:
BEGIN DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE( name => 'all_priv_analysis_pol'); END; /
So now you ensure that you use the best practice of least privilege for users.
Cheers!
Great article, but please note, that this is a separately licensed product. However, it is included with Advanced Security