Have you ever been in a situation where users in a database seem to have way too many privileges? And you are willing to revoke some of them, but at the same time, afraid that you might withdraw something essential by mistake, thus, creating a major outage in your company?

Of course, it is really recommended to audit your database, at least in production.

Auditing is the monitoring and recording of configured database actions, from both database users and nondatabase users.

– Oracle Database Release 19 Security Guide

Auditing is an excellent way to know exactly what is going on in a database, therefore know which privileges are actually used.

Of course, every action defined in an audit policy will be captured: so you better be selective! And you also need to take care of space monitoring (audit trails can get really big!), purge (to precisely avoid space problems), or archive (legal reasons might force you to keep audit trails for a certain time).

But is there another way, different from audit, to answer the following vital questions :

  • Which privilege has been actually used, at least once, in my database?
  • Which privilege is granted but should not?

Actually yes, there is another straightforward way: Privilege Analysis!

What is Privilege Analysis?

Privilege analysis dynamically captures privileges used by database users and applications.

– Oracle Database Release 19 Security Guide

It is a feature that captures privileges that are used (and NOT used) in a database, at runtime. Its findings are stored in data dictionary views.

How useful is it?

Privilege Analysis will help you enforce the principle of least privilege …

The principle of least privilege recommends that users, systems, and processes only have access to resources […] that are absolutely necessary to perform their assigned function. […It] limits the potential damage posed via unsanctioned activities, whether intentional or unintentional.

– Center for Internet Security

… and avoid privilege creep:

Privilege creep is when an employee changes roles and keeps their previously assigned privileges, while also gaining new privileges.

– Center for Internet Security

For example, you may want to compare privileges requested by an application manager, to what is useful and really used, especially if you or your company are not in charge of creating or maintaining the application. It will also help you bust users that have a dangerous privilege like select any table!

Can I use it?

Privilege Analysis used to be part of the extra-cost option “Oracle Database Vault” … But since 2018, it is not anymore and can be used with Oracle Enterprise Edition at no extra-cost, starting from Oracle Database version 12.1.

How does it work?

First of all, Privilege Analysis work in a non-CDB, a CDB root, or in a PDB.

There are several types of Privilege Analysis policies :

  • Role-based: The privileges will be captured if they are used by a session that is granted the specified list of roles.
  • Context-based: The privileges will be captured if a session meets the condition of a SYS_CONTEXT function.
  • Role-and-Context-based: It is the combination of the two types above, so privileges will be captured according to a specified list of roles AND a SYS_CONTEXT condition that is met.
  • Database-wide: All the privileges in the database will be captured, except those used by SYS. It is an unconditional analysis.

Only one policy can be enabled at a time, but …

The only exception is that you can enable a database-wide privilege analysis policy at the same time as a non-database-wide privilege analysis policy, such as a role or context attribute-driven analysis policy.

– Oracle Database Release 19 Security Guide

How do I implement it?

In the second part of this blog post, we will see how easy Privilege Analysis can be implemented, using DBMS_PRIVILEGE_CAPTURE PL/SQL package and also Oracle Enterprise Manager. Stay tuned!