Create database-link in another user’s schema

Ever wondered, why you can’t create db-links in the schema of another user – not even as DBA?

When creating objects like tables in another schema, we prefix the tablename with the name of the target-schema (e.g. “create table appuser.first_table …”). But in db-link-names we are allowed to use dots as part of the name – and hence can’t prefix like usual.

Here are three solutions to this problem:

Marko Sutic uses the “password hijacking approach”,

Neil Johnson creates a db-link-create-procedure in the target-schema

and on “Oracle DBA Tips” we find a sophisticated solution using DBMS_SYS_SQL without any need to create foreign objects or hijack passwords.

The latter I hadn’t tested yet, but it looks very interesting.

My favorite is the db-link-create-procedure. Here is a generic procedure for that matter:

CREATE PROCEDURE targetschema.create_db_link (linkname in varchar2, remote_user in varchar2, remote_pwd in varchar2, conn_string in varchar2) is
  V_SQL varchar2(1000);
BEGIN
    V_SQL := 'CREATE DATABASE LINK ' || linkname || ' CONNECT TO ' || remote_user || ' IDENTIFIED BY ' || remote_pwd || ' USING ''' || conn_string || '''';
    EXECUTE IMMEDIATE (V_SQL);
END create_db_link;

grant create database link to targetschema;

exec targetschema.create_db_link('linkname','user','pwd','connectstring');

And maybe a cleanup afterwards:

revoke create database link from targetschema;

drop procedure targetschema.create_db_link;

Dropping database-links in another schema is analogously:

CREATE PROCEDURE targetschema.drop_db_link (linkname in varchar2) is
  V_SQL varchar2(100);
BEGIN
    V_SQL := 'DROP DATABASE LINK ' || linkname;
    EXECUTE IMMEDIATE (V_SQL);
END drop_db_link;
select * from dba_db_links;

exec targetschema.drop_db_link('linkname');
Advertisements

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

%d bloggers like this: