Enable database auditing

Oracle database auditing can be enabled by setting the audit_trail initialization parameter.
You can check the current status using following select:

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

Value can have a number of different possibilites:

• none or false: auditing is not enabled
• OS: audit records are written to your operating system
• db or true / db, extended: audit trail records are written to the SYS.AUD$ table in your database. Under normal circumstances, this table exists in the SYSTEM tablespace.
• xml / xml, extended: audit trail records are written to XML OS files.

If you want to enable auditing, the value should not be NONE or FALSE.

You change the value either in the init<ORACLE_SID>.ora file if you are using a pfile, or in the spfile using following command:

SQL> alter system set audit_trail=DB scope=spfile;

System altered.

You need to bounce (stop and restart) the database in order to let the parameter become active.

Now, if you want to enable auditing when users connect, use following statement to enable session logon (creation) auditing:

SQL> audit create session;

Audit succeeded.

If you want to disable a certain audit action, you can use the NOAUDIT statement:

SQL> noaudit create session;

Noaudit succeeded.

Now, you can use the dba_audit_trail or any other audit table to check for audit records:

-- select all invalid logon sessions for a certain user ordered by last logon attempt
select * from dba_audit_session where username='username' and returncode != 0 order by timestamp desc;

If you want to know to what operating system file location audit files are written, you can run following query:

SQL> select value from v$parameter where name='audit_file_dest';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/product/10.2.0/rdbms/audit

To check the tablespace the AUD$ table belongs to, use following select:

SQL> select tablespace_name from dba_tables where owner='SYS' and table_name='AUD$';

TABLESPACE_NAME
------------------------------
SYSTEM