Named Credential Management in OEM 13c using EM CLI

The Enterprise Manager credential subsystem enables Enterprise Manager administrators to store credentials in a secure manner as preferences or operation credentials. The credentials can then be used to perform various system management activities such as real-time monitoring, patching, provisioning, and other target administrative operations.

In Oracle Enterprise Manager 12c/ 13c (aka OEM) there is such a great facility called “Named Credentials”. This allows someone to create a pair of username/password which can be used afterwards without knowing the actual password. Named credentials can be defined on target (i.e. single database or host) or on global level (available on all targets of specified type).

Here are highlights for my posted article:

1) List Named Credentials
2) Create Named Credentials
- Target Based
- Global
3) Add Grantee to Named Credentials
4) Testing Credentials
5) Modify Existing Named Credentials
6) Delete Named Credentials


Log In to OEM console via EM CLI:

$ export EMCLI_HOME=/u01/app/OEM/13c
$ export PATH=$PATH:$EMCLI_HOME/bin

$ emcli login -username=gmohapatra
Enter password : 

Login successful
$ 

Lets see some commands to view or list targets with different ways:
-- To list all database targets
emcli get_targets targets='%database%'
-- To List all Administrators with specifics columns only
emcli list -resource="Administrators" -columns="USER_NAME,REPOS_ACCOUNT_STATUS"
-- List all Targets for few columns with format emcli list -resource="Targets"  -columns="TARGET_TYPE,TARGET_NAME" -colsize="TARGET_TYPE:25,TARGET_NAME:60" -search="TARGET_TYPE='oracle_database'" 
-- List all Targets for few columns with format for database targets
emcli list -resource="Targets" -columns="TARGET_TYPE,TARGET_NAME" -colsize="TARGET_TYPE:25,TARGET_NAME:60" -search="TARGET_TYPE='oracle_database'" 
-- List all Targets for few columns with format for database targets with condition
emcli list -resource="Targets" -columns="TARGET_TYPE,TARGET_NAME" -colsize="TARGET_TYPE:25,TARGET_NAME:60" -search="TARGET_TYPE='oracle_database'" 
-search="TYPE_QUALIFIER3='DB'" 

1) List Named Credentials:

emcli list -resource=NamedCredentials

emcli list -resource=NamedCredentials | awk '{ print $2 $3}'

emcli list -resource=NamedCredentials -search="CredCreatedDate > '1-Jan-2017 12:37:20 PM'"

emcli list -resource=NamedCredentials -columns="CredName,CredOwner"

emcli list -resource=NamedCredentials -format=name:csv > '/path/filename.csv'

Note: timestamp has to be in the format DD-MON-YYYY HH:MI:SS AM/PM
Some resources need a bind variable to be passed to get output. A bind variable is created in the resource and then referenced in the command. For example, this command will list all the default preferred credentials for target type oracle_database.

The below one is the best approch to list Named Credentials:

$ emcli list -resource=NamedCredentials -columns="CredName,CredOwner"
Credential Name                                                 Credential Owner    
NC_ORACLE_D_2017-12-28-054606                                   GMOHAPATRA         
NC_INST_SYS_TESTDB                                              GMOHAPATRA         
NC_GL_TESTDB_SYS                                                SYSMAN             
NC_TESTBOX_SYS                                                  SCOTT          
Rows:4
$ 

If you want to take all Named credentials to be stored as .csv file, then you can use below command:

emcli list -resource=NamedCredentials -format=name:csv > '/u03/Backup/oem/NC/all_nc_05Feb2018.csv'

Similarly, you can use bellow command to list "Prefered Credentials"

Here is an example,

$ emcli list -resource="PreferredCredentialsDefault" -bind="TargetType='oracle_database'" -colsize="SetName:15,TargetType:15"

May be you have requirenment to list all administrator's Named Credentials. At that time you need to connect OMS and run the below query from sysman

user or connect sys as sysdba and fetch the result:

e.g.,
SELECT cred_owner, cred_name, cred_type_name, target_type, user_name,
DECODE (cred_scope, 1, 'Global', 2, 'Target') "cred_scope",
to_char(trunc(cred_created_date),'DD-Mon-RRRR') "created_on" ,
to_char(trunc(cred_updated_date),'DD-Mon-RRRR') "updated_on"
FROM sysman.em_nc_creds
WHERE cred_owner <> '<SYSTEM>' 
ORDER BY 1,2,3;


2) Create Named Credentials :

-- Create Named Credentials as a Target Scope:

To be able to create a named credential, you need to know the target name (unless you create a global credential), target type and credential type associated with the target type. Let’s say I want to create a named credential for my database named “TESTDB”. First I need to login to our EM12c server, and list targets named “TESTDB”:

./emcli create_named_credential -auth_target_type=oracle_database \
-cred_scope=Instance -target_type=oracle_database \
-target_name=TESTDB -cred_type=DBCreds -cred_name=NC_INST_SYS_TESTDB \
-attributes="DBUserName:SYS;DBPassword:Hell0W0rld;DBRole:sysdba" -test

Note: Use \ symbol to continue multiple lines to execute

-- Creating GLOBAL credential

emcli create_named_credential -auth_target_type=oracle_database \
-cred_scope=GLOBAL -target_type=oracle_database \
-target_name=TESTDB -cred_type=DBCreds -cred_name=NC_GL_TESTDB_SYS \
-attributes="DBUserName:SYS;DBPassword:Hell0W0rld;DBRole:sysdba" -test

Error:

Provide -test_target_name and -test_target_type to test global named credential.

Fix: chnage -test to -test_target_name=Global

emcli create_named_credential -auth_target_type=oracle_database \
-cred_scope=GLOBAL -target_type=oracle_database \
-target_name=TESTDB -cred_type=DBCreds -cred_name=NC_GL_TESTDB_SYS \
-attributes="DBUserName:SYS;DBPassword:Hell0W0rld;DBRole:sysdba" -test_target_name=Global

Credential NC_GL_TESTDB_SYS created.

Note: Use target_type=Cluster Database for RAC environments

-- creating Named Credential with passing a properties file.

$ cat named.txt

auth_target_type=oracle_database
cred_scope=Instance
target_type=oracle_database
target_name=TESTDB
cred_type=DBCreds
cred_name=TEST_CRED
attributes=DBUserName:SYS;DBPassword:yourpassword;DBRole:sysdba

./emcli create_named_credential -properties_file=named.txt

3) Add Grantee to Named Credentials:

-- Add Grantee to existing named credential

$ emcli grant_privs -name="SCOTT" -privilege="GET_CREDENTIAL;CRED_NAME=NC_GL_TESTDB_SYS:CRED_OWNER=GMOHAPATRA"
Privileges granted to user/role "SCOTT" successfully
$ 

-- Add to a group like DBA_ADMIN_GROUP

$ emcli grant_privs -name="DBA_ADMIN_GROUP" -privilege="GET_CREDENTIAL;CRED_NAME=NC_GL_TESTDB_SYS:CRED_OWNER=GMOHAPATRA"
Privileges granted to user/role "DBA_ADMIN_GROUP" successfully
$

--To grant read access to all users who are in the group “SEC_USER_GROUP” execute the following statement:

emcli grant_privs -name="SEC_USER_GROUP" -privilege="GET_CREDENTIAL;CRED_NAME=<credential_name>:CRED_OWNER=Credential owner"

--To grant full access to the users in the group “My_SEC_ADMIN”you can use the following:

emcli grant_privs -name="SEC_USER_GROUP" -privilege="FULL_CREDENTIAL;CRED_NAME=<credential_name>:CRED_OWNER=Credential owner"

Of course there is a lot more that can be achieved on this subject.

-- Access on named Credential: Predefine access

FULL_CREDENTIAL
EDIT_CREDENTIAL
GET_CREDENTIAL
CREATE_CREDENTIAL

-- To Revoke access

emcli revoke_privs -name="SCOTT" -privilege="FULL_CREDENTIAL;CRED_NAME=NC_DBSNMP_GLOBAL:CRED_OWNER=GMOHAPATRA"

e.g.,

$ emcli revoke_privs -name="SCOTT" -privilege="FULL_CREDENTIAL;CRED_NAME=NC_DBSNMP_GLOBAL:CRED_OWNER=GMOHAPATRA"
Privileges revoked from user/role "SCOTT" successfully
$ 

4) Testing Credentials:

The following command can be used to test named credentials.

emcli test_named_credential -cred_names="NC_INST_SYS_TESTDB" -target_type="oracle_database" -target_name="TESTDB"

5) Modify Existing Named Credentials;

modify_named_credential

This command modifies the settings of a named credential.  Only specify the particular option you wish to modify.

emcli modify_named_credential
-cred_name=NC_OS_EXAM01
-attributes=”HostPassword:Tiger”

emcli modify_named_credential -cred_name=NC_INST_SYS_TESTDB2 -attributes="DBUserName:SYS;DBPassword:pass123word;DBRole:sysdba" -test

More on Grants and Privilleges:

Imagine we have several administrators who only consume the Named Credentials. If a new colleague arrives, he has to be granted use ‘View’ rights on all (maybe a lot) Named Credentials. Same applies if a new Named Credential is added to the pool. It has to be granted to all admins. Or the other way around: the current admin leaves his job: all change rights on all Named Credentials have to be changed…. This calls for the usage of a group functionality.

This functionality is  not available in the GUI of OEM, neither explained in the manuals (at least I couldn’t find it). Luckely we have EMCLI (Enterprise Manager Command Line Interface), giving a lot more functionality then the GUI does. And since it is command line, we can also script against it. Installation instructions for EMCLI can be found in your OEM instance: Navigate to Setup > Command Line Interface…

One small downside on this construction is the fact that the admins can not be ‘Super Administrator’ anymore, the will have be ‘demoted’ to ‘Admin’.

What we will do is:

Create required accounts and roles in OEM
Create a user who will be owner of all named credentials (it’s pure administrative)
List all named credentials
Grant access to the named credentials to the roles
Grant the appropriate roles to the administrators
Login in the OEM console using an account with super administrator privileges (i.e. sysman).

Navigate to Setup > Security > Roles and click “Create”. Give this role the name “MY_SEC_ADMIN”, Click “Review” and then “Finish”
Navigate to Setup > Security > Roles and click “Create”. Give this role the name “SEC_USER_GROUP”, Click “Review” and then “Finish”
Navigate to Setup > Security > Roles and click “Create”. Give this role the name “MY_PUBLIC”, click “Next”, grant all privileges you might need  and continue until “Finish”
Navigate to Setup > Security > Administrators  and click “Create”. Give this user the name “Credential_owner”, select “Superadministrator”, click “Review” and then “Finish”

-- Create DBA_ADMIN_GROUP:

Navigate to Setup > Security > Roles and click “Create”. Give this role the name “DBA_ADMIN_GROUP”, click “Next”, grant all privileges you might need  and continue until “Finish”

Logout from OEM console, and login again using the newly created account Credential_owner

Navigate to Setup > Security > Named Credentials
Create a new named credential

e.g.,

emcli grant_privs -name="DBA_ADMIN_GROUP" -privilege="GET_CREDENTIAL;CRED_NAME=NC_GL_TESTDB_SYS:CRED_OWNER=GMOHAPATRA"

Here,  DBA_ADMIN_GROUP -- is a group

$ emcli grant_privs -name="SCOTT" -privilege="GET_CREDENTIAL;CRED_NAME=NC_DBSNMP_GLOBAL:CRED_OWNER=GMOHAPATRA"
Privileges granted to user/role "SCOTT" successfully
$

Here, The administrator can view or use the credential
emcli grant_privs -name="SCOTT" -privilege="GET_CREDENTIAL;CRED_NAME=NC_DBSNMP_GLOBAL:CRED_OWNER=GMOHAPATRA" 

e.g., Giving FULL access on NC

emcli grant_privs -name="SCOTT" -privilege="FULL_CREDENTIAL;CRED_NAME=NC_GL_TESTDB_SYS:CRED_OWNER=GMOHAPATRA"

$ emcli grant_privs -name="SCOTT" -privilege="FULL_CREDENTIAL;CRED_NAME=NC_DBSNMP_GLOBAL:CRED_OWNER=GMOHAPATRA"
Privileges granted to user/role "SCOTT" successfully
$

Here, Admin can view,edit and drop the NC also.

The NC owner can give the above access to any admin but not other owner's NC. It will throw error like below example:
e.g.

$ emcli grant_privs -name="SCOTT" -privilege="FULL_CREDENTIAL;CRED_NAME=NC_GL_TESTDB_SYS:CRED_OWNER=SYSMAN"
Error: Insufficient privileges: "GMOHAPATRA"
$

-- To Revoke access

emcli revoke_privs -name="SCOTT" -privilege="FULL_CREDENTIAL;CRED_NAME=NC_DBSNMP_GLOBAL:CRED_OWNER=GMOHAPATRA"

e.g.,

$ emcli revoke_privs -name="SCOTT" -privilege="FULL_CREDENTIAL;CRED_NAME=NC_DBSNMP_GLOBAL:CRED_OWNER=GMOHAPATRA"
Privileges revoked from user/role "SCOTT" successfully
$ 

6) Delete Named Credentials

To Delete any named credential, you should have FULL access on that NC or you should be owner of that NC.

$ emcli delete_named_credential -cred_owner='GMOHAPATRA' -cred_name='NC_DBSNMP_GLOBAL'
Credential deleted.
$