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.

About these ads

10 thoughts on “How to Create a Database Link in Another User’s Schema

  1. Hi Neil,

    your method is much better because there is no risk to upset users which is my major concern during hijacking user’s password.

    My solution is not very practical but it worked for me several times so I stopped searching for another solution mostly because I don’t create db links in another user’s schema very often.

    But anyway, thanks for sharing this useful information.

    Regards,
    Marko

  2. Pingback: Blogroll Report 27/11/2009-04/12/2009 « Coskan’s Approach to Oracle

  3. Hi,

    I am trying the same methodological steps, but it still results in an error while ‘excuting’ the procedure.
    Moreover when I create a procedure, it reutrns with the confirmation of the procedure being compiled & not created.
    Procedure created in the ‘User A’ with user ‘SYS’.

    Error starting at line 47 in command:
    execute xxx.cre_db_lnk
    Error report:
    ORA-00933: SQL command not properly ended
    ORA-06512: at “xxx”, line 3
    ORA-06512: at line 1
    00933. 00000 – “SQL command not properly ended”

    • Hi Pulin,
      Would you be able to post the SQL*Plus output showing the creation of the procedure with errors and the output of “show errors” after trying to create the procedure?
      Thanks

  4. Pingback: Create database-link in another user’s schema | Logbuffer-Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s