Connection Audit

Tracking what users are coming and going on a database is an important process.  One of the main reasons for this is because when something goes wrong or data is missing it maybe a good idea to have a starting place to review what could have potentially happened.    Also, from a SOX compliance stand point, it will keep a lot of people out of trouble when the auditors come a calling.  In oracle, auditing is easy to setup.

The first step in setting up auditing on Oracle is to set the init.ora parameter.  This parameter can be either manually added to the init.ora file.  I find it easier if, with Oracle10g, to just update the spfile.  Remember though, if you update the spfile, you need to recreate the pfile so this option will be enabled is you ever need to use the pfile.

From SQL*Plus as SYS user turn on auditing:

Alter system set audit_trail=DB scope=spfile;

The database will need to be shutdown and started up again to read this parameter into the database configuration.

Now, you will want to enable auditing on the database.  What!, auditing is not enabled when you set the option in the init.ora file!  That would be correct, the init.ora file just tell Oracle that you want to use auditing.  Now we are going to tell Oracle what to audit for.  In this case we are going to audit for logon and logoff connections.

From SQL*Plus as SYS user issue the following command:

Audit connect;

This will now log every logon and logoff that connect to the database.

To check and see what is being audited by session, the dba_audit_session view is used.  This view will contain all information for sessions that are being audited since auditing was enabled.  The return code column should report a 0 if the logon/logoff was successful.  Other reason codes you should look out for will be 1070 and 28000.

Ora-1070 = invalid logon/password

Ora-28000 = account is locked/expired