Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

How Oracle Stores Passwords

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Published:
Updated:

How Oracle Stores Passwords

Sean D. Stuber 2009-06-09

Up to date through Oracle version 11g


Overview:

Many times a DBA may want to replicate a user from one system to another and migrate that same password,such as refreshing a production system into a test database then resetting passwords to their test values.  Or, a privileged user may want to log in as another user for the purposes of testing some installation or in older versions (8i) granting privileges for that user. Some may simply be curious as to the nature of Oracle's security and want to examine where Oracle stores the authentication information as well as how it is exposed.


This article will attempt to explain the storage with a high level description of the algorithms used to secure the passwords as well as how to use this information to achieve the purposes above.

This article does not show, or attempt to show, how to break the algorithms or hack into other user accounts. Many of the queries and ddl used in the code snippets require enhanced privileges to run including:

SELECT ANY DICTIONARY, ALTER USER and ALTER SYSTEM.


Enjoy!


Passwords storage in version 10g and lower is a relatively simple process.


How does Oracle authenticate you?

Your username is stored in plain text but the password information is stored as a hash.  When you log in, your password is concatenated to the end of your username and that larger string is then hashed and compared that to the stored hash.  If they match then you have a valid password.


Why does Oracle use Hashing intead of Encrypting?

Hashing isn't a reversible operation; but encryption is.  The ramifications of this are simple: since you can't reverse a hash, you can't extract the password from a hash; but, if the password is encrypted it could possibly be unencrypted (albeit with difficulty, but still possible.)


In theory, an extremely lucky guess of random characters could pass with a false-positive since hash algorithms can duplicate.  Oracle use a 16 digit hexadecimal number which yields 18,446,744,073,709,551,616 possible hashes.  So, while it is possible two different strings could hash to the same value, the probability of finding one is remote.


The hash of the username and password is stored in the PASSWORD column of SYS.USER$ and is exposed through the DBA_USERS view.  The algorithm that generates that hash is the same in all versions and platforms and does not include any identifying info from the database, host, instance or other information beyond the username and password.  Thus, if your username and password are the same on multiple databases they will all have the same hash value.


Prior to 9i introducing GRANT ANY OBJECT PRIVILEGE, it used to be necessary for authorized users (even with DBA role) to log in as an object owner in order to apply grants for those objects.  While no longer needed in recent versions for that particular task, it can still be useful to log in as another user from time to time.


If you don't know the other user's password you can temporarily change the password then restore it by saving the hash and then using the "IDENTIFIED BY VALUES" clause of the ALTER USER command to restore the hash value when you are done.


SQL> connect dbauser/dbapassword@mydatabase
Connected.
SQL> select username,password from dba_users where username = 'TESTUSER';

USERNAME   PASSWORD
---------- ------------------
TESTUSER   AEB6397C8E7598A7

SQL> alter user TESTUSER identified by temppwd1;

User altered.

SQL> connect testuser/temppwd1@mydatabase
Connected.
SQL> --do some work as testuser
SQL> connect dbauser/dbapassword@mydatabase
Connected.
SQL> alter user TESTUSER identified by values 'AEB6397C8E7598A7';

User altered.


Caveats to above:

* You must have authority to query DBA_USERS and you must have authority to ALTER USER


* If the user has a profile that doesn't allow password reuse you might not be allowed to put the password back.  If you have authority to change profiles you could change the profile to allow it, or alter the user to a different profile then restore the profile.


* If you have password expirations, your user will get a reset allowing that password to stick around longer than it normally would be allowed.


* Following the steps aboveallows you to impersonate another user, doing so may violate your company's security/ethics policies.


* If you lose, mistype, cut-n-paste incorrectly, or otherwise fail to restore the password hash to original value that user will not be able to log in until a new password is created.


* The above information is for 10g and lower passwords.  


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

Changes introduced with Database Version 11g

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


In 11g, if the PASSWORD_VERSIONS column of DBA_USERS contains 10G then all of the above still applies.  

If it does NOT contain 10g then you must use other methods.


11g introduces new security and the ability to support case sensitive passwords.

First, DBA_USERS no longer exposes the PASSWORD value, instead it will be NULL and you will have to go to SYS.USER$ to find the hash value.


However, if your PASSWORD_VERSIONS does not contain 10g then SYS.USER$.PASSWORD will not be reliable.  The reason is the hashing algorithm populating that field doesn't support case sensitivity.  So, regardless of the sensitivity setting and regardless of the upper or lower case of your password the hash will be the same.  If your PASSWORD_VERSIONS is 11g only then you will need to look in the SYS.USER$.SPARE4 column and you will see a much larger hex number.  This is because Oracle has switched to the SHA-1 (http://en.wikipedia.org/wiki/SHA-1) algorithm.  I didn't do any black-box reverse engineering to determine that, it's stated plainly within the documentation (http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authentication.htm#DBSEG30321)


Interestingly, if you assign the same password to a user the resulting hash values will be distinct; but they are still usable in an "IDENTIFIED BY VALUES" clause to restore a password.  This new algorithm is more secure than the old, and unlike the 10g and lower hashing does not appear to use the username to generate the hash instead a new salt value is added which is stored in the last 20 characters of the SPARE4 hash.  It is possible the salt is somehow derived from the username but Oracle has not documented this.


While the new algorithm is known to be stronger than the old, it is still subject to attack.  Like the original you can't unhash, but it is subject to cryptanalysis attack and has been shown that hash collisions can be found in significantly less time than brute force attack. (announced by Prof. Xiaoyun Wang in 2005, and accepted by NIST)


The SPARE4 column of SYS.USER$ is not documented by Oracle and, while it has existed prior to 11g it was not used.  As new versions and patches come out, Oracle may change where and how the password information is exposed.


The following snippets show the affects on the PASSWORD_VERSIONS, PASSWORD and SPARE4 columns based on how you change the password and the setting of the sec_case_sensitive_logon parameter.


Note, the password_versions is flagged 10g if PASSWORD is populated in SYS.USER$  even if you are using 11g case sensitive passwords.  So, be careful when looking at the versions, you should double check against the underlying SYS.USER$ table and V$PARAMETER to be sure.



Setup:

Show a sample user setup  "TESTUSER" with password "testpwd"

SQL> show parameters sec_case;

NAME                                 TYPE        VALUE                                                                                                
------------------------------------ ----------- ------------------------------                                                                       
sec_case_sensitive_logon             boolean     TRUE                                                                                                 
SQL> alter user testuser identified by testpwd;

User altered.

SQL> select username,password,password_versions from dba_users where username = 'TESTUSER';

USERNAME   PASSWORD             PASSWORD_VERSIONS                                                                                                     
---------- -------------------- --------------------                                                                                                  
TESTUSER                        10G 11G                                                                                                               

SQL> select name,password,spare4 from sys.user$ where name = 'TESTUSER';

NAME       PASSWORD             SPARE4                                                                                                                
---------- -------------------- ---------------------------------------------------------------                                                       
TESTUSER   AEB6397C8E7598A7     S:17F9149EFD0BDD9DBA305D6910D5928640F7727B29F261D851C58D37FA9A                                                        

SQL> connect testuser/testpwd;
Connected.


Changing the password:

Showing the mixed case password is observed based on the v$parameter setting shown earlier.

SQL> connect dbauser/dbapassword;
Connected.
SQL> alter user testuser identified by TestPwd;

User altered.

SQL> connect testuser/testpwd;
ERROR:
ORA-01017: invalid username/password; logon denied 


Warning: You are no longer connected to ORACLE.
SQL> connect testuser/TestPwd;
Connected.


Comparing hashes:

Notice the PASSWORD field in both dba_users and user$ have not changed even though the password

did change, but the SPARE4 has changed (note, your SPARE4 will likely be different)

SQL> connect dbauser/dbapassword;
Connected.
SQL> select username,password,password_versions from dba_users where username = 'TESTUSER';

USERNAME   PASSWORD             PASSWORD_VERSIONS                                                                                                     
---------- -------------------- --------------------                                                                                                  
TESTUSER                        10G 11G                                                                                                               

SQL> select name,password,spare4 from sys.user$ where name = 'TESTUSER';

NAME       PASSWORD             SPARE4                                                                                                                
---------- -------------------- ---------------------------------------------------------------                                                       
TESTUSER   AEB6397C8E7598A7     S:5531B589807E81251B13E95219509D6FF2CE7E8D6C3889FFA4FCA9B94729                                                        


Changing passwords by hash value rather than literal:

Note the PASSWORD_VERSIONS column changes based on which hash value is used.

Also note a 10g only password is still case IN-sensitive even though the database is still case sensitive.

SQL> alter user testuser identified by values 'AEB6397C8E7598A7';

User altered.

SQL> select username,password,password_versions from dba_users where username = 'TESTUSER';

USERNAME   PASSWORD             PASSWORD_VERSIONS                                                                                                     
---------- -------------------- --------------------                                                                                                  
TESTUSER                        10G                                                                                                                   

SQL> select name,password,spare4 from sys.user$ where name = 'TESTUSER';

NAME       PASSWORD             SPARE4                                                                                                                
---------- -------------------- ---------------------------------------------------------------                                                       
TESTUSER   AEB6397C8E7598A7                                                                                                                           

SQL> connect testuser/testpwd;
Connected.
SQL> connect testuser/TestPwd;
Connected.
SQL> connect dbauser/dbapassword;
Connected.
SQL> alter user testuser identified by values 'S:C7C0B0D97F60CA87C0CEB1663522C76509BD2FF84624774EAED94982A453';

User altered.

SQL> select username,password,password_versions from dba_users where username = 'TESTUSER';

USERNAME   PASSWORD             PASSWORD_VERSIONS                                                                                                     
---------- -------------------- --------------------                                                                                                  
TESTUSER                        11G                                                                                                                   

SQL> select name,password,spare4 from sys.user$ where name = 'TESTUSER';

NAME       PASSWORD             SPARE4                                                                                                                
---------- -------------------- ---------------------------------------------------------------                                                       
TESTUSER                        S:C7C0B0D97F60CA87C0CEB1663522C76509BD2FF84624774EAED94982A453                                                        

SQL> connect testuser/testpwd;
ERROR:
ORA-01017: invalid username/password; logon denied 


Warning: You are no longer connected to ORACLE.
SQL> connect testuser/TestPwd;
Connected.


Turning off case sensitivity:

Notice that case insensitivity only uses the PASSWORD column of sys.user$ but an 11g password

does not populate that field, thus making it impossible for the user to log in.

SQL> connect dbauser/dbapassword;
Connected.
SQL> alter system set sec_case_sensitive_logon=FALSE;

System altered.

SQL> select username,password,password_versions from dba_users where username = 'TESTUSER';

USERNAME   PASSWORD             PASSWORD_VERSIONS                                                                                                     
---------- -------------------- --------------------                                                                                                  
TESTUSER                        11G                                                                                                                   

SQL> select name,password,spare4 from sys.user$ where name = 'TESTUSER';

NAME       PASSWORD             SPARE4                                                                                                                
---------- -------------------- ---------------------------------------------------------------                                                       
TESTUSER                        S:C7C0B0D97F60CA87C0CEB1663522C76509BD2FF84624774EAED94982A453                                                        

SQL> connect testuser/testpwd;
ERROR:
ORA-01017: invalid username/password; logon denied 


Warning: You are no longer connected to ORACLE.
SQL> connect testuser/TestPwd;
ERROR:
ORA-01017: invalid username/password; logon denied 


SQL> connect dbauser/dbapassword;
Connected.
SQL> alter system set sec_case_sensitive_logon=TRUE;

System altered.

SQL> exit


I hope this helps illustrate how to examine your passwords as well as manipulate and restore them effectively.

Questions and comments welcome.


Thanks,

Sean D. Stuber


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


To replicate the tests above on an 11g system



The tests assume the following users have been created

CREATE USER DBAUSER IDENTIFIED BY dbapassword;
GRANT DBA TO DBAUSER;
ALTER USER DBAUSER DEFAULT ROLE ALL;
CREATE USER TESTUSER IDENTIFIED BY testpwd;
GRANT CREATE SESSION TO TESTUSER;


Script for 11g Tests

column name format a10
column username format a10
column password format a20
column password_versions format a20
column spare4 format a63
set lines 150
spool 11g_password_tests.txt
show parameters sec_case;
alter user testuser identified by testpwd;
select username,password,password_versions from dba_users where username = 'TESTUSER';
select name,password,spare4 from sys.user$ where name = 'TESTUSER';
connect testuser/testpwd;
connect dbauser/dbapassword;
alter user testuser identified by TestPwd;
connect testuser/testpwd;
connect testuser/TestPwd;
connect dbauser/dbapassword;
select username,password,password_versions from dba_users where username = 'TESTUSER';
select name,password,spare4 from sys.user$ where name = 'TESTUSER';
alter user testuser identified by values 'AEB6397C8E7598A7';
select username,password,password_versions from dba_users where username = 'TESTUSER';
select name,password,spare4 from sys.user$ where name = 'TESTUSER';
connect testuser/testpwd;
connect testuser/TestPwd;
connect dbauser/dbapassword;
alter user testuser identified by values 'S:C7C0B0D97F60CA87C0CEB1663522C76509BD2FF84624774EAED94982A453';
select username,password,password_versions from dba_users where username = 'TESTUSER';
select name,password,spare4 from sys.user$ where name = 'TESTUSER';
connect testuser/testpwd;
connect testuser/TestPwd;
connect dbauser/dbapassword;
alter system set sec_case_sensitive_logon=FALSE;
select username,password,password_versions from dba_users where username = 'TESTUSER';
select name,password,spare4 from sys.user$ where name = 'TESTUSER';
connect testuser/testpwd;
connect testuser/TestPwd;
connect dbauser/dbapassword;
alter system set sec_case_sensitive_logon=TRUE;
exit
11
75,707 Views
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT

Comments (4)

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Author

Commented:
At the time the article was written 11gR1 was the most recent release.

I have confirmed the scripts and results above for 11gR2
Rich OluConsultant

Commented:
ststuber,

Could I use this to migrate my database from 10g to 11g using schema level import/export?
1. Create user in 11g
2. Note user hash value on 10g
3. export schema on 10g
4. Create user in 11g
5. Restore password with 'identified by value' in 11g
6. import schema into 11g

Would my user be able to log in with same password?

R.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Author

Commented:
yes, if the 10g hash is preserved then the 10g password should still work in 11g, regardless of the 11g case sensitivity setting.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Author

Commented:
I keep an updated version of this article on my blog

https://seanstuber.com/how-oracle-stores-passwords/

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.