7 Steps To Setup Privilege Analysis

Last week I showed you how Privilege Analysis can help you sort out and standardize security in your database.

Today, I’ll show you how to setup Privilege Analysis in quick and easy steps.

Steps To Setup Privilege Analysis

Step 1) Create a privilege analysis policy, using DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE

Step 2) Enable the privilege analysis policy, using DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE

Step 3) Leave the policy enabled for a period of time: hours, days, weeks, months. Leave the policy enabled, for as long as you feel comfortable that you captured the right amount of information.
This is the time the users are using the database, and actually using their privileges. The length of time the policy is enabled, depends on how long you need to confirm you captured an adequate amount of information.

Step 4) Disable the privilege analysis policy, using DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE.

Step 5) Generate the findings, and analyze the captured results, using DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT.

Step 6) Take action, when applicable and remove unused privileges.

Step 7) Remove the captured information, if no longer needed, using DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE.

Granularity of the Privilege Analysis

When creating a privilege analysis policy, you have a few options, for the granularity of the analysis.

To implement the granularity, Oracle is using constants as the type parameter of the DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE procedure.

• granularity: database level: all privileges are analyzed in the database, except privileges used by SYS. Use the constant G_DATABASE.
• granularity: role level: all privileges are analyzed for the specified roles. Use the constant G_ROLE.
• granularity: context level: when specific condition applies -> all privileges are analyzed when the specified condition evaluates to true. Use the constant G_CONTEXT.
• granularity: role and context level: all privileges are analyzed for the specified role, when the specified condition evaluates to true. Use the constant G_ROLE_AND_CONTEXT.

To view information about existing privilege analysis policies in the database, you can query the view DBA_PRIV_CAPTURES.

Before we begin with the example, we need to setup a few things:

1) create user hr_test
2) create roles hr_query, hr_admin
3) grant select on tables owned by hr user to the hr_query role
4) grant insert, update, delete on tables owned by hr user to hr_admin role
5) grant hr_query, hr_admin role to the user hr_test

Below are the statements to setup our exercise:

-- create hr_test user:

create user hr_test identified by Oracl3#123;
grant create session to hr_test;

-- create the roles hr_query, hr_admin:

create role hr_query;
create role hr_admin;

-- grant privileges to the roles hr_query, hr_admin:

grant select on hr.employees to hr_query;
grant select on hr.departments to hr_query;
grant select on hr.regions to hr_query;

grant insert, update, delete on hr.employees to hr_admin;
grant insert, update, delete on hr.departments to hr_admin;
grant insert, update, delete on hr.regions to hr_admin;

-- grant the roles to hr_test user

grant hr_test, hr_admin to hr_test;

Once we create the setup, we can implement the privilege analysis.

Privilege Analysis Example:

The steps for different granularity levels are the same. The only difference is in Step 1 Creating The Capture.

1) Create The Capture

To create the capture at the ROLE level, requires the constant G_ROLE to be used.
For our example, let’s analyze the hr_query and hr_admin role.

BEGIN
DBMS_PRIVILEGE_CAPTURE.create_capture(
name        => 'HR_Role_Capture',
type        => DBMS_PRIVILEGE_CAPTURE.G_ROLE,
roles       => role_name_list('HR_QUERY', 'HR_ADMIN')
);
END;
/

To create the capture at the CONTEXT level, requires the constant G_CONTEXT to be used.

For our example, let’s analyze the privileges used by the user hr_test.

BEGIN
DBMS_PRIVILEGE_CAPTURE.create_capture(
name        => 'HR_Context_Capture',
type        => DBMS_PRIVILEGE_CAPTURE.G_CONTEXT,
condition   => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''HR_TEST'''
);
END;
/

To create the capture at ROLE and CONTEXT level, requires the constant G_ROLE_CONTEXT to be used.

For our example, let’s analyze the privileges used in hr_admin role, only used by the user hr_test.

BEGIN
DBMS_PRIVILEGE_CAPTURE.create_capture(
name        => 'HR_Role_Context_Capture',
type        => DBMS_PRIVILEGE_CAPTURE.G_ROLE_AND_CONTEXT,
roles       => role_name_list('HR_ADMIN'),
condition   => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''HR_TEST'''
);
END;
/

To create the capture at the DATABASE level, requires the constant G_DATABASE to be used.

BEGIN
DBMS_PRIVILEGE_CAPTURE.create_capture(
name        => 'DB_Capture',
type        => DBMS_PRIVILEGE_CAPTURE.G_DATABASE
);
END;
/

This is the only step that is different for the different context.

All the other steps are similar.

2) Enable The Capture

exec DBMS_PRIVILEGE_CAPTURE.enable_capture('HR_Role_Capture');
select name, type,enabled,roles from dba_priv_captures;

NAME                           TYPE       ENABLED ROLES
------------------------------ ---------- ------- --------------------------
HR_Role_Capture                ROLE       Y       ROLE_ID_LIST(125, 126)

3) Run Daily Activity.

The hr_test user can run multiple select statements and DML statements on the hr tables.

The duration of this step it is up to you.

4) Disable The Capture

exec DBMS_PRIVILEGE_CAPTURE.disable_capture('HR_Role_Capture');

5) Analyze The Capture

exec DBMS_PRIVILEGE_CAPTURE.generate_result('HR_Role_Capture');

Check used/unused privileges in the data dictionary.

In our example, the monitored roles, had only object privileges granted.

The DBA_USED_OBJPRIVS reports on the used object privileges during the capture.

select CAPTURE, sequence, USERNAME, USED_ROLE, OBJ_PRIV, OBJECT_OWNER, OBJECT_NAME, OBJECT_TYPE 
from DBA_USED_OBJPRIVS order by capture,sequence;

CAPTURE         USERNAME USED_ROLE   OBJ_PRIV   OBJECT_OWN OBJECT_NAME  OBJECT_TYP
--------------- -------- ----------- ---------- ---------- ------------ ----------
HR_Role_Capture HR_TEST  HR_ADMIN    DELETE     HR         EMPLOYEES    TABLE
HR_Role_Capture HR_TEST  HR_QUERY    SELECT     HR         EMPLOYEES    TABLE
HR_Role_Capture HR_TEST  HR_ADMIN    INSERT     HR         REGIONS      TABLE

The DBA_UNUSED_OBJPRIVS reports on the unused object privileges during the capture.

select capture,rolename,OBJ_PRIV,OBJECT_OWNER,OBJECT_NAME,OBJECT_TYPE 
from DBA_UNUSED_OBJPRIVS order by rolename;
...

6) This is the step you take action and remove unused privileges if applicable.

7) Optionally you can remove the capture information collected.

exec DBMS_PRIVILEGE_CAPTURE.drop_capture('HR_Role_Capture');

Let’s put all this together into one mind map, to remember it easier! Feel free to print the mind map!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!