In the testing phase, we normally test functionality and break the application. There is something that’s missing. Now, most developers are not experts in how privileges work in Oracle, this is not a bad thing because privileges can be complex to implement. Because of this, applications frequently are moved into production over privileged. Addressing over privileged applications is really not all that difficult to accomplish, it just takes some time.
The basics:
- To run dbms_privilege_capture you’ll need to grant the CAPTURE_ADMIN role to the user running dbms_privilege_capture.
- dbms_privilege_capture can capture in four modes:
- G_DATABASE mode: will capture all privileges used in the database. I use this one a lot to get a broad picture of what privileges are being used in the database.
- G_ROLE mode: will capture all access to specific roles. Will capture the usage of a role.
- G_CONTEXT: will capture the privileges used when a context test is true .
- G_ROLE_AND_CONTEXT: will capture privileges used when a context test is true and a role is used.
- The create_capture procedure creates a capture policy that defines the conditions when the privilege is captured, along with giving a name for the capture.
sys.dbms_privilege_capture.create_capture(
name in varchar2,
description in varchar2,
type in varchar2,
roles in varchar2,
condition in varchar2);- The enable_capture procedure After you’ve created the capture policy, you’ll need to enable it to start the capture. For the first run, I normally create the capture policy, then enable the capture in the same PL/SQL block.
sys.dbms_privilege_capture.enable_capture(
name in varchar2,
run_name in varchar2 default null
);
- disable_capture: After you’re done capturing privileges, you’ll need to disable the capture, so you can generate the results. The only parameter is the name parameter.
sys.dbms_privilege_capture.disable_capture(
name => 'check_beckys_use_of_processor_and_cashier'
);
- generate_results: Once you’re done capturing the privileges for a run, we need to generate the results. The overhead of running privilege capture is pretty low; however, generating the results will take some CPU and may take a few seconds to finish, depending on how long the privilege capture ran.
sys.dbms_privilege_capture.generate_result(
name in varchar2
run_name in varchar2 default null
dependency in pl/sql boolean default false
);
- drop_capture: After a while, just like anything else, you’ll want to clean up old captures. To do that, you’ll need to drop them. It’s quite simple, just execute the following.
begin
sys.dbms_privilege_capture.drop_capture(name => ‘<capture name>’);
end;
/
Oracle provides the rdms_privilege_capture package and views so you can access what privileges are actually being used by an application. I use cytoscape (open source) to visualize complex role highicarcies and see just where I can prune excess roles and privileges.
Now that we got that out of the way, let’s look at the output. Because we’re using this in testing the software, the focus is on finding out where you can adjust your privileges to achieve least privilege.
SYS.DBA_UNUSED_PRIVS to view any privileges that are unused. You’ll want to put some focus here to trim out privileges that are not being used in your testing.
DBA_USED_PRIVS to view the privileges that were used during your testing. You may find that some of the roles can be broken down to fine grained roles and use role hierarchies, or go the opposite direction and consolidate roles.
DBA_USED_SYSPRIVS to find system privileges that were used during your testing.
DBA_USED_OBJPRIVS to find object privileges that were used during your testing.
DBA_USED_USERPRIVS to find user privileges that were used during your testing.
DBA_USED_PUBPRIVS to find user privileges that were used during your testing. Now, my practice has been to revoke public privileges whenever possible, grant those privileges to named roles, then grant those roles to users.
There are quite a few other views that you can look at to remove unused privileges these include: DBA_UNUSED_PRIVS, DBA_UNUSED_SYSPRIVS_PATH, DBA_UNUSED_SYSPRIVS, DBA_UNUSED_OBJPRIVS_PATH, DBA_UNUSED_OBJPRIVS, DBA_UNUSED_USERPRIVS_PATH, and DBA_UNUSED_USERPRIVS.
I’ll dig deeper into these in a later post.