Sriram Sanka – My Experiences with Databases & More

Let `s discuss about The Changes Of  DBA_USERS Especially In PASSWORD Column In Oracle 11g and 10g.

DBA_USERS   Gives Us Information about all users of the database.

And it contains password hash value In its PASSWORD Column

ind>  col TABLE_NAME format a10
ind> col COLUMN_NAME format a10
ind> col COMMENTS format a25
ind>  select TABLE_NAME,COLUMN_NAME, COMMENTS
  2   from dict_columns
  3   where TABLE_NAME='DBA_USERS' and COLUMN_NAME='PASSWORD';

TABLE_NAME COLUMN_NAM COMMENTS
---------- ---------- -------------------------
DBA_USERS  PASSWORD   Encrypted password

1 row selected.

Yes..It contains the encrypted value Based On Concatenation of Username and Password
This Is How/why two Users with same password can have different Encrypted hash values.

ind> create user satya identified by satya;

User created.

ind>  create user kalyani identified by satya;

User created.

ind> select username,password
  2  from dba_users
  3  where USERNAME in ('KALYANI','SATYA');

USERNAME                       PASSWORD
------------------------------ ------------------------------
KALYANI                        E81F7CB996A56BA9
SATYA                          218ED5615AAE5F6B

2 rows selected.

ind> drop user satya;

User dropped.

ind> drop user satyab;
drop user satyab
          *
ERROR at line 1:
ORA-01918: user 'SATYAB' does not exist

ind> create user satya identified by backpain;

User created.

ind> create user satyab identified by ackpain;

User created.

ind> select username,password
  2  from dba_users
  3  where username like 'SATYA%';

USERNAME                       PASSWORD
------------------------------ ------------------------------
SATYA                          458070F68E74206E
SATYAB                         458070F68E74206E

2 rows selected.

Here You can Observe that User SATYA and SATYAB with same hash value,
as the Concatenation of USERNAME and PASSWORD are Same.

But In Oracle 11g Oracle Wont store any password hash values in DBA_USERS.PASSWORD column.As Per the Document

http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/statviews_5081.htm#REFRN23302

Password Column  is deprecated in favor of   the AUTHENTICATION_TYPE column

But You can get the password from “User$“.

SQL> create User a identified by "a";

User created.

SQL> create User b identified by "a";

User created.

SQL> select dbms_metadata.get_ddl('USER','A') from dual;

DBMS_METADATA.GET_DDL('USER','A')
--------------------------------------------------------------------------------

   CREATE USER "A" IDENTIFIED BY VALUES 'S:298EDEE1721E71B950D55CCB9ABA7EE5C596E
A6B0CCFF098E88889B98BD5;AFCC9478DFBF9029'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"

SQL> select dbms_metadata.get_ddl('USER','B') from dual;

DBMS_METADATA.GET_DDL('USER','B')
--------------------------------------------------------------------------------

   CREATE USER "B" IDENTIFIED BY VALUES 'S:25E85C1466288EE377681D131DF1920B33448
CD4108F8DDFC580A3315A39;9017AAA5BF2D9732'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"

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

NAME                           PASSWORD
------------------------------ ------------------------------
SPARE4
--------------------------------------------------------------------------------
A                              AFCC9478DFBF9029
S:298EDEE1721E71B950D55CCB9ABA7EE5C596EA6B0CCFF098E88889B98BD5

SQL> select password,username from dba_users
  2  where length(username)=1;

PASSWORD                       USERNAME
------------------------------ ------------------------------
                               A
                               B

SQL>

You can Find My Post here at Orafaq.
http://www.orafaq.com/forum/mv/msg/171754/510127/136607/#msg_510127
For more reference
http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/statviews_5081.htm#REFRN23302
Hope You enjoyed It 🙂
Sriram