Determining Least Privilege Access Using Privilege Analysis

This tutorial covers the steps required to capture privileges used by users during a short period of time to help you decide which privileges need to be revoked or kept.

Time to Complete

Approximately 25 minutes.

Introduction

The Privilege Analysis feature allows you to:
Define a privilege capture
Start a privilege analysis during a period of time
Generate results about privileges and roles used and unused during the analysis period
Compare used and unused privileges using views to decide which privileges and or roles need to be revoked or kept
Delete capture analysis
Scenario

In this tutorial, you will perform three types of analysis:

Capture privileges used by all users
Capture privileges used through roles
Capture privileges used through contexts
Prerequisites

Before starting this tutorial, you should:
Oracle Database 12c should be installed.
You need a started database.
The environment used in the development of this tutorial is as follows:

ORACLE_HOME: /u01/app/oracle/product/12.1.0
TNS Listener port: 1521
Container databases:
SID: cdb1
SID: cdb2
Pluggable databases (in cdb1):
pdb1
pdb2
Non-CDB SID: noncdb

Create Users, Roles, Tables and Grant Privileges and Roles
Use SQL*Plus to connect as system to create the users JIM and TOM , roles, and grant privileges and or roles.

Connect as SYSTEM:

. oraenv
[enter cdb1 at the prompt]
[code language=”sql”]
sqlplus system/oracle@localhost:1521/pdb1

Create users.
create user jim identified by jim;
create user tom identified by tom;
create user ann identified by ann;

Create roles.
create role HR_MGR;
create role SALES_CLERK;
create role ANALYST;

Grant privileges.
grant create session to jim, tom, ann;

grant select, update, delete, insert on hr.employees to HR_MGR;
grant HR_MGR to JIM;

grant select on sh.sales to SALES_CLERK;
grant SALES_CLERK to TOM;

grant select any table to ANALYST;
grant ANALYST to ann;
[/code]

Define the Captures
In this section, you will prepare the three types of captures.

Define the capture of privileges used by all users.
Define the capture of privileges used by all users.
Use the DBMS_PRIVILEGE_CAPTURE package and the CREATE_CAPTURE procedure with the appropriate type of capture.
[code language=”sql”]
exec SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE ( –
name => ‘All_privs’, –
description => ‘All privs used’, –
type => dbms_privilege_capture.g_database)
[/code]

Define the capture of privileges used through roles.
Define a capture of privileges used by roles HR_MGR, ANALYST, and SALES_CLERK. Use the DBMS_PRIVILEGE_CAPTURE package and the CREATE_CAPTURE procedure with the appropriate type of capture and the list of roles analyzed.
[code language=”sql”]
exec SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE ( –
name => ‘Role_privs’, –
description => ‘Privs used by HR_MGR, SALES_CLERK’, –
type => dbms_privilege_capture.g_role, –
roles => role_name_list(‘HR_MGR’, ‘SALES_CLERK’,’ANALYST’))
[/code]

Define the capture of privileges used through contexts.
Define a capture of privileges used by the user TOM or by the specific role SALES_CLERK.
Use the DBMS_PRIVILEGE_CAPTURE package and the CREATE_CAPTURE procedure with the appropriate type of capture, the list of roles analyzed and the context in which the analysis would take place once started.
[code language=”sql”]
exec SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE ( –
name => ‘Special_capt’, –
description => ‘Special’, –
type => dbms_privilege_capture.g_role_and_context, –
roles => role_name_list(‘SALES_CLERK’), –
condition => ‘SYS_CONTEXT(”USERENV”,”SESSION_USER”)=”TOM”’)
[/code]

List the existing captures.
Use the DBA_PRIV_CAPTURES view.
Note: You may see different role ID results.
[code language=”sql”]
COL name FORMAT A12
COL type FORMAT A16
COL enabled FORMAT A2
COL roles FORMAT A24 COL context FORMAT A43

select name, type, enabled, roles, context
from dba_priv_captures;
[/code]

Start Privilege Captures and Analyze

Start and analyze the capture of privileges used by all users.
Start capturing the privileges while users are performing their daily work using privileges. Use the ENABLE_CAPTURE procedure.
[code language=”sql”]
exec SYS.DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE (name => ‘All_privs’)

exit
[/code]
[Note that you must exit SQL*Plus and then relaunch SQL*Plus in the next step, otherwise you may see unintended results in the privilege analysis views in later steps of this tutorial.]
The users JIM, TOM, and ANN run SQL statements using privileges. JIM, and ANN who select rows from HR.EMPLOYEES table and TOM who selects rows from SH.SALES table.
[code language=”sql”]
sqlplus jim/jim@localhost:1521/pdb1
select * from hr.employees where salary < 3000;

connect tom/tom@localhost:1521/pdb1
select * from sh.sales where amount_sold < 6.42 and cust_id = 6452;

connect ann/ann@localhost:1521/pdb1
select * from hr.employees where salary > 3000 and rownum <=5 order by salary desc;

exit
[/code]
[Note you must exit SQL*Plus and then relaunch SQL*Plus in the next step.]

Stop capturing. Use the DISABLE_CAPTURE procedure.
[code language=”sql”]
sqlplus system/oracle@localhost:1521/pdb1
exec SYS.DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE (name => ‘All_privs’)
[/code]
Generate the capture results. It may take a few minutes. Use the GENERATE_RESULT procedure.
[code language=”sql”]
exec SYS.DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (name => ‘All_privs’)
[/code]
Display the object privileges used during the capture period. Use the DBA_USED_OBJPRIVS view.
[code language=”sql”]
COL username FORMAT A10
COL object_owner FORMAT A12
COL object_name FORMAT A30
COL obj_priv FORMAT A25

select username, object_owner, object_name, obj_priv
from dba_used_objprivs
where username in (‘JIM’, ‘TOM’)
and object_name in (‘SALES’ , ‘EMPLOYEES’);
[/code]
Display the privileges used by ANN. Use the DBA_USED_PRIVS view. Does Ann, or the ANALYST role, really need the SELECT ANY TABLE privilege?
[code language=”sql”]
select username, used_role, sys_priv, object_name FROM dba_used_privs
where username =’ANN’ and object_name = ‘EMPLOYEES’;
[/code]

Display the system privileges used. Use the DBA_USED_SYSPRIVS view.
[code language=”sql”]
select username, sys_priv FROM dba_used_sysprivs
where username IN (‘JIM’, ‘TOM’, ‘ANN’);
[/code]

Display the path of the privileges used if the privileges were granted to roles, and roles to users. Use the DBA_USED_OBJPRIVS_PATH view.
[code language=”sql”]
COL object_name FORMAT A12
COL path FORMAT A32
COL obj_priv FORMAT A10

select username, obj_priv, object_name, path
from dba_used_objprivs_path
where username IN (‘TOM’,’JIM’,’ANN’)
and object_name IN (‘SALES’,’EMPLOYEES’);
[/code]
JIM is granted select, update, delete, insert privileges on HR.EMPLOYEES table through HR_MGR role. He used only the SELECT privilege thus far.
Use the DBA_UNUSED_PRIVS view to list the unused privileges. You can decide which of the unused privileges can be revoked if necessary.
[code language=”sql”]
select username, sys_priv, obj_priv, object_name, path
from dba_unused_privs
where username=’JIM’
and object_name = ‘EMPLOYEES’;
[/code]

Start and analyze the capture of privileges used through roles.
Delete the previous capture so as to remove all previous captured information from the views. Use the DROP_CAPTURE procedure.
[code language=”sql”]
exec SYS.DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE (name => ‘All_privs’)
[/code]
Verify that there is no data left from the All_privs capture. Use the DBA_UNUSED_PRIVS view. There should be no rows returned by the query.
[code language=”sql”]
select username, sys_priv, obj_priv, object_name, path
from dba_unused_privs
where username=’JIM’;
[/code]
Start capturing the privileges while users are performing their daily work using roles.
[code language=”sql”]
exec SYS.DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE (name => ‘Role_privs’)
[/code]
The users JIM and TOM run SQL statements using privileges. JIM who selects rows from HR.EMPLOYEES table and TOM who selects rows from SH.SALES table.

[code language=”sql”]
connect jim/jim@localhost:1521/pdb1
select * from hr.employees where salary < 3000;

connect tom/tom@localhost:1521/pdb1
select * from sh.sales where amount_sold < 6.42 and cust_id = 6452;
[/code]

Stop capturing.

[code language=”sql”]
connect system/oracle@localhost:1521/pdb1
exec SYS.DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE (name => ‘Role_privs’)
[/code]

Generate the capture results.
[code language=”sql”]
exec SYS.DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (name =>’Role_privs’)
[/code]

Display the object privileges used by the roles HR_MGR and SALES_CLERK during the capture period.
[code language=”sql”]
COL used_role FORMAT A14

select username, object_owner, object_name, obj_priv, used_role
from dba_used_objprivs
where used_role in (‘HR_MGR’, ‘SALES_CLERK’);
[/code]

Display the system privileges used by the roles HR_MGR and SALES_CLERK during the capture period.
[code language=”sql”]
select username, sys_priv, used_role
from dba_used_sysprivs
where used_role in (‘HR_MGR’, ‘SALES_CLERK’);
[/code]

No rows are returned because no system privileges were used.

HR_MGR is granted select, update, delete on HR.EMPLOYEES table. The role used by JIM during the capture period used the SELECT privilege.
The unused privileges are visible in DBA_UNUSED_PRIVS view. You can decide which of the unused privileges or role can be revoked if necessary.
[code language=”sql”]
COL username FORMAT A12
COL path FORMAT A32
COL object FORMAT A10
COL sys_priv FORMAT A10
COL obj_priv FORMAT A10

select sys_priv, obj_priv, object_name, path
from dba_unused_privs
where rolename IN (‘HR_MGR’, ‘SALES_CLERK’);
[/code]

Define the capture of privileges used through contexts.
Delete the previous capture so as to remove all captured information from the views.

exec SYS.DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE (name => ‘Role_privs’)

Verify that there is no data left from the Role_privs capture. There should be no rows returned by the query.
[code language=”sql”]
select sys_priv, obj_priv, object_name, path
from dba_unused_privs
where rolename IN (‘HR_MGR’, ‘SALES_CLERK’);
[/code]

Start capturing the privileges while users are performing their daily work using privileges.

[code language=”sql”]
exec SYS.DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE (name => ‘Special_capt’)
The users JIM and TOM run SQL statements using privileges. JIM selects rows from HR.EMPLOYEES table and TOM selects rows from SH.SALES table.

connect jim/jim@localhost:1521/pdb1
select * from hr.employees where salary < 3000;

connect tom/tom@localhost:1521/pdb1
select * from sh.sales where amount_sold < 6.42 and cust_id = 6452;
[/code]

Stop capturing.

[code language=”sql”]
connect system/oracle@localhost:1521/pdb1
exec SYS.DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE (name =>’Special_capt’)
[/code]

Generate the capture results.

[code language=”sql”]
exec SYS.DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (name => ‘Special_capt’)
[/code]
Display the object privileges used during the capture period.

[code language=”sql”]
COL username FORMAT A10
COL owner FORMAT A8
COL object FORMAT A16
COL obj_priv FORMAT A10
COL used_role FORMAT A14

select username, object_owner, object_name, obj_priv, used_role
from dba_used_objprivs
where username =’TOM’ OR used_role=’SALES_CLERK’;
[/code]

Check whether any system privileges were used. There should be no rows returned, because no system privileges were used.

[code language=”sql”]
select username, sys_priv from dba_used_sysprivs;
[/code]
Display the path of the privileges used if the privileges were granted to roles, and roles to users. Use the DBA_USED_OBJPRIVS_PATH view.

[code language=”sql”]
COL object FORMAT A12
COL path FORMAT A32
COL obj_priv FORMAT A10

select username, obj_priv, object_name, path
from dba_used_objprivs_path
where username IN (‘TOM’,’JIM’)
and object_name IN (‘SALES’,’EMPLOYEES’);
[/code]

TOM is granted the select privilege on the SH.SALES table through SALES_CLERK role. He used the privilege.
The unused privs are visible in DBA_UNUSED_PRIVS view.
The query returns no rows, because there are no unused privileges. Therefore, there is no privilege that has been unnecessarily granted.

[code language=”sql”]
select username, sys_priv, obj_priv, object_name, path
from dba_unused_privs
where username=’TOM’ OR rolename=’SALES_CLERK’;
[/code]

Delete Captures
List all captures to delete:

[code language=”sql”]
COL name FORMAT A12
COL type FORMAT A12
COL enabled FORMAT A2
COL roles FORMAT A26

select name, type, enabled, roles, context
from dba_priv_captures;
[/code]

Delete all captures.

[code language=”sql”]
exec SYS.DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE (name => ‘Special_capt’)
[/code]

Resetting Your Environment

Delete users and roles.

[code language=”sql”]
drop user jim cascade;
drop user tom cascade;
drop user ann cascade;

drop role hr_mgr;
drop role sales_clerk;
drop role analyst;
[/code]

Summary
In this tutorial, you have learned how to:

Define a privilege capture for:
Privileges used by all users
Privileges used through roles
Privileges used through contexts
Start a privilege analysis during a period of time
Generate results about privileges and roles used and unused during the analysis period
Compare used and unused privileges using views to decide which privileges and / or roles need to be revoked or kept
Delete capture analysis
Resources

To learn more about Oracle Database 12c refer to additional OBEs in the Oracle Learning Library
Credits

Lead Curriculum Developer: Dominique Jeunot
Other Contributors: Jean-Francois Verrier
Help OLL About Oracle Contact Us Terms of Use