In the first part of this blog post, we described what Privilege Analysis is and explored its possibilities. Now let’s discover how you can implement it.

How can I implement it?

First of all, you need to use an administrative user with, at least, the role CAPTURE_ADMIN.

Create a policy

With this administrative user, you can create a capture policy. In other words, you describe the scope of the privileges you want to capture, based on the available types of capture: Role-based, Context-based, Role-and-Context-based, or Database-wide. These 4 types are detailed in the previous blog post of this serie.

In this example, we are going to implement a Role-and-Context-based capture thanks to DBMS_PRIVILEGE_CAPTURE package. Let’s say we have an applicative user APP_METAL that has a specific role R_METAL_MANAGER, and we want to capture every privilege used by APP_METAL thanks to role R_METAL_MANAGER:

The newly created capture policy is then listed in view DBA_PRIV_CAPTURES :

Enable a capture

You may have noticed that, so far, the capture policy is listed as ENABLED=N in view DBA_PRIV_CAPTURES. Once we create the capture policy, we can now effectively start the capture by enabling the policy and naming the current run, if you want to identify it easier later :

You don’t have to name the capture : if you omit this parameter, then its default name will be NULL.

View DBA_PRIV_CAPTURES now shows a currently running capture, with the name defined above :

Disable a capture

When the period of time we want to cover has come to an end, we can stop the running capture by disabling it :

Generate report

At this time, the capture results are not available yet. We need to generate them :

Display results

The generated results will be available in the following dictionary views :

Used or unused privilegesUsed or unused privileges + corresponding grant path
All type of privilegesDBA_[UN]USED_PRIVS
Objects privilegesDBA_[UN]USED_OBJPRIVSDBA_[UN]USED_OBJPRIVS_PATH
System privilegesDBA_[UN]USED_SYSPRIVSDBA_[UN]USED_SYSPRIVS_PATH
User privilegesDBA_[UN]USED_USERPRIVSDBA_[UN]USED_USERPRIVS_PATH

Then we can simply query one of those views to get the required results, for example :

Drop a policy

When the capture policy is no longer needed, we can drop it :

But be aware that dropping the policy will also drop the associated results in dictionary views!

Is it manageable in Oracle Enterprise Manager?

All the actions we performed can also be executed with Oracle Enterprise Manager, starting with version 12.3. On the target page, navigate to menu “Security” > “Privilege Analysis”. Then you’ll have a list to the existing policies, and a “Create” button:

Privilege Analysis policy creation in Oracle Enterprise Manager
Privilege Analysis policy creation in Oracle Enterprise Manager

You’ll notice that the required fields are exactly the same we used when invoking procedure DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE in PL/SQL :

Oracle Enterprise Manager capture

So now what?

First of all, make sure to run capture on a realistic workload, during any appropriate period: Run every application, batch process, yearly process possible, to make sure you do not miss any relevant activity. You can also schedule a capture during a specific moment, by using Oracle Scheduler for example. Do not forget to end the capture and generate the report afterwards, to get the results stored in dictionary views.

Privilege Analysis is a great help to understand what is going on in your system, to spot the privileges that are used and not used. In the end, Privilege Analysis will help you revoke any unnecessary privilege, that could cause harm to your system, if misused, intentionally or not. Privilege Analysis is instrumental if you or your company are not in control of the code (when using a third-party app from an external vendor, for example.)