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.