Password Case-Sensitivity Beginning with Oracle 11g, when you create a user or alter an existing user to change his password, the password is case-sensitive. Dba_users.password_versions shows "10G 11G" ("10G 11G 12C" in 12c). The 10g password hash is stored in sys.user$.password, 11g hash in spare4 beginning with "S:".[note] If you import into 11g a dump file created from a 10g database (without pre-creating the account in the 11g database), or upgrade a 10g database to 11g, password_versions shows "10G" only. Alter'ing this user with a password creates both 10g and 11g hashes (and 12c hash as well in 12c). Laurent Schneider has a good article on password case-sensitivity http://laurentschneider.com/wordpress/2008/03/alter-user-identified-by-values-in-11g.html (Note: The text under, not above, Sql*Plus demo on his page explains the demo.) The following are interesting findings from his web page, with my re-wording and additions: (1) When only the 10g password hash exists (as a result of "alter user identified by values '<10g hash>'" or importing a 10g user or upgrading 10g database), the 10g hash is used and the password is case-insensitive regardless the setting of sec_case_sensitive_logon. (2) When only the 11g password hash exists (as a result of "alter user identified by values 'S:<11g hash>'"), if sec_case_sensitive_logon is true, the 11g hash is used and the password is case-sensitive. If sec_case_sensitive_logon is false, Oracle tries to use the 10g hash, which does not exist, and login fails. (3) When both hashes exist (as a result of "create user..." or the documented "alter user..." or undocumented "alter user ... identified by values '<10g hash>;S:<11g hash>'"), the 11g hash is used when sec_case_sensitive_logon is true, and the 10g hash is used if the parameter is false. Parameter sec_case_sensitive_logon is not as simple as whether the password can be case-sensitive. We may as well call it something like use_11g_password_if_exist. Case-sensitivity, or lack of it, is just a symptom of which password hash is used. In short, the process goes like this: if exists 11g hash if sec_case_sensitive_logon is true use 11g hash else --i.e. if it's false use 10g hash end if end if Because of the above finding (1) or the first if-condition here, we can't simply say the parameter enforces use of 11g password; without 11g hash, the param is not even relevant. Now some practical problems. 1. What's the new syntax for "identified by values"? create|alter user identified by values '<10g hash>;<11g hash>' (the order in the values clause makes no difference; <11g hash>;<10g hash> works too), where <10g hash> is user$.password and <11g hash> is user$.spare4 including "S:". 2. If a user is created in 11g database by importing a dump created on a 10g database or by upgrading the database, only the 10g password hash exists in the 11g database. How can you add the 11g password hash? The solution is to alter user identified by . Is there a way to do it without knowing the cleartext password? If you know the 11g hash corresponding to the password, of course you can alter user identified by values '<10g hash>;<11g hash>'. But that's an unlikely situation because it means there's another 11g database where this user exists with the same password (which you don't know the clear text of) and its 11g hash is available. 3. Some 10g based software seems to work only with sec_case_sensitive_logon set to false after the database upgrade or importing from 10g to 11g. Some ODBC drivers based on 10g client seem to (to be verified) blindly change the password to upper-case before sending it to the database for logon. You can manually change the password to all capital and set sec_case_sensitive_logon back to its default true. If it works, that's it. Alternatively, remove the 11g hash by alter user identified by values '<10g hash>'. 4. How do I remove the 10g hash leaving only the 11g hash in a documented way? If you don't want to run the undocumented command alter user identified by values '<11g hash>'; you have to add sqlnet.allowed_logon_version=11 to /network/admin/sqlnet.ora on the server. After that, alter user or create user in a new session will generate the 11g hash only, no 10g hash. There's no need to reload or restart the listener or database and the database connection can be either local or through Oracle Net. Note: This parameter is replaced by allowed_logon_version_server and allowed_logon_version_client in 12c. Ref: How to Allow Login to Database Based on the Client Version (Doc ID 402193.1) 2015-09 _______________ [note] In 12c, spare4 has "S:", "H:" and "T:". See slide 7 of http://www.petefinnigan.com/UKOUG-Conference-Passwords.pdf