Saturday, November 24, 2007

Enable Database Auditing

I was recently asked about a "trick" I sometimes use to help troubleshoot issues in connecting to a database - in particular, when using an Operating System authenticated connection. The "trick" is really very simple: enable database auditing, audit connections, attempt to connect as the user having the issue, and then review the results.

Oracle have, of course, much more thoroughly documented auditing than I will do here. So, as usual, I recommend taking a look at the official documentation for 9i Release 2, 10g Release 2, and/or 11g Release 1 if needed.

Beginning with the 11g release, auditing is enabled by default and the audit stream is, also by default, captured in the database in a sys-owned table called "aud$". The audit information can be captured into the operating system audit trail rather than the database if desired. Auditing is enabled (or disabled) by the audit_trail parameter. The Database Reference Guide for 9i Release 2, 10g Release 2, and 11g Release 1 contains the valid settings for this parameter as well as a description for each setting.

Verify if Auditing is Enabled

To verify if auditing is enabled in the database, connect to the database as a dba (or sysdba) user via SQL*Plus. I will use a sysdba user here as I already know that auditing is not enabled and I will, therefore, need to enable it and restart the instance.

SQL*Plus: Release 10.2.0.3.0 - Production on Sat Nov 24 15:00:05 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

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

NAME        TYPE        VALUE
----------- ----------- -----
audit_trail string      NONE

Enable Auditing if Necessary

Since the audit_trail parameter is NONE, I need to enable auditing by setting the value to DB, stopping the instance, starting the instance, and then verifying the change:

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

System altered.

SQL> shutdown immediate;

[ database shutdown ]

SQL> startup open;

[ database opened ]

SQL> show parameter audit_trail

NAME        TYPE        VALUE
----------- ----------- -----
audit_trail string      DB

Enable Connection Auditing

In order to audit the connection attempts to the database, I need to tell Oracle that I want to do so:

SQL> audit connect;

Audit succeeded.

Attempt to Connect

To illustrate the auditing of connections, I will connect as a valid user then attempt to connect as a non-existent user. Both of these connection attempts are from a new SQL*Plus session:

SQL> connect hr
Enter password:
Connected.
SQL> connect bad_user
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

Review the Results

Using a SQL*Plus session as a DBA user (here I am using my sysdba session previously started) you can query the sys.aud$ table for the results of the connection auditing:

SQL> select userid, returncode from sys.aud$;

USERID   RETURNCODE
-------- ----------
HR                0
BAD_USER       1017

2 rows selected.

Here you can see that my "good" connection attempt was done as the HR user and that the return code was 0 while the "bad" connection attempt was done as the BAD_USER user (which does not exist) and the return code was 1017. The 1017 return code corresponds to the "ORA-01017: invalid username/password; logon denied" error message.

Being able to capture and query this information can often times be helpful in determining the user id of an Operating System authenticated user that is having difficulty in establishing a connection to the database. Once you know the user id being used, you can ensure that you have properly created that user in the database. In addition, the dba_audit_session view presents information related to audited connect/disconnect operations. See your respective Oracle documentation for more details on this view.

Turning off the Connection Auditing

After performing the auditing steps above, you may wish to disable the connection auditing. To do so, simply enter the following command in SQL*Plus as a DBA user:

SQL> noaudit connect;

Noaudit succeeded.