Oracle DB Links
What is a Database Link?
A database link is a pointer that defines a one-way communication path from an Oracle Database server to another database server
The following link types are supported:
Private database link - belongs to a specific schema of a database. Only the owner of a private database link can use it. Public database link - all users in the database can use it. Global database link - defined in an OID or Oracle Names Server. Anyone on the network can use it.
Syntax:
CREATE DATABASE LINK <<linkname>> CONNECT TO <<targetdb user>> IDENTIFIED BY <<passwd>> USING 'tns_conn_str';
Drop a DBlink:
DROP DATABASE LINK remotedb;
Close Database Link
ALTER SESSION CLOSE DATABASE LINK <link_name>;
Drop a database link when you are not the owner
Method1:
We can use the proxy user feature so that we can connect to the database link owner without knowing or changing its password
SQL> conn test/test Connected. SQL> create database link test1 connect to dbaoper identified by <<passwd> using 'prod'; Database link created. SQL> conn dbaoper/<<passwd>> Connected. SQL> ALTER USER test GRANT CONNECT THROUGH dbaoper; User altered. SQL> CONNECT dbaoper[test]/<<passwd>> Connected. SQL> sho user USER is "TEST" SQL> DROP DATABASE LINK test1; Database link dropped.
Method2:
The method below shows how to achieve the same without hijacking the password and hence – reduced risk to user upset.
SQL> show user USER is "SYS" SQL> create user test identified by test; User created. SQL> grant create database link,create session to test; Grant succeeded. SQL> CREATE or REPLACE PROCEDURE test.p AS BEGIN EXECUTE IMMEDIATE 'CREATE DATABASE LINK testlink ' ||'CONNECT TO dbaoper IDENTIFIED BY <<passwd>> ' ||'USING ''prod'''; END; / Procedure created. SQL> exec test.p PL/SQL procedure successfully completed. SQL> col owner for a10 col db_link for a15 col username for a10 select owner,db_link,username from dba_db_links where owner='TEST'; OWNER DB_LINK USERNAME ---------- -------------- ---------- TEST TESTLINK.WORLD DBAOPER
External references
Oracle allows three kinds of external references to DB-links, which are resolved as follows:
Named Link: The username specified in the link is used. You specify the username and password used to connect to the remote database (this database link is sometimes called fixed user database link).
CREATE DATABASE LINK test_link CONNECT TO scott IDENTIFIED BY tiger USING 'prod';
Anonymous Link: The session username is used. If you omit the CONNECT TO clause, the database link uses the username and password of each user who is connected to the database (this database link is sometimes called connected user database link).
CREATE DATABASE LINK test_link USING 'prod';
Privileged Link: The username of the invoker is used. The current user must be a ‘global’ user with a valid account on the remote database for the link to succeed. If the database link is used directly, that is, not from within a stored object, then the current user is the same as the connected user.
When executing a stored object (such as a procedure, view, or trigger) that initiates a database link, CURRENT_USER is the username that owns the stored object, and not the username that called the object. For example, if the database link appears inside procedure SCOTT.show_emp (created by SCOTT), and user JONES calls procedure SCOTT.show_emp, the current user is SCOTT.
However, if the stored object is an invoker-rights function, procedure, or package (new in Oracle8i), the invoker’s authorization ID is used to connect as a remote user. For example, if the privileged database link appears inside procedure SCOTT.show_emp (an invoker-rights procedure created by SCOTT), and user JONES calls procedure SCOTT.show_emp, then CURRENT_USER is JONES and the procedure executes with JONES’s privileges.
CREATE DATABASE LINK test_link CONNECT TO CURRENT_USER USING 'prod';
Public database links
We can also create a database link as PUBLIC. Be very careful with PUBLIC database links, they may open a door for everybody to a remote database. We suggest, NOT TO USE public database links without Authentication.
Shared PUBLIC DB-Link with Authentication
A shared PUPLIC DB-Link with Authentication uses a single network connection to create a PUBLIC database link that can be shared between multiple users with more security. This DB-Link is available only with the multi-threaded server configuration.
Example
SQL> CREATE SHARED PUBLIC DATABASE LINK test_link CONNECT TO scott IDENTIFIED BY tiger AUTHENTICATED BY dbaoper IDENTIFIED BY <> USING 'prod';
The AUTHENTICATED clause specifies the username and password (dbaoper/<<passwd>>) on the target instance (prod). This clause authenticates the user to the remote server and is required for security. The specified username and password (dbaoper/<<passwd>>) must be a valid username and password on the remote instance (SOL1). The username and password are used only for authentication. No other operations are performed on behalf of this user.
How to create multiple db links with global_names set as ‘TRUE”
Setting GLOBAL_NAMES=TRUE => To enforce that a dblink has same name as the GLOBAL_NAME of the DB it connects to.
Lets test this with an example,
DB_NAME | DB_DOMAIN | GLOBAL_NAME | GLOBAL_NAMES |
bharat_test | world | bharat_test.world | FALSE |
prod | —– | prod | TRUE |
As seen in the table, we have set GLOBAL_NAMES=TRUE in the prod database, so any db link from prod to other database should be created as global_name of that database.
Here db link in “prod” should be created as “bharat_test.world”
To check the database global name
SQL> select global_name from global_name; GLOBAL_NAME --------------------------- bharat_test.world
To rename the database global name,
Alter database rename GLOBAL_NAME to <<new global_name>>;
Here we will create 2 db links in ‘prod’ database and will test them,
SQL> create database link will_fail connect to dbaoper identified by password using 'bharat_test'; Database link created. SQL> create database link bharat_test.world connect to dbaoper identified by password using 'bharat_test'; Database link created.
Now test these links,
SQL> select sysdate from dual@will_fail;
select sysdate from dual@will_fail
*
ERROR at line 1:
ORA-02085: database link WILL_FAIL.WORLD connects to NAGITEST.WORLD
SQL> select sysdate from dual@bharat_test.world
SYSDATE
---------
02-APR-13
<< THE RESULT IS SUCCESS>>
Here we could see that setting GLOBAL_NAMES=TRUE limits the db link name to that of the target db global name. So the big question is
“How Do We Create Multiple DB Links In This Scenario?”
The answer would be “leave global_names on but use @ in the dblink name. Put the global name first in the dblink name and then extra stuff after an @”
Example:
SQL> create database link bharat_test.world@new_db_link connect to dbaoper identified by password using 'bharat_test'; Database link created. SQL> select sysdate from dual@bharat_test.world@new_db_link; SYSDATE --------- 02-APR-13