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;
The ANY privileges only come into effect when you change tables in
other schemas.
To capture changes in your own schema, you need to audit
The privilege CREATE TABLE
The
actions ALTER TABLE, DROP TABLE
For example:
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))
*/