Search My Oracle Blog

Custom Search

14 February, 2007

Creating Database Links

Normallly, the DBA can create objects (Tables/Views etc) in other schemas without knowing the passwords for those other schemas. However, he cannot do so for Database Links. A "CREATE DATABASE LINK SCOTT.DBLINK AS ..." command actually still creates a DBLink with the name "SCOTT.DBLINK" in the DBA's Schema instead of in SCOTT's schema.

What I do is is to use EXECUTE IMMEDIATE as in this code :

grant create database link to scott;

create or replace procedure scott.tmpcrtdblink



execute immediate 'create database link remote_db_app connect to remote_shadow_user identified by remote_password using ''tns_connect_string'' ' ;



execute scott.tmpcrtdblink;

drop procedure scott.tmpcrtdblink;

revoke create database link from scott;

select db_link, username, host from dba_db_links where owner = 'SCOTT';

The remote_shadow_user is NOT the owner of the base tables in the remote_db but another "shadow" account with SELECT only privileges on the base tables.
Similarly, if other users (eg TOM) locally need to access the remote database , they have their own dblinks --- particularly so when SCOTT and TOM are accessing different tables and/or with different privileges in remote_db (thus, using different "shadow" accounts in the remote_db as well).
Since I really don't have database accounts for end-users but only accounts for applications / application schemas, dblinks are required only between applications and so it becomes easy to come up with meaningful names for the "shadow" account {use a name which representswhich application in which database will use this account} and the dblink name {use a name which represents which database and whichapplication/schema/shadow the dblink connects to}.

No comments:

Aggregated by

Aggregated by
This blog is being aggregated by