Skip to content

Privilege Analysis

Privilege Analysis published on Комментариев к записи Privilege Analysis нет

Oracle Database 12c offers a new package to analyze used privileges. Privilege Analysis requires Oracle Database Vault, which is a licensed option.
good tutorial:

saved copy

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]

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;

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.

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

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.

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

Stop capturing. Use the DISABLE_CAPTURE procedure.

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

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

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

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

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

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

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

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.

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

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

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

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

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;

Stop capturing.

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

Generate the capture results.

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

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

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.

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

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.

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

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

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;

Stop capturing.

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

Generate the capture results.

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

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

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;

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

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

Delete Captures
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;

Delete all captures.

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

Resetting Your Environment

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;

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

[свернуть]

Screen Shot 2015-12-02 at 16.35.48

Screen Shot 2015-12-02 at 16.34.56

Privilege Analysis Types and Conditions
When creating an analysis, you first define the targeted objects to be analyzed in used privileges. You do that by setting the type of analysis:
• Database analysis: If no condition is given, it analyzes the used privileges (except privileges used by administrative users) within the whole database.
• Role analysis: If roles are defined, it analyzes the privileges exercised through any given role. For example, if you create a privilege analysis policy to analyze on PUBLIC, the privileges that are directly and indirectly granted to PUBLIC are analyzed when they are used.
• Context-specific analysis: If the contexts are defined, it analyzes the privileges that are used through a given application module or specified contexts.

Reporting Used Privileges
Your third step is to generate a report. Reporting includes two types of results:
• Used privileges visible in DBA_USED_xxx and DBA_USED_xxx_PATH views
• Unused privileges visible in DBA_UNUSED_xxx and DBA_UNUSED_xxx_PATH views

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Primary Sidebar

Яндекс.Метрика