Oracle database Unified Auditing and SYS Log Limitations

Tradition (Standard) auditing is deprecated by Oracle and “Unified Auditing” is the way to go strategy in your Oracle database infrastructure landscape. If you would like to know how to enable….please check my old blog post here: https://geodatamaster.com/2016/01/16/exploring-oracle-12c-unified-auditing/

Moreover, many organizations would like to extend auditing to SYSLOG to integrate it with SIEM, and this is possible of course but there is catch that you need to be aware of before proceeding……let me illustrate the SYSLOG setup first:

set the parameter in your oracle database and “restart” is required:

SQL> alter system set UNIFIED_AUDIT_SYSTEMLOG = ‘LOCAL7.EMERG’ scope=spfile;

System altered.

after that edit /etc/syslog.conf file , this action will require “root” power and the following entry:

vi /etc/rsyslog.conf

local7.emerg /var/log/audit.log

restart syslog logger service:

systemctl stop rsyslog

systemctl start rsyslog

systemctl status rsyslog

*** To Test as SYS account i will create a dummy account:

SQL> create user c##sept_2021 identified by sept_2021;

checking audit.log

tail -f /var/log/audit.log

 journal:Oracle Unified Audit[7307]: LENGTH: ‘212’ TYPE:”4″ DBID:”2841088985″ SESID:”2993775527″ CLIENTID:”” ENTRYID:”1″ STMTID:”5″ DBUSER:”SYS” CURUSER:”SYS” ACTION:”51″ RETCODE:”1031″ SCHEMA:”” OBJNAME:”C##SEPT_2021″ PDB_GUID:”86B637B62FDF7A65E053F706E80A27CA”

Sep 27 19:06:48 oracle-19c-srv journal: Oracle Unified Audit[7307]: LENGTH: ‘212’ TYPE:”4″ DBID:”2841088985″ SESID:”2993775527″ CLIENTID:”” ENTRYID:”1″ STMTID:”5″ DBUSER:”SYS” CURUSER:”SYS” ACTION:”51″ RETCODE:”1031″ SCHEMA:”” OBJNAME:”C##SEPT_2021″ PDB_GUID:”86B637B62FDF7A65E053F706E80A27CA”

unfortunately the following important information are missing, only subset of the unified audit record fields are written to ensure that the audit record entries do not exceed the maximum allowed size for a SYSLOG entry “typically 1024 bytes”.

– OS_USERNAME

– USERHOST

– TERMINAL

– SYSTEM_PRIVILEGE_USED

– SQL_TEXT

– SQL_BINDS

To find/list the action codes description for audit, you can query audit_actions table, in our example action 51 corresponds to —-> CREATE USER

SQL> select * from audit_actions;

So, be careful when you design your audit strategy !!

Reference: Doc ID 2520613.1 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s