Oracle Database Auditing

Oracle auditing is a very powerful thing. It gives you the ability to track of users’ activities.
You can simply identify what they are doing to your DB:).

Audit records can be stored in Operating System files on in Database. Initialization parameter audit_trail identifies where audit logs should be saved.

–To save it in OS, open initSID.ora parameter file and add/change the following parameter:

*.audit_trail = OS

–To save it in Database:

*.audit_trail = DB

There exists additional option when you are saving logs in the database. If you set audit_trail parameter to db_extended, it will tell the database to record audit records in the database together with bind variables (SQLBIND) and also (SQLTEXT).

*.audit_trail = DB_EXTENDED

–To see if it is enabled, query the following:

select name,value
from v$parameter
where name='audit_trail' ;

–My result is:

NAME        VALUE
------------------
audit_trail DB

Note you should reboot the database for the change to take effect.

In my case logs will be recorded in SYS.AUD$ table. But if you set audit_trail=OS, then you should also indicate where to save log files. Initialization parameter audit_file_dest indicates it. Default is:

–In Windows

%ORACLE_BASE%\ADMIN\ORCL\ADUMP

–In Unix

$ORACLE_HOME/rdbms/audit

There are four levels of auditing: statement, privilege, object, and fine-grained access.

1. Statement Auditing

This auditing involves monitoring of execution of SQL statements.

For example:

audit create table;
audit drop table;
audit truncate table;

–Or simply

audit table;

You also have the ability to audit statements executed by some special user.

For example:

audit create table by mkupatadze;

Or you can audit statements executed by some special user whenever this SQL statement fails or successes.

For example:

audit create table by mkupatadze whenever not successful;

or

audit create table by mkupatadze whenever successful;

Default is both of them successful and not successful.

Other options which you can use in auditing are by access and by session.
By access – one audit record will be created for each executed statement.
By session -oracle will try to merge multiple audit entries into one record when the session and the action audited match. But it only works for SQL statements other than DDL.

If statement is DDL then oracle behaves as if you indicated by access option. So one audit record will be created for each executed DDL statement doesn’t matter which option you indicated.(It is very important to note)

For example:

audit create table by mkupatadze by access whenever successful;

So one audit record will be created for each executed create table statement by mkupatadze user whenever it is successful.

1.1 Identifying Enabled Statement Auditing Options

–Query the following view:

select user_name
       ,audit_option
       ,success
       ,failure
from dba_stmt_audit_opts

–My result

USER_NAME   | AUDIT_OPTION | SUCCESS    | FAILURE
--------------------------------------------------------
MKUPATADZE  | CREATE TABLE | BY ACCESS  | BY ACCESS

1.2 Disabling Statement Auditing

noaudit table;
noaudit create table by mkupatadze;

Note that whenever successful or whenever not successful options is not necessary.

2. Privilege Auditing

This audit option audits privileges, for example, select any table,create any table… and so on..

Audit Example:

audit create any table;
audit create any table by mkupatadze;
audit create any table by mkupatadze by access;
audit create any table by mkupatadze by access whenever successful;

In the first example, one audit record will be created for each executed create any table statement. The second one specifies the user…Other examples are self-explanatory.

2.1 Identifying Enabled Privilege Auditing Options

–Query this

select user_name
       ,privilege
       ,success
       ,failure
from dba_priv_audit_opts

–My result

USER_NAME |PRIVILEGE       |SUCCESS  |FAILURE
----------------------------------------------
NULL      |CREATE ANY TABLE|BY ACCESS|BY ACCESS
MKUPATADZE|CREATE ANY TABLE|BY ACCESS|BY ACCESS

2.2 Disabling Privilege Auditing

noaudit create any table;
noaudit create any table by mkupatadze;
noaudit create any table by mkupatadze whenever successful;

Note that if you have enabled auditing for some user,in my case mkupatadze, you must indicate by username clause to disable it. As you have seen in previous section, two different records were created in dba_priv_audit_opts for mkupatadze and for other users.

Also note that by access or by session clauses must not be specified, or it gives an error.

3. Object Auditing

audit select on hr.employees;
audit select on hr.employees by access;
audit select on hr.employees by session whenever successful;

3.1 Identifying Enabled Object Auditing Options

–Query this

select owner
        , object_name
        , object_type
        , del
        , sel
from dba_obj_audit_opts

–My result

OWNER     |OBJECT_NAME|OBJECT_TYPE|DEL|SEL
------------------------------------------
MKUPATADZE|EMPLOYEES  |TABLE      |-/-|S/A

Note that I have selected just DEL and SEL, there are many. DEL means delete object privilege, SEL means select object privilege.
The symbol “-” means that no audit option is enabled.
“A” means by access.
“S” means by session.
From “S/A” means that by session auditing is enables when it is successful and by access auditing is enabled when it is not successful.
So first place is for successful and second one for not successful auditing.

3.2 Disabling Object Auditing

noaudit select on hr.employees;
noaudit select on hr.employees whenever successful;

Note that by access or by session must not be specified.

See Audit Logs

The view dba_audit_trail is based on the SYS.AUD$ table. So if you query from this view, you will see the content of SYS.AUD$ table.

select username, timestamp, action_name
from dba_audit_trail

or

select * from sys.aud$

Purging Audit Logs

You can manually delete records from sys.aud$ table or create a job which periodically purges the table. SYS.AUD$ table is created in SYSTEM tablespace by default.

To manually purge audit records older than 60 days, execute the following as user SYS:

delete from sys.aud$ where timestamp# < sysdate-60;

Some aspects in this post is from Sybex.OCA.Oracle.10g.Administration.I.Study.Guide.1Z0-042 book.