Select Page

OEM Database Preferred Credentials

Chad Cleveland | | July 23, 2019

Fact: Credentials are an essential part of Oracle Enterprise Manager (OEM) security. Named credentials can be used to connect to targets and are saved. Credentials, which are stored in the repository, are also used for default connectivity which allows for easy access and provides another layer of security. A new feature of OEM Cloud Control 12c is the ability to share credentials.

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.

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017
sharepoint ideas

9 Awesome Things You Can Do with SharePoint

This blog post discusses out-of-the-box uses for SharePoint that you can execute in just a few clicks without using any code.

Amol Gharat | May 23, 2017
Oracle DBA Skills

8 Things Every Beginner Oracle DBA Should Know

A checklist of eight critical skills and areas an entry-level Oracle DBA should be familiar with to succeed on the job.

Patrick Gates | November 30, 2016

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS