Masking/Hiding/Encrypting password column in db_link view?
Oracle Database Tips by Donald Burleson
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
col username for a10
col host for a20
col owner for a10
select l.name link, l.userid username, l.host, l.ctime created,
from sys.link$ l, sys.user$ u
where l.OWNER# = u.USER#;
LINK USERNAME HOST OWNER
--------------- ---------- -------------------- --------------------
DHC9_C CDHC DHC_C.WORLD CDHC
DHC9_C CDHC DHC_C.WORLD HR_DDHC
DHC9_C CDHC DHC_C.WORLD HR_TEAM
DHCPRIVATE CDHC DHC.WORLD HIR
How would you make the password encrypted? Can I mask the
dblink password or hide the db_link password??
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:
select db_link, username, host, created
where owner = ?FRED?;
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.