Skip to content
Home » Oracle » Create DB Links for Another User, Can We?

Create DB Links for Another User, Can We?

The answer is no, at least, not directly. You can see the reason why we can't create database links for another user in this post.

Prerequisites to Create DB Links

Before proceeding any further, you have to make sure the users involving in the database link have been granted proper system privileges:

1. Grant Create Database Link

Grant CREATE DATABASE LINK or CREATE PUBLIC DATABASE LINK to the user in local database, whichever privilege is to be used.

SQL> grant create database link to hr;

Grant succeeded.

2. Grant Create Session

Grant CREATE SESSION to the user in REMOTE database.

SQL> grant create session to hr;

Grant succeeded.

Create a DB Link in Another User

Let's try to create a database link for HR by SYS and see what will happen.

SQL> show user;
USER is "SYS"
SQL> create database link HR.DBLINK1 connect to hr identified by hr using 'erpdb';

Database link created.

SQL> select db_link from dba_db_links where owner = 'HR';

no rows selected

HR does not have it. Let's check SYS.

SQL> select db_link from dba_db_links where owner = 'SYS';

DB_LINK
--------------------------------------------------------------------------------
HR.DBLINK1

As you can see, the newly created database link falls in SYS realm, not HR, which means that qualifying the object by adding schema name does not work here. We drop it for more tests.

SQL> drop database link HR.DBLINK1;

Database link dropped.

In case that SQL parser misunderstand our intention, we can try another way to make sure the schema is specified by double-quoting all the identifiers.

SQL> create database link "HR"."DBLINK1" connect to hr identified by hr using 'erpdb';

Database link created.

SQL> select db_link from dba_db_links where owner = 'HR';

no rows selected

SQL> select db_link from dba_db_links where owner = 'SYS';

DB_LINK
--------------------------------------------------------------------------------
HR.DBLINK1

Unluckily, it doesn't work, either. The above result shows that we cannot qualify any database link with the name of a schema. Why?

Periods are Counted

This is because periods (".", dots) are permitted in name of database links, so Oracle database interprets the entire name including periods for your database links.

Further reading: DB Link, How and Why

The Correct Way to Create Private Database Links

You have to create the database link in that user's session.

SQL> drop database link HR.DBLINK1;

Database link dropped.

SQL> conn hr/hr;
Connected.
SQL> create database link DBLINK1 connect to hr identified by hr using 'erpdb';

Database link created.

SQL> select db_link from all_db_links where owner = 'HR';

DB_LINK
--------------------------------------------------------------------------------
DBLINK1

CURRENT_SCHEMA

Maybe you were considering to switch CURRENT_SCHEMA to target user like the following. But I can tell you that it doesn't work, either.

SQL> alter session set current_schema = HR;

Session altered.

SQL> select sys_context('USERENV','CURRENT_SCHEMA') from dual;

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
HR

For more explanations about how to create database links, please visit Oracle 19c SQL Language Reference: CREATE DATABASE LINK.

Tags: