5.3 Determining Least Privilege Access Using Privilege Analysis

«« Previous
Next »»

1. Overview


Purpose

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

2. 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.

1. Connect as SYSTEM:

. oraenv
[enter cdb1 at the prompt]

sqlplus system/oracle@localhost:1521/pdb1

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

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

4. 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;

3. Define the Captures


In this section, you will prepare the three types of captures.
  • Define the capture of privileges used by all users.
1. 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.

exec SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE ( - 
        name           =>  'All_privs', - 
        description    =>  'All privs used', -
        type           =>   dbms_privilege_capture.g_database)
  • Define the capture of privileges used through roles.
1. 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.

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'))
  • Define the capture of privileges used through contexts
1. 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. 

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''')
  • List the existing captures.
1. Use the DBA_PRIV_CAPTURES view.
Note: You may see different role ID results. 
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;

Determining Least Privilege Access Using Privilege Analysis


4. Start Privilege Captures and Analyze


  • Start and analyze the capture of privileges used by all users.
1. Start capturing the privileges while users are performing their daily work using privileges. Use the ENABLE_CAPTURE procedure. 

exec SYS.DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE (name => 'All_privs')

exit
[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.]

2. 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. 

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
[Note you must exit SQL*Plus and then relaunch SQL*Plus in the next step.]


3. Stop capturing. Use the DISABLE_CAPTURE procedure. 

sqlplus system/oracle@localhost:1521/pdb1
exec SYS.DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE (name => 'All_privs')

4. Generate the capture results. It may take a few minutes. Use the GENERATE_RESULT procedure. 

exec SYS.DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (name => 'All_privs')

5. Display the object privileges used during the capture period. Use the DBA_USED_OBJPRIVS view. 

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');

Determining Least Privilege Access Using Privilege Analysis

6. 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?

select username, used_role, sys_priv, object_name FROM dba_used_privs
where  username ='ANN' and object_name = 'EMPLOYEES';

Determining Least Privilege Access Using Privilege Analysis

7. Display the system privileges used. Use the DBA_USED_SYSPRIVS view. 

select username, sys_priv FROM dba_used_sysprivs
where  username IN ('JIM', 'TOM', 'ANN');

Determining Least Privilege Access Using Privilege Analysis

8. 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. 

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');

Determining Least Privilege Access Using Privilege Analysis

9. 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. 

select username, sys_priv, obj_priv, object_name, path
from   dba_unused_privs
where  username='JIM'
and object_name = 'EMPLOYEES';

Determining Least Privilege Access Using Privilege Analysis

  • Start and analyze the capture of privileges used through roles.
1. Delete the previous capture so as to remove all previous captured information from the views. Use the DROP_CAPTURE procedure. 

exec SYS.DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE (name => 'All_privs')

2. 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.

select username, sys_priv, obj_priv, object_name, path
from   dba_unused_privs
where  username='JIM';

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

exec SYS.DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE (name => 'Role_privs')

4. 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.
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;

5. Stop capturing. 

connect system/oracle@localhost:1521/pdb1
exec SYS.DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE (name => 'Role_privs')

6. Generate the capture results.

exec SYS.DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (name =>'Role_privs')

7. Display the object privileges used by the roles HR_MGR and SALES_CLERK during the capture period.

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');

Determining Least Privilege Access Using Privilege Analysis

8. Display the system privileges used by the roles HR_MGR and SALES_CLERK during the capture period. 

select username, sys_priv, used_role 
from dba_used_sysprivs
where used_role in ('HR_MGR', 'SALES_CLERK');
No rows are returned because no system privileges were used.

9. 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. 

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');

Determining Least Privilege Access Using Privilege Analysis
  • Define the capture of privileges used through contexts.
1. Delete the previous capture so as to remove all captured information from the views.

exec SYS.DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE (name => 'Role_privs')

2. Verify that there is no data left from the Role_privs capture. There should be no rows returned by the query.

select sys_priv, obj_priv, object_name, path
from   dba_unused_privs
where  rolename IN ('HR_MGR', 'SALES_CLERK');

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

exec SYS.DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE (name => 'Special_capt')

4. 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;

5. Stop capturing.

connect system/oracle@localhost:1521/pdb1
exec SYS.DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE (name =>'Special_capt')

6. Generate the capture results.

exec SYS.DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (name => 'Special_capt')

7. Display the object privileges used during the capture period. 

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';

Determining Least Privilege Access Using Privilege Analysis

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

select username, sys_priv from dba_used_sysprivs;

9. 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. 

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');

Determining Least Privilege Access Using Privilege Analysis

10. 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. 


select username, sys_priv, obj_priv, object_name, path
from   dba_unused_privs
where  username='TOM' OR rolename='SALES_CLERK';

5. Delete Captures


1. List all captures to delete:
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;

Determining Least Privilege Access Using Privilege Analysis

2. Delete all captures.
exec SYS.DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE (name => 'Special_capt')

6. Resetting Your Environment


1. Delete users and roles.

drop user jim cascade;
drop user tom cascade;
drop user ann cascade;

drop role hr_mgr;
drop role sales_clerk;
drop role analyst;

«« Previous
Next »»