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.
