How can I audit actions done by my schema within my schema using Oracle Unified Auditing?

Question

I want to be able to audit specific actions within my own schema (such as DDL, inserts, updates, anything I feel like). I am on Oracle 19C with mixed mode for auditing.

I have a user "SPLUNK", which has the following table:

CREATE TABLE BAT 
(
  ID INT GENERATED ALWAYS AS IDENTITY NOT NULL 
, NAME VARCHAR2(20) 
);

I create and enable an audit policy to test any changes to the table structure as follows:

CREATE AUDIT POLICY ALTER_TABLE_POLICY_SPL
 ACTIONS ALTER ON SPLUNK.BAT;
 
AUDIT POLICY ALTER_TABLE_POLICY_SPL;

If I, as the SPLUNK user, run the following SQL statement:

ALTER TABLE BAT 
ADD (AGE INT );

Then run this statement:

select * from unified_audit_trail
ORDER BY EVENT_TIMESTAMP DESC;

My alter statement is NOT included in the audit results. However, if I create another user TESTUSER, give ALTER and SELECT privileges to SPLUNK.BAT:

GRANT ALTER ON SPLUNK.BAT TO TESTUSER;
GRANT SELECT ON SPLUNK.BAT TO TESTUSER;

Then I run this, as the TESTUSER:

ALTER TABLE SPLUNK.BAT 
ADD (FAVORITENUM INT );

My alter statement IS included in the unified_audit_trail. What gives? Specifically, why is my alter statement for the TESTUSER user audited but not for the SPLUNK user?

More importantly, how could I change my audit policy to audit the alter table action on SPLUNK.BAT by ALL users, including the SPLUNK user?

Answer 1

Some audit policies are audited only in new sessions. I guess you used the same session the whole time for SPLUNK user.

SQL> grant dba to splunk identified by splunk;

Grant succeeded.

SQL> conn splunk/splunk
Connected.
SQL> show user
USER is "SPLUNK"
SQL> CREATE TABLE BAT ( ID INT GENERATED ALWAYS AS IDENTITY NOT NULL, NAME VARCHAR2(20) );

Table created.

SQL> CREATE AUDIT POLICY ALTER_TABLE_POLICY_SPL ACTIONS ALTER ON SPLUNK.BAT;

Audit policy created.

SQL> AUDIT POLICY ALTER_TABLE_POLICY_SPL;

Audit succeeded.

SQL> ALTER TABLE BAT ADD (AGE INT );

Table altered.

SQL> select dbusername, sql_text from unified_audit_trail where object_name in ('BAT');

no rows selected

No result. Start a new session:

SQL> conn splunk/splunk
Connected.
SQL> show user
USER is "SPLUNK"
SQL> ALTER TABLE BAT ADD (FAVORITENUM INT );

Table altered.

SQL> select dbusername, sql_text from unified_audit_trail where object_name in ('BAT') order by event_timestamp desc;

DBUSERNAME           SQL_TEXT
-------------------- ----------------------------------------
SPLUNK               ALTER TABLE BAT ADD (FAVORITENUM INT )

SQL> ALTER TABLE BAT DROP (AGE);

Table altered.

SQL> ALTER TABLE BAT DROP (FAVORITENUM);

Table altered.

SQL> select dbusername, sql_text from unified_audit_trail where object_name in ('BAT') order by event_timestamp desc;

DBUSERNAME           SQL_TEXT
-------------------- ----------------------------------------
SPLUNK               ALTER TABLE BAT DROP (FAVORITENUM)
SPLUNK               ALTER TABLE BAT DROP (AGE)
SPLUNK               ALTER TABLE BAT ADD (FAVORITENUM INT )