Audit logs missing for database user activity after enabling Unified Auditing in security policies

Cloud Platforms Oracle Cloud
, , , , , , , ,
1

We recently migrated to Unified Auditing on our Oracle Database 19c in OCI, and now we’re missing critical audit logs for database user activity. This is creating significant compliance risk as we can’t track privileged user actions.

After switching from traditional auditing to Unified Auditing, we enabled some default audit policies, but we’re not seeing the detailed activity logs we need. Specifically, we’re missing:

  • DDL statements executed by application users
  • SELECT queries on sensitive tables
  • Failed login attempts from specific schemas

The audit trail appears to be active, but it’s only capturing a fraction of what we were auditing before. I’m concerned about the Unified Auditing policy setup and whether we need custom audit policies. Has anyone experienced similar issues with audit log coverage after migrating to Unified Auditing?

2

For custom audit policies, use CREATE AUDIT POLICY statements. You can create granular policies that target specific operations, users, and objects. For example, to audit DDL by application users, you’d create a policy that audits CREATE, ALTER, DROP statements when executed by certain roles or users.

Also important - don’t forget to actually ENABLE the policy after creating it. I’ve seen many cases where policies were created but never enabled, so no logs were generated.

3

Thanks, I ran that query and confirmed only two policies are enabled: ORA_SECURECONFIG and ORA_ACCOUNT_MGMT. So I definitely need to create custom policies for the application-specific auditing we require. What’s the best approach for creating policies that cover DDL and SELECT operations on specific tables?

4

Unified Auditing doesn’t automatically migrate your traditional audit settings. The default policies (ORA_SECURECONFIG, ORA_ACCOUNT_MGMT) are enabled but they’re focused on security-critical operations, not application-level auditing.

You need to create custom audit policies for your specific requirements. Check what policies are currently enabled:


SELECT * FROM audit_unified_enabled_policies;

That will show you what’s actually active versus what you think should be capturing logs.

5

One thing to watch out for with Unified Auditing - the audit trail can grow very quickly if you’re not managing it properly. Make sure you’re using DBMS_AUDIT_MGMT to set retention policies and purge old records. Otherwise, you’ll run into performance issues and storage problems with the AUDSYS schema.

I recommend setting up automated purge jobs that run weekly to clean up audit records older than your compliance retention period (usually 90 days or 1 year depending on your requirements).

6

Good point about retention management. We have a 1-year retention requirement, so I’ll need to configure that properly. I’m going to work on creating the custom policies for DDL and SELECT auditing. Any specific recommendations for policy design to avoid capturing too much unnecessary data?

7

Here’s a comprehensive guide to resolve your audit coverage gaps:

Understanding the Issue: Unified Auditing uses a completely different architecture from traditional auditing. Default policies focus on security events (failed logins, privilege grants, system configuration changes) but don’t capture application-level activity. You must explicitly create and enable custom policies for application auditing.

Step 1: Unified Auditing Policy Setup for DDL Create a policy to capture DDL operations:


CREATE AUDIT POLICY app_ddl_audit
ACTIONS CREATE TABLE, ALTER TABLE, DROP TABLE,
        CREATE INDEX, DROP INDEX, CREATE VIEW
WHEN 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') NOT IN (''SYS'',''SYSTEM'')'
EVALUATE PER SESSION;

Enable it:


AUDIT POLICY app_ddl_audit;

Step 2: Custom Audit Policy for Sensitive Data Access Create policy for SELECT on specific tables:


CREATE AUDIT POLICY sensitive_data_access
ACTIONS SELECT ON hr.employees,
        SELECT ON finance.salary_data,
        SELECT ON customers.credit_cards;

Enable it:


AUDIT POLICY sensitive_data_access;

Step 3: Failed Login Auditing Create policy for authentication failures:


CREATE AUDIT POLICY failed_login_audit
ACTIONS LOGON
WHEN 'SYS_CONTEXT(''USERENV'',''SESSIONID'') = 0'
EVALUATE PER SESSION;

Enable it:


AUDIT POLICY failed_login_audit;

Step 4: DBMS_AUDIT_MGMT Usage for Retention Initialize audit trail management:


BEGIN
  DBMS_AUDIT_MGMT.INIT_CLEANUP(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    default_cleanup_interval => 168);
END;
/

Set retention period (365 days for 1-year compliance):


BEGIN
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    last_archive_time => SYSTIMESTAMP - INTERVAL '365' DAY);
END;
/

Create purge job:


BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    audit_trail_purge_interval => 168,
    audit_trail_purge_name => 'UNIFIED_AUDIT_PURGE_JOB',
    use_last_arch_timestamp => TRUE);
END;
/

Step 5: Verify Policy Coverage Check enabled policies:


SELECT policy_name, enabled_option, user_name
FROM audit_unified_enabled_policies
ORDER BY policy_name;

Test audit capture:


-- Perform test DDL
CREATE TABLE test_audit_table (id NUMBER);

-- Query audit trail
SELECT event_timestamp, dbusername, action_name,
       object_schema, object_name, sql_text
FROM unified_audit_trail
WHERE object_name = 'TEST_AUDIT_TABLE'
ORDER BY event_timestamp DESC;

Step 6: Custom Audit Policy Creation Best Practices

  • Use WHEN clauses to filter by user, IP, or time to reduce volume
  • Use EVALUATE PER SESSION instead of PER STATEMENT for frequent operations
  • Combine related actions in single policies rather than creating many small policies
  • Exclude system users (SYS, SYSTEM) from application auditing
  • Use object-specific policies rather than schema-wide when possible

Additional Policies for Complete Coverage:

Privilege usage auditing:


CREATE AUDIT POLICY privilege_usage_audit
ACTIONS EXECUTE ON DBMS_RLS, EXECUTE ON DBMS_CRYPTO
WHEN 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') NOT IN (''SYS'')';

AUDIT POLICY privilege_usage_audit;

Data modification auditing:


CREATE AUDIT POLICY data_modification_audit
ACTIONS INSERT ON sensitive_schema.*,
        UPDATE ON sensitive_schema.*,
        DELETE ON sensitive_schema.*;

AUDIT POLICY data_modification_audit;

Monitoring and Maintenance:

  • Review audit trail size weekly: `SELECT * FROM dba_audit_mgmt_config_params;
  • Monitor purge job execution: `SELECT * FROM dba_scheduler_job_run_details WHERE job_name = ‘UNIFIED_AUDIT_PURGE_JOB’;
  • Export audit data before purging for long-term archive if needed
  • Review and adjust policies quarterly based on actual compliance needs

Performance Considerations:

  • Unified Auditing has less overhead than traditional auditing
  • Expect 2-5% performance impact with comprehensive auditing
  • Use EVALUATE PER SESSION for high-frequency operations
  • Regularly purge old records to prevent AUDSYS tablespace growth

This solution comprehensively addresses all three focus areas: proper Unified Auditing policy setup with custom policies for application-specific requirements, effective use of DBMS_AUDIT_MGMT for retention and purge management, and custom audit policy creation following best practices for granular, efficient auditing. Your compliance risk should be fully mitigated once these policies are in place and actively capturing the required user activity.