Oracle Grid Control: The Importance of Deleting the emkey

Disclaimer: In most countries, looking at user passwords is illegal. Never try what is written below on a system that somebody other than you can access.

Oracle Grid Control documentation warns against leaving the emkey in the Grid Control repository. It says:

After the emkey has been copied, you must remove it from the Management Repository as it is not considered secure. If it is not removed, data such as database passwords, server passwords and other sensitive information can be easily decrypted.

You may wonder: how easily?

A Bit of Background

When you deal with management tools, you want to collect metrics and run various tasks on different targets. Unless you evolve in an very advanced security infrastructure such as a PKI or another “real” Single Sign-On solution, it’s likely that you’ll need a username/password to connect to a remote server, a remote database, or a remote application server. Because Grid Control enables you to automate a great number of tasks, it has to be able to connect without prompting the users for credentials. In order to do that, it has to know the real passwords because it will itself authenticate on the targets. Because there no magic in there, it has to store these informations in its repository!

As a consequence, if the password you type to connect to the Grid Control is stored in a HASH form only, the one you store in Grid Control to run a task, collect data, or simply avoid typing it when you drill down to a target, has to be reversible. But I guess it’s the same for all management solution.

Let’s Be More Specific

Oracle Management Service 10.2 uses several ways to protect these sensitive data, including Virtual Private Database and Password Encryption.

• To overcome the first one, you have to be able to connect to the database as the SYS user.
• To overcome the second one, you have to know the encrypted password form, the key, and the associated algorithm.

Obviously, the key used to cipher the password is the emkey. It is located in $OMS_HOME/sysman/config/emkey.ora by default, and it can be generated/configured with "emctl xxx emkey". So the next question is, “Where are stored the ciphered passwords?”.

Getting the Ciphered Credentials

The answer is the CREDENTIAL_VALUE column of SYSMAN.MGMT_CREDENTIALS2. As it is not very explicit, you may want to join the CREDENTIAL_GUID column with the same column in one of the tables that describe what the credentials are used for. Here is a list of those tables:

select table_name
  from dba_tab_columns
 where owner='SYSMAN'
   and column_name='CREDENTIAL_GUID'
   and table_name!='MGMT_CREDENTIALS2';

TABLE_NAME
------------------------------
MGMT_TARGET_CREDENTIALS
MGMT_HOST_CREDENTIALS
MGMT_ENTERPRISE_CREDENTIALS
MGMT_CONTAINER_CREDENTIALS
MGMT_JOB_CREDENTIALS
MGMT_COLLECTION_CREDENTIALS
MGMT_COLLECTION_TEMPLATE_CREDS
MGMT_NESTED_JOB_CRED_INFO

For example, let’s assume that the SCOTT/TIGER credentials are stored in the Normal credentials associated with the target named ORCL in the OMS repository. You need to get the CREDENTIAL_GUID. To do that, you can query the repository as below.

define target_name=ORCL
define target_type=oracle_database
define credential_set_name=DBCredsNormal

col user_name format a25

select c.credential_guid, c.user_name
  from sysman.mgmt_targets t,
       sysman.mgmt_target_credentials c
 where t.target_guid=c.target_guid
   and target_name='&target_name'
   and target_type='&target_type'
    and c.credential_set_name = '&credential_set_name';

 CREDENTIAL_GUID                  USER_NAME
-------------------------------- -----------------------------------
FD4A021A43571519CF0AAC30B898FFFF GREGORY

And once you have the credential_guid, you can query the mgmt_credentials2 table.

define credential_guid=FD4A021A43571519CF0AAC30B898FFFF

col credential_set_column format a25
col  credential_value format a85
set lines 120

select credential_set_column,
    credential_value credential_value
 from sysman.mgmt_credentials2
  where credential_guid='&credential_guid';

So all we need now is the algorithm.

How to Get the Algorithm?

Well . . . This I can’t tell! This is because the PL/SQL package that contains the decipher algorithm is wrapped. Unwrapping it is far beyond my skills even if some people (like you, maybe?) can try. So what I can tell is that the algorithm is stored in the repository as a function named SYSMAN.DECRYPT. This function relies on DBMS_OBFUSCATION_TOOLKIT and as you can see, it’s not usable if the emkey has been removed from the repository:

sqlplus / as sysdba

define credential_guid=FD4A021A43571519CF0AAC30B898FFFF

select credential_set_column,
    sysman.decrypt(credential_value) credential_value
  where credential_guid='&credential_guid';

    sysman.decrypt(credential_value) credential_value
    *
ERROR at line 2:
ORA-28239: no key provided
ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT_FFI", line 84
ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT", line 233
ORA-06512: at "SYSMAN.DECRYPT", line 9

However, if you register the emkey in the repository (which assumes you have the privilege to do so), deciphering the password becomes as easy as the query below:

emctl config emkey -copy_to_repos

sqlplus / as sysdba
define credential_guid=FD4A021A43571519CF0AAC30B898FFFF

select credential_set_column,
    sysman.decrypt(credential_value) credential_value
 from sysman.mgmt_credentials2
   where credential_guid='&credential_guid';

CREDENTIAL_SET_COLUMN     CREDENTIAL_VALUE
------------------------- -----------------
username                  scott
password                  tiger
role                      normal

Conclusion

I’ll let you make your own conclusion. What I’m very sure of now is that the command below has to be run successfully after you install Grid Control.

emctl config emkey -remove_from_repos

Oracle Enterprise Manager 10g Release 4 Grid Control
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
Please enter repository password:

The Em Key has been removed from the Management Repository.
Make a backup copy of OH/sysman/config/emkey.ora file and store it on another machine.
WARNING: Encrypted data in Enterprise Manager will become unusable if the emkey.ora file is lost or corrupted.
emctl status emkey

Oracle Enterprise Manager 10g Release 4 Grid Control
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
Please enter repository password:

The Em Key is configured properly.

Grégory.

P.S.: Thank you to The Pythian Group security officers making me so paranoid!