create one private database link for two users oracle

Question

I am trying to create one private database link to be accessed by only two users but it gives me syntax error.

My code:

CREATE DATABASE LINK "mylink"
 CONNECT TO USER1,USER2
 IDENTIFIED BY password123
 USING 'mydb';

Answer 1

When you select from a table with dblink first time in current session, then oracle needs to connect from one server to another.

So your current oracle instance is SRV1, and other is MYDB. When you issue first time in current session

select * from t@mylink

then oracle create session from SRV1 instance to MYDB instance. How oracle do it? Sends the connect command with username, password and TNS name (MYDB here). This information we provide when create dblink - user, password, TNS name (db name).

You need TWO private database links, first for one user (A), and the second for another (B).

SQL> connect a/password@SRV1
SQL> create database link mylink connect to user_from_MYDB identified by some_password...
SQL> connect b/password@SRV1
SQL> create database link mylink connect to user_from_MYDB identified by some_password...

• EdStevens: You got the 'do this' from @Euard Okhvat. The 'background' is that you must understand that a db link is merely a means for one db (DB-A) to act as a client to another (DB-B) - just like sqlplus connecting to DB-B. The CONNECT TO and IDENTIFIED BY in the link definition are the credentials of a specific user in the remote database. They are not the 'owning' or 'using' user in the database in which the link is defined, which appears to be how you were tying to use them.