Oracle 11gR2 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 sujeet identified by patna123;
User created.
SQL> grant create session to sujeet;
Grant succeeded.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select username,password from dba_users where username='sujeet';
USERNAME PASSWORD
------------------------------ ------------------------------
sujeet 8DEC0D889E8E9A6B
SQL> alter user sujeet identified by patna123;
User altered.
SQL> conn amit/patna123
Connected.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter user sujeet identified by values '8DEC0D889E8E9A6B';
User altered.
SQL> conn sujeet/patna123
Connected.
In 11g if you query password field, it will return NULL.
SQL> select username,password from dba_users where username='AMIT';
USERNAME PASSWORD
------------------------------ ------------------------------
sujeet
Let's first see Case-sensitive password feature in 11g and then steps to change/restore passwords
SQL> create user sujeet identified by patna123;
User created.
SQL> grant connect,resource to sujeet;
Grant succeeded.
SQL> conn sujeet/Patna123
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn sujeet/patna
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 sujeet/PATNA123
Connected.
SQL> conn / as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon=true;
System altered.
SQL> conn sujeet/PATNA123
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn sujeet/patna123
Connected.
Now to reset the password in 11g, we need to query spare4 column in user$ table
SQL> select spare4 from user$ where name='SUJEET';
SPARE4
--------------------------------------------------------------------------------
S:2D058976AE8FAD8ECFCDB93835ACEE94C83EDE19169209155BB81FEE7DBB
SQL> alter user sujeet identified by patna321;
User altered.
SQL> conn sujeet/patna321
Connected.
SQL> conn / as sysdba
Connected.
SQL> alter user amit identified by values 'S:2D058976AE8FAD8ECFCDB93835ACEE94C83EDE19169209155BB81FEE7DBB';
User altered.
SQL> conn sujeet/patna32
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn sujeet/patna321
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 ('sujeet','TEST');
USERNAME PASSWORD
------------------------------ --------
sujeet 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 sujeet
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 sujeet/patna321
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='SUJEET';
PASSWORD SPARE4
------------------------------ ----------------------------------------------------------------------
S:2D058976AE8FAD8ECFCDB93835ACEE94C83EDE19169209155BB81FEE7DBB
SQL> alter system set sec_case_sensitive_logon=true;
System altered.
SQL> alter user amit identified by SUJEET;
User altered.
SQL> select password,spare4 from user$ where name='SUJEET';
PASSWORD SPARE4
------------------------------ ----------------------------------------------------------------------
9DEC0D889E8E9A6B S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE
So to reset the password, following needs to be used.
SQL> select password,spare4 from user$ where name='SUJEET';
PASSWORD SPARE4
------------------------------ ----------------------------------------------------------------------
9DEC0D889E8E9A6B S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE
SQL> alter user sujeet identified by values 'S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE;9DEC0D889E8E9A6B';
User altered.
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 sujeet identified by patna123;
User created.
SQL> grant create session to sujeet;
Grant succeeded.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select username,password from dba_users where username='sujeet';
USERNAME PASSWORD
------------------------------ ------------------------------
sujeet 8DEC0D889E8E9A6B
SQL> alter user sujeet identified by patna123;
User altered.
SQL> conn amit/patna123
Connected.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter user sujeet identified by values '8DEC0D889E8E9A6B';
User altered.
SQL> conn sujeet/patna123
Connected.
In 11g if you query password field, it will return NULL.
SQL> select username,password from dba_users where username='AMIT';
USERNAME PASSWORD
------------------------------ ------------------------------
sujeet
Let's first see Case-sensitive password feature in 11g and then steps to change/restore passwords
SQL> create user sujeet identified by patna123;
User created.
SQL> grant connect,resource to sujeet;
Grant succeeded.
SQL> conn sujeet/Patna123
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn sujeet/patna
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 sujeet/PATNA123
Connected.
SQL> conn / as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon=true;
System altered.
SQL> conn sujeet/PATNA123
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn sujeet/patna123
Connected.
Now to reset the password in 11g, we need to query spare4 column in user$ table
SQL> select spare4 from user$ where name='SUJEET';
SPARE4
--------------------------------------------------------------------------------
S:2D058976AE8FAD8ECFCDB93835ACEE94C83EDE19169209155BB81FEE7DBB
SQL> alter user sujeet identified by patna321;
User altered.
SQL> conn sujeet/patna321
Connected.
SQL> conn / as sysdba
Connected.
SQL> alter user amit identified by values 'S:2D058976AE8FAD8ECFCDB93835ACEE94C83EDE19169209155BB81FEE7DBB';
User altered.
SQL> conn sujeet/patna32
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn sujeet/patna321
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 ('sujeet','TEST');
USERNAME PASSWORD
------------------------------ --------
sujeet 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 sujeet
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 sujeet/patna321
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='SUJEET';
PASSWORD SPARE4
------------------------------ ----------------------------------------------------------------------
S:2D058976AE8FAD8ECFCDB93835ACEE94C83EDE19169209155BB81FEE7DBB
SQL> alter system set sec_case_sensitive_logon=true;
System altered.
SQL> alter user amit identified by SUJEET;
User altered.
SQL> select password,spare4 from user$ where name='SUJEET';
PASSWORD SPARE4
------------------------------ ----------------------------------------------------------------------
9DEC0D889E8E9A6B S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE
So to reset the password, following needs to be used.
SQL> select password,spare4 from user$ where name='SUJEET';
PASSWORD SPARE4
------------------------------ ----------------------------------------------------------------------
9DEC0D889E8E9A6B S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE
SQL> alter user sujeet identified by values 'S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE;9DEC0D889E8E9A6B';
User altered.
