We have been trying to enable auditing on few DDL activities in autonomous database.

As per the following code we created, enabled and verified the audit configs. Everything looks normal to us, however none of the table create/drop activities (listed at the end) are not being tracked in

unified_audit_trail

table.

We can only see audit management related activities like

DBMS_AUDIT_MGMT BEGIN dbms_audit_mgmt.flush_unified_audit_trail; END;

in

unified_audit_trail

table.

I must be missing something, can you please help me navigate through it.

-- Connect to ADMIN user
-- DB Version
SELECT *
  FROM v$version;

-- Verfiy Audit Status
SELECT parameter
     , value
  FROM v$option
 WHERE parameter = 'Unified Auditing';

-- Enable immediate write mode
BEGIN
  dbms_audit_mgmt.set_audit_trail_property(audit_trail_type          => dbms_audit_mgmt.audit_trail_unified
                                         , audit_trail_property      => dbms_audit_mgmt.audit_trail_write_mode
                                         ,audit_trail_property_value => dbms_audit_mgmt.audit_trail_immediate_write);
END;
/

-- Validate audit configs
SELECT * 
  FROM dba_audit_mgmt_config_params;

-- Create an audit policy
CREATE AUDIT POLICY xx_ddl_test_policy
  PRIVILEGES CREATE ANY TABLE
           , ALTER ANY TABLE
           , DROP ANY TABLE
  WHEN 'SYS_CONTEXT (''USERENV'', ''SESSION_USER'') IN (''ADMIN'',''XXTST'')' 
EVALUATE PER STATEMENT ;

-- Enable Audit Policy
AUDIT POLICY xx_ddl_test_policy;

-- Validate policy
SELECT *
  FROM audit_unified_enabled_policies
 WHERE policy_name IN ('XX_DDL_TEST_POLICY');

SELECT *
  FROM audit_unified_policies
 WHERE policy_name in (SELECT policy_name
                         FROM audit_unified_enabled_policies);

-- Flush audit trail
EXEC dbms_audit_mgmt.flush_unified_audit_trail;

-- Connect to XXTST user
-- Test Audit Policy
CREATE TABLE xx_test (vc_col VARCHAR2(25));

INSERT INTO xx_test ('Test 01');
COMMIT;

TRUNCATE TABLE xx_test;

DROP TABLE xx_test;

-- Verify audit trial
SELECT *
  FROM unified_audit_trail
 ORDER BY event_timestamp DESC;

CREATE AUDIT POLICY xx_ddl_test_policy
  PRIVILEGES CREATE ANY TABLE
           , ALTER ANY TABLE
           , DROP ANY TABLE
           , CREATE TABLE
  ACTIONS DROP TABLE, ALTER TABLE
  WHEN 'SYS_CONTEXT (''USERENV'', ''SESSION_USER'') IN (''ADMIN'',''CHRIS'')' 
EVALUATE PER STATEMENT ;

-- Enable Audit Policy
AUDIT POLICY xx_ddl_test_policy;

EXEC dbms_audit_mgmt.flush_unified_audit_trail;

CONN CHRIS

CREATE TABLE xx_test (vc_col VARCHAR2(25));

INSERT INTO xx_test VALUES ('Test 01') ;
COMMIT;

TRUNCATE TABLE xx_test;

DROP TABLE xx_test;

CREATE TABLE hr.t ( c1 INT );

ALTER TABLE hr.t ADD c2 INT;

DROP TABLE hr.t;

SELECT dbusername, action_name, system_privilege_used, sql_text
  FROM unified_audit_trail
 ORDER BY event_timestamp DESC
 FETCH FIRST 6 ROWS ONLY;
 /*
DBUSE ACTION_NAME          SYSTEM_PRIVILEGE_USE SQL_TEXT
----- -------------------- -------------------- --------------------------------------------------
CHRIS DROP TABLE           DROP ANY TABLE       DROP TABLE hr.t
CHRIS ALTER TABLE          ALTER ANY TABLE      ALTER TABLE hr.t ADD c2 INT
CHRIS CREATE TABLE         CREATE ANY TABLE     CREATE TABLE hr.t ( c1 INT )
CHRIS DROP TABLE           <null>               DROP TABLE xx_test
CHRIS ALTER TABLE          <null>               TRUNCATE TABLE xx_test
CHRIS CREATE TABLE         CREATE TABLE         CREATE TABLE xx_test (vc_col VARCHAR2(25))
*/