How To Change/Restore User Password in 11G

Oracle 11g introduces Case-sensitive passwords for database authentication. Along with this if you wish to change the password (temporarily) and reset it back to old , you will find that password field in dba_users is empty. Prior to 11g we could use following technique to change/restore password

SQL> create user amit identified by amit;

User created.

SQL> grant create session to amit;

Grant succeeded.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select username,password from dba_users where username='AMIT';

USERNAME                       PASSWORD
------------------------------ ------------------------------
AMIT                           9DEC0D889E8E9A6B

SQL> alter user amit identified by abc;

User altered.

SQL> conn amit/abc
Connected.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter user amit identified by values '9DEC0D889E8E9A6B';

User altered.

SQL> conn amit/amit
Connected.

In 11g if you query password field, it will return NULL.

SQL> select username,password from dba_users where username='AMIT';

USERNAME                       PASSWORD
------------------------------ ------------------------------
AMIT

Let’s first see Case-sensitive password feature in 11g and then steps to change/restore passwords

SQL> create user amit identified by AMIT;

User created.

SQL> grant connect,resource to amit;

Grant succeeded.

SQL> conn amit/amit
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn amit/AMIT
Connected.

This behavior is controlled by “sec_case_sensitive_logon” initialization paramter. If the value is true then it will enforce case sensitive passwords

SQL> select NAME,VALUE from V$SPPARAMETER where NAME='sec_case_sensitive_logon';

NAME                                     VALUE
---------------------------------------- --------------------
sec_case_sensitive_logon                 TRUE

SQL> conn / as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon=false;

System altered.

SQL> conn amit/amit
Connected.
SQL> conn / as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon=true;

System altered.

SQL> conn amit/amit
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn amit/AMIT
Connected.

Now to reset the password in 11g, we need to query spare4 column in user$ table

SQL> select spare4 from user$ where name='AMIT';

SPARE4
--------------------------------------------------------------------------------
S:2D058976AE8FAD8ECFCDB93835ACEE94C83EDE19169209155BB81FEE7DBB

SQL> alter user amit identified by abc12;

User altered.

SQL> conn amit/abc12
Connected.
SQL> conn / as sysdba
Connected.
SQL> alter user amit identified by values 'S:2D058976AE8FAD8ECFCDB93835ACEE94C83EDE19169209155BB81FEE7DBB';

User altered.

SQL> conn amit/abc12
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn amit/AMIT
Connected.

As per Metalink Note 429465.1 , view DBA_USERS has new column PASSWORD_VERSIONS rendered as follows:

decode(length(u.password),16,'10G ',NULL)||NVL2(u.spare4, '11G ' ,NULL)
for example:

SQL> select USERNAME, PASSWORD_VERSIONS from dba_users where rownum <5;

USERNAME                       PASSWORD
------------------------------ --------
SYS                            10G 11G
SYSTEM                         10G 11G
OUTLN                          10G 11G
DIP                            10G 11G

In this case it means both old and new-style hash values are available for the users, the new hash value is stored in the USER$.SPARE4 column, as long as this remains NULL it means the password has not been changed since the migration and the user will have the old case insensitive password.

SQL> create user test identified by test;

User created.

SQL> select USERNAME, PASSWORD_VERSIONS from dba_users where username in (‘AMIT’,'TEST’);

USERNAME                       PASSWORD
—————————— ——–
AMIT                           11G
TEST                           10G 11G

As I had reset password using only spare4 string, password will be case -sensitive irrespective of setting for sec_case_sensitive_logon parameter value. i.e why we see value of “11G”  for user Amit.

Update

When resetting the password, we need to also query password column from user$ column if we wish to use case-insensitive feature in future. i.e In my above example I used only spare4 column value to reset the password. Now if I set sec_case_sensitive_logon=false , I will not be able to connect.

SQL> alter system set sec_case_sensitive_logon=false;

System altered.

SQL> conn amit/amit
ERROR:
ORA-01017: invalid username/password; logon denied

In case we wish to use both, we need to set identified by values ‘S:spare4;password’. As I didnot use password field while resetting, I find that password field in user$ is empty. To correct it, I had to change the password again.

SQL> select password,spare4 from user$ where name='AMIT';

PASSWORD                       SPARE4
------------------------------ ----------------------------------------------------------------------
                               S:2D058976AE8FAD8ECFCDB93835ACEE94C83EDE19169209155BB81FEE7DBB

SQL>  alter system set sec_case_sensitive_logon=true;

System altered.

SQL> alter user amit identified by AMIT;

User altered.

SQL> select password,spare4 from user$ where name='AMIT';

PASSWORD                       SPARE4
------------------------------ ----------------------------------------------------------------------
9DEC0D889E8E9A6B               S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE

So to reset the password, following needs to be used.

SQL> select password,spare4 from user$ where name='AMIT';

PASSWORD                       SPARE4
------------------------------ ----------------------------------------------------------------------
9DEC0D889E8E9A6B               S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE

SQL> alter user amit identified by values 'S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE;9DEC0D889E8E9A6B';

User altered.

Thanks to Laurent for pointing this. You can see his article for more information.You can use below code to get the password script

select 'alter user '||name||' identified by values '''||password||''';' from user$ where spare4 is null and password is not null
union
select 'alter user '||name||' identified by values '''||spare4||';'||password||''';' from user$ where spare4 is not null and password is not null;