jump to navigation

How to retrieve the hash value for a password in Oracle 11g 20 October 2010

Posted by David Alejo Marcos in Oracle 11.2, SQL - PL/SQL.
Tags:
trackback

As we all know, Oracle has decided to do not show the hash value for the password on the dba_users view.

The problem:

Saving your passwords to a file as below does not work anymore:

SELECT 'ALTER USER ' || username || ' identified by values '''|| password || ''';' FROM dba_users;

This is necessary, in our case, for releases, DEV/QA refreshes, etc…

The solution:

I first wrote the following query:

MYUSER@mydb> SELECT substr (trim(DBMS_METADATA.get_ddl ('USER','MYUSER')),
2 REGEXP_INSTR(trim(DBMS_METADATA.get_ddl ('USER','MYUSER')),'''',1,1,0,'m'), 20
3 ) PASSWD
4 FROM DUAL;
PASSWD
--------------------------------------------------------------------------------
'2YYYYY4DXXXXXX'

Note.- The hash value has been modified for security reasons.

I was working under the assumption Oracle will not change the algorithm any time soon (currently the length is 16 characters); but I also found “dangerous” to make this assumption.

So my next query is:

select REGEXP_SUBSTR(DBMS_METADATA.get_ddl ('USER','MYUSER'), '''[^'']+''') PASSWD from dual;

which I find more elegant.

MYUSER@MYDB> select REGEXP_SUBSTR(DBMS_METADATA.get_ddl ('USER','MYUSER'), '''[^'']+''') PASSWD from dual;
PASSWD
--------------------------------------------------------------------------------
'XXXXX4YYYYYZZZZZZ'

The code we need to use from now on is:

set long 150
set linesize 150
set longchunksize 150
select 'alter user '||username||' identified by values '||REGEXP_SUBSTR(DBMS_METADATA.get_ddl ('USER',USERNAME), '''[^'']+''')||';' from dba_users

As always, comments are welcome.

About these ads

Comments»

1. andres - 3 November 2011

Thank you very much!

2. Amol - 26 December 2011

It is useful information. Thanks you.

3. Derya - 7 March 2012

Hi David,

It returns below string:
It is a little bit strange and very long and does not work.

alter user GALATA identified by values ‘S:68E199E9ADE1835AA0BC4F1EF6323DD0F2CA2E0595702DC5C2C1AA29438B;EEA1A29F45B12520′;

Do you have any suggestions?

Thank you.

David Alejo Marcos - 9 March 2012

Hi,

what version are you working with?

I did the following test in 11.2.0.1 and it was fine:

ORCLDBA@DAVIDLAB> create user mytest identified by f1rstp3ssword;
 
User created.
 
Elapsed: 00:00:00.10
ORCLDBA@DAVIDLAB> grant connect to mytest;
 
Grant succeeded.
 
Elapsed: 00:00:00.03
ORCLDBA@DAVIDLAB> show user
USER is "ORCLDBA"
ORCLDBA@DAVIDLAB> conn mytest/f1rstp3ssword@DAVIDLAB
Connected.
 
MYTEST@DAVIDLAB> show user;
USER is "MYTEST"
MYTEST@DAVIDLAB> conn ORCLDBA@DAVIDLAB
Enter password:
Connected.
 
ORCLDBA@DAVIDLAB> show user
USER is "ORCLDBA"
ORCLDBA@DAVIDLAB> select 'alter user '||username||' identified by values '||REGEXP_SUBSTR(DBMS_METADATA.get_ddl ('USER',USERNAME), '''[^'']+''')||';'
  2  from dba_users
  3  where username = 'MYTEST';
 
'ALTERUSER'||USERNAME||'IDENTIFIEDBYVALUES'||REGEXP_SUBSTR(DBMS_METADATA.GET_DDL('USER',USERNAME),'''[^'']+''')||';'
------------------------------------------------------------------------------------------------------------------------
 
alter user MYTEST identified by values 'S:A4CE024B548C4C11C886A735F0D00314C1050C7A9C4FC492585BA2BC8520;D9402F621C938AC1';
 
Elapsed: 00:00:00.39
ORCLDBA@DAVIDLAB> alter user mytest identified by someth1ngdiffer3nt;
 
User altered.
 
Elapsed: 00:00:00.06
ORCLDBA@DAVIDLAB> conn mytest/someth1ngdiffer3nt@DAVIDLAB
Connected.
 
MYTEST@DAVIDLAB> show user
USER is "MYTEST"
MYTEST@DAVIDLAB> conn ORCLDBA@DAVIDLAB
Enter password:
Connected.
 
ORCLDBA@DAVIDLAB> show user
USER is "ORCLDBA"
ORCLDBA@DAVIDLAB> alter user MYTEST identified by values 'S:A4CE024B548C4C11C886A735F0D00314C1050C7A9C4FC492585BA2BC8520;D9402F621C938AC1';
 
User altered.
 
Elapsed: 00:00:00.09
ORCLDBA@DAVIDLAB> conn mytest/f1rstp3ssword@DAVIDLAB
Connected.
 
MYTEST@DAVIDLAB> show user
USER is "MYTEST"
MYTEST@DAVIDLAB>
deryaoktay - 9 March 2012

Hi David,

It is fallacy of mine that, I had used the user password in another database’s link. Thus, link user password hash value is not same as database user’s password hash value.

My target and soruce dbs are both 11.2.0.2.

Thanks.

4. ratzebine - 19 March 2012

thank you VERY much for this trick
BTW: I had the same problem as Derya but I managed to get the whole info with

set long 150
set linesize 150

sqlplus trims those long selects (with SQL*Developer I got it but working from commandline as oracle with sqlplus is needed sometimes as well)
150 is a bit too much but 120 was too short for longer usernames and the whole “alter …” statement, not just the REGEXP-part

David Alejo Marcos - 23 March 2012

Hello,

Thank you for your update. I will change the settings on the post to reflect your input.

Regards,

David Alejo-Marcos.

5. Geoff - 24 May 2012

super helpful. except, I could not get the output beyond 80 characters despite the “set linesize 150 long 150″. Solution was to add “set longchunksize 150″ … it defaults to 80, which is not enough. (oracle 11.2.0.1.0)


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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: