OEM Database Preferred Credentials
Author: Chad Cleveland | 6 min read | July 23, 2019
Reality: I like to be able to gather Information from a series of databases in one swoop, rather than connecting to each. Something as simple as checking Database parameters (SQL92_Security, Is Auditing Turned on, Memory Settings…) …or something as easy as creating a New DBA account, resetting passwords in all databases.
Issue: Recently worked a project for more than 30 non-prod to production series of database for a migrations to new hosts. The customer asked for a number of different items such as validate Character Sets, reset service accounts, and get size of database.
OEM Should make this easy for you, but you have to be able to connect to each database. Setting preferred credentials stores database(s) account and passwords in secure fashion. This is helpful in situations mentioned above. Or it can allow you to share a credential with access to RUN Performance to developer in a production database, without allowing him the password or connectivity outside of a controlled application (OEM).
Datavail can locate targets with missing preferred credentials via querying the OEM repository. We can then build dynamic emcli (Enterprise Manager Command Line Interface) commands to build the preferred Credentials in the OEM Application.
I just used queries to locate the missing database targets, generate the EMCLI commands, build the preferred credentials in OEM. Finally, I was able generate the reports and requests from OEM in one swoop rather than having to connect to each host and each database. 🙂
Here’s the snapshot.
SQL> @pc.sql
USER_NAME TARGET_NAME TARGET_TYPE CRED_OWNER CRED_NAME USER_NAME DISPLAY_NAME TARGET_STATUS
---------- ------------ -------------------- --------------- --------------- --------------- ------------------------------ -------------
hdenv1t oracle_database Normal Database Credentials 1
hddev1t oracle_database SYSDBA Database Credentials 1
SQL>
SQL> @create_pc.sql à Generate EMCLI Commands with a standard password for sys and system.
STTM
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
emcli create_named_credential -auth_target_type=oracle_database -cred_scope=Instance -target_type=oracle_database -target_name=hdenv1t -cred_type=DBCreds -cred_name= hdenv1t_normal_pc -attributes="DBUserName:system;DBPassword:SecretPass1234#;DBRole:normal"
emcli create_named_credential -auth_target_type=oracle_database -cred_scope=Instance -target_type=oracle_database -target_name=por99t -cred_type=DBCreds -cred_name= hdenv1t_sysdba_pc -attributes="DBUserName:sys;DBPassword:Kap0w1Sec#;DBRole:sysdba"
No Login to OEM via emcli and build those creds!
oemoms01> emcli login -username=ccleveland
Enter password :
Login successful
oemoms01>
oemoms01> emcli create_named_credential -auth_target_type=oracle_database -cred_scope=Instance -target_type=oracle_database -target_name=hdenv1t -cred_type=DBCreds -cred_name= hdenv1t_normal_pc -attributes="DBUserName:system;DBPassword:SecretPass1234#;DBRole:normal"
Credential hdenv1t_normal_pc created.
oemoms01> emcli create_named_credential -auth_target_type=oracle_database -cred_scope=Instance -target_type=oracle_database -target_name=por99t -cred_type=DBCreds -cred_name= hdenv1t_sysdba_pc -attributes="DBUserName:sys;DBPassword:Kap0w1Sec#;DBRole:sysdba"
Credential hdenv1t_sysdba_pc created.
And swoosh, there you have it. OEM Credentials made simple.
