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 11.1.0.6.0 - Production on Tue Nov 13 19:36:05 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> connect system/oracle
Connected.
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
Connected.
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
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect test/Demo
ERROR:
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
Connected.
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
ERROR:
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
Connected.
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?
http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authentication.htm#CHDBBGFG
Enabling or Disabling Password Case Sensitivity
http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authentication.htm#CHDJDCGI
