Mining EM Metadata - Password Lookup

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.