Maybe you don't know about ORACLE secret series 1

Maybe you don't know about the ORACLE secret series-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 cocould use following technique to change/restore passwordSQL> create user liu identified by liu; User created. www.2cto.com SQL> grant create se Ssion to liu; Grant succeeded. SQL> conn sys as sysdbaEnter password: Connected. SQL> select username, password from dba_users where username = 'liu'; USERNAME PASSWORD ------------------------------ ---------------------------- LIU >alter user amit identified by abc; User altered. SQL> conn amit/abc www.2cto.com Connected. SQL> conn sys as sysdbaEnter password: Connected. SQL> alter user L IU identified by values '9dec0d889e8e9a6b '; User altered. SQL> conn liu/liuConnected. in 11g if you query password field, itwill return NULL. SQL> select username, password fromdba_users where username = 'liu '; username password login LIULet's first see Case-sensitive passwordfeature in 11g and then steps to change/restore passwordsSYS @ orcl> create user L Iu identified byliu; the user has been created. SYS @ orcl> grant connect to liu; authorization successful. SYS @ orcl> conn liu/liuERROR: ORA-01017: invalid username/password; logon denied warning: you are no longer connected to ORACLE. @> Conn liu/LIU is connected. LIU @ orcl> This behavior is controlled by "sec_case_sensitive_logon" initialization paramter. If the value is true then it will enforce casesensitive passwordsLIU @ orcl> conn/as sysdba is connected. SYS @ orcl> sho parameter pfilename type value ----------------------------------------------- ---------------------------- spfile string \ opt \ DBHOME_1 \ DATABASE \ spfile orcl. ORASYS @ orcl> sho parameter sec_case_sensiNAME type value specified parameter sec_case_sensitive_logon boolean TRUESYS @ orcl> alter system setsec_case_sensitive_logon = false; system changed. SYS @ orcl> conn liu/liu is connected. LIU @ orcl> alter system setsec_case_sensitive_logon = true; alter system setsec_case_sensitive_logon = true * row 1st error: ORA-01031: insufficient permission LIU @ orcl> conn/as sysdba connected. SYS @ orcl> alter system setsec_case_sensitive_logon = true; the system has changed. SYS @ orcl> conn liu/LIU; connected. LIU @ orcl> conn liu/liuERROR: 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 is connected. SYS @ orcl> select spare4 from user $ wherename = 'liu'; SPARE4------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------S: alias @ orcl> alter user liu identified byabc; the user has changed. SYS @ orcl> conn liu/ABC is connected. LIU @ orcl> CONN/as sysdba is connected. SYS @ orcl> alter user liu identifiedby values's: 813731A84040EA2C4DF1545B869495ECA28C81486A11E5E19344F88BA312 '; the USER has changed. SYS @ orcl> conn liu/ABCERROR: ORA-01017: invalid username/password; logon denied warning: you are no longer connected to ORACLE. @> CONN/as sysdba is connected. SYS @ orcl> conn LIU/LIU is 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 11GSYS @ orcl> select username, PASSWORD_VERSIONSFROM DBA_USERS WHERE USERNAM E in ('sys ', 'dna', 'Liu '); username password ---------------------------- SYS 10G 11 GDNA 10G 11 GLIU 11GIn 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 pa Ssword. 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 11 GLIU 11GLIU2 10G 11 GDNA 10G 11GAs I had reset passwordusing only spare4 string, password will be case-sensitive irrespective ofsetting for sec_case_sensitive_logon parameter valueUpdateWhen resetting the password, we need toalso quer Y 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/LiuERROR: ORA-01017: invalid username/password; logon denied warning: you are no longer connected to ORACLE. @> Conn LIU/LIU is connected. LIU @ orcl> CONN/as sysdba is connected. SYS @ orcl> alter system SETSEC_CASE_SENSITIVE_LOGON = FALSE; the SYSTEM has changed. SYS @ orcl> sho parameter SEC_CASENAME type value between two sec_case_sensitive_logon boolean FALSESYS @ orcl> conn liu/LIUERROR: ORA-01017: invalid username/password; logon denied warning: you no longer connect to ORACLE. @> Conn liu/liuERROR: ORA-01017: invalid username/password; logon deniedIn 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 -- Optional ----------------------------------------------------------------------- S: Response @ orcl> alter system setsec_case_sensitive_logon = true; the system has changed. SYS @ orcl> alter user liu identified byabcabc; the user has changed. SYS @ orcl> select password, spare4 fromuser $ where name = 'liu'; PASSWORD------------------------------SPARE4--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------622BF185A48AEFD1S: alias @ orcl> alter user Liu identified byvalues's: FF0C2DBE8CBFCCECF290452C0389A9117101E1025B47504F9CEE356AF0EF; 622BF185A48AEFD1 '; the user has changed. SYS @ orcl> selectusername, password_versions from dba_users where username like 'Liu % '; username password certificate -------- LIU2 10G 11 GLIU 10G 11GSYS @ orcl> sho parameter sec_caseNAME type value specified eclipsec_case_sensitive_logon boolean TRUESYS @ orcl> conn LIU/abcabc is connected. LIU @ orcl> conn LIU/ABCABCERROR: ORA-01017: invalid username/password; logondenied warning: you are no longer connected to ORACLE. @> Conn/as sysdba is connected. SYS @ orcl> alter system setsec_case_sensitive_logon = false; the system has changed. SYS @ orcl> conn liu/ABCABC is connected. LIU @ orcl>