Utilizing Out-of-the-Box Unified Audit Policies in Oracle 19c

 

Utilizing Out-of-the-Box Unified Audit Policies in Oracle 19c

Oracle 19c offers powerful auditing capabilities via Unified Auditing, which consolidates all audit records into a single repository. One of its most useful features is the availability of Out-of-the-Box (OOB) policies like ORA_SECURECONFIG, which are automatically enabled and provide a strong baseline for database security monitoring.

In this blog, we’ll demonstrate how to:

• Review the built-in auditing policies.

• Create and drop a user.

• Query the unified audit trail to verify audit records.

---

 Step 1: Check Auto-Enabled Unified Audit Policies

To view which auditing policies are enabled in your database, use the following SQL command:

sql
SELECT DISTINCT POLICY_NAME 
FROM audit_unified_enabled_policies;

This will list all the policies that are currently in effect, including predefined ones like ORA_SECURECONFIG, ORA_LOGON_FAILURES, and others.

---

 Step 2: Review Policy Details

Let's say we are interested in knowing whether the DROP USER action is audited under the ORA_SECURECONFIG policy. Use this query:

sql
SELECT audit_option 
FROM audit_unified_policies 
WHERE policy_name = 'ORA_SECURECONFIG' 
AND audit_option LIKE 'DROP%' 
ORDER BY 1;

If DROP USER appears in the result, it confirms that this action is audited by the policy.

---

Step 3: Create and Drop a User

To test the auditing policy, perform the following actions:

sql
CREATE USER test_audit IDENTIFIED BY oracle12;
DROP USER test_audit;

These operations will be captured by the Unified Auditing framework if the auditing policy is enabled properly.

---

Step 4: Check Audit Records in UNIFIED_AUDIT_TRAIL

Query the unified audit trail to confirm whether the DROP USER action was audited:

sql
COLUMN event_timestamp FORMAT A30
COLUMN dbusername FORMAT A10
COLUMN action_name FORMAT A20
COLUMN object_schema FORMAT A10
COLUMN object_name FORMAT A20

SELECT event_timestamp,
       dbusername,
       action_name,
       object_schema,
       object_name
FROM unified_audit_trail
WHERE action_name LIKE 'DROP USER%'
ORDER BY event_timestamp;

This output will show when the action occurred, which user performed it, and the object affected.

---

Conclusion

Oracle’s Unified Auditing simplifies and strengthens your security auditing strategy. By leveraging Out-of-the-Box policies like ORA_SECURECONFIG, you can automatically track sensitive operations such as user creation and deletion without additional configuration.

Tip: Always review which policies are auto-enabled after installing or upgrading your Oracle database, and customize your auditing policies as per your compliance requirements.