I like order, I like patterns, I like consistency in general, but especially in the databases I manage.
Most of the databases I manage, were not created or setup by me. I inherited these databases with all the good, the bad and the ugly.
Can you guess which category privilege management fits in?
THE UGLY.
Some databases are setup right (at least from my point of view), and some are not.
I see lots of roles with different naming convention fulfilling the same purpose. One is called HR_SELECT_ROLE, another one HR_QUERY, and yet another HR_RO. The privileges granted to them are similar.
I see users working within the same department with different roles setup, having way too many privileges.
I see application accounts granted DBA role, when a few system privileges would be enough.
Once users, roles and privileges are setup, nobody wants to take any risks in changing them.
What if something breaks? What if the application is no longer running ok? What if a user cannot run the month end process?
All of the above are valid concerns!
What if there is a tool that can tell you what privileges a user is using over a period of time, and what privileges it is not using? Wouldn’t that be great?
In 12.1 Oracle introduced Privilege Analysis feature. This feature helps you in many ways:
- it allows you to track the privileges used by a certain user
- it allows you to identify the privileges not used by a certain user
- it allows you to safely revoke the unused privileges, once you identified them
- it allows you to retire unused roles, and safely standardize the roles and naming conventions.
- it allows you strengthen the security in your database, and have a least privilege model for the users.
The Privilege Analysis requires you to run Enterprise Edition, and it used to require the Oracle Database Vault license in 12c. However in 18c Oracle has changed the licensing, and it no longer requires Database Vault license. You can check out the licensing document here.
Whether or not it still requires licensing in 12c, I am not sure. You should check that with Oracle.
The Privilege Analysis is done with the package DBMS_PRIVILEGE_CAPTURE, and it contains a series of steps:
- create a privilege analysis policy
- enable the policy
- leave the policy enabled for a period of time: hours, days, weeks, months. As long as you feel comfortable that you captured the right amount of information.
- disable the policy
- analyze your result and findings
- take action if possible: remove unused privileges.
I am sure you are curious to see how DBMS_PRIVILEGE_CAPTURE works!
Stay tuned next week to find out how to use the Privilege Analysis feature!
