Expired passwords in 11g database using 10g client

When I create a new user/schema in an Oracle database I specify a password and immediately use the PASSWORD EXPIRE clause to expire this one-time-use only password. This way I make sure the user/application who is going to use this new account will be forced to specify his own password. This works great unless you have a pre 10.2.0.5 Oracle client connecting to an 11g database.

When connecting with a pre 10.2.0.5 client (for example SQL*Plus) after you login, the user will correctly receive the message that the password is expired and will be prompted for the new password. After typing the new password twice, he/she will receive an ORA-01017 (invalid username/password. Login denied) and the password stays unchanged. This will happen every time the user tries to login, in practice making the user unusable.

The problem is due to the case-insenstive 10g client and the use of case-sensitive passwords in 11g databases (enabled by default, which is good!). There are a couple of ways to fix/get around this problem:

  • Install the 10.2.0.5 patchset on your 10g clients or install the 11g client software instead
  • Don’t expire passwords

Ask the user to change the password for the account as soon as possible and check if this is done by looking at the timestamp in the ptime column of the sys.user$ table, which will show you the date/time of the last password change for an account.
Example query:

SELECT name, TO_CHAR(ptime,'DD-MM-YYYY HH24:MI:SS') "Password Changed" FROM sys.user$ WHERE name='&orauser';
  • Disable case-sensitive passwords in the 11g database (not recommended!)

Set the initialization parameter “sec_case_sensitive_logon” to FALSE.

ALTER SYSTEM SET sec_case_sensitive_logon=FALSE SCOPE=BOTH;

By the way, use the PASSWORD statement instead of ALTER USER …. IDENTIFIED BY … making sure passwords are not sent clear-text over the network.

A short description of this problem can be found in MOS note 888432.1

This entry was posted in Database and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

Blue Captcha Image
Refresh

*