Ensuring the principle of “least privilege” is paramount for database security. It dictates that users should only be granted the exact permissions necessary to perform their tasks, minimizing the potential attack surface. Oracle Database 19c makes this easier to achieve with built-in privilege analysis capabilities.

Privilege Analysis: From Database Vault to Core Functionality

Historically, before Oracle Database 19c, the functionality to perform detailed privilege analysis was part of the Oracle Database Vault option, requiring a separate license. However, starting with 19c, this powerful feature is now included within the Enterprise Edition. This significant change moves the documentation from the Database Vault guide to the main Security Guide, making it more accessible to all Enterprise Edition users.

Introducing DBMS_PRIVILEGE_CAPTURE

The key to leveraging privilege analysis in Oracle 19c is the DBMS_PRIVILEGE_CAPTURE package. To utilize this package, you must first be granted the CAPTURE_ADMIN role.

The typical workflow for using DBMS_PRIVILEGE_CAPTURE involves these steps:

1. Create a privilege analysis policy. (CREATE_CAPTURE)
2. Enable the policy. (ENABLE_CAPTURE)
3. Allow sufficient time for the required analysis. During this period, database activity is monitored.
4. Disable the privilege analysis policy. (DISABLE_CAPTURE)
5. Analyze the collected results. (GENERATE_RESULT and querying relevant data dictionary views).
6. Drop the policy if it and the recorded data are no longer needed. (DROP_CAPTURE)

Crafting Your Analysis Policy with CREATE_CAPTURE

The CREATE_CAPTURE procedure offers flexibility in defining the scope of your privilege analysis through its type parameter:

• G_DATABASE: This analyzes all privilege usage across the entire database, excluding the SYS user. When using this type, the ROLES and CONDITION parameters are not required.
• G_ROLE: This focuses the analysis on the privilege usage of specific roles defined in the ROLES parameter. You use the ROLE_NAME_LIST function to provide a list of roles.
• G_CONTEXT: This allows you to analyze privilege usage based on a boolean expression specified in the CONDITION parameter. These conditions can incorporate calls to SYS_CONTEXT to evaluate various session attributes.
• G_ROLE_AND_CONTEXT: This combines the previous two, analyzing privilege usage only when both the roles specified in the ROLES parameter and the boolean expression in the CONDITION parameter evaluate to TRUE.

Important Note: All newly created privilege analysis policies are initially in a disabled state.

Practical Examples of CREATE_CAPTURE

Here are some examples demonstrating how to create different types of privilege analysis policies:

Analyzing the entire database:SQL

-- Connect to a privileged user 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;
/

Analyzing specific roles (DBA and RESOURCE):SQL

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

Analyzing activity when the session user is ‘TEST’:SQL

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

Analyzing activity for DBA or RESOURCE roles when the user is ‘TEST’ or ‘EMP’:SQL

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

Examining Existing Policies with DBA_PRIV_CAPTURES

You can view details of the created privilege capture policies using the DBA_PRIV_CAPTURES data dictionary view:SQL

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;

Initiating and Halting Analysis with ENABLE_CAPTURE and DISABLE_CAPTURE

Once your policy is defined, you use the ENABLE_CAPTURE procedure to start the data collection process. Typically, only one database-wide (G_DATABASE) policy and one non-database-wide policy can be enabled simultaneously.SQL

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

After allowing a sufficient period for representative database activity to occur, you disable the capture using the DISABLE_CAPTURE procedure:SQL

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

Generating Analysis Results with GENERATE_RESULT

With the capture disabled, you need to process the collected data to populate the privilege analysis views. This is done using the GENERATE_RESULT procedure:SQL

BEGIN
 DBMS_PRIVILEGE_CAPTURE.generate_result('db_pol');
END;
/

Exploring Privilege Analysis Views

Oracle introduced a set of data dictionary views starting in 12c (and enhanced in later versions) that provide insights into privilege usage based on the captured data. These views allow you to identify used and unused privileges at various levels:

• DBA_PRIV_CAPTURES: Displays information about the privilege capture policies.
• DBA_USED_OBJPRIVS: Shows object privileges that have been used.
• DBA_USED_OBJPRIVS_PATH: Provides the call path for used object privileges.
• DBA_USED_PRIVS: A consolidated view of used privileges (both system and object).
• DBA_USED_PUBPRIVS: Displays used public privileges.
• DBA_USED_SYSPRIVS: Shows used system privileges.
• DBA_USED_SYSPRIVS_PATH: Provides the call path for used system privileges.
• DBA_USED_USERPRIVS: Displays privileges directly granted to users that have been used.
• DBA_USED_USERPRIVS_PATH: Provides the call path for used user privileges.
• DBA_UNUSED_OBJPRIVS: Shows object privileges that have not been used during the capture period.
• DBA_UNUSED_OBJPRIVS_PATH: Provides potential call paths for unused object privileges.
• DBA_UNUSED_PRIVS: A consolidated view of unused privileges.
• DBA_UNUSED_SYSPRIVS: Shows system privileges that have not been used.
• DBA_UNUSED_SYSPRIVS_PATH: Provides potential call paths for unused system privileges.
• DBA_UNUSED_USERPRIVS: Displays privileges directly granted to users that have not been used.
• DBA_UNUSED_USERPRIVS_PATH: Provides potential call paths for unused user privileges.

By querying these views after generating the results, you can gain valuable insights into which privileges are actually being used and identify candidates for revocation, thus strengthening your database’s security posture by adhering to the principle of least privilege. Oracle 19c’s integration of this powerful feature directly into the Enterprise Edition makes proactive security management more accessible than ever before.