Masking/Hiding/Encrypting password column in db_link view? Oracle Database Tips by Donald Burleson

Question:

How can I mask the password from the link$ table and in the dba_db_links, all_db_links and user_db_links views?
If I get into a database I can see passwords from databases to which a dblink connects.

If I fire this query:

col link for a30
col username for a10
col host     for a20
col owner    for a10
select l.name link, l.userid username, l.host, l.ctime created, u.name owner,
l.password
from   sys.link$ l, sys.user$ u
where  l.OWNER# = u.USER#;

I get

LINK            USERNAME   HOST                 OWNER                PASSWORD
--------------- ---------- -------------------- -------------------- --------
DHC9_C          CDHC       DHC_C.WORLD          CDHC                 CDHC2005
DHC9_C          CDHC       DHC_C.WORLD          HR_DDHC              CDHC2005
DHC9_C          CDHC       DHC_C.WORLD          HR_TEAM              CDHC2005
DHCPRIVATE      CDHC       DHC.WORLD            HIR                  CDHC2005

How would you make the password encrypted? Can I mask the dblink password or hide the db_link password??

Answer:

Lots of Oracle professionals have an issue with the non encrypted password in the dba_db_links view.  No, there is no direct way to encrypt the password column because it is needed in unencrypted form to connect to the remote database.  There has been an enhancement request to hide the password column from the user_db_links view, but it has not yet been implemented.  There are several ways to workaround this exposed db_link password issue:

• On the remote database, create the remote link user ID with the absolute minimum of privileged required to access the remote data.
   

• On the master database, redefine the all_user_links and user_db_links to omit the passwords.

Start by ensuring that only your SYS and SYSTEM users can see the db link views (dba_db_links, all_db_links, user_db_links), and you can drop the view definitions for the all_db_links, user_db_links views, leaving only the dba_db_links view, only viewable by SYS and SYSTEM.  You can also alter the view definition for user_db_links to omit the password column:

create view fred_user_db_links as
select db_link, username, host, created
from user_db_links
where owner = ?FRED?;

drop view user_db_links;

If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.