📋Steps in this guide1/8

Capture Privilege Usage (DBMS_PRIVILEGE_CAPTURE) in Oracle Database 12c Release 1 (12.1)

Use the DBMS_PRIVILEGE_CAPTURE package to capture privilege usage, helping you to identify unused privileges.

oracle 12cconfigurationintermediate

by OracleDba

19 views

Basic Usage

In order to use the package you must be granted the role. Regardless of what you are trying to monitor, the basic usage of the package is the same. - Create a privilege analysis policy. ( ) - Enable it. ( ) - Wait for the required analysis period. - Disable the privilege analysis policy. ( ) - Analyze the results. ( and query dictionary views) - Drop the policy if it, and the recorded data, is no longer needed. ( ) The main differences between analysis runs will be based around the call to procedure, which is discussed below. The time waited between enabling and disabling the capture is a really import part of the process. You must wait for a representative period of time, or you might miss some important activity. For example, some privileges may be associated with tasks which happen infrequently, like year end jobs. If you don't sample during a representative period, you may incorrectly conclude certain privileges are unnecessary. In a multitenant environment, the policies are container-specific.

CREATE_CAPTURE

The procedure allows you to create privilege analysis policies with varying degrees of granularity. - : Analyzes all privilege usage on the database, except the SYS user. The and parameters are not needed. - : Analyzes all privilege usage by the roles specified in the parameter. Use the function to specify the roles. - : Analyzes all privilege usage when the boolean expression specified in the parameter evaluates to TRUE. Conditions can include combinations of calls to the . - : Analyzes all privilege usage when both the and criteria are true. All policies are created in a disabled state. The following code gives a simple example of each. The view displays information on the existing privilege capture policies.

Code/Command (click line numbers to comment):

-- Connect to a privileged using in a PDB.
conn / as sysdba
alter session set container = pdb1;

-- Whole database (type = G_DATABASE).
begin
  dbms_privilege_capture.create_capture(
    name        => 'db_pol',
    type        => dbms_privilege_capture.g_database
  );
end;
/

-- One or more roles (type = G_ROLE).
begin
  dbms_privilege_capture.create_capture(
    name        => 'role_pol',
    type        => dbms_privilege_capture.g_role,
    roles       => role_name_list('DBA', 'RESOURCE')
  );
end;
/

-- A user defined condition, when user is TEST (type = G_CONTEXT).
begin
  dbms_privilege_capture.create_capture(
    name        => 'cond_pol',
    type        => dbms_privilege_capture.g_context,
    condition   => 'sys_context(''userenv'', ''session_user'') = ''TEST'''
  );
end;
/

-- Combination of roles and conditions (type = G_ROLE_AND_CONTEXT).
begin
  dbms_privilege_capture.create_capture(
    name        => 'role_cond_pol',
    type        => dbms_privilege_capture.g_role_and_context,
    roles       => role_name_list('dba', 'resource'),
    condition   => 'sys_context(''userenv'', ''session_user'') in (''TEST'',''EMP'')'
  );
end;
/

column name format a15
column roles format a20
column context format a30
set linesize 100

select name,
       type,
       enabled,
       roles,
       context
from   dba_priv_captures
order by name;

NAME            TYPE             E ROLES                CONTEXT
--------------- ---------------- - -------------------- ------------------------------
cond_pol        CONTEXT          N                      SYS_CONTEXT('USERENV', 'SESSIO
                                                        N_USER') = 'TEST'

db_pol          DATABASE         N
role_cond_pol   ROLE_AND_CONTEXT N ROLE_ID_LIST(4, 3)   SYS_CONTEXT('USERENV', 'SESSIO
                                                        N_USER') IN ('TEST','EMP')

role_pol        ROLE             N ROLE_ID_LIST(4, 3)

4 rows selected.

SQL>

ENABLE_CAPTURE

The procedure is used to enable a capture policy. Typically, only one analysis policy can be enabled at a time. The exception to this is one and one none policy can be enabled at the same time.

Code/Command (click line numbers to comment):

begin
  dbms_privilege_capture.enable_capture('db_pol');
  dbms_privilege_capture.enable_capture('cond_pol');
end;
/

DISABLE_CAPTURE

As soon as you have waited a representative amount of time, the capture can be disabled using the procedure.

Code/Command (click line numbers to comment):

begin
  dbms_privilege_capture.disable_capture('db_pol');
  dbms_privilege_capture.disable_capture('cond_pol');
end;
/

GENERATE_RESULTS

Once a capture is complete, the procedure should be used to push the captured information to the data dictionary views.

Code/Command (click line numbers to comment):

begin
  dbms_privilege_capture.generate_result('db_pol');
end;
/

Privilege Analysis Views

The following views have been added in Oracle 12c to allow you to query the results of privilege analysis runs. - DBA_PRIV_CAPTURES - DBA_USED_OBJPRIVS - DBA_USED_OBJPRIVS_PATH - DBA_USED_PRIVS - DBA_USED_PUBPRIVS - DBA_USED_SYSPRIVS - DBA_USED_SYSPRIVS_PATH - DBA_USED_USERPRIVS - DBA_USED_USERPRIVS_PATH DBA_UNUSED_OBJPRIVS DBA_UNUSED_OBJPRIVS_PATH DBA_UNUSED_PRIVS DBA_UNUSED_SYSPRIVS DBA_UNUSED_SYSPRIVS_PATH DBA_UNUSED_USERPRIVS DBA_UNUSED_USERPRIVS_PATH - DBA_UNUSED_OBJPRIVS - DBA_UNUSED_OBJPRIVS_PATH - DBA_UNUSED_PRIVS - DBA_UNUSED_SYSPRIVS - DBA_UNUSED_SYSPRIVS_PATH - DBA_UNUSED_USERPRIVS - DBA_UNUSED_USERPRIVS_PATH The information displayed by these views will help you decide which grants and roles should be amended.

DROP_CAPTURE

Once your analysis is complete, you can optionally choose to drop the captured information. Only disabled policies can be dropped.

Code/Command (click line numbers to comment):

begin
  dbms_privilege_capture.drop_capture('cond_pol');
  dbms_privilege_capture.drop_capture('db_pol');
  dbms_privilege_capture.drop_capture('role_cond_pol');
  dbms_privilege_capture.drop_capture('role_pol');
end;
/

Example

In this section we will look at an example of privilege analysis. Create a user with a high degree of privilege by giving it the and roles. Start capturing the privilege usage for these roles against this user. Perform some actions as the user. Disable the capture process and push the results to the data dictionary. Check the privileges that were used during the capture period by querying the data dictionary. What system privileges were used during the capture period? We can get that information from the , or views. These look straight forward, with the exception of the privilege. This would need to be investigated further, but in many cases, this would just be one of those eccentricities of Oracle that can be ignored. If you have the ability to create tables, you also have the ability to index those tables. As a result, the use of the privilege is not really necessary in most cases. How were these privileges granted to the user? We can get that information from the view. So the privileges came from a variety of roles, but looking at the output from the column, all of them stem from the grant of the DBA role. What object privileges were necessary? We can get this information from the , or views. How were these privileges granted to the user? We can get that information from the view. Once again, the privileges came from a variety of roles, but looking at the output from the column, all of them stem from the grant of the DBA role. What can we conclude from this? - All privileges used were granted via the DBA role, so no direct privileges are necessary. - With the exception of the privilege, which would need further investigation in a real situation, all the privileges used are quite basic, so this user really doesn't need the and roles. So the solution here seems quite simple. Create a custom role to apply any necessary privileges, then revoke the and roles. With the analysis complete, we can optionally remove the captured information from the data dictionary. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

conn / as sysdba
alter session set container = pdb1;

create user priv_test_user identified by priv_test_user;
grant dba, resource to priv_test_user;

begin
  dbms_privilege_capture.create_capture(
    name        => 'dba_res_user_pol',
    type        => dbms_privilege_capture.g_role_and_context,
    roles       => role_name_list('DBA', 'RESOURCE'),
    condition   => 'sys_context(''userenv'', ''session_user'') = ''PRIV_TEST_USER'''
  );

  dbms_privilege_capture.enable_capture(
    name        => 'dba_res_user_pol'
  );
end;
/

conn priv_test_user/priv_test_user@pdb1

create table tab1 (
  id number,
  description varchar2(50),
  constraint tab1_px primary key (id)
);

create sequence tab1_seq;

create view tab1_view as
select * from tab1;

insert into tab1
select level, 'Description of ' || to_char(level)
from   dual
connect by level <= 5;

commit;

select name from v$database;

conn / as sysdba
alter session set container = pdb1;

begin
  dbms_privilege_capture.disable_capture(
    name        => 'dba_res_user_pol'
  );

  dbms_privilege_capture.generate_result(
    name        => 'dba_res_user_pol'
  );
end;
/

column username format a20
column sys_priv format a20

select username, sys_priv
from   dba_used_sysprivs
where  capture = 'dba_res_user_pol'
order by username, sys_priv;

USERNAME             SYS_PRIV
-------------------- --------------------
PRIV_TEST_USER       CREATE ANY INDEX
PRIV_TEST_USER       CREATE SEQUENCE
PRIV_TEST_USER       CREATE SESSION
PRIV_TEST_USER       CREATE TABLE
PRIV_TEST_USER       CREATE VIEW

5 rows selected.

SQL>

column username format a20
column used_role format a30
column sys_priv format a20
column path format a50
set linesize 200

select username, sys_priv, used_role, path
from   dba_used_sysprivs_path
where  capture = 'dba_res_user_pol'
order by username, sys_priv;

USERNAME             SYS_PRIV             USED_ROLE                      PATH
-------------------- -------------------- ------------------------------ --------------------------------------------------
PRIV_TEST_USER       CREATE ANY INDEX     IMP_FULL_DATABASE              GRANT_PATH('PRIV_TEST_USER', 'DBA', 'IMP_FULL_DATA
                                                                         BASE')

PRIV_TEST_USER       CREATE ANY INDEX     IMP_FULL_DATABASE              GRANT_PATH('PRIV_TEST_USER', 'DBA', 'DATAPUMP_IMP_
                                                                         FULL_DATABASE', 'IMP_FULL_DATABASE')

PRIV_TEST_USER       CREATE SEQUENCE      OLAP_DBA                       GRANT_PATH('PRIV_TEST_USER', 'DBA', 'OLAP_DBA')
PRIV_TEST_USER       CREATE SESSION       EM_EXPRESS_BASIC               GRANT_PATH('PRIV_TEST_USER', 'DBA', 'EM_EXPRESS_AL
                                                                         L', 'EM_EXPRESS_BASIC')

PRIV_TEST_USER       CREATE TABLE         DATAPUMP_EXP_FULL_DATABASE     GRANT_PATH('PRIV_TEST_USER', 'DBA', 'DATAPUMP_EXP_
                                                                         FULL_DATABASE', 'EXP_FULL_DATABASE')

PRIV_TEST_USER       CREATE TABLE         DATAPUMP_EXP_FULL_DATABASE     GRANT_PATH('PRIV_TEST_USER', 'DBA', 'DATAPUMP_EXP_
                                                                         FULL_DATABASE')

PRIV_TEST_USER       CREATE VIEW          DBA                            GRANT_PATH('PRIV_TEST_USER', 'DBA')

7 rows selected.

SQL>

column username format a20
column obj_priv format a8
column object_owner format a15
column object_name format a20
column object_type format a11

select username, obj_priv, object_owner, object_name, object_type 
from   dba_used_objprivs
where  capture = 'dba_res_user_pol';

USERNAME             OBJ_PRIV OBJECT_OWNER    OBJECT_NAME          OBJECT_TYPE
-------------------- -------- --------------- -------------------- -----------
PRIV_TEST_USER       SELECT   SYS             V_$DATABASE          VIEW

1 row selected.

SQL>

column username format a20
column obj_priv format a8
column object_owner format a15
column object_name format a20
column used_role format a20
column path format a30
set linesize 200

select username, obj_priv, object_owner, object_name, used_role, path 
from   dba_used_objprivs_path
where  capture = 'dba_res_user_pol';

USERNAME             OBJ_PRIV OBJECT_OWNER    OBJECT_NAME          USED_ROLE            PATH
-------------------- -------- --------------- -------------------- -------------------- ------------------------------
PRIV_TEST_USER       SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('PRIV_TEST_USER', '
                                                                                        DBA', 'SELECT_CATALOG_ROLE')

PRIV_TEST_USER       SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('PRIV_TEST_USER', '
                                                                                        DBA', 'EXP_FULL_DATABASE', 'SE
                                                                                        LECT_CATALOG_ROLE')

PRIV_TEST_USER       SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('PRIV_TEST_USER', '
                                                                                        DBA', 'IMP_FULL_DATABASE', 'SE
                                                                                        LECT_CATALOG_ROLE')

PRIV_TEST_USER       SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('PRIV_TEST_USER', '
                                                                                        DBA', 'DATAPUMP_EXP_FULL_DATAB
                                                                                        ASE', 'EXP_FULL_DATABASE', 'SE
                                                                                        LECT_CATALOG_ROLE')

PRIV_TEST_USER       SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('PRIV_TEST_USER', '
                                                                                        DBA', 'DATAPUMP_IMP_FULL_DATAB
                                                                                        ASE', 'EXP_FULL_DATABASE', 'SE
                                                                                        LECT_CATALOG_ROLE')

PRIV_TEST_USER       SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('PRIV_TEST_USER', '
                                                                                        DBA', 'DATAPUMP_IMP_FULL_DATAB
                                                                                        ASE', 'IMP_FULL_DATABASE', 'SE
                                                                                        LECT_CATALOG_ROLE')

PRIV_TEST_USER       SELECT   SYS             V_$DATABASE          SELECT_CATALOG_ROLE  GRANT_PATH('PRIV_TEST_USER', '
                                                                                        DBA', 'EM_EXPRESS_ALL', 'EM_EX
                                                                                        PRESS_BASIC', 'SELECT_CATALOG_
                                                                                        ROLE')


7 rows selected.

SQL>

conn / as sysdba
alter session set container = pdb1;

create role custom_role;
grant create sequence to custom_role;
grant create session to custom_role;
grant create table to custom_role;
grant create view to custom_role;
grant select on sys.v_$database to custom_role;

grant custom_role to priv_test_user;
revoke dba, resource from priv_test_user;

begin
  dbms_privilege_capture.drop_capture(
    name        => 'dba_res_user_pol'
  );
end;
/

Comments (0)

Please to add comments

No comments yet. Be the first to comment!