Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

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

The concept of "least privilege" is a common topic when discussing database security. It revolves around making sure the minimum level of privileges are granted to a user to allow them to do their job. Any unnecessary privileges represent a possible security loophole.

When starting a new project it can be relatively simple to follow the least privilege idea. It is significantly harder to implement it on existing systems, where excessive privilege has already been granted.

Oracle 12c introduced the DBMS_PRIVILEGE_CAPTURE package, which allows you to track the privileges being used, making it much simpler to perform privilege analysis, which in turn allows you to revoke unnecessary privileges and attain a least privilege state.

Basic Usage

In 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_CAPTURE package is the same.

  1. Create a privilege analysis policy. (CREATE_CAPTURE)
  2. Enable it. (ENABLE_CAPTURE)
  3. Wait for the required analysis period.
  4. Disable the privilege analysis policy. (DISABLE_CAPTURE)
  5. Analyze the results. (GENERATE_RESULT and query dictionary views)
  6. 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_CAPTURE

The CREATE_CAPTURE procedure allows you to create privilege analysis policies with varying degrees of granularity.

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 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 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_DATABASE policy 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.

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_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>

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

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?

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

For more information see:

Hope this helps. Regards Tim...

Back to the Top.