One of the most important Security Principals while maintain your Enterprise Database Landscape is implementing the “least privilege” principle. The least privilege principle is applied from IT Security level on any enterprise system and system administrators should regularly review and fix excessive privileges.
One of the very nice “security” features of Oracle 12c release, is the ability to track and capture privileges of running applications using the package (DBMS_PRIVILEGE_CAPTURE).
To simulate on a sandbox machine:
Remark: if you are going to use an account other than ‘SYS’, then you need to grant the account “CAPTURE_ADMIN” role.
- Create a privilege analysis policy, for example:
BEGIN
DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
name => ‘MON_PRIV_DB’,
type => DBMS_PRIVILEGE_CAPTURE.G_DATABASE
);
END;
/
In my example I used (G_DATABASE) which implies to monitor and track all privilege for all accounts in the database except ‘SYS’ account. You have the ability to configure the policy to different things:
DBMS_PRIVILEGE_CAPTURE.G_ROLE : which track privielges on the context of of specific oracle database role.
DBMS_PRIVILEGE_CAPTURE.G_CONTECT: will track privileges based on conditions that are evaluated to ‘TRUE’.
DBMS_PRIVILEGE_CAPTURE.G_ROLE_AND_CONTECT : will track privileges for a role or a list of roles and if a condition is evaluated to ‘TRUE’.
- After creating the privilege analysis policy you will need to “enable” it, by executing the following:
exec DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE(name => ‘MON_PRIV_DB’);
Remark: you can’t enable more than 2 privilege analysis policy.
Error reference: ORA-47934: Two privilege captures are already enabled
- After a while you can later on disable the policy (for example after 1-2 hours) by executing the following:
exec DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE(name => ‘MON_PRIV_DB’);
- Generate the results by executing the following:
exec DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT(name => ‘MON_PRIV_DB’);
- There are many views that you can query to find very useful information, for example:
* this query will list users with system privileges that are not being used by this account
select * from DBA_UNUSED_SYSPRIVS ;
- At the end you can DROP the capture policy (if you want to) by executing the following:
exec DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE(name => ‘MON_PRIV_DB’);
Update November 2018:
This feature is not anymore licensed as part of database vault, so you can use it if you have Oracle EE
resources:
http://www.petefinnigan.com/weblog/archives/00001448.htm
