Understanding Oracle password hashing

This is an explanation of how the password hashing works on Oracle databases across 9i and 10g. For the explanation two users are chosen on a 10g and in a 9i database, namely user1 and user2 and their encrypted passwords are displayed. The test is first done on a 10g database

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio
NLSRTL Version 10.2.0.3.0 - Production


SQL> select username,password from dba_users where username like 'USER%';

USERNAME PASSWORD
------------------------------ ------------------------------
USER1 BBE7786A584F9103
USER2 1718E5DBB8F89784


Now both the user1 and user2 have their own usernames as their passwords as shown below.

SQL> conn user1/user1
Connected.
SQL>
SQL> conn user2/user2
Connected.

The password of user2 is changed to that of 'user1', just to see how the password encryption mechanism works.

SQL> conn /as sysdba
Connected.
SQL>
SQL> alter user user2 identified by user1;

User altered.

User2's old password does not work anymore, but the new password is in effect now as shown below.


SQL> conn user2/user2
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL>
SQL>
SQL> conn user2/user1
Connected.
SQL>

When querying the passwords of user1 and user2 this time, shows up different values for both the users inspite of the fact that both the users have the same password assigned to them.


SQL> conn /as sysdba
Connected.
SQL>
SQL> select username,password from dba_users where username like 'USER%';


USERNAME PASSWORD
------------------------------ ------------------------------
USER1 BBE7786A584F9103
USER2 83566E16913442F3


This shows that Oracle does not only encrypts just the password but also takes into account some other factor with the password for encryption. This other factor could be assumed as the username as such.

The above was tested on a 10g database,the same which one tested on a 9i database shows no difference in the password encryption mechanism which is evident from the output shown below.


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production



SQL> create user user1 identified by user1;

User created.

SQL> grant connect,resource to user1;

Grant succeeded.

SQL> create user user2 identified by user1;

User created.

SQL> grant connect,resource to user2;

Grant succeeded.

SQL> select username,password from dba_users where username like '%USER%';

USERNAME PASSWORD
------------------------------ ------------------------------
USER1 BBE7786A584F9103
USER2 83566E16913442F3


SQL> conn user1/user1
Connected.
SQL>
SQL> conn user2/user2
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL>
SQL> conn user2/user1
Connected.
SQL>
SQL> conn /as sysdba
Connected.
SQL>
SQL> alter user user2 identified by user2;

User altered.

SQL> select username,password from dba_users where username like '%USER%';

USERNAME PASSWORD
------------------------------ ------------------------------
USER1 BBE7786A584F9103
USER2 1718E5DBB8F89784 <--- The password here is
'user2' which shows
the same encrypted
text in the first query
output at the top of this page.


whether its a CREATE USER/ALTER USER the password encryption is the same irrespective of the type of DDL statement.

Oracle 10gR2 Database Parameters - Reference

This is in continuation of my earlier post on 9iR2 Database Parameters Reference, here is a link to the master index of parameters and data dictionary description in 10gR2 and a link to the description of v$ views in 10gR2.