Oracle database links without password

What is a database link?

A database link allows us to access objects on a database DATB2 from a database DATB1 without copying the contents. They are a kind of channel and are often used in the definition of snapshots. The snapshots are refreshed on after business hours and data is accessed locally rather than remotely for remaining time, with better performance.

The syntax for creating a database link is:


CREATE DATABASE LINK LDB.US.ORACLE.COM CONNECT TO scott IDENTIFIED BY tiger USING 'DATB2';

Assuming there is a table t_a1 belonging to scott in the database DATB2, it is now possible to select from this table with the command:
SELECT * FROM t_a1@LDB;

The security risk is that the password used for the database link is stored in clear text in the view user_db_links and on the table SYS. The risk is bigger if the parameter O7_DICTIONARY_ACCESSIBILITY is set to true.


SQL> descr user_db_links
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DB_LINK                                   NOT NULL VARCHAR2(128)
 USERNAME                                           VARCHAR2(30)
 PASSWORD                                           VARCHAR2(30)
 HOST                                               VARCHAR2(2000)
 CREATED                                   NOT NULL DATE

SQL> select * from user_db_links;
DB_LINK            USERNAME   PASSWORD     HOST       CREATED
-----------------  ---------  ------------ ---------- ---------
LDB.US.ORACLE.COM  SCOTT      TIGER        DATB2      25-FEB-04

SQL> descr sys.link$
 Name                                           Null?     Type
 --------------------------------------------- ---------- ------------------ 
 OWNER#                                         NOT NULL  NUMBER
 NAME                                           NOT NULL  VARCHAR2(128)
 CTIME                                          NOT NULL  DATE
 HOST                                                     VARCHAR2(2000)
 USERID                                                   VARCHAR2(30)
 PASSWORD                                                 VARCHAR2(30)
 FLAG                                                     NUMBER
 AUTHUSR                                                  VARCHAR2(30)
 AUTHPWD                                                  VARCHAR2(30)

SQL> column name format a32
SQL> select NAME, USERID, PASSWORD FROM sys.link$;

NAME                             USERID                         PASSWORD
-------------------------------- ------------------------------ ------------
DBLTEST.WORLD                    TESTUSER                       TES21UY
DBLPROD.WORLD                    PRODUSER                       D89787ABS
LDB.US.ORACLE.COM                SCOTT                          TIGER

It is possible, though, to create a database link without specifying a username and password, in a concept similar to a proxy account.

Oracle database link without password

To clarify the idea, let's consider a database DATB1 with a user user1 needing a database link to the database DATB2; the user on DATB2 be user2 with password pwd2. The tnsmanes entry for the database DATB2 be ALIASDATB2. The database link is needed to select from a few tables in the schema user2 (tables t_a1, t_a2, ..., t_a10).

As we have already seen, the statement to create the database link would be:

CREATE DATABASE LINK DBLDATB2 CONNECT TO user2 IDENTIFIED BY pwd2 USING 'ALIASDATB2';

This way the user1 can execute the statements:
SELECT * FROM t_a1@DBLDATB2;

The solution without password requires that we create a user user1 in the database DATB2 having the same password pwd1 as in the database DATB1.


CREATE USER user1 IDENTIFIED BY pwd1;
GRANT CONNECT TO user1;
GRANT SELECT ON user2.t_a1  TO user1;
GRANT SELECT ON user2.t_a1  TO user1;
... ... ...
GRANT SELECT ON user2.t_a10 TO user1;

On the database DATB1 the user user1 can now create a database link without specifying any password:

CREATE DATABASE LINK ldb2 USING 'ALIASDATB2';

The statement:

SELECT * FROM user2.t_a1@DBLDATB2;

works without any problem because the access to the table user2.t_a1 will occur with a connection using the username user1 with password pwd1, which correspond to an active account on DATB2 as well.

[Home] [Web Design] [HTML tutorials] [Javascript] [PSP] [About us] [Links] [Anonymous email] [Best hosting] [Daily Oracle Life] [IT jobs in Switzerland] [Web Submission] [Web traffic]

Rate this article ...
Very poor	Poor	Average	Good	Very good