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:
|
1 2 3 4 5 6 7 8 9 |
BEGIN DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE( name => 'METAL_DETECTOR', description => 'What the hell is this APP_METAL doing ?', type => DBMS_PRIVILEGE_CAPTURE.G_ROLE_AND_CONTEXT, roles => role_name_list('R_METAL_MANAGER'), condition => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'')=''APP_METAL'''); END; / |
The newly created capture policy is then listed in view DBA_PRIV_CAPTURES :
|
1 2 3 4 5 6 7 |
SQL> select NAME, TYPE, ENABLED, RUN_NAME from DBA_PRIV_CAPTURES; NAME TYPE ENABLED RUN_NAME _________________ ___________________ __________ ___________ METAL_DETECTOR ROLE_AND_CONTEXT N ORA$DEPENDENCY DATABASE N |
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 :
|
1 2 3 4 5 6 |
BEGIN DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ( name => 'METAL_DETECTOR', run_name => 'METAL_DETECTOR_runtime_01'); END; / |
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 :
|
1 2 3 4 5 6 7 |
SQL> select NAME, TYPE, ENABLED, RUN_NAME from DBA_PRIV_CAPTURES; NAME TYPE ENABLED RUN_NAME _________________ ___________________ __________ ____________________________ METAL_DETECTOR ROLE_AND_CONTEXT Y METAL_DETECTOR_RUNTIME_01 ORA$DEPENDENCY DATABASE N |
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 :
|
1 2 3 4 5 6 |
BEGIN DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE ( name => 'METAL_DETECTOR' ); END; / |
Generate report
At this time, the capture results are not available yet. We need to generate them :
|
1 2 3 4 5 6 7 |
BEGIN DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT ( name => 'METAL_DETECTOR', dependency => TRUE ); END; / |
Display results
The generated results will be available in the following dictionary views :
| Used or unused privileges | Used or unused privileges + corresponding grant path | |
| All type of privileges | DBA_[UN]USED_PRIVS | |
| Objects privileges | DBA_[UN]USED_OBJPRIVS | DBA_[UN]USED_OBJPRIVS_PATH |
| System privileges | DBA_[UN]USED_SYSPRIVS | DBA_[UN]USED_SYSPRIVS_PATH |
| User privileges | DBA_[UN]USED_USERPRIVS | DBA_[UN]USED_USERPRIVS_PATH |
Then we can simply query one of those views to get the required results, for example :
|
1 2 3 4 5 6 7 8 |
SQL> select MODULE, USERNAME, USED_ROLE, SYS_PRIV, OBJ_PRIV, OBJECT_NAME, PATH from DBA_USED_PRIVS where CAPTURE like '%METAL%' ; MODULE USERNAME USED_ROLE SYS_PRIV OBJ_PRIV OBJECT_NAME PATH ___________ ____________ __________________ ___________________ ___________ ______________ _______________________________ app_module APP_METAL APP_METAL DELETE ANY TABLE METAL_BANDS [APP_METAL, R_METAL_MANAGER] app_module APP_METAL R_METAL_MANAGER SELECT METAL_BANDS [APP_METAL, R_METAL_MANAGER] |
Drop a policy
When the capture policy is no longer needed, we can drop it :
|
1 2 3 4 5 6 |
BEGIN DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE( name => 'METAL_DETECTOR' ); END; / |
But be aware that dropping the policy will also drop the associated results in dictionary views!
|
1 2 3 4 5 |
SQL> select MODULE, USERNAME, USED_ROLE, SYS_PRIV, OBJ_PRIV, OBJECT_NAME, PATH from DBA_USED_PRIVS where CAPTURE like '%METAL%' ; no rows selected |
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:
You’ll notice that the required fields are exactly the same we used when invoking procedure DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE in PL/SQL :
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.)
