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:
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.