Oracle Database Discussions

user1356432 wrote:
Hi, We are in oracle 11.2.0.4.0. We need to recreate one of our database links that connect to a remote database as the remote database planned to be migrated. We don't have the password for the user used in the creation of the mentioned database link. Understand that from 11.2.0.4 onwards we can not create the database link by using the old password. In fact the password storing mechanism has changed. Any other way to achieve this apart from resetting the password in remote database? Thanks. Regards; Gopal

If you could "retrieve" the password, then it would be totally insecure & defeat the purpose of having a password in the first place.

New password needs to manually established.

user1356432, in my opinion your easiest approach would be 1) ask the remote DBA if they have a record of the password and if so to provide it, 2) have the password changed and use the new password in your DDL.

- -

-- Mark D Powell --

Mark D Powell wrote:
user1356432, in my opinion your easiest approach would be 1) ask the remote DBA if they have a record of the password and if so to provide it, 2) have the password changed and use the new password in your DDL.
- -
-- Mark D Powell --

AFAIK, Oracle does not allow DDL across DBLink.

John Thorton wrote:
Mark D Powell wrote:
user1356432, in my opinion your easiest approach would be 1) ask the remote DBA if they have a record of the password and if so to provide it, 2) have the password changed and use the new password in your DDL.
- -
-- Mark D Powell --
AFAIK, Oracle does not allow DDL across DBLink.

I don't think he was talking about executing ddl across the link, but rather the ddl to create the link.

Have a look at

select * from SYS.LINK$;

JohnWatson2 wrote:
It is possible to decrypt the password stored in sys.link$.passwordx if the database link was created in releases <= 11.2.0.2, because the key is stored in the passwordx column concatenated to the encrypted password. The encryption algorithm is known. This article gives the detail,
Core dump of a DBA's mind: Decrypting Oracle database DB link password (Versions <= 11.2.0.2) 
I do not know the author, but it is correct. Even though your database is 11.2.0.4, if the link was created back when it was 11.2.0.2 the technique will work. In later releases it doesn't, I think because of salting. 

Still possible with 12c - starting from 18c (New Feature) you can encrypt it using a wallet.

https://mahmoudhatem.wordpress.com/2016/12/08/reverse-engineering-db-link-password-decryption-in-plsql/

We don't have the password for the user used in the creation of the mentioned database link. 

You have a HUGE security hole is you can't change passwords when you need to that are used in your key systems.

I suggest you plug that security hole - doing so will also fix the problem you report.

You should NEVER have an outage because you can't change passwords when you need/want to.

John, the DDL is my reply is the DDL to re-create the database link so it is local.

- -

HTH -- Mark D Powell --

The DBA in remote site has provided us the hash of the password used in the creation of database link. Is it possible to recreate the database link by using this hash though 11.2.0.4 does not allow using value clause in database link creation. Regards; Gopal

Gopal, the following code worked on a 11.2.0.4 system but not on a 12.1.0.2 system to decrypt the password in a database link.  you can try it.

- -

set echo off
--
-- Show database link passwords on 10g n 11g, not 12.
--
-- 2012-11-22  Paul M Wright   blog
-- http://www.oracleforensics.com/wordpress/index.php/2012/11/22/database-link-security/
-- 2014-05-07  Mark D Powell   Mod q to get username, reformat q, reformat output
--
set pagesize 999
set verify   off
column name     format a30
column password format a30
column userid   format a20
column username format a20

select
  u.name                                                                         as username
,l.name
,l.userid
,utl_raw.cast_to_varchar2( dbms_crypto.decrypt(
                   (substr(l.passwordx,19)), 4353, (substr(l.passwordx,3,16)) ) ) as password
from
  sys.link$ l
,sys.user$ u
where
  l.name  = upper('&link_name')  and
  u.user# = l.owner#
/

- -

HTH -- Mark D Powell --

Use a new password.

user1356432 wrote:
The DBA in remote site has provided us the hash of the password used in the creation of database link. Is it possible to recreate the database link by using this hash though 11.2.0.4 does not allow using value clause in database link creation. Regards; Gopal

No, you can't.

Do you have a full metadata database export?  Does that not have create link in it?

It worked. Thank you very much!