Basic features of the Oracle audit in the database.

What is the Oracle audit?

This short guide to the Oracle audit will give you an overview about this feature of the Oracle databases; please see our other article for more details. The Oracle audit is the capacity and the process of logging, keeping a list of the database activity; for example, the users connections (username, server, timestamp), the creation of new objects, the dropping of existing ones.

In organisations that deal with sensitive data (police, banks and so on), auditing is normally a stringent and legal requirement. By others, it helps in investigations about blunders or vicious actions.

How is the Oracle auditing enabled?

The auditing is turned off by default; when turned on, the DBA has to decide whether to generate the logs inside the database inself (the table SYS.aud$) or in operating system files. In this article we will deal only with database auditing.
To enable it, the parameter audit_trail must be set to DB in init.ora

  
  audit_trail = DB

and the instance must be restarted.
This is not enough: something to be audited must be specified by a privileged user.

Connecting as sysdba, you can start the auditing of the sessions (connections):

  
SQL> connect / as sysdba
Connected.
SQL> AUDIT SESSION;
Audit succeeded.

Views on SYS.AUD$

There are a few available Oracle dictionary views that offer a more comfortable representation of the contents of SYS.aud$ ; one of them is DBA_AUDIT_TRAIL:

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

OS_USERNAME  USERNAME         ACTION_NAME        TIMESTAMP
-----------  --------------- ------------------- ---------------
oracle       DBSNMP           LOGOFF BY CLEANUP  13:01 09-jan-2004
slinares     APL_P_0002945903 LOGOFF BY CLEANUP  13:55 09-jan-2004
Randraca     APL_P            LOGON              13:30 09-jan-2004                       0
sjones       O_JONES          LOGON              17:55 09-jan-2004

  
SELECT os_username, username, terminal, action_name as action, timestamp,
logoff_time, returncode as rcode
FROM dba_audit_session ORDER BY timestamp;

OS_USER  USERNAME TERMINAL   ACTION   TIMESTAMP         LOGOFF_TIME           RCODE
-------------------------------------------------------------------------------------
Sadmin   SADMIN   SERV4      LOGOFF   15:30 09-jan-2004 15:30 09-jan-2004     0
Randraca APL_P    PACY2      LOGON    15:30 09-jan-2004                       0
Sadmin   SADMIN   PBGPRD02B  LOGOFF   15:30 09-jan-2004 15:30 09-jan-2004     0
Sadmin   SADMIN   PBGPRD02B  LOGOFF   15:30 09-jan-2004 15:30 09-jan-2004     0
Sadmin   SADMIN   PTGPRD02B  LOGOFF   15:30 09-jan-2004 15:30 09-jan-2004     0
sm24202  APL_P    W102821    LOGON    15:30 09-jan-2004                       0
Sadmin   SADMIN   PTGPRD02B  LOGOFF   15:30 09-jan-2004 15:30 09-jan-2004     0
Sadmin   SADMIN   PTGPRD02B  LOGOFF   15:30 09-jan-2004 15:30 09-jan-2004     0
Sadmin   SADMIN   PTGPRD02B  LOGOFF   15:30 09-jan-2004 15:30 09-jan-2004     0
Sadmin   SADMIN   PTGPRD02B  LOGOFF   15:30 09-jan-2004 15:30 09-jan-2004     0
Sadmin   SADMIN   PTGPRD02B  LOGON    15:30 09-jan-2004                       0
Sadmin   SADMIN   PTGPRD02B  LOGON    15:30 09-jan-2004                       0
Sadmin   SADMIN   PTGPRD02B  LOGOFF   15:30 09-jan-2004 15:30 09-jan-2004     0
Sadmin   SADMIN   PTGPRD02B  LOGOFF   15:30 09-jan-2004 15:30 09-jan-2004     0
Sadmin   SADMIN   PTGPRD02B  LOGOFF   15:30 09-jan-2004 15:30 09-jan-2004     0

As you see, both login time and logoff timed are audited; if the logoff time is null, it means that the user is still connected.
There is an array of options, but keep in mind that it is not possible to audit everything. Common options are

AUDIT CREATE USER
AUDIT TABLE
AUDIT GRANT

In this section, the user SYSTEM enables the audit for TABLE and the user scott creates and then drops a table t_test

  
SQL> audit table;
Audit succeeded.

SQL> connect scott/tiger
Connected.
SQL> create table t_test (c1 number);

Table created.

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

SQL> show time
time OFF
SQL> set time on
10:08:41 SQL> show user
USER is "SCOTT"
10:08:44 SQL> drop table t_test;

Table dropped.

10:08:54 SQL> connect system
Enter password:
Connected.
10:12:02 SQL> alter session set nls_date_format = 'hh24:mi dd-mon-yyyy';
Session altered.

10:12:15 SQL> SELECT os_username, username, action_name, OBJ_NAME,  timestamp 
FROM dba_audit_trail
10:12:22   2  ORDER BY timestamp;

OS_USERNAME          USERNAME  ACTION_NAME   OBJ_NAME TIMESTAMP
--------------------------------------------------------------------------------
MARTE\Administrator  SCOTT     CREATE TABLE  T_TEST  10:08 11-jan-2004
MARTE\Administrator  SCOTT     DROP TABLE    T_TEST  10:08 11-jan-2004

Can the auditing be by-passed?

The purpose of this introduction is not a description of all the options (there are very many and please consult the documentation and http://metalink.oracle.com) We are more interested in determining whether the Oracle auditing can be by-passed.
The logs are contained in the SYS.AUD$ table; a possible attack is to truncate this table.
Suppose that the TRUNCATE TABLE is being audited in the database:

  
10:12:28 SQL> show user
USER is "SYSTEM"

15:11:38 SQL> audit truncate;
Audit succeeded.

15:11:46 SQL> select count(*) from sys.aud$;

  COUNT(*)
----------
         2

15:12:06 SQL> create user lala identified by lala;
User created.

15:12:24 SQL> grant connect, dba to lala;
Grant succeeded.

15:12:33 SQL> select count(*) from sys.aud$;

  COUNT(*)
----------
         2

15:12:44 SQL> create table t2 (c1 number);
Table created.

15:13:03 SQL> insert into t2 values (1);
1 row created.

15:13:16 SQL> commit;
Commit complete.

15:13:18 SQL> truncate table t2;
Table truncated.

15:13:28 SQL> select count(*) from sys.aud$;

  COUNT(*)
----------
         4

15:13:54 SQL> SELECT os_username, username, action_name, timestamp 
              FROM dba_audit_trail ORDER BY timestamp;

OS_USERNAME          USERNAME  ACTION_NAME       TIMESTAMP
-------------------- --------- ----------------- --------------------
MARTE\Administrator  SCOTT     CREATE TABLE      10:08 11-jan-2004
MARTE\Administrator  SCOTT     DROP TABLE        10:08 11-jan-2004
MARTE\Administrator  SYSTEM    CREATE TABLE      15:13 11-jan-2004
MARTE\Administrator  SYSTEM    TRUNCATE TABLE    15:13 11-jan-2004

Therefore, TRUNCATE TABLE is logged in the table SYS.aud$.
The user lala, who has DBA privileges, will now connect and truncate the sys.aud$ table; the question is whether this truncate action will be logged. In other words, which comes first: the truncate action or the logging of this action? You can try yourself.

If you have found this introduction useful, please see our second article for a more in-deep discussion.

[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