I made a bet the other day with somebody I work with. We were in need of some passwords to re-create database links during a migration from 11.1.0.7 on AIX to Exadata X2-2. He said it was no longer possible in 11g+, I said it was. In previous rdbms versions, there was a password field included in the dba_db_links view you could use along with the values keyword to re-create the database links without knowing the password. Since this field was removed from the view, he assumed it was no longer possible to use the values keyword when creating database links.
I was willing to make the bet because I knew we can still do exports/data pumps. When these are imported on the target side, they don't have the original password, so the encrypted password must still be stored somewhere, and there must be some statement that's still used.
I created a schema in 11g, created a db link and exported it. Looking at the mostly binary file, I see this:
In previous versions of Oracle, this password field was much shorter due to the weaker encryption used...today they've switched to SHA-1, combined with a salt field (stored in spare4 in sys.user$).
So...the syntax for the statement is still valid...but where is it getting the encrypted password that's no longer included in dba_db_links? I could have run a trace on the export, but I thought it would be faster to look at the source of the dba_db_links view and check the underlying tables. Here's the query for the view:
SELECT u.name,
l.name,
l.userid,
l.HOST,
l.ctime
FROM sys.link$ l, sys.user$ u
WHERE l.owner# = u.user#;
...and describing sys.link$:
Name Null? Type
----------------------------------------- --------
OWNER# NOT NULL NUMBER
NAME NOT NULL VARCHAR2(128)
CTIME NOT NULL DATE
HOST VARCHAR2(2000)
USERID VARCHAR2(30)
PASSWORD VARCHAR2(30)
FLAG NUMBER
AUTHUSR VARCHAR2(30)
AUTHPWD VARCHAR2(30)
PASSWORDX RAW(128)
AUTHPWDX RAW(128)
So, after I got the user_id from dba_users for the db_link owner, I selected from sys.link$ and was able to pull my encrypted password.
For the client I'm working with today, they have hundreds of apps and thousands of passwords. For security concerns, very few people are privy to this information...so re-creating db links can take a very long time due to coordination efforts. This process could be used in the future by dba's who don't need to know the passwords stored in the database links in order to migrate the db links. This will hugely improve their efficiency, but more importantly, it means I won the bet. :)
I was willing to make the bet because I knew we can still do exports/data pumps. When these are imported on the target side, they don't have the original password, so the encrypted password must still be stored somewhere, and there must be some statement that's still used.
I created a schema in 11g, created a db link and exported it. Looking at the mostly binary file, I see this:
CREATE DATABASE LINK "EXADATA1.MYCOMPANY.COM" CONNECT TO "TESTER" IDENTIFIED BY VALUES '054B7E837E4DDD8040336B8D6551171D7A179DC8D8E2148ABC' USING 'REMOTEDB.MYCOMPANY.COM';
In previous versions of Oracle, this password field was much shorter due to the weaker encryption used...today they've switched to SHA-1, combined with a salt field (stored in spare4 in sys.user$).
So...the syntax for the statement is still valid...but where is it getting the encrypted password that's no longer included in dba_db_links? I could have run a trace on the export, but I thought it would be faster to look at the source of the dba_db_links view and check the underlying tables. Here's the query for the view:
SELECT u.name,
l.name,
l.userid,
l.HOST,
l.ctime
FROM sys.link$ l, sys.user$ u
WHERE l.owner# = u.user#;
...and describing sys.link$:
Name Null? Type
----------------------------------------- --------
OWNER# NOT NULL NUMBER
NAME NOT NULL VARCHAR2(128)
CTIME NOT NULL DATE
HOST VARCHAR2(2000)
USERID VARCHAR2(30)
PASSWORD VARCHAR2(30)
FLAG NUMBER
AUTHUSR VARCHAR2(30)
AUTHPWD VARCHAR2(30)
PASSWORDX RAW(128)
AUTHPWDX RAW(128)
So, after I got the user_id from dba_users for the db_link owner, I selected from sys.link$ and was able to pull my encrypted password.
For the client I'm working with today, they have hundreds of apps and thousands of passwords. For security concerns, very few people are privy to this information...so re-creating db links can take a very long time due to coordination efforts. This process could be used in the future by dba's who don't need to know the passwords stored in the database links in order to migrate the db links. This will hugely improve their efficiency, but more importantly, it means I won the bet. :)
