• Certifications

  • Moderator at

  • Member at

  • Archives

  • June 2011
    M T W T F S S
    « May   Jul »
     12345
    6789101112
    13141516171819
    20212223242526
    27282930  
  • Recent Posts

  • Blog Stats

    • 62,627 hits
  • Enter your email address to follow this blog and receive notifications of new posts by email.

Password Hash In Oracle 11g

Posted by sriram on June 7, 2011


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

Advertisements

102 Responses to “Password Hash In Oracle 11g”

  1. After looking over a handful of the blog posts on your website, I seriously appreciate your technique of writing a blog. I book marked it to my bookmark website list and will be checking back soon. Please check out my website too and tell me what you think.

  2. Great Post! I look forward to seeing more from you in the future. There are some very great ideas above.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Puddintopia

The Ramblings and Ravings Of Author Jason A. Rust

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

flashdba

Oracle databases, storage and the high-performance world of flash memory

jarneil

The thoughts of Jason Arneil

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

Martins Blog

Trying to explain complex things in simple terms

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Talip Hakan Ozturk's ORACLE BLOG

The secret of success is at your fingertips!...

Martin Widlake's Yet Another Oracle Blog

Oracle performance, Oracle statistics and VLDBs

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Oracle Scratchpad

Just another Oracle weblog

Oracle DBA Blog

Procedures, learnings and fixes encountered by a Junior DBA

Oracle ACE Director Kai Yu's Oracle Blog

Share Oracle Tips and Tricks and Related News

Online Identity & Access Management

One Stop Shop for Identity & Access Management

Uwe Hesse

about Database Technology

Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

Support

WordPress.com Support

Kamran Agayev's Oracle Blog

Oracle Certified Master

%d bloggers like this: