Not all users are equal in front of Oracle audit.

There are a couple of idiosyncrasies in the Oracle auditing and the result is that the potentially most dangerous users (those that have being granted SYSDBA) can get away with serious damages. Things have somewhat changed with version 9.2.X, but smart intruders need not be worried, we will show why.

Audit for nomal Oracle users

Let us assume that database is heavily audited (sessions, create, alter etc.) and that the user SCOTT performs a couple of DDL statements: CREATE TABLE and DROP TABLE.



C:>sqlplus scott/tiger

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> ALTER SESSION set nls_date_format = 'hh24:mi dd-mon-yyyy';
Session altered.

SQL> select sysdate from dual;

SYSDATE
-----------------
17:40 23-jan-2004

SQL> CREATE TABLE t_abc (c1 number(3));
Table created.

SQL> insert into t_abc values (7);
1 row created.

SQL> DROP TABLE t_abc;
Table dropped.

SQL> select sysdate from dual;

SYSDATE
-----------------
17:41 23-jan-2004

SQL> exit

This happened between 17:40 and 17:41. Let's now connect externally from a privileged operating system account as sysdba:


C:>sqlplus /nolog
SQL> connect / as sysdba
Connected.

SQL> ALTER SESSION set nls_date_format = 'hh24:mi dd-mon-yyyy';
Session altered.

SQL> SELECT os_username, username, action_name, timestamp 
     FROM dba_audit_trail
     ORDER BY timestamp;

OS_USERNAME USERNAME ACTION_NAME TIMESTAMP
--------------------------------------------------------------------------------
Administrator SCOTT LOGOFF 17:39 23-jan-2004
Administrator SCOTT CREATE TABLE 17:41 23-jan-2004
Administrator SCOTT DROP TABLE 17:41 23-jan-2004


SQL> SELECT os_username, username, terminal, action_name as action, timestamp,
  2 logoff_time
  3 FROM dba_audit_session ORDER BY timestamp;

OS_USERNAME USERNAME TERMINAL ACTION TIMESTAMP LOGOFF_TIME 
----------------------------------------------------------------------------------
Administrator SCOTT MARTE LOGOFF 17:39 23-jan-2004 17:41 23-jan-2004

The records in the SYS.AUD$ are as expected; the login and logout time of the user SCOTT were stored in the audit table, as much as the CREATE and DROP statements.
Let us now create and drop a table having connected as sysdba:


C:>sqlplus /nolog

SQL> connect / as sysdba
Connected.

SQL> ALTER SESSION set nls_date_format = 'hh24:mi:ss dd-mon-yyyy';
Session altered.

SQL> CREATE TABLE t_xyz (d1 number);
Table created.

SQL> insert into t_xyz values (10);
1 row created.

SQL> DROP TABLE t_xyz;
Table dropped.

SQL> exit

The user has therefore created a table and dropped it.
We now connect as SYSTEM and check the contents of the audit table


C:>sqlplus system

Enter password:

SQL> ALTER SESSION set nls_date_format = 'hh24:mi dd-mon-yyyy';
Session altered.

SQL> SELECT os_username, username, action_name, timestamp 
     FROM dba_audit_trail
     ORDER BY timestamp;

OS_USERNAME USERNAME ACTION_NAME TIMESTAMP
------------------------------ --------------------------- 
Administrator SCOTT LOGOFF 17:39 23-jan-2004
Administrator SCOTT CREATE TABLE 17:41 23-jan-2004
Administrator SCOTT DROP TABLE 17:41 23-jan-2004
Administrator SYSTEM LOGON 18:53 23-jan-2004

As we see there is no trace of the login of the sysdba user and of his DDL's (CREATE and DROP TABLE). Still connected as SYSTEM (which has DBA privileges) let us create a table


SQL> CREATE TABLE T_TEST (f1 number);
Table created.

SQL> DROP TABLE T_TEST;
Table dropped.

SQL> SELECT os_username, username, action_name, obj_name, timestamp 
      FROM dba_audit_trail
      ORDER BY timestamp;

OS_USERNAME USERNAME ACTION_NAME OBJ_NAME TIMESTAMP
-------------------------------------------------------------------------
Administrator SCOTT LOGOFF 17:39 23-jan-2004
Administrator SCOTT CREATE TABLE T_ABC 17:41 23-jan-2004
Administrator SCOTT DROP TABLE T_ABC 17:41 23-jan-2004
Administrator SYSTEM LOGON 18:53 23-jan-2004
Administrator SYSTEM CREATE TABLE T_TEST 18:54 23-jan-2004
Administrator SYSTEM DROP TABLE T_TEST 18:54 23-jan-2004

6 rows selected.

We see that the commands executed by a sysdba user are not recorded in the audit table; if the user (even a DBA like system) connects using SQLPLUS username/password instead, the statements are audited.


C:>sqlplus sys
Enter password:
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper

Enter user-name: sys as sysdba
Enter password:

SQL> CREATE TABLE t_12345 (c1 number);
Table created.

SQL> ALTER SESSION set nls_date_format = 'hh24:mi dd-mon-yyyy';
Session altered.

SQL> set line 120
SQL> SELECT os_username, username, action_name, obj_name, timestamp 
      FROM dba_audit_trail
      ORDER BY timestamp;

OS_USERNAME USERNAME ACTION_NAME OBJ_NAME TIMESTAMP
------------------------------------------------------------------------
Administrator SCOTT LOGOFF 17:39 23-jan-2004
Administrator SCOTT CREATE TABLE T_ABC 17:41 23-jan-2004
Administrator SCOTT DROP TABLE T_ABC 17:41 23-jan-2004
Administrator SYSTEM LOGOFF 18:53 23-jan-2004
Administrator SYSTEM CREATE TABLE T_TEST 18:54 23-jan-2004
Administrator SYSTEM DROP TABLE T_TEST 18:54 23-jan-2004
Administrator SYS LOGON 20:43 23-jan-2004

Again, the user connected as sysdba has not left any record in the SYS.AUD$ table.

Conclusions (before 9.2.X)

If a user connects as sysdba there is no trace of his or her activities in the audit table.
Any other user, even with DBA privileges, is regularly audited.

What's new for the audit in Oracle 9.2.X

The Oracle version 9.2 introduced a few changes, in order to meet more stringent requirements for security.


SQL> show parameter audit

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations boolean FALSE
audit_trail string DB

C:>sqlplus /nolog

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> create table t_sys (c1 number);
Table created.

SQL> drop table t_sys;
Table dropped.

On UNIX the sysdba's activity is audited on operating system files


SQL> connect / as sysdba
Connected.
SQL> show parameter audit

NAME TYPE VALUE
------------------------------------ ----------- -
audit_file_dest string ?/rdbms/audit
audit_sys_operations boolean TRUE
audit_trail string DB
transaction_auditing boolean TRUE

$ cd $ORACLE_HOME/rdbms/audit
$ ls -ltr

-rw-r----- 1 oracle dba 990 Jan 24 09:39 ora_105.aud
-rw-r----- 1 oracle dba 1376 Jan 26 09:34 ora_6355.aud
$ more ora_6355.aud
Audit file /opt/oracle/9.2.0.3/rdbms/audit/ora_6355.aud
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.3.0 - Production
ORACLE_HOME = /opt/oracle/9.2.0.3
System name: SunOS
Node name: daddy04 
Release: 5.8
Version: Generic_108528-23
Machine: sun4u
Instance name: PRODS20
Redo thread mounted by this instance: 1
Oracle process number: 22
Unix process pid: 6355, image: oracle@daddy04 (TNS V1-V3)

Mon Jan 26 09:34:36 2004
ACTION : 'CONNECT'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: j-carter
CLIENT TERMINAL: pts/8
STATUS: 0

Mon Jan 26 09:34:42 2004
ACTION : 'SELECT NAME name_col_plus_show_param,DECODE(TYPE,1,'boolean',2,'string',3,'integer',4,
'file',6,'big integer','unknown'
) TYPE,VALUE value_col_plus_show_param 
FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER('%sys%') ORDER BY name_col_plus_show_param,
ROWNUM'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: j-carter
CLIENT TERMINAL: pts/8
STATUS: 0

Mon Jan 26 09:34:51 2004
ACTION : 'SELECT NAME name_col_plus_show_param,DECODE(TYPE,1,'boolean',2,'string',3,'integer',4,
'file',6,'big integer','unknown'
) TYPE,VALUE value_col_plus_show_param FROM V$PARAMETER 
WHERE UPPER(NAME) LIKE UPPER('%audit%') 
ORDER BY name_col_plus_show_param,ROWNUM'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: j-carter
CLIENT TERMINAL: pts/8
STATUS: 0

On Windows the sysdba's activities are recorded in the Event Log

Example of Even Log

The details are:

The Oracle instance, user, privileges and statement are recorded

[Home] [Web Design] [HTML tutorials] [Javascript] [PSP] [About us] [Links] [Anonymous email] [Best hosting] [Daily Oracle Life] [IT jobs in Switzerland] [Web Submission] [Web traffic]

Rate this article ...
Very poor	Poor	Average	Good	Very good