list database monitoring users

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

1. you copy your encryption key to your repository database, on the OMS server


$ 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.

2. you decrypt the credentials for db monitoring


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


3. remove the security leak


$ 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