As I've posted before, Enterprise Manager's (EM) metadata
presents a wealth of information that can come in handy for your day-to-day DBA
work. In this article I'd like to share how
stored passwords in EM (Preferred Credentials) can be used to your advantage.
For an EM agent to interact with a server, the agent needs
that server's password. To work without
human interaction that agent needs to store
the server's password. It also can store
other target passwords, like that for databases. The passwords are obviously stored in an
encrypted format, but with the right SQL and function combinations you can
retrieve any password, in clear text, for any username programmatically. It all starts with MGMT_CREDENTIALS2.
Each set of credentials is stored in MGMT_CREDENTIALS2, where
a "credential set" is made up of the username and password. Each credential set is identified by a
CREDENTIAL_GUID, which maps to MGMT_TARGET_CREDENTIALS. This table is used to map credential sets to
their targets for each administrator within EM, as you can save credentials for
multiple targets for multiple EM administrators.
The following is an example of putting this together in a
script (pass_lookup.sql):
SET ECHO off FEEDBACK off
HEADING off PAGESIZE 10000 TAB off VERIFY off
DEFINE USERNAME='&1';
DEFINE TARGET_NAME='&2';
DEFINE TARGET_TYPE='&3';
SELECT DISTINCT
DECRYPT(c.credential_value)
FROM mgmt_target_credentials tc,
mgmt_credentials2 c, mgmt_targets t
WHERE UPPER(c.key_value) =
'&USERNAME' -- Username whose
password we're retrieving
AND c.credential_set_column = 'password'
AND t.target_guid = tc.target_guid
AND tc.credential_guid = c.credential_guid
AND t.target_type = '&TARGET_TYPE'
AND t.target_name = '&TARGET_NAME';
EXIT
The reason C.CREDENTIAL_SET_COLUMN is filtered on
"password" is that in this case we only want the password value, not
the value of the username.
Taking this a step further, you can see how retrieval of the
password can now be used within code, such as:
USER="DBA_USER"
PASS=`sqlplus -S sysman/sysman_pass @pass_lookup.sql "$USER" "dbname"
"rac_database" | grep -v "^$"`
Those lines set the variable $PASS with the password value
for a given target and username. Now
obviously you first need to know the password for SYSMAN of the repository, but
that's a relatively small detail to code for and the above should give you a
good start if you want to use the EM repository as a place to store and
retrieve passwords.
