Pointing a private database link at another database without knowing the password...sorta...

Pointing a private database link at another database without knowing the password...

Prerequisites:

• SOURCEDBA source database which has a schema that owns a private database link
• TARGETDB1, the original target database for the link
• TARGETDB2, a clone of TARGET1 that was renamed to "TARGETDB2"

Why?

I have an application that I support that uses a pair of databases, one holds detail info and one holds summary info. Various apps and processes log into them but they are also connected together with 2 private DB links, one in each direction. I am often asked to duplicate databases in dev and test. Once I clone and rename the pair databases I need to make sure the links are configured correctly for the new names... I definitely don't want them pointed at the DBs they were originally configured for as that could wreak havoc when the app servers were turned on for this cloned set of databases.

Unfortunately ALTER DATABASE LINK doesn't allow you to just change the destination. Probably good from a security perspective but I can dream...

For this application the passwords are determined by another group. Of course I have SYS and SYSTEM but everything else is up to their product support group. From what I understand they are good about changing their passwords, they generate strong passwords and change them quarterly so I am glad they are on top of things.

But it complicates my cloning procedure. I can't easily log in as their schema owner user in order to update the links.

Here is a way for me to update links for this clond set of DBs without knowing any of the passwords involved.

Get the original link DDL from the SOURCEDB

Run this query to get link definitions:

SELECT OWNER, DB_LINK, DBMS_METADATA.GET_DDL('DB_LINK',DB_LINK,OWNER) as DDL 
FROM DBA_DB_LINKS;

I now have DDL for the link I am interested in... it includes an encrypted version of the password needed to log into TARGET_USER:

CREATE DATABASE LINK MYLINK
 CONNECT TO "TARGET_USER" IDENTIFIED BY VALUES '0526043491995E0A2D4CD80665CF98F671D9F7642EB4B6AA69'
 USING 'TARGETDB1';

Get access to the link owner schema
Use proxy user authentication to allow access to the owner of the link on SOURCEDB for "ImaDBA" user. This is required to drop and receate a private DB link.

connect system@sourcedb

alter user sourceuser grant connect through ImaDBA;
-- Grant permissions if required
grant create database link to sourceuser;

connect ImaDBA[sourceuser]@sourcedb

Recreate the link using the new source
Using the DDL from above, replace TARGETDB1 with TARGETDB2, the new target.

drop database link MYLINK;
CREATE DATABASE LINK MYLINK
 CONNECT TO "TARGET_USER" IDENTIFIED BY VALUES '0526043491995E0A2D4CD80665CF98F671D9F7642EB4B6AA69'
 USING 'TARGETDB2';

-- Test the link
select count(*) from dual@MYLINK;

-- Clean up and revoke permissions if required
connect system@sourcedb
revoke create database link from sourceuser;

Now you have pointed the DB link from TARGETDB1 to TARGETDB2.