alter user identified by values in 11g

I wrote about dba_users changes in 11g .

When spooling 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.

When using both hashes, switching back and forth to 11g mechanism is possible.

16 thoughts on “alter user identified by values in 11g”

  1. Chen Shapira

    Any idea what happens to users upgraded from 10g? Does the upgrade add the 11g hash?

    Very nice find about the 11g hash hidden away as spare4 column. In our test 11g system I used the hash value kept in password column to impersonate other users, but I didn’t realize that by doing that the password was no longer case sensitive.

  4. Pingback: AskDba.org Weblog | How To Change/Restore User Password in 11G

  5. housy

    does that mean if only the 11g oracle hash is used ,
    you can not use “alter user u identified by values” to reset the password? no matter sec_case_sensitive_logon is set ?

    i do not have a 11g in my hand, just curious

  6. Laurent Schneider Post author

    yes you can…

    either


    alter user u identified by values
    'S:8B1765172812D9F6B62C2A2B1E5FEF203200A44B4B87F9D934DABBB809A4;18FE58AECB6217DB';

    to have it case sensitive and case insensitive compatible

    or

    alter user u identified by values
    'S:8B1765172812D9F6B62C2A2B1E5FEF203200A44B4B87F9D934DABBB809A4';

    which will not work if you changed the case sensitivity to false

    or


    alter user u identified by values '18FE58AECB6217DB';

    which makes it case insensitive only (old style). Well, did I answer you question correctly? I think that was all in the post

  7. Pingback: Alter user identified by values on 11G without using SYS.USER$ « Coskan’s Approach to Oracle

  9. Laurent Schneider Post author

    Ow yes, using dbms_metadata is way better 🙂

    Also it will work in any version >= 9iR1

    The point of my post is exactly to demonstrate the “dba_users” approach is not safe and will not work in 11g!

  10. John

    Here is an Oracle problem I have…
    An application account, let’s call it uapp, is not able to connect to Oracle. There are obviously, a lot of ppl and programs using it hence, I can’t change it.

    Oracle stores the password in hash format. You can’t tell what the password is, from it. So, how would you know the decrypted password if all you have is the encrypted one? What is Oracle best practices here?

    Thanks,
    J.

  12. Lawrence

    John, the hash is a one way hash so you can not reverse it to determine the original clear text password. The way you are authenticated is your clear text entered password is hashed and the results is compared to the stored hash value.

  13. Pingback: ALTER USER IDENTIFIED BY VALUES – Simon Krenger

  14. Pingback: Password Change 11G | Oracle-dba

  16. Pingback: Legacy users get ORA-01017 in 12.2 – Laurent Schneider

Comments are closed.