How to audit sys into an OS file owned by root

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…

About these ads

, ,

  1. #1 by Roberto on March 30, 2010 - 15:30

    audit_syslog_level parameter exists from Oracle10.2+
    How can I get a similar behavior in earlier versions?

  2. #2 by Uwe Hesse on March 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. #3 by roberto on March 31, 2010 - 12:49

    Well,
    have you any experience about audit_dump_dest files read (and grep-ed) by scheduled script

  4. #4 by Justin on 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. #5 by Uwe Hesse on 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!” :-)

  1. Blogroll Report 29/01/2009 – 05/02/2010 « Coskan’s Approach to Oracle
  2. Fine Grained Auditing and SYS_CONTEXT « The Oracle Instructor
  3. Auditing ALTER SYSTEM commands « The Oracle Instructor

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 )

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,890 other followers

%d bloggers like this: