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.
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.
This may appear to be a complex solution to this problem, but it consists of four simple parts:
- 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.
- Populate that table from user and NC data stored elsewhere in the SYSMAN schema.
- Use SQL to build EM CLI argfiles to grant and revoke access.
- Wrap those SQL and CLI commands into a shell script.
- 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;
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.
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.
There are four essential elements to complete on this screen
- Give the Job a name.
- Add a useful description.
- Select Target Type of Host
- 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.
- Select Script as the Command Type.
- 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).
- Name the Interpreter to be used
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.
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.
You can grant permissions on this job to other OEM administrators. I may do that someday but not right now.
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.
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.