I am quite familiar with the SYSMAN tables but this one required me some googling beyond the Oracle documentation.
The list of targets in your Oracle Enterprise Manager is in SYSMAN.MGMT_TARGETS. Each database target is monitored by a database user, typically DBSNMP.
To retrieve this information, you need some to hijack your database, read this : Gökhan Atil
- you copy your encryption key to your repository database, on the OMS server
- you decrypt the credentials for db monitoring
- remove the security leak
$ emctl config emkey -copy_to_repos Enter Enterprise Manager Root (SYSMAN) Password :
Now anyone with select any table on your repository will see all passwords. You don’t want to do this, but unfortunately you have to do this because even the username is encrpyted.
SELECT * FROM ( SELECT target_name, sysman.em_crypto.decrypt ( c.cred_attr_value, c.cred_salt) cred, cred_attr_name attr FROM SYSMAN.EM_TARGET_CREDS c JOIN SYSMAN.mgmt_targets t USING (target_guid) JOIN sysman.EM_NC_CRED_COLUMNS c USING (cred_guid) WHERE c.target_type = 'oracle_database' AND c.set_name = 'DBCredsMonitoring' ) PIVOT ( MAX (cred) FOR (attr) IN ( 'DBUserName' AS USERNAME, 'DBRole' AS "ROLE") )
TARGET_NAME USERNAME ROLE ----------- -------- ------ DB01 dbsnmp NORMAL DB02 dbsnmp NORMAL DB03 sys SYSDBA
$ emctl config emkey -remove_from_repos Enter Enterprise Manager Root (SYSMAN) Password :
Now the em_crypto won’t work any more
select sysman.em_crypto.decrypt('0','0') from dual * Error at line 2 ORA-28239: no key provided ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 67 ORA-06512: at "SYS.DBMS_CRYPTO", line 44 ORA-06512: at "SYSMAN.EM_CRYPTO", line 250 ORA-06512: at line 1
This information could be used to change the password dynamically accross all databases.
emcli login \ -username=sysman \ -password=sysmanpw emcli update_db_password \ -target_name=DB01 \ -user_name=dbsnmp \ -change_at_target=yes \ -old_password=oldpw \ -new_password=newpw \ -retype_new_password=newpw