Oracle public shared database link not working for non-DBA users

Question

I need to convert a database link that I have in Oracle from a normal public database link to a public shared database link.

I ran the following as my user with DBA privileges to make the link shared:

DROP PUBLIC DATABASE LINK "MYDBLINK";

CREATE SHARED PUBLIC DATABASE LINK "MYDBLINK"
AUTHENTICATED BY SOME_USER
IDENTIFIED BY thepassword
USING 'OTHERDB';

I used the same username and password as the database link was already using.

When I query across the database link with my user that has DBA privileges, it works fine. But when I use a non-DBA privileged user, and I inspect the database link, it shows that the user name for the database link is null, and if I try to query across the link, I get:

ORA-01017: invalid username/password; logon denied

Any ideas why the link would behave differently for non-DBA users?

try to define IDENTIFIED BY 'thepassword' in single quotes. — Barbaros Özhan
– Barbaros Özhan, Commented Oct 4, 2017 at 15:23
@BarbarosÖzhan That should only matter if the passwords are case sensitive, which they are not in our db. Also, it works fine for the DBA user. — Andrew Mairose
– Andrew Mairose, Commented Oct 4, 2017 at 15:31

atokpas · Accepted Answer · 2017-10-05 09:58:13Z

Authentication to the remote database is done by using the credential defined on AUTHENTICATED BY clause. Once the connection is established, operation on the remote database proceeds with the privileges of the user defined on CONNECT TO clause or CURRENT USER, no the AUTHENTICATED BY schema.

In your case, you have not defined CONNECT TO schema hence it is using the current user in the local database to operate on the remote database. If your currently logged in user on the local database doesn't exist on the remote server then you get this error.

Here is a demo.

On a Local Database(AS SYSDBA)

SQL> CREATE SHARED PUBLIC DATABASE LINK link2remotedb
AUTHENTICATED BY userA_uat IDENTIFIED BY userA_uat
USING 'ORCLUAT';  

Database link created.

Then I connected by a user which exists on my local database but not on the remote database.

SQL> conn jay/jay
Connected.
SQL> select * from address@link2remotedb;
select * from address@link2remotedb
                      *
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from LINK2REMOTEDB

The error has occurred because the SELECT operation used the current user which is JAY to select from the ADDRESS table on the remote database. JAY user doesn't exist on the remote database.

So, what next?

Define the user in CONNECT TO clause.

SQL> CREATE SHARED PUBLIC DATABASE LINK link2remotedb
CONNECT TO userA_uat IDENTIFIED BY userA_uat
AUTHENTICATED BY userA_uat IDENTIFIED BY userA_uat
USING 'ORCLUAT'; 

Database link created.

SQL> conn jay/jay
Connected.
SQL> select * from address@link2remotedb;

Documentation

Using Shared Database Links

Collectives™ on Stack Overflow

Oracle public shared database link not working for non-DBA users

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related