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>
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
