ALTER USER COMMAND IN 11g

alter user identified by values in 11g When executing alter user commands in 11g, it is important to understand the mechanism. Oracle 11g supports both sensitive and insensitive passwords. When issuing an CREATE/ALTER USER IDENTIFIED BY PASSWORD, both the insensitive and the sensitive hashes are saved. SQL> create user u identified by u; User created. SQL> grant create session to u; Grant succeeded. SQL> connect u/U ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. SQL> connect u/u Connected. Per default only the proper case works SQL> alter system set sec_case_sensitive_logon=false; System altered. SQL> connect u/U Connected. SQL> conn u/u Connected. When sec_case_sensitive_logon=false, both uppercase and lowercase passwords work (10g behavior). When issuing a create user identified by values, you must chose if you want to have both passwords, only the case insensitive or only the case sensitive. SQL> select password,spare4 from user$ where name='U'; PASSWORD ------------------------------ SPARE4 -------------------------------------------------------------- 18FE58AECB6217DB S:8B1765172812D9F6B62C2A2B1E5FEF203200A44B4B87F9D934DABBB809A4 The hashes are in USER$. SQL> alter user u identified by values '18FE58AECB6217DB'; User altered. SQL> alter system set sec_case_sensitive_logon=true; System altered. SQL> conn u/u Connected. SQL> conn u/U Connected. When only the 10g oracle hash is used as a value, the password is case insensitive whatever the setting of sec_case_sensitive_logon is. SQL> alter user u identified by values 'S:8B1765172812D9F6B62C2A2B1E5FEF203200A44B4B87F9D934- DABBB809A4'; User altered. SQL> alter system set sec_case_sensitive_logon=false; System altered. SQL> conn u/u ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. SQL> conn u/U ERROR: ORA-01017: invalid username/password; logon denied When only the 11g oracle hash is used as a value, the password is case sensitive and if the setting of sec_case_sensitive_logon is on false, the login failed as there is no 10g string. This setting is probably the most secure setting as the 10g string is not saved in USER$. SQL> alter user u identified by values 'S:8B1765172812D9F6B62C2A2B1E5FEF203200A44B4B87F9D934- DABBB809A4;18FE58AECB6217DB'; SQL> alter system set sec_case_sensitive_logon=true; System altered. SQL> conn u/u Connected. SQL> conn u/U ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. SQL> conn / as sysdba Connected. SQL> alter system set sec_case_sensitive_logon=false; System altered. SQL> conn u/u Connected. SQL> conn u/U Connected.