Creating a database link on behalf of another user

I had to create a database link on behalf of another user whose password I did not know. This was performed as the “SYS” user in an Oracle 11.2.0.4.0 environment.

The requirements were as follows:
a. User test_user would connect to test_db
b. Execute an SQL over a db link as uat_user on uat_db
c. Database link should be named test_2_uat
d. uat_users password on uat_db was known. Assume it is mypassword
e. the tnsnames file has an entry for the uat_db named uat_db and is verified by
tnsping uat_db

Steps:
01. Log on to test_db as SYS user

02. Create the below procedure:

CREATE or replace PROCEDURE test_user.DEAN_DB_LINK AS
BEGIN
  EXECUTE IMMEDIATE 'CREATE DATABASE LINK test_2_uat '
       ||'CONNECT TO UAT_USER IDENTIFIED BY "mypassword" '
       ||'USING ''uat_db''';
END DEAN_DB_LINK;
/

03. After the above is created grant privilege to test_user to create a link

GRANT CREATE DATABASE LINK TO TEST_USER;

04. Execute the above procedure (as SYS)

EXECUTE TEST_USER.DEAN_DB_LINK;

05. Check that the link was created

SELECT * FROM DBA_DB_LINKS;

06. Revoke the privilege from test_user

REVOKE CREATE DATABASE LINK  FROM TEST_USER;

Update – March 21, 2019

A friend reminded me to cleanup the procedure:

drop PROCEDURE test_user.DEAN_DB_LINK;

This is based on the information at:
How to Create a Database Link in Another User’s Schema

Author: Dean Capps

Database consultant at Amazon Web Services. View all posts by Dean Capps