Maybe you don't know about ORACLE secret series 1

Login; Useraltered. SQLconnamitabcConnected. SQLconnsysassysdbaEnterpassword: Connected. SQLalteruserLIUidentifiedbyvalues9DEC0D889E8E9A6B; Useraltered. SQLconnliul

LIU 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 LIU identified by values '9dec0d889e8e9a6b '; User altered. SQL conn liu/l

LIU 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 LIU identified by values '9dec0d889e8e9a6b ';

User altered.

SQL> conn liu/liu

Connected.

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

SQL> select username, password fromdba_users where username = 'liu ';

USERNAME PASSWORD

------------------------------------------------------------

LIU

Let's first see Case-sensitive passwordfeature in 11g and then steps to change/restore passwords

SYS @ orcl> create user liu identified byliu;

The user has been created.

SYS @ orcl> grant connect to liu;

Authorization successful.

SYS @ orcl> conn liu/liu

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: you are no longer connected to ORACLE.

@> Conn liu/LIU

Connected.

LIU @ orcl>

This behavior is controlled"Sec_case_sensitive_logon"Initialization paramter. If the value is true then it will enforce casesensitive passwords

LIU @ orcl> conn/as sysdba

Connected.

SYS @ orcl> SHO PARAMETER PFILE

NAME TYPE VALUE

-----------------------------------------------------------------------------

Spfile string \ opt \ DBHOME_1 \ DATABASE \ SPFILE

ORCL. ORA

SYS @ orcl> sho parameter sec_case_sensi

NAME TYPE VALUE

-----------------------------------------------------------------------------

Sec_case_sensitive_logon boolean TRUE

SYS @ orcl> alter system setsec_case_sensitive_logon = false;

The system has been changed.

SYS @ orcl> conn liu/liu

Connected.

LIU @ orcl> alter system setsec_case_sensitive_logon = true;

Alter system setsec_case_sensitive_logon = true

*

Row 3 has an error:

ORA-01031: insufficient Permissions

LIU @ orcl> conn/as sysdba

Connected.

SYS @ orcl> alter system setsec_case_sensitive_logon = true;

The system has been changed.

SYS @ orcl> conn liu/LIU;

Connected.

LIU @ orcl> conn liu/liu

ERROR:

ORA-01017: invalid username/password; logondenied

Warning: you are no longer connected to ORACLE.

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

@> Conn/as sysdba

Connected.

SYS @ orcl> select spare4 from user $ wherename = 'liu ';

SPARE4

Bytes ----------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------

S: 813731A84040EA2C4DF1545B869495ECA28C81486A11E5E19344F88BA312

SYS @ orcl> alter user liu identified byabc;

The user has changed.

SYS @ orcl> conn liu/ABC

Connected.

LIU @ orcl> CONN/AS SYSDBA

Connected.

SYS @ orcl> alter user liu identifiedby values's: 813731A84040EA2C4DF1545B869495ECA28C81486A11E5E19344F88BA312 ';

The user has changed.

SYS @ orcl> conn liu/ABC

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: you are no longer connected to ORACLE.

@> CONN/AS SYSDBA

Connected.

SYS @ orcl> conn LIU/LIU

Connected.

LIU @ orcl>

As per Metalink Note429465.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:

SYS @ orcl> select username, PASSWORD_VERSIONSFROM DBA_USERS where username = 'liu ';

USERNAME PASSWORD

--------------------------------------

LIU 11G

SYS @ orcl> select username, PASSWORD_VERSIONSFROM DBA_USERS where username in ('sys ', 'dna', 'Liu ');

USERNAME PASSWORD

--------------------------------------

SYS 10G 11G

DNA 10G 11G

LIU 11G

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

SYS @ orcl> create user LIU2 IDENTIFIED BYLIU2;

The user has been created.

SYS @ orcl> SELECTUSERNAME, PASSWORD_VERSIONS FROM DBA_USERS where username in ('sys ', 'dna', 'Liu ', 'liu2 ');

USERNAME PASSWORD

--------------------------------------

SYS 10G 11G

LIU 11G

LIU2 10G 11G

DNA 10G 11G

As I had reset passwordusing only spare4 string, password will be case-sensitive irrespective ofsetting for sec_case_sensitive_logon parameter value

Update

When resetting the password, we need toalso query password column from user $ column if we wish to use case-insensitivefeature in future. i. e In my abve example I used only spare4 column value toreset the password. now if I set sec_case_sensitive_logon = false, I will not beable to connect.

SYS @ orcl> conn liu/Liu

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: you are no longer connected to ORACLE.

@> Conn LIU/LIU

Connected.

LIU @ orcl> CONN/AS SYSDBA

Connected.

SYS @ orcl> alter system SETSEC_CASE_SENSITIVE_LOGON = FALSE;

The system has been changed.

SYS @ orcl> sho parameter SEC_CASE

NAME TYPE VALUE

-----------------------------------------------------------------------------

Sec_case_sensitive_logon boolean FALSE

SYS @ orcl> conn liu/LIU

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: you are no longer connected to ORACLE.

@> Conn liu/liu

ERROR:

ORA-01017: invalid username/password; logon denied

In case we wish to useboth, we need to setIdentified by values's: spare4; password'. As I didnot usepassword field while resetting, I find that password field in user $ is empty. To correct it, I had to change the password again.

SYS @ orcl> select password, spare4 fromuser $ where name = 'liu ';

PASSWORD

------------------------------

SPARE4

Bytes ------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------

S: 813731A84040EA2C4DF1545B869495ECA28C81486A11E5E19344F88BA312

SYS @ orcl> alter system setsec_case_sensitive_logon = true;

The system has been changed.

SYS @ orcl>

SYS @ orcl> alter user liu identified byabcabc;

The user has changed.

SYS @ orcl> select password, spare4 fromuser $ where name = 'liu ';

PASSWORD

------------------------------

SPARE4

Bytes ------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------

622BF185A48AEFD1

S: FF0C2DBE8CBFCCECF290452C0389A9117101E1025B47504F9CEE356AF0EF

SYS @ orcl> alter user liu identified byvalues's: FF0C2DBE8CBFCCECF290452C0389A9117101E1025B47504F9CEE356AF0EF; 622BF185A48

AEFD1 ';

The user has changed.

SYS @ orcl> selectusername, password_versions from dba_users where username like 'Liu % ';

USERNAME PASSWORD

--------------------------------------

LIU2 10G 11G

LIU 10G 11G

SYS @ orcl> sho parameter sec_case

NAME TYPE VALUE

-----------------------------------------------------------------------------

Sec_case_sensitive_logon boolean TRUE

SYS @ orcl> conn LIU/abcabc

Connected.

LIU @ orcl> conn LIU/ABCABC

ERROR:

ORA-01017: invalid username/password; logondenied

Warning: you are no longer connected to ORACLE.

@> Conn/as sysdba

Connected.

SYS @ orcl> alter system setsec_case_sensitive_logon = false;

The system has been changed.

SYS @ orcl> conn liu/ABCABC

Connected.

LIU @ orcl>