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}.
4 comments:
Hi Hemant, thank you for this.
I have 2 questions.
1. When I try to create DB link after granting create DB link to the user as sys, by setting the current session as user, it fails with insufficient privileges. Can we avoid if we use the above method ?
2. How do I create DB link if I don't know the remote user password?
Thank you
1. The account that is to own the database link must issue the CREATE DATABASE LINK command.
You cannot use SYS (or any DBA account) with "alter session set CURRENT_SCHEMA=SCOTT" to do this.
So, either explicitly login as SCOTT or create a procedure in the SCOTT schema (as a DBA account that has a CREATE ANY PROCEDURE privilege) that invokes the CREATE DATABASE LINK command. By default the Procedure gets created with AUTHID DEFINER so when it is executed by the DBA, it is executed as if being executed by SCOTT -- thus inheriting the CREATE DATABASE LINK privilege that has been granted to SCOTT.
(There are CREATE ANY PROCEDURE and EXECUTE ANY PROCEDURE privileges that may be granted to DBAs so you can create and execute the procedure in and from the SCOTT schema that I demonstrate. But there is no "CREATE ANY DATABASE LINK" privilege)
There is a CREATE PUBLIC DATABASE LINK privilege -- but then the Database Link can be invoked by all users.
2. The CREATE DATABASE LINK command can have a CONNECT TO CURRENT_USER clause (not requiring a Password) --- but that would require a Global User available in both databases through a Directory Service (LDAP). This is similar to the method that can be used in SQL Server with Windows accounts in Windows AD.
Also note that a PUBLIC DATABASE LINK also requires either (a) a username identified by password or (b) a Global User as CURRENT_USER
Look up the CREATE DATABASE LINK syntax in the SQL Language Reference for your particular Oracle Version.
Post a Comment