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

as

begin

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

end;

/

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 orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016