How to Create a Database Link in Another User’s Schema

I saw a post recently describing how to create a database link in another user’s schema. I wanted to post an alternative method so created this post in order to refer to in a comment on the original post. The original post demonstrates how to hijack a user account’s password temporarily, which is a useful piece of information to have, and can be viewed here:

Marko Sutic’s Oracle Blog – Create a Database Link in another user’s schema

The method below shows how to achieve the same without hijacking the password and hence – reduced risk to user upset. However this method does involve rather powerful privileges and the creation of some objects.

Create a test user who is not allowed to create database links.

CREATE USER otheruser IDENTIFIED BY unknown_pwd;
GRANT create session TO otheruser;

Now create our power user with CREATE & DELETE ANY PROCEDURE and CREATE DATABASE LINK WITH ADMIN privileges

CREATE USER poweruser IDENTIFIED BY known_pwd;
GRANT create session TO poweruser;
GRANT create database link TO poweruser WITH ADMIN OPTION;
GRANT create any procedure TO poweruser;
GRANT drop any procedure TO poweruser;
GRANT execute any procedure TO poweruser;

Now we can use this powerful account to create a procedure in the unprivileged user’s schema which then gives us the ability to run DDL as that user (thanks to definer rights).

CONN poweruser/known_pwd

CREATE PROCEDURE otheruser.cre_db_lnk AS
BEGIN
	EXECUTE IMMEDIATE 'CREATE DATABASE LINK newlink '
			||'CONNECT TO remoteuser IDENTIFIED BY pw '
			||'USING ''remotetns''';
END cre_db_lnk;
/

Procedure created.

We can then briefly grant CREATE DATABASE LINK privileges, run the procedure and revoke the privileges again.

GRANT create database link TO otheruser;

exec otheruser.cre_db_lnk

REVOKE create database link FROM otheruser;

DROP PROCEDURE otheruser.cre_db_lnk;

And hey presto, we’ve created a database link in the other schema.

SELECT owner,db_link FROM dba_db_links;

OWNER           DB_LINK
--------------- ---------------
OTHERUSER       NEWLINK

This example is a bit scruffy but you could wrap the power user’s tasks and privileges up in another procedure and lock the account. I’ve used a method similar to this in the past as part of a scheduled refresh of test databases. After the clone a standard set of procedures was called to recreate database links in various schemas.