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.