Sometimes, the requirement may arise to audit all actions of the Oracle Database superuser sys. This feature was introduced already in the 9i version, together with the parameter AUDIT_SYS_OPERATIONS. If set to true, that leads to the generation of files in the directory, determined by the parameter AUDIT_FILE_DEST. These files will contain then a protocol of all the actions of sys. By default, the files in that directory – all with the suffix aud – contain only the protocol of all connections as sys, but not all the actions done after the connect. They look like this:
Audit file /u01/app/oracle/admin/orcl/adump/ora_1057.aud
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name: Linux
Node name: uhesse
Release: 2.6.9-55.ELsmp
Version: #1 SMP Fri Apr 20 17:03:35 EDT 2007
Machine: i686
Instance name: orcl
Redo thread mounted by this instance: 0 <none>
Oracle process number: 0
Unix process pid: 1057, image: oracle@uhesse
Tue Feb 2 17:43:12 2010
ACTION : 'CONNECT'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/6
STATUS: 0
But even if we set AUDIT_SYS_OPERATIONS=true, although the audit file will then contain all the actions of sys also, it belongs to the operating system user, used to install the database – usually the user oracle. And likely, the person who has the privilege to connect as sys also has the privilege to connect as oracle on the OS level. That makes the approach with AUDIT_SYS_OPERATIONS somewhat useless from a security viewpoint. That is why we introduced in version 10g (already) the possibility to have the audit file owned by root – which may make it harder to manipulate or delete it for the DBA person. This is how that works:
First, we have to set two parameters – the cooperation of someone able to logon as sysdba is required here…
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for Linux: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production SQL> show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /u01/app/oracle/admin/orcl/adu mp audit_sys_operations boolean FALSE audit_syslog_level string audit_trail string NONE SQL> alter system set audit_sys_operations=true scope=spfile; System altered. SQL> alter system set audit_syslog_level='LOCAL1.WARNING' scope=spfile; System altered.
Then, we logon as root and modify the file /etc/syslog.conf:
-bash-3.00$ su - Password: [root@uhesse ~]# echo local1.warning /var/log/audit.log >> /etc/syslog.conf [root@uhesse ~]# cat /etc/syslog.conf # Log all kernel messages to the console. # Logging much else clutters up the screen. #kern.* /dev/console # Log anything (except mail) of level info or higher. # Don't log private authentication messages! *.info;mail.none;authpriv.none;cron.none /var/log/messages # The authpriv file has restricted access. authpriv.* /var/log/secure # Log all the mail messages in one place. mail.* -/var/log/maillog # Log cron stuff cron.* /var/log/cron # Everybody gets emergency messages *.emerg * # Save news errors of level crit and higher in a special file. uucp,news.crit /var/log/spooler # Save boot messages also to boot.log local7.* /var/log/boot.log local1.warning /var/log/audit.log
After having done that, we need to restart the syslog logger, also as root user:
[root@uhesse ~]# /etc/rc.d/init.d/syslog restart Shutting down kernel logger: [ OK ] Shutting down system logger: [ OK ] Starting system logger: [ OK ] Starting kernel logger: [ OK ]
The last step is to restart the instance, in order to make the previous changes of the non-dynamic parameters active. If afterwards someone connects to the instance as sysdba, all his actions are recorded:
SQL> connect / as sysdba
Connected.
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 orcl
uhesse
10.2.0.1.0 02-FEB-10 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO
On the OS level, the oracle user can not even see the content of the file, nor modify or delete it:
-bash-3.00$ ls -rtl /var/log/ total 1260 [...] -rw------- 1 root root 34457 Feb 2 17:40 cron -rw------- 1 root root 66395 Feb 2 17:40 boot.log -rw------- 1 root root 377235 Feb 2 17:44 messages -rw------- 1 root root 1410 Feb 2 17:44 audit.log -bash-3.00$ cat /var/log/audit.log cat: /var/log/audit.log: Permission denied -bash-3.00$ whoami oracle
Only root can:
-bash-3.00$ su -
Password:
[root@uhesse ~]# cat /var/log/audit.log | grep instance
Feb 2 17:44:46 uhesse Oracle Audit[1184]:
ACTION : 'select * from v$instance'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/6 STATUS: 0
Of course, the usual aspects of auditing apply in this case also: It is an effort to do (for the system and for people who maintain it), it is space consuming, it may impose performance problems – especially if now large actions are performed by sys, like running catalog.sql or catproc.sql, for example. These actions may lead to very much auditing now…
#1 von Roberto am März 30, 2010 - 15:30
audit_syslog_level parameter exists from Oracle10.2+
How can I get a similar behavior in earlier versions?
#2 von Uwe Hesse am März 31, 2010 - 11:03
Roberto,
I am not aware of another solution for that problem (audit file owned by OS user oracle) in versions before 10g.
#3 von roberto am März 31, 2010 - 12:49
Well,
have you any experience about audit_dump_dest files read (and grep-ed) by scheduled script
#4 von Justin am April 30, 2013 - 08:40
Hello Uwe,
I’m a bit late to the party, but do you know if it is possible to redirect ’normal‘ auditing to syslog as well? This would be on 11.2 though.
regards
/Justin
#5 von Uwe Hesse am April 30, 2013 - 09:25
Justin, yes, that is possible with setting AUDIT_TRAIL=OS and AUDIT_SYSLOG_LEVEL as in the posting. I have just checked that with 11.2.0.3 Possible does not necessarily mean recommended, though.
This configuration is at least unusual, and you should test it thoroughly before taking it into production. As I like to say „Don’t believe it, test it!“ 🙂
#6 von Eshwar am Februar 22, 2019 - 23:37
how can i add YEAR to the audit file ?
example:
Feb 2 17:44:46 uhesse Oracle Audit[1184]:
needs to be:
Feb 2 17:44:46 2019 uhesse Oracle Audit[1184]: