Skip to Main Content
  • Questions
  • Database links without specifying password (using Oracle Wallet)

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: August 03, 2017 - 2:37 pm UTC

Last updated: August 05, 2017 - 3:15 am UTC

Version: 12.1.0.2

Viewed 10K+ times! This question is

You Asked

Is it possible to create a database link without specifying the password (say somehow using a oracle wallet)?

As of now we use passwords for everything -
JDBC connection
database connections (languages other than Java)
Creating database links (automatically, w/o involvement of DBA)
Connecting to the database etc

Now we plan to do away with the sharing of password. Can you suggest possible features that Oracle has that can help us? Also what are the possible limitations of Oracle wallets??

and Connor said...

For any kind of connection operation, the wallets should be fine. An example here

https://connormcdonald.wordpress.com/2015/09/21/connection-shortcuts-with-a-wallet/

That will work in everything I can think of (java, odp.net, etc etc) but not with database links.

When it comes to database links, you've got a few options

1) hardcode the username/passwords in there (which I think you're trying to avoid)

2) consistent username/passwords across databases where appropriate

In this case, you do:

create database link MYLINK using 'string';

ie, you do *not* specify the username and password. In this case, the username / password of the session using the link will be passed over to the remote session. A similar variant to that is when you have all of your users managed in a global LDAP directory, which looks like:

create database link MYLINK CONNECT TO CURRENT_USER using 'string'


Hope this helps.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Add-on comment/doubt

J. Laurindo Chiappa, August 04, 2017 - 1:02 pm UTC

Great answer, Connor : I don´t knew any of the options for #2 option, learned something new today, thanks...
An add-on : as you know, using proxy user, a (corretly permissioned) user A can connect 'in behalf of' another user B, even if A don´t know B´s password...

In this case where proxy-user priv is in use, could be sent B´s username/password to the database link created by A user via #2 answer, ie, not specifying username/password ??


Regards,

J. Laurindo Chiappa
Connor McDonald
August 05, 2017 - 3:15 am UTC

SQL> conn demo/demo
Connected.

SQL> create database link remote_db using 'db122';

Database link created.
SQL> conn / as sysdba
Connected.
SQL> alter user demo grant connect through scott;

User altered.

SQL> conn scott[demo]/tiger
Connected.

SQL> select * from tab@remote_db;
select * from tab@remote_db
                  *
ERROR at line 1:
ORA-25430: connected user database links are not supported in proxy session



Can you please share an example for #2

A reader, August 04, 2017 - 1:34 pm UTC

Can you please share an example for #2( consistent username/passwords across databases where appropriate )?
Connor McDonald
August 05, 2017 - 3:03 am UTC

SQL> conn scott/tiger
Connected.

SQL> create database link remote_db using 'db122';

Database link created.

SQL> select * from tab@db122;

TNAME
---------------------------------------------------------------
TABTYPE  CLUSTERID
------- ----------
APP_BRANCH
TABLE

APP_BRANCH1
TABLE



Notice I didnt nominate a user/pass. So as long as the password for SCOTT is TIGER in the remote database (as it is in my local database) I can query remotely.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database