One of the top features of the Oracle Database 12c that attracted me is capturing the privileges used (and unused). This feature is introduced to strengthen the security of the database to enforce the principle of least privilege. Many application users today have excessive privileges assigned, and believe me, have seen many applications connect to the database using an account with “DBA” role or “DBA like” role… Using the “Privilege Capture” feature of the Oracle Database 12c, we can now clearly and exactly know which privileges are used by the application or user and which privileges can be revoked.
New PLSQL API DBMS_PRIVILEGE_CAPTURE is added to 12c. The procedures available in this package are:
- CREATE_CAPTURE
- ENABLE_CAPTURE
- DISABLE_CAPTURE
- GENERATE_RESULT
- DROP_CAPTURE
The new default role in 12c database CAPTURE_ADMIN has execute privilege on this new package. CAPTURE_ADMIN role is granted to DBA role as well.
As the subprograms or procedure names indicate, you must first define a policy using the CREATE_CAPTURE procedure. The policy can be at the database level analysis, or role level or based on a context.
Once policy is created, it is not enabled by default. Use the ENABLE_CAPTURE program to enable the policy.
After the analysis period, you disable the policy using the DISABLE_CAPTURE program.
Once the policy is disabled, you are ready to generate the results using the GENERATE_RESULT program. Once the policy is created by giving a name, that name is the parameter to all the other programs.
By running the GENERATE_RESULT, the following new data dictionary views are populated…
- DBA_USED_xxx
- DBA_USED_xxx_PATH
- DBA_UNUSED_xxx
- DBA_UNUSED_xxx_PATH
The DBA_USED_ views show the privileges used by the user for the policy. The DBA_UNUSED_ views show the privileges that are assigned to the user, but are not used. The _PATH views show the privilege path (how the privileged was given to the user, through which role).
Reference to the views:
- 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_COL_TABS
- DBA_UNUSED_OBJPRIVS
- DBA_UNUSED_OBJPRIVS_PATH
- DBA_UNUSED_PRIVS
- DBA_UNUSED_SYSPRIVS
- DBA_UNUSED_SYSPRIVS_PATH
- DBA_UNUSED_USERPRIVS
- DBA_UNUSED_USERPRIVS_PATH
Once you have documented all the information required, you can use the DROP_CAPTURE program to delete the policy and all the usage data collected.
Update on 01-May-2014
Published an article on this subject in OTech Magazine. Download the article from http://www.slideshare.net/slideshow/embed_code/34193245
Read the article and other articles in OTech Magazine at http://www.otechmag.com/2014/otech-magazine-winter-2014/