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 - Production on Sat Nov 24 15:00:05 2007

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

SQL> connect / as sysdba
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:
SQL> connect bad_user
Enter password:
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$;

-------- ----------
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.

Thursday, November 15, 2007


With apologies to David Bowie.

Friday, 16 November 2007 marks the end (at least of this phase) of my tenure with Oracle Corporation. I will be turning in my badge. I worked with a fantastic and dedicated bunch of individuals. I wish them all the very best of luck and success. I will, of course, continue to be an Oracle advocate and will continue my relationship with Oracle "from the outside".

Tuesday, November 13, 2007

Oracle 11g and ORA-01017: invalid username/password; logon denied

If you have recently created an 11g database one behavior change that you may find confusing (if you are not aware of it) is that passwords are now case-sensitive by default. This behavior is controlled by the SEC_CASE_SENSITIVE_LOGON parameter. This behavior is easily demonstrated in SQL*Plus:

SQL*Plus: Release - Production on Tue Nov 13 19:36:05 2007

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

SQL> connect system/oracle
SQL> show parameter sec_case_sensitive_logon

NAME                     TYPE    VALUE
------------------------ ------- -----
sec_case_sensitive_logon boolean TRUE

SQL> create user test identified by demo;

User created.

SQL> grant create session to test;

Grant succeeded.

As you can see, on my system the value of the "sec_case_sensitive_logon" logon parameter is the default of "true". This means that passwords are case-sensitive. Therefore, in order to connect as the test user I need to specify the password in lower case:

SQL> connect test/demo

If I specify the password in anything other than the lower case I used when the user was created I will receive an error:

SQL> connect test/DEMO
ORA-01017: invalid username/password; logon denied

SQL> connect test/Demo
ORA-01017: invalid username/password; logon denied

If you use SQL*Plus for your administrative tasks it is also possible to encounter a potentially confusing situation even if you are aware of this password case-sensitivity:

SQL> connect system/oracle
SQL> drop user test cascade;

User dropped.

SQL> create user test identified by demo;

User created.

SQL> grant create session to test;

Grant succeeded.

SQL> connect test/demo
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

I just demonstrated that the password is case-sensitive and I am entering the password in lower case as I specified when I created the user. Or did I?

SQL> connect test/DEMO

Well, clearly I can connect by specifying the password in upper case, but I created the user with the password in lower case and I know the value of the sec_case_sensitive_logon parameter is true...

The culprit in this case is a SQL*Plus system variable: sqlcase

SQL> show sqlcase
sqlcase UPPER

Ah ha! Because the value of the sqlcase system variable was UPPER, SQL*Plus automatically converted my SQL (including the password) to upper case. So, while I did enter the password in lower case, it was transparently converted to upper case before the statement was submitted to the database; therefore, my user was created with an upper case password!

For more information on password case sensitivity see:

What Are the Oracle Database Built-in Password Protections?

Enabling or Disabling Password Case Sensitivity

Saturday, November 10, 2007

Configuring Windows Authentication

In order to connect to Oracle running on Windows using an operating system authenticated account, there are surprisingly few steps to take:

1. Ensure the sqlnet.ora file has a required entry

2. Verify (or set) a database/instance parameter

3. Create a database user

When connecting to the database using operating system authentication, you do not need to specify the "normal" user id -- instead you simply use a "/" character (without the quotes) and no password (the whole point!). Oracle allows for two classes of operating system authenticated users: enterprise users and external users. I am illustrating how to create and connect as an external user (i.e. everything is on the same machine). Creating and using an enterprise user requires a directory server and additional coordination between the database administrator and the operating system/directory server administrator.

The sqlnet.ora file Entry

When Oracle is initially installed and configured on the Windows platform the sqlnet.ora file is created in the %ORACLE_HOME%\network\admin folder. There may be multiple entries in this file; however, in order to connect using o/s authentication the file must have an entry similar to:


If this entry is not present in the sqlnet.ora file you are using then a connection attempt using o/s authentication will fail.

The Database/Instance Parameter

The os_authent_prefix parameter controls whether or not Oracle adds a prefix to the user id being presented during the o/s authentication process. By default the value of this parameter is "OPS$" though it is not uncommon for it to be changed or simply be set to an empty string (""). However, in order to create the user in the database, the value of this parameter must be known. Fortunately, determining the value is as simple as connecting to the database as a capable of successfully executing the SQL*Plus "show parameter" command:

SQL> show parameter os_authent_prefix

----------------- ----------- -----
os_authent_prefix string OPS$

As you can see, on my system this value has not been changed from the default value. This means that Oracle will prefix the "OPS$" value to the beginning of my Windows user name when I attempt to connect using o/s authentication. The Windows user name presented will be of the form DOMAIN or HOSTNAME\USERNAME. On my laptop (which is not part of a domain) my Windows user name will be presented to Oracle as "LIVERPOOL\MARKWILL".

Creating the Database User

Knowing that the value of the os_authent_prefix parameter is "OPS$" and that my Windows user name is "LIVERPOOL\MARKWILL" a database user named "OPS$LIVERPOOL\MARKWILL" will need to be created in the database:

SQL*Plus: Release - Production on Sat Nov 10 14:07:33 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> connect / as sysdba
SQL> create user "OPS$LIVERPOOL\MARKWILL" identified externally
2 default tablespace users
3 temporary tablespace temp
4 quota unlimited on users;

User created.

SQL> grant connect, alter session to "OPS$LIVERPOOL\MARKWILL";

Grant succeeded.

That's it!

Now I am ready to test my freshly created user that will connect to the database via my Windows-authenticated account (notice the connect string is just the "/" -- I am connecting to the database specified by the ORACLE_SID value on my system):

SQL*Plus: Release - Production on Sat Nov 10 14:11:14 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> connect /

-------------------------------- ------------------------

1 row selected.

If you wish to connect to a database by explicitly specifying a tns alias the connect string would look like:

connect /@tnsalias

For example:

connect /@lt11gr1

Specifying an Operating System Authenticated Account in ODP.NET

Just as the connect string in SQL*Plus consists of a "/" and possibly a tns alias, the connect string in an ODP.NET application can be as simple as:

"User Id=/"


"User Id=/; Data Source=mydatasource"

NOTE: There is a behaviour change from ODP.NET versions prior to 11g and the 11g release. In previous versions connection pooling was not used for operating system authenticated connections regardless of the setting of the "pooling" parameter in the connection string. Beginning with the 11g release, connection pooling is now supported using operating system authenticated connections.

Happy connecting!