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:
emkeyhas 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
- 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
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
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.
P.S.: Thank you to The Pythian Group security officers making me so paranoid!