Oracle 19c Mixed Mode Auditing - Oracle's Default auditing - do they work?

Dears,

Its a quick blog, I wanted to check weather oracle's default AUDITING works as it is written in the document?

I picked 11.2 guide Oracle 11.2 Audit Document & tested the results in 19c.

Interestingly with my tests..

1. SYS user drop command in a schema in PDB is properly captured in OS Audit Trail (as per audit_sys_operation param)

2. ALTER TABLE command from SYS and APPLICATION (the owner of the object) are trapped properly in unified_audit_trail view

3. DROP TABLE command from PDBADMIN (drop any table by default isnt granted to PDBADMIN) targeting the application object was trapped in unified_audit_trail

So all worked as expected. Please see below...

OS audit trail for the drop run by SYS:

Audit file: /u01/app/oracle/admin/ORA19C/adump/F8B08577DBE37CF5E05305C2A8C0FBD0/ORA19C_ora_11276_20230628073917822599918146.aud

which is <ADUMP>/<PDB GUID>/*ora* process id trace

Wed Jun 28 07:57:43 2023 +01:00

LENGTH : '295'

ACTION :[33] 'drop table opdb1_public_user.test'

DATABASE USER:[1] '/'

PRIVILEGE :[6] 'SYSDBA'

CLIENT USER:[6] 'oracle'

CLIENT TERMINAL:[5] 'pts/0'

STATUS:[1] '0'

DBID:[9] '793483480'

SESSIONID:[10] '4294967295'

USERHOST:[20] 'vcentos79-oracle-sa1'

CLIENT ADDRESS:[0] ''

ACTION NUMBER:[2] '12'

The commands ran were:

As SYS:

SQL> alter table opdb1_public_user.test add (tabcreate varchar2(20));

Table altered.

SQL> drop table opdb1_public_user.test;

Table dropped.

As Application user:

SQL> alter table opdb1_public_user.test add (tabcreate1 varchar2(10));

Table altered.

SQL> alter table test add (tabcreate2 varchar2(10));

Table altered.

SQL>

As PDBADMIN:

SQL> drop table opdb1_public_user.test purge;

Table dropped.

SQL>

The query to get information on above commands:

set lines 1000

set pages 20000

set colsep ,

col action_name for a12

col OS_USERNAME for a20

col USERHOST for a20

col dbusername for a20

col subclient_program_name for a30

col object_schema for a20

col object_name for a40

col subsql_text for a30

col system_privilege_used for a20

col audit_option for a20

col object_privileges for a20

select action_name

,return_code

,to_char(cast(EVENT_TIMESTAMP as date),'DD/MON/YYYY HH24:MI:SS') evnttime

,SESSIONID

,OS_USERNAME

,USERHOST

,dbusername

,substr(client_program_name,1,30) subclient_program_name

,object_schema

,object_name

,substr(sql_text,1,20) subsql_text

,system_privilege_used

,audit_option

,object_privileges

from unified_audit_trail

order by 1,3;

To filter on object name, use below..

select action_name

,return_code

,to_char(cast(EVENT_TIMESTAMP as date),'DD/MON/YYYY HH24:MI:SS') evnttime

,SESSIONID

,OS_USERNAME

,USERHOST

,dbusername

,substr(client_program_name,1,30) subclient_program_name

,object_schema

,object_name

,substr(sql_text,1,20) subsql_text

,system_privilege_used

,audit_option

,object_privileges

from unified_audit_trail

where object_name='TEST'

order by 1,3

/

Thanks