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.

Comments

The password is always annoying while you are working with the database. As you are linking without knowing the password. This is a great post for me. I am searching for this code even on the stack but not find anything related to this one. But I do not understand step link over schema. Please give me some clarification on this step.

On the source DB if you are DBA then you have access to query information about the link (including the password hash). On the target DB you need Proxy user access to the target schema to create the new Private DB Link.

Add new comment