重庆思庄Oracle、Redhat认证学习论坛

标题: Oracle 12c: Track and analyze privilege utilization with DBMS_PRIVILEGE_CAPTURE [打印本页]

作者: 郑全 时间: 2016-10-8 13:01
标题: Oracle 12c: Track and analyze privilege utilization with DBMS_PRIVILEGE_CAPTURE
本帖最后由郑全于 2019-12-9 14:25 编辑

Basic UsageIn order to use the DBMS_PRIVILEGE_CAPTURE package you must be granted the CAPTURE_ADMIN role. Regardless of what you are trying to monitor, the basic usage of the DBMS_PRIVILEGE_CAPTUREpackage is the same.

Create a privilege analysis policy. (CREATE_CAPTURE)
Enable it. (ENABLE_CAPTURE)
Wait for the required analysis period.
Disable the privilege analysis policy. (DISABLE_CAPTURE)
Analyze the results. (GENERATE_RESULT and query dictionary views)
Drop the policy if it, and the recorded data, is no longer needed. (DROP_CAPTURE)

The main differences between analysis runs will be based around the call to CREATE_CAPTURE 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.
Note. In a multitenant environment, the policies are container-specific.
CREATE_CAPTUREThe CREATE_CAPTURE procedure allows you to create privilege analysis policies with varying degrees of granularity.

G_DATABASE : Analyzes all privilege usage on the database, except the SYS user. The ROLES and CONDITION parameters are not needed.
G_ROLE : Analyzes all privilege usage by the roles specified in the ROLES parameter. Use the ROLE_NAME_LIST function to specify the roles.
G_CONTEXT : Analyzes all privilege usage when the boolean expression specified in the CONDITION parameter evaluates to TRUE. Conditions can include combinations of calls to theSYS_CONTEXT.
G_ROLE_AND_CONTEXT : Analyzes all privilege usage when both the ROLES and CONDITION criteria are true.

All policies are created in a disabled state. The following code gives a simple example of each.

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

The DBA_PRIV_CAPTURES view displays information on the existing privilege capture policies.

COLUMN name FORMAT A15COLUMN 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', 'SESSION_USER') = 'TEST'
db_pol       DATABASE       Nrole_cond_pol ROLE_AND_CONTEXT N ROLE_ID_LIST(4, 3) SYS_CONTEXT('USERENV', 'SESSION_USER') IN ('TEST','EMP')
role_pol       ROLE          N ROLE_ID_LIST(4, 3)
4 rows selected.

SQL>

ENABLE_CAPTURE The ENABLE_CAPTURE 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 G_DATABASE and one none G_DATABASEpolicy can be enabled at the same time.

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 DISABLE_CAPTURE procedure.

BEGIN
DBMS_PRIVILEGE_CAPTURE.disable_capture('db_pol');
DBMS_PRIVILEGE_CAPTURE.disable_capture('cond_pol');
END;
/

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

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

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.

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 DBA and RESOURCE roles.

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;

Start capturing the privilege usage for these roles against this 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;
/

Perform some actions as the PRIV_TEST_USER user.

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;

Disable the capture process and push the results to the data dictionary.

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

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 DBA_USED_PRIVS, DBA_USED_SYSPRIVS or DBA_USED_SYSPRIVS_PATH views.

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>

These look straight forward, with the exception of the CREATE ANY INDEX 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 CREATE ANY INDEX privilege is not really necessary in most cases.

How were these privileges granted to the user? We can get that information from the DBA_USED_SYSPRIVS_PATH view.

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_DATABASE')
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_ALL', '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>

So the privileges came from a variety of roles, but looking at the output from the PATH column, all of them stem from the grant of the DBA role.

What object privileges were necessary?

We can get this information from the DBA_USED_PRIVS, DBA_USED_OBJPRIVS or DBA_USED_OBJPRIVS_PATH views.

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>

How were these privileges granted to the user?
We can get that information from the DBA_USED_OBJPRIVS_PATH view.

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', 'SELECT_CATALOG_ROLE')
PRIV_TEST_USER    SELECT SYS          V_$DATABASE       SELECT_CATALOG_ROLE  GRANT_PATH('PRIV_TEST_USER', ' DBA', 'IMP_FULL_DATABASE', 'SELECT_CATALOG_ROLE')
PRIV_TEST_USER    SELECT SYS          V_$DATABASE       SELECT_CATALOG_ROLE  GRANT_PATH('PRIV_TEST_USER', ' DBA', 'DATAPUMP_EXP_FULL_DATABASE', 'EXP_FULL_DATABASE', 'SELECT_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', 'SELECT_CATALOG_ROLE')
PRIV_TEST_USER    SELECT SYS          V_$DATABASE       SELECT_CATALOG_ROLE  GRANT_PATH('PRIV_TEST_USER', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE', 'IMP_FULL_DATABASEDBA', 'EM_EXPRESS_ALL', 'EM_EXPRESS_BASIC', 'SELECT_CATALOG_ROLE')

7 rows selected.

SQL>

Once again, the privileges came from a variety of roles, but looking at the output from the PATH 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 CREATE ANY INDEX 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 DBA andRESOURCE roles.

So the solution here seems quite simple.
Create a custom role to apply any necessary privileges, then revoke the DBA and RESOURCE roles.

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;

With the analysis complete, we can optionally remove the captured information from the data dictionary.

BEGIN
DBMS_PRIVILEGE_CAPTURE.drop_capture( name => 'dba_res_user_pol' );
END;
/

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)