Resolve PDP Error for Named Credentials

Tags

, ,

Background

Some of our named credentials use a privileged account to perform root actions via sudo. That account is not the same as the OEM agent binary owner and does not belong to the binary owner’s o/s groups for security reasons.

Sometime that causes problems, like this:

PDP execution may have failed 3430 Insecure operation – please consult your administrator pbrun8.5.1-01[112628]: 3201.07 Exec of /usr/bin/pb_sudo failed: Operation not permitted

The Powerbroker error is a symptom and not the real problem.  The real issue is that the privileged account lacks access to directories in the EM agent home.

Solution

Log into the host as the OEM binary owner and change the permissions as shown:

cd $AGENT_BASE
cd ../

 chmod 755 agent
 cd agent
 chmod 755 agent_inst

cd agent_inst
 chmod 775 diag
 chmod 755 bin install sysman
 chmod 740 internal
 
cd sysman
 chmod 755 ApplicationsState/ config/ emd/ log/ opmn/ recv/
 ls -las

Notice that we’re not changing any file permissions and we are not altering contents of the core/release directories, just agent_inst.

By the way:  This solution makes a very simple and convenient OEM Job.

 

Verification

In the console click through to Setup | Security | Named Credentials and highlight the privileged credential you need to test.  Select the previously broken host name from the Target Name list and hit the Test button.

image2017-1-12 7-58-53.png

Catalog your Named Credentials

Tags

, , , ,

You can catalog your named credentials quickly and easily with EM CLI.

emcli login -username=SYSMAN

emcli sync

NCCATALOG=/u01/oem/backups/named_credential_catalog.lst
[ $NCCATALOG ] && rm -f ${NCCATALOG}
touch ${NCCATALOG}

for thisNC in `emcli list_named_credentials | awk '{ print $1 }' | grep -v "Credential"`; do
emcli get_named_credential -cred_name=${thisNC} >>${NCCATALOG}
done

cat ${NCCATALOG}

 

Your results will look like this:

Credential Name:CRED_SYSDBA
Credential Owner:SYSMAN
Credential Type:DBCreds
Credential Target Type:oracle_database
Credential Username:sys
Credential Scope:global
Credential Guid:<string>
Credential Stripe:TARGETS
Credential Columns:
 DBPassword=******
 DBRole=sysdba
 DBUserName=sys

Credential Name:CRED_SUPERMAN
Credential Owner:SYSMAN
Credential Type:HostCreds
Credential Target Type:host
Credential Username:superman
Credential Description:Superman administrator account
Credential Scope:global
Credential Guid:<string>
Credential Stripe:TARGETS
Credential Columns:
 HostPassword=******
 HostUserName=superman
 PDPTYPE=SUDO
 RUNAS=oracle

You Can Not Outsmart a SYSMAN Password Change

Tags

, , , , , ,

There are no circumstances where manually changing the passwords for the SYSMAN-named database accounts will end happily.  Never attempt to change the passwords for SYSMAN, SYSMAN_APM, SYSMAN_BIPLATFORM, SYSMAN_MDS, SYSMAN_OPSS, or SYSMAN_RO from within the database.

OEM security is managed by the WebLogic admin server.  The database passwords are only part of the puzzle.  Your WLS relies on wallets and other encrypted files to keep it all straight.  When you do the password change in the repository OEM, security validations against those files will fail and OEM will be hopelessly broken.

When you invoke this simple emctl command a slew of activities occur

emctl config oms -change_repos_pwd

 

That triggers this chain of actions (from .. sysman/log.secure.log):

oms.AdminCredsWalletUtil setInstanceHome.177 – Getting credentials from wallet
oms.AdminCredsWalletUtil setInstanceHome.192 – Read the credentials from wallet
util.EmctlUtil logp.251 – Connecting over t3s to: oms01.demo.com/7103 using id: weblogic
oms.ChangeReposPwd logp.251 – Getting repos conn as user sys as sysdba
oms.ChangeReposPwd logp.251 – Getting repos conn as user sys as sysdba
oms.ChangeReposPwd logp.251 – SYSMAN password changed in the backend successfully.
oms.ChangeReposPwd logp.251 – Getting lock on table EM_UPDATE_DATASOURCES_LOCK
oms.ChangeReposPwd logp.251 – Getting repos conn as user SYSMAN
oms.ChangeReposPwd logp.251 – Successfully obtained lock on table EM_UPDATE_DATASOURCES_LOCK
oms.ChangeReposPwd logp.251 – Start change SYSMAN_MDS password
oms.ChangeReposPwd logp.251 – Changed SYSMAN_MDS password
oms.ChangeReposPwd logp.251 – Changing OPSS admin user’s pwd
oms.ChangeReposPwd logp.251 – Changed OPSS admin pwd
oms.ChangeReposPwd logp.251 – Changing APM admin user’s pwd
oms.ChangeReposPwd logp.251 – Changed APM admin pwd
oms.ChangeReposPwd logp.251 – Getting repos conn as user SYSMAN
oms.AdminCredsWalletUtil setInstanceHome.177 – Getting credentials from wallet
oms.AdminCredsWalletUtil setInstanceHome.192 – Read the credentials from wallet
util.EmctlUtil logp.251 – Connecting over t3s to: oms01.demo.com/7103 using id: weblogic
util.EmctlUtil logp.251 – Updating datasource : emgc-sysman-pool
util.EmctlUtil logp.251 – Updating WLS datasource :emgc-sysman-pool: Done startEditSession()
util.EmctlUtil logp.251 – Updating WLS datasource : DB datasource :emgc-sysman-pool: IS found
util.EmctlUtil logp.251 – Updating password
util.EmctlUtil logp.251 – Updating WLS datasource :emgc-sysman-pool: Invoked save
util.EmctlUtil logp.251 – Updating WLS datasource :emgc-sysman-pool: Done activate()
oms.ChangeReposPwd logp.251 – Updating OWSM DataSource
util.EmctlUtil logp.251 – Updating datasource : mds-owsm
util.EmctlUtil logp.251 – DataSource URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST= …
util.EmctlUtil logp.251 – Updating WLS datasource :mds-owsm: Done startEditSession()
util.EmctlUtil logp.251 – Updating WLS datasource : DB datasource :mds-owsm: IS found
util.EmctlUtil logp.251 – Updating URL
util.EmctlUtil logp.251 – Updating password
util.EmctlUtil logp.251 – Updating userName
util.EmctlUtil logp.251 – Updating WLS datasource :mds-owsm: Invoked save
util.EmctlUtil logp.251 – Updating WLS datasource :mds-owsm: Done activate()
oms.ChangeReposPwd logp.251 – Updating APM DataSource
util.EmctlUtil logp.251 – Updating datasource : apm-DBDS
util.EmctlUtil logp.251 – DataSource URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST= …
util.EmctlUtil logp.251 – Updating WLS datasource :apm-DBDS: Done startEditSession()
util.EmctlUtil logp.251 – Updating WLS datasource : DB datasource :apm-DBDS: IS found
util.EmctlUtil logp.251 – Updating URL
util.EmctlUtil logp.251 – Updating password
util.EmctlUtil logp.251 – Updating userName
util.EmctlUtil logp.251 – Updating WLS datasource :apm-DBDS: Invoked save
util.EmctlUtil logp.251 – Updating WLS datasource :apm-DBDS: Done activate()
oms.ChangeReposPwd logp.251 – Updating APM-MDS DataSource
util.EmctlUtil logp.251 – Updating datasource : mds-ApplicationMDSDB
util.EmctlUtil logp.251 – DataSource URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST= …
util.EmctlUtil logp.251 – Updating WLS datasource :mds-ApplicationMDSDB: Done startEditSession()
util.EmctlUtil logp.251 – Updating WLS datasource : DB datasource :mds-ApplicationMDSDB: IS found
util.EmctlUtil logp.251 – Updating URL
util.EmctlUtil logp.251 – Updating password
util.EmctlUtil logp.251 – Updating userName
util.EmctlUtil logp.251 – Updating WLS datasource :mds-ApplicationMDSDB: Invoked save
util.EmctlUtil logp.251 – Updating WLS datasource :mds-ApplicationMDSDB: Done activate()
oms.ChangeReposPwd logp.251 – Updating OPSS DataSource
util.EmctlUtil logp.251 – Destroying datasource sysman-opss-ds
util.EmctlUtil logp.251 – destroyDataSource completed with status = true
util.EmctlUtil logp.251 – Creating datasource sysman-opss-ds
util.EmctlUtil logp.251 – Datasource created. Targetting to 5 servers.
util.EmctlUtil logp.251 – Targeting to EMGC_ADMINSERVER
util.EmctlUtil logp.251 – Targeted to EMGC_ADMINSERVER
util.EmctlUtil logp.251 – Targeting to EMGC_OMS1
util.EmctlUtil logp.251 – Targeted to EMGC_OMS1
util.EmctlUtil logp.251 – Targeting to EMGC_OMS2
util.EmctlUtil logp.251 – Targeted to EMGC_OMS3
util.EmctlUtil logp.251 – Targeting to EMGC_OMS3
util.EmctlUtil logp.251 – Targeted to EMGC_OMS3
util.EmctlUtil logp.251 – Targeting to EMGC_OMS4
util.EmctlUtil logp.251 – Targeted to EMGC_OMS4
util.EmctlUtil logp.251 – Targeting to cluster BIP_cluster
util.EmctlUtil logp.251 – Targeted to BIP_cluster
util.EmctlUtil logp.251 – createDataSource completed with status = true
oms.ChangeReposPwd logp.251 – Updating mds password in domain…
util.EmctlUtil logp.251 – Updating datasource : mds-sysman_mds
util.EmctlUtil logp.251 – Updating WLS datasource :mds-sysman_mds: Done startEditSession()
util.EmctlUtil logp.251 – Updating WLS datasource : DB datasource :mds-sysman_mds: IS found
util.EmctlUtil logp.251 – Updating password
util.EmctlUtil logp.251 – Updating WLS datasource :mds-sysman_mds: Invoked save
util.EmctlUtil logp.251 – Updating WLS datasource :mds-sysman_mds: Done activate()
oms.ChangeReposPwd logp.251 – MDS password in domain updated successfully.
oms.ChangeReposPwd logp.251 – Waiting for jps/opss re-initialization to complete
oms.ChangeReposPwd logp.251 – Updating repository password in Credential Store. Try#1
mas.CredStoreUtil logp.251 – deleteCredential : Exit mapName -EM keyName – REPOS_DETAILS status = true
mas.CredStoreUtil logp.251 – setGenericCredential : Exit mapName -EM keyName – REPOS_DETAILS
mas.CredStoreUtil logp.251 – getCredential : Got creds for mapName -EM_BIP keyName – EM_BIP_DETAILS
oms.ChangeReposPwd logp.251 – Getting repos conn as user sys as sysdba
oms.ChangeReposPwd logp.251 – Changing :SYSMAN_BIPLATFORM: in back-end
oms.ChangeReposPwd logp.251 – Changed :SYSMAN_BIPLATFORM: user in back-end
util.EmctlUtil logp.251 – Updating datasource : bip_datasource
util.EmctlUtil logp.251 – Updating WLS datasource :bip_datasource: Done startEditSession()
util.EmctlUtil logp.251 – Updating WLS datasource : DB datasource :bip_datasource: IS found
util.EmctlUtil logp.251 – Updating password
util.EmctlUtil logp.251 – Updating WLS datasource :bip_datasource: Invoked save
util.EmctlUtil logp.251 – Updating WLS datasource :bip_datasource: Done activate()
oms.ChangeReposPwd logp.251 – Commiting the getLockConn to release lock on EM_UPDATE_DATASOURCES_LOCK
oms.ChangeReposPwd logp.251 – Done commiting the getLockConn

 

Don’t Despair

The  emctl change_repos_password command will clean up any mess you’ve made.

It starts by making a clean password change on the backend (the repository), updates the local reference files, and then propogates the change to all OMS’s in your environment.

I deliberately messed up one of our lab servers and within minutes of invoking emctl change_repos_password all my handiwork was fixed and I was back in business.

I’m Not Waiting – Collect my Metric Extensions Now!

Tags

, , , ,

We use OEM to populate other system management systems at our company by providing configuration and metric data of all kinds.  When we get requests that require data that OEM doesn’t collect out of the box, we build metric extensions.

If you’ve worked with 12c metric extensions you know that the agents appear to perform these special metric collections whenever OEM feels like it.  At least that’s the way it seems.

I need to be able to collect these metrics at-will in response to user complaints or requests, so I put together a SQL query that builds the EM CLI commands to force a collection using the collect_metric verb.

In this example I want to collect a metric named ME$DEMO01 from all the members of an OEM group named rays_demo_hosts.

SELECT 'emcli collect_metric -target_type=host -target_name='
                || target_name
                || ' -metric_name="ME\$DEMO01"'
 AS cli_command
 FROM sysman.mgmt$group_members
 WHERE target_type = 'host' AND group_name = 'rays_demo_hosts';

That query results several lines like this:

emcli collect_metric -target_type=host -target_name=hostinmind01.madeup.com -metric_name="ME\$DEMO01"

Deployment Method 1 – Cut and Paste

The simplest means of executing this script is to execute the query in a tool like TOAD (or even at the command line) and copy and paste each line into an emcli session.

Deployment Method 2 – Spool into an argfile

If you decide to spool the results to a file, remove the keyword emcli from the query first and then execute the block using the emcli argfile verb.

SET echo OFF
SET heading OFF feedback OFF

spool /tmp/me_demo.lst
 SELECT 'collect_metric -target_type=host -target_name='
                || target_name
                || ' -metric_name="ME\$DEMO01"'
 AS cli_command
 FROM sysman.mgmt$group_members
 WHERE target_type = 'host' AND group_name = 'rays_demo_hosts';
spool off

Then:

emcli login -username=SYSMAN

emcli sync

emcli argfile /tmp/demo.lst

Deployment Method 3 – OEM Job System

The two steps from Deployment Method 2 can be built into a simple OEM Job to run on any host with SQL+ and EM CLI client installed (like your OEM management server), using this kind of logic:

#!/bin/bash

export ORACLE_HOME=/x/home/oracle/product/em/MW12105/oms
export EMCLI=${ORACLE_HOME}/bin/emcli
SQL_CONNECT=<sysman connect string to OMR>
SYSMAN_PWD=<   >
export SPOOLFILE=/tmp/me_demo.lst

# -------------------------------------------------------------------------

${ORACLE_HOME}/bin/sqlplus -S ${SQL_CONNECT} <<EOF
SET echo OFF
SET pages 999 lines 32767 trimspool ON head OFF feedback OFF
spool ${SPOOLFILE}

SELECT 'collect_metric -target_type=host -target_name='
 || target_name
 || ' -metric_name="ME\$DEMO01"'
 AS cli_command
 FROM sysman.mgmt$group_members
 WHERE target_type = 'host' AND group_name = 'rays_demo_hosts';

spool off
exit
EOF

$EMCLI logout
$EMCLI login -username=SYSMAN -password=${SYSMAN_PWD} -force
$EMCLI sync
$EMCLI argfile ${SPOOLFILE}
$EMCLI logout

export SQL_CONNECT=" "
export SYSMAN_PWD=" "

exit 0

I generally create two versions of the same Job – one with a scheduled recurring time and a second to run immediately, with names like update_me_demo01 and update_me_demo01_immediate, respectively.

Plugin versions on agent does not support target type rac_database

Tags

, , ,

I wasn’t able to promote database targets after I applied the July bundle patch.  I quickly opened an SR and received the following excellent, if scary, advice.   The solution, as you’ll see, involves directly manipulating data in the SYSMAN schema.

The Error Message

“Plugin versions on agent https://xyz123.demo.org:1874/emd/main/ does not support target type rac_database”.

In this case the agents appeared to have a later release of the database plugin than the management server.   A quick check of the OMS and agents through the EM console showed that I was fully deployed with 12.0.8.  Everything’s fine, it just doesn’t work.

Evaluating the Problem

It seems my production OEM environment had entered some sort of quantum state, but I’m confident quantum computing wasn’t in this bundle patch.  Something must be seriously wrong.  That’s where Oracle Support came to the rescue.

The SYSMAN schema contains all sort of data and database objects behind the OEM application, including a detailed listing of acceptable plugin ranges – the min and max version your system will allow you to manage.

select * from em_target_types where type_resource_bundle like 'oracle.sysman.db.rsc';

sr313275776401_01

There’s my problem.  The max plugin version for RAC database was set to 12.1.0.7!

Action Plan (Thanks to Oracle Support)

0. Take a complete backup of repos DB  [ I chose to do a guaranteed restore point ].  The fix involves recompiling a hand-edited version of three packaged procedures, their dependent SQL, and several other objects.  Backup your OMR.

1. Navigate to <MW_HOME>/plugins/oracle.sysman.db.oms.plugin_12.1.0.8.0/sql/db_mds/12.1.0.8.0/targetType/ path on the OMS server.

i.e. ls -lrt has_data_upgrade_*

ls -lrt cluster_data_upgrade_

ls -lrt rac_database_data_upgrade_*

2. Find the last data upgrade sql file for has, cluster and rac_database.  In this case there were six SQL files for each plugin, so I edited has_data_upgrade_6.sql, etc.

 

3. Make a back up those each of those files.  We’ll edit and run these copies.

i.e. cp cluster_data_upgrade_6.sql cluster_data_upgrade_6_bkp.sql

4. Edit each backup file from step 3.  The two edits per file are shown in red below:  correct the plugin version ID and add a commit statement at the bottom of each file.

 DECLARE
 l_type_property_list SMP_EMD_NVPAIR_ARRAY;
 l_type_property SMP_EMD_NVPAIR;
 BEGIN

l_type_property_list := SMP_EMD_NVPAIR_ARRAY();
 l_type_property := SMP_EMD_NVPAIR( 'inherit_cred_metadata',
 '1');
 l_type_property_list.extend(1);
 l_type_property_list(l_type_property_list.count) :=
 l_type_property;
 l_type_property := SMP_EMD_NVPAIR(
 'inherit_cred_target_type',
 'oracle_database');
 l_type_property_list.extend(1);
 l_type_property_list(l_type_property_list.count) :=
 l_type_property;
 l_type_property := SMP_EMD_NVPAIR( 'use_parent_creds',
 '1');
 l_type_property_list.extend(1);
 l_type_property_list(l_type_property_list.count) :=
 l_type_property;
 l_type_property := SMP_EMD_NVPAIR( 'TargetVersion',
 'Version');
 l_type_property_list.extend(1);
 l_type_property_list(l_type_property_list.count) :=
 l_type_property;

mgmt_target.register_target_type(
 p_target_type_in => 'oracle_pdb',
 p_type_meta_ver_in => '1.4',
 p_parent_target_type_in => NULL,
 p_parent_type_meta_ver_in => NULL,
 p_type_property_list_in => l_type_property_list,
 p_type_display_name_in => 'Pluggable Database',
 p_type_display_nlsid_in => 'oracle_pdb_title',
 p_type_resource_bundle_in => 'oracle.sysman.db.rsc',
 p_monitored_by => '2',
 p_plugin_id => 'oracle.sysman.db',
 p_plugin_version_id => '12.1.0.8.0', 
 p_category_prop_defs => NULL,
 p_ocm_gc_merged => '1',
 p_type_relationship => 1,
 p_client => 'OTHERS',
 p_plugin_min_version_id => '12.1.0.1.0',
 p_product_id => NULL);
END;
.. lots of other stuff
 commit; 
 /

5. Run back up scripts from step 4 on the repository database as the sysman user:

@<MW_HOME>/plugins/oracle.sysman.db.oms.plugin_12.1.0.8.0/sql/db_mds/12.1.0.8.0/targetType/cluster_data_upgrade_6_bkp.sql

6. Running the following query on the repository database as the sysman user, verify that the Cluster, Cluster Database and Oracle High Availability Service targets MAX_PLUGIN_VERSION is 12.1.0.8.0:

 select * from em_target_types where type_resource_bundle like 'oracle.sysman.db.rsc';

Turn OEM Job Output into Lists

Tags

, , , , ,

Context

We have an OEM job that we run after each system-wide Unix password change to verify success on all hosts.

The OEM Job is run against Dynamic Groups of hosts.  In this example I’ve limited it to the group DBHosts.

check_pwd_job2

The job consists of a very simple call for id

check_pwd_job01

The Credentials for the job contain the new password, of course.

The job quickly tries to connect to each host with that named named credential and either succeeds or throws an error.  The Job’s screen output provides immediate feedback to the operator, but I need to share the list of exceptions with Operations so they can correct it.

Gathering Data

Job results are stored in sysman.mgmt$job_step_history. We can run a simple query to find our exceptions.  The list I sent to Operations excluded the end_time and status.

SELECT target_name,
       end_time,
       status
FROM sysman.mgmt$job_step_history
WHERE job_name LIKE 'CHECK ORACLE PASSWORD%'
 AND  status NOT IN ( 'Succeeded' )
 AND  end_time > SYSDATE - 7
ORDER BY target_name;

check_pwd_job3

SELinux blocked my .Xauthority

Tags

, , , , ,

I was attempting to install an OEM management server on a new host in the lab using runInstaller.  Of course the installer is an X-windows app so I need to configure port forwarding to get the display back to MacBook.

I added the new host and its bastion to my ~/.ssh/config file to set up port forwarding:

Host 10.123.45.678
ConnectTimeout 60
StrictHostKeyChecking ask
ProxyCommand none
UserKnownHostsFile ~/.ssh/known_Hosts
User oracle

Host newlaboms.raysdemo.com
ProxyCommand ssh -W %h:%p 10.123.45.678
StrictHostKeyChecking no
UserKnownHostsFile ~/.ssh/known_Hosts
VisualHostKey no
ForwardX11 yes
ForwardAgent yes
User oracle

Pretty straight-forward and it’s worked plenty of times before, so I expected no problems.

Frustration

When I ssh’d to newlaboms I was hit with an xauth error:

xauth:  timeout in locking authority file /home/oracle/.Xauthority

Quick solutions include ensuring proper ownership of my home directory (no problem), that I could write a new file there (touch temp.file — ok), and adequate space on the home’s file system (no problem).

My ‘id’ line looked wrong:

uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

All that “context=” stuff was added by SELinux.  This is a new host and SELinux is enabled by default and disabled by my S/A’s as part of the build process.  They’d missed it this time – and this is how you check:

> /usr/sbin/getenforce;
Enforcing

Enhanced security is being enforced by SELinux!

Temporary Fix

So try this:

> sudo /usr/sbin/setenforce 0 ;
> /usr/sbin/getenforce;
Permissive

Now log out and back in to notice that your .Xauthority file has been created and port forwarding will work!

Run ‘id’ and you’ll see the simple results you expect.

Permanent Change

The setenforce command does not require a server reboot but it’s also not going to survive a reboot.  To make the change permanent, ask your system admin to edit /etc/selinux/config to set “SELINUX=permissive”

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#       enforcing - SELinux security policy is enforced.
#       permissive - SELinux prints warnings instead of enforcing.
#       disabled - No SELinux policy is loaded.
SELINUX=enforcing
# SELINUXTYPE= can take one of these two values:
#       targeted - Targeted processes are protected,
#       mls - Multi Level Security protection.
SELINUXTYPE=targeted

 

 

 

 

 

User Defined Target Properties

Tags

, , , , , ,

We can create our own target property classifications using EM CLI.  In this example we’ll create a new property named “Product Type”.  In my shop we’ll use that property to identify Oracle RAC and single instance databases, but also noSQL targets that we’re adding to our EM environment.

Here’s the syntax for creating the new target property:

emcli add_target_property -target_type=”*” -property=”Product Type”
Property “Product Type” added successfully

The new target property can now be applied with EM CLI using the syntax:

emcli set_target_property_value -property_records=”target_name:target_type:property_name:property_value”

emcli set_target_property_value property_records=”SPECIAL_DEMO_2:rac_database:Product Type:OracleRAC”;
Properties updated successfully

That’s cool, but now I need to apply it to >500 targets, right?  Let’s build the CLI statement from a SQL query against the SYSMAN schema:

SELECT DISTINCT
’emcli set_target_property_value -property_records=”‘
|| target_name
|| ‘:’
|| target_type
|| ‘:Product Type:OracleRAC”;’
FROM sysman.mgmt$target_properties
WHERE target_type LIKE ‘rac_database’
AND target_name NOT IN ( SELECT target_name
FROM sysman.mgmt$target_properties
WHERE property_name = ‘udtp_1’
AND property_value = ‘OracleRAC’ );

Notice that the query is filtered by the property_value (OracleRAC) and a property_name of udtp_1!   Oracle has assigned a generic-looking name to our custom user defined target property name, just as they use orcl_gtp* for OEM-defined properties.  These abstractions are only used in the repository and within the OEM application itself.  The user-readable property name appears in the console.

Test the new property by running the CLI command for a single target and then execute the subquery  (containing the udtp_1 property_name above) against the OMR.   Confirm that it’s performing as expected by looking up the target’s properties in the console.

Hang onto that SQL query after your deployment to periodically label new targets as they’re added to your environment.  I have several SQL queries like this posted to a single Information Publisher report (surprise!) that I mail to myself nightly.  You can also set up an OEM Job to execute the CLI commands for you.

 

OEM Preferred Connect Strings

Tags

, , , ,

You can specify a connection string for database targets in two ways:  fill in the blanks and let OEM create the connection string (my preference) or paste a Preferred Connect String on the bottom of the page.

dedicatedserver_connectstring01

Our default setting in our shop is to use multi-threaded servers. On the rare occasion when we need to change if, we paste the connect string from tnsnames.ora in place and add the SERVER=DEDICATED parameter.

Some things to keep in mind:

  • The Preferred Connect String takes precedence over strings OEM might generate from the individual entries
  • The string should start with “DESCRIPTION=”
  • Keep those darn () straight.  The easiest way to do that is to paste the string into an editor that supports syntax highlighting

 

How to Share OEM Named Credentials

Tags

, , , , , , ,

Named Credentials

Named credentials (NC) are an excellent way to support the work performed by OEM users/administrators without requiring them to actually know key passwords. Named credentials also provide a single location to update when the passwords change.

The Problem

Named credentials can only be shared with individual users/administrators inside OEM. You can’t grant the privilege to an EM role and expect the privilege to propagate the role members like you can with a similar database role.  NC’s are an internal OEM construct and not database objects.

Our need for an automated solution to manage our named credentials arose as we started using OEM for provisioning and patching. We have a group of named credentials owned by our SYSMAN account – some to share and many to use internally. At first I made the grants to individuals as needed through the console but as the number of users increased (yay) so did the overhead associated the named credentials (boo).  More importantly we also needed a quick way to share new named credentials with several EM administrators and revoke access to others.

Solution

This may appear to be a complex solution to this problem, but it consists of four simple parts:

  1. Build a control table in the OMR containing a complete list of SYSMAN-owned named credentials with a flag indicating whether each NC should be shared.
  2. Populate that table from user and NC data stored elsewhere in the SYSMAN schema.
  3. Use SQL to build EM CLI argfiles to grant and revoke access.
  4. Wrap those SQL and CLI commands into a shell script.
  5. Build an OEM Job to perform those updates on a regular basis

Step 1 – Build the Control Table

The first step in managing share Named Credentials is to catalog them and define which will be shared through this process. I created a table named shared_named_credentials in my work-horse SCOOP schema with three columns:

  • CRED_NAME is the name of the credential stored in the OMR
  • GUID is the global unique identifier for the NC
  • SHARED_YN is the flag you set to either share or hide it. As you can see from the DDL, the default value is ‘Y’ – all newly created NC’s will be shared unless you change their flag manually.
CREATE TABLE SCOOP.SHARED_NAMED_CREDENTIALS
 (
   CRED_NAME  VARCHAR2(64 BYTE)          NOT NULL,
   GUID       RAW(16)                    NOT NULL,
   SHARE_YN   CHAR(1 BYTE)               DEFAULT 'Y'
 )
 TABLESPACE MGMT_TABLESPACE
 LOGGING 
 NOCOMPRESS 
 NOCACHE
 MONITORING;
 
 
 CREATE UNIQUE INDEX SCOOP.SHARED_NAMED_CREDENTIALS_PK ON SCOOP.SHARED_NAMED_CREDENTIALS
 (CRED_NAME)
 LOGGING
 TABLESPACE MGMT_TABLESPACE

);
 
 ALTER TABLE SCOOP.SHARED_NAMED_CREDENTIALS ADD (
   CONSTRAINT SHARED_NAMED_CREDENTIALS_PK
   PRIMARY KEY
   (CRED_NAME)
   USING INDEX SCOOP.SHARED_NAMED_CREDENTIALS_PK
   ENABLE VALIDATE);
 
 GRANT INSERT, SELECT ON SCOOP.SHARED_NAMED_CREDENTIALS TO SYSMAN;

nc_blog02_table1.png

Step 2 – Populate the Control Table

Information about Named Credentials is stored in OMR table sysman.em_nc_creds. I try to use views whenever possible but this time we can’t avoid accessing the table directly.

As you can see, it’s a very simple query to gather data for an INSERT statement.

Note:  I’ve used the cred_guid in this model to avoid problems with renamed NC’s.

INSERT INTO scoop.shared_named_credentials
 ( SELECT cred_name,
          cred_guid,
          'Y'
     FROM   sysman.em_nc_creds
     WHERE  cred_owner = 'SYSMAN'
            AND cred_guid NOT IN ( SELECT guid
                                 FROM   scoop.shared_named_credentials ) );
COMMIT;

Step 3 – Generate EM CLI statements

The SQL statements below will be spooled into EM CLI argfiles at run-time. The first statement will generate the GRANT privileges for EM administrators with a specific role (LOCAL_DBA_ROLE in these examples).

SELECT   DISTINCT
         'grant_privs -name="'
         || u.user_name
         || '" -privilege="GET_CREDENTIAL;CRED_NAME='
         || c.cred_name
         || ':CRED_OWNER=SYSMAN";'
         as cli_command
FROM     sysman.gc_user_roles u,
         scoop.shared_named_credentials c,
         sysman.mgmt_priv_grants a
WHERE    u.role_name IN ( 'LOCAL_DBA_ROLE' )
         AND UPPER( c.share_yn ) = 'Y'
ORDER BY cli_command;

The second statement revokes existing NC privileges your users may have received based on the share_yn flag in the control table.

SELECT DISTINCT
       'revoke_privs -name="'
       || a.grantee
       || '" -privilege="GET_CREDENTIAL;CRED_NAME='
       || b.cred_name
       || ':CRED_OWNER=SYSMAN";'
       as cli_command
FROM   sysman.mgmt_priv_grants a,
       scoop.shared_named_credentials b
WHERE  a.priv_name = 'GET_CREDENTIAL'
       AND a.guid = b.guid
       AND a.grantee IN ( SELECT user_name
                          FROM   sysman.gc_user_roles
                          WHERE  role_name IN ( 'LOCAL_DBA_ROLE' ) )
       AND b.share_yn <> 'Y';

Step 4 – Wrap all of it into a shell script

I’m a firm believer in encapsulating as much as possible into shell functions. It makes your run-time procedure easier to read and it segregates specific tasks for analysis and debugging.

nc_script1.png

nc_script2.png

nc_script3.pngnc_script4.png

Output Log

Setting the environment ...

Updating the list of named credentials owned by SYSMAN


0 rows created.


Commit complete.


Creating the argfile to revoke permissions


revoke_privs -name="BOBBY" -privilege="GET_CREDENTIAL;CRED_NAME=NC_AGENT_INSTALL:CRED_OWNER=SYSMAN";
revoke_privs -name="PHIL" -privilege="GET_CREDENTIAL;CRED_NAME=NC_ORACLE:CRED_OWNER=SYSMAN";
revoke_privs -name="MICKEY" -privilege="GET_CREDENTIAL;CRED_NAME=NC_AGENT_INSTALL:CRED_OWNER=SYSMAN";
...

Creating the argfile to grant permissions


grant_privs -name="JERRY" -privilege="GET_CREDENTIAL;CRED_NAME=NC_SHARED_ORACLE:CRED_OWNER=SYSMAN";
grant_privs -name="JERRY" -privilege="GET_CREDENTIAL;CRED_NAME=NC_SHARED_ORACLE_ROOT:CRED_OWNER=SYSMAN";
grant_privs -name="PHIL" -privilege="GET_CREDENTIAL;CRED_NAME=NC_SHARED_ORACLE:CRED_OWNER=SYSMAN";
grant_privs -name="PHIL" -privilege="GET_CREDENTIAL;CRED_NAME=NC_SHARED_ORACLE_ROOT:CRED_OWNER=SYSMAN";
grant_privs -name="BRENT" -privilege="GET_CREDENTIAL;CRED_NAME=NC_SHARED_ORACLE_ROOT:CRED_OWNER=SYSMAN";
...

Login successful
Synchronized successfully

Applying grants


Privileges granted to user/role "JERRY" successfully
Privileges granted to user/role "JERRY" successfully
Privileges granted to user/role "PHIL" successfully
Privileges granted to user/role "PHIL" successfully
Privileges granted to user/role "BRENT" successfully
...

Revoking older grants


Privileges revoked from user/role "BOBBY" successfully
Privileges revoked from user/role "PHIL" successfully
Privileges revoked from user/role "MICKEY" successfully
...

Logout successful

Current grants


GRANTEE CRED_NAME
-------------------------------- --------------------------------
JERRY NC_SHARED_ORACLE
JERRY NC_SHARED_ORACLE_ROOT
PHIL NC_SHARED_ORACLE
PHIL NC_SHARED_ORACLE_ROOT
BILL NC_SHARED_ORACLE
BILL NC_SHARED_ORACLE_ROOT
MICKEY NC_SHARED_ORACLE
MICKEY NC_SHARED_ORACLE_ROOT
...

Step 6 – Deploy Using OEM Jobs

The Job development and deployment process is straight-forward.  The heavy lifting was already done with the shell script.

General Tab

There are four essential elements to complete on this screen

  1. Give the Job a name.
  2. Add a useful description.
  3. Select Target Type of Host
  4. Pick the host where the job will be executed.  The shell script requires SQL+ and EMCLI, both of which are installed on your management servers, so it makes sense to run the job on an OMS.

nc_blog02_job1.png

Parameters Tab

  1. Select Script as the Command Type.
  2. Paste the script into the OS Script box.  You can edit this script after the job has been saved when required (unlike Information Publisher reports).
  3. Name the Interpreter to be used

nc_blog02_job2.png

Credentials Tab

It may seems incestuous to use Named Credentials to manage Named Credentials.  If you feel that way you need to get a hobby.

Select the NC for the operating system account that owns your MW home.

nc_blog02_job3.png

Schedule Tab

I have two versions of this Job.  One is named simply UPDATE_NAMED_CREDENTIALS and the other has _IMMEDIATE tacked on to its name.  The only difference between the two is their schedule.  I kick off the IMMEDIATE job right after I make any change to SYSMAN’s NC’s.  The other one runs early every morning, local time.

nc_blog02_job4.png

Access Tab

You can grant permissions on this job to other OEM administrators.  I may do that someday but not right now.

 

Summary

There is some set-up and analysis required before this will work.  You need to build and populate the control table.  Before your first deployment job runs you’ll probably want to set the SHARE_YN flags.  Since the script handles both grants and revokes there is no harm in running the job a couple of times to get things set the way you want.

In this post I avoided specifics on two important elements:

  • You’ll need to decide how to manage the connect strings within the shell script.  I showed the simplest way in my illustrations.  The SYSMAN password is stored inside the job definition which is probably stored as a CLOB in the OMR so the risk is contained.  You might explore alternatives like passing variables into the script via the Job control system.
  • We grant a specific home-built OEM role for the administrators that will be using OEM for provisioning and patching.  You may choose to simply share these NC’s with all of your EM Administrators/users or base grants on existing OEM roles.  It’s your choice.

Scalability

If/when we decide to expand this functionality to another set of EM Admins (if we choose to maintain another set of NC’s to share with DBAs from the Command center, for instance) another Y/N column could be added to the control table  and the SQL scripts updated to match.