Introduction
It is all too common to find database users that have privileges over and above those required for the function they perform. Developers or DBAs, for expediency whilst developing a system, will quite often grant users such privileges as select an table, delete any table, update any table etc, or in extreme cases DBA role. Unfortunately, by the time the application is fully developed, it seems there is no will, budget or consideration to remove the higher level privileges for more moderate and accurate privileges.
It is then left to the conscientious developers, DBAs or application support personnel to try a resolve the glaring security hole in a system that in many cases may already be live. It is not an envious task. If the DBA revokes a privilege and breaks the application he is castigated and dragged over the hot coals of the serious incident team. If the DBA leaves things as they are and there is a security breach as a result of the elevated privileges, then again, the DBA is often the individual that takes the heat with managers asking how did he allow this user to have such privileges. (Quite often its the same managers that said there was not the time or the budget to fix the issue before going live).
In 12c there is now a package to help security conscious individuals identify what permissions are used by a user. Thus allowing the the permissions not used to be revoked.
Useful Documentation
Oracle Documentation for Privilege Analysis
Step by Step - Set-up the policy and enable the policy
1. Set-up a capture policy called check_user_privs for the user testuser0001.
- sqlplus '/ as sysdba'
- SQL> BEGIN
- SQL> DBMS_PRIVILEGE_CAPTURE.create_capture(
- SQL> name => 'check_user_privs',
- SQL> type => DBMS_PRIVILEGE_CAPTURE.g_context,
- SQL> condition => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''TESTUSER001'''
- SQL> );
- SQL> END;
- SQL> /
Note - By default the policy will be created disabled.
2. Enable the capture policy
- sqlplus '/ as sysdba'
- SQL> BEGIN
- SQL> DBMS_PRIVILEGE_CAPTURE.enable_capture('check_user_privs');
- SQL> end;
- SQL> /
Leave the policy in place for a period of time. The amount of time will be dependent on the application. The DBA needs to be sure that the application has performed all possible actions to ensure they have captured all privilege usage. It may be easier to enable the policy for set periods of time over a few weeks analysing the data after each period. Trying to analyse several days or weeks of data in one go may be quite onerous.
Step by Step - Disable the policy and generate the results.
1. Disable the policy.
- sqlplus '/ as sysdba'
- SQL> begin
- SQL> DBMS_PRIVILEGE_CAPTURE.disable_capture('check_user_privs');
- SQL> END;
- SQL> /
2. Generate the results.
- sqlplus '/ as sysdba'
- SQL> BEGIN
- SQL> DBMS_PRIVILEGE_CAPTURE.generate_result('check_user_privs');
- SQL> end;
- SQL> /
The following views can now be accessed to find out what privileges were or were not made use of by the user and what captures exist.
- DBA_PRIV_CAPTURES
- DBA_USED_OBJPRIVS
- DBA_USED_OBJPRIVS_PATH
- DBA_USED_PRIVS
- DBA_USED_PUBPRIVS
- DBA_USED_SYSPRIVS
- DBA_USED_SYSPRIVS_PATH
- DBA_USED_USERPRIVS
- DBA_USED_USERPRIVS_PATH
- DBA_UNUSED_OBJPRIVS
- DBA_UNUSED_OBJPRIVS_PATH
- DBA_UNUSED_PRIVS
- DBA_UNUSED_SYSPRIVS
- DBA_UNUSED_SYSPRIVS_PATH
- DBA_UNUSED_USERPRIVS
- DBA_UNUSED_USERPRIVS_PATH
Step by Step - Drop the policy.
- sqlplus '/ as sysdba'
- SQL> BEGIN
- SQL> DBMS_PRIVILEGE_CAPTURE.drop_capture(
- SQL> name => 'check_user_privs'
- SQL> );
- SQL> END;
- SQL> /
Note When you drop the policy you also remove the result form the views listed above.
