Oracle Syntax for Creating Database Link Owned by Another User

Question

The typical syntax for creating a db link is as follows:

create database link remote_db_link 
connect to remote_user 
identified by remote_password 
using 'remote_db'

But I'd like my DB link owned by another account after it's created. Is there a way to do this?

The following does NOT work:

create database link anotheruser.remote_db_link 
connect to remote_user 
identified by remote_password 
using 'remote_db'

Answer 1

Sathya is correct, in that the CREATE DATABASE LINK syntax does not allow creating a database link in another schema. HOWEVER...

WORKAROUND

It IS possible to create a database link in another user's schema, as long as anotheruser has CREATE DATABASE LINK privilege, and the user you are connected as has CREATE ANY PROCEDURE privilege.

Here's the workaround I use:

    create procedure anotheruser."tmp_doit_200906121431"
    is
    begin
      execute immediate '
        create database link remote_db_link 
        connect to remote_user 
        identified by remote_password 
        using ''remote_db'' ';
    end;
    /
    begin
      anotheruser."tmp_doit_200906121431";
    end;
    /
    drop procedure anotheruser."tmp_doit_200906121431"
    /

Let's unwind that. First, I create a procedure in the anotherusers's schema; this procedure contains the CREATE DATABASE LINK statement that I want to run.

When the procedure is executed, it runs as the owner of the procedure, such that the CREATE DATABASE LINK statement is executed by anotheruser.

The name of the procedure is not important, except that I need to make sure that it doesn't conflict with any existing object name. I use lowercase letters (enclosing the procedure name in double quotes), using "tmp" to mark this object as "temporary", and using the current yyyymmddhh24miss as the part of the procedure name. (I usually run a query of DBA_OBJECTS to check that a matching object_name does not exist.)

For a "one-off" type admin function, this is a viable workaround. I prefer this to the other alternative: saving the anotheruser's password, changing the password, connecting as the user, and resetting anotheruser's password back to the saved.)