Tuesday, February 5, 2013

Gotcha: Oracle11g Password Case Sensitivity

We are in the process of upgrading to Oracle 11g. Today, a friend at work asked about an issue he had with connecting to Oracle 11g from Unix. He was able to connect fine in SQL*Tools, SQL*Plus etc, but not when connecting from a script. The script kept failing with invalid user name or password error. The difference was that the script actually used an encrypted password and decrypted it using ccrypt utility.

We tried the decrypt option on command line, and it turns out the password was all upper case. Same script used to work in 10g. Next, we tried SQL*Plus with upper case password. It failed while the same thing worked in Oracle 10g. There we realized that Oracle 11g passwords may be case sensitive.

A beautiful post about the same, confirmed this. Apparently, passwords are case sensitive in Oracle 11g by default. This can be overridden as mentioned in the above link. Just to be complete, I am including that here:
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

You can also look at Oracle page about authentication for more details. AskTom talks about this change.

It was a real gotcha for us today.

References
http://www.oracle-base.com/articles/11g/case-sensitive-passwords-11gr1.php
http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams211.htm
http://docs.oracle.com/cd/B28359_01/network.111/b28531/authentication.htm#DBSEG3225
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:708040800346734217

No comments :

Post a Comment

I will be happy to hear your comments or suggestions about this post or this site in general.