Audit Report Requirement for User Group Permissions

Products

• CA Harvest Software Change Manager
• CA Harvest Software Change Manager - OpenMake Meister

Issue/Introduction

We need to produce a report for our auditors that will show which user groups have been granted permission to execute processes for each of the projects, and the list of users in each user group.

Environment

Release : 13.x, 14.x

Component : CA Harvest Software Change Manager

Resolution

This SQL will accomplish the goal:

SELECT distinct HARENVIRONMENT.ENVIRONMENTNAME,
HARUSERGROUP.USERGROUPNAME,
HARUSER.USERNAME,
HARUSER.REALNAME
FROM HARENVIRONMENT
INNER JOIN HARSTATE ON HARENVIRONMENT.ENVOBJID = HARSTATE.ENVOBJID
INNER JOIN HARSTATEPROCESS ON HARSTATE.STATEOBJID = HARSTATEPROCESS.STATEOBJID
INNER JOIN HARSTATEPROCESSACCESS ON HARSTATEPROCESS.STATEOBJID = HARSTATEPROCESSACCESS.STATEOBJID
AND HARSTATEPROCESS.PROCESSOBJID = HARSTATEPROCESSACCESS.PROCESSOBJID
INNER JOIN HARUSERGROUP ON HARUSERGROUP.USRGRPOBJID = HARSTATEPROCESSACCESS.USRGRPOBJID
INNER JOIN HARUSERSINGROUP ON HARUSERGROUP.USRGRPOBJID = HARUSERSINGROUP.USRGRPOBJID
INNER JOIN HARUSER ON HARUSER.USROBJID = HARUSERSINGROUP.USROBJID
WHERE ENVIRONMENTNAME IN ('Project A', 'Project B', 'Project C')  /* <--- List project names here */
AND USERGROUPNAME != 'Public'
ORDER BY HARENVIRONMENT.ENVIRONMENTNAME,
HARUSERGROUP.USERGROUPNAME,
HARUSER.REALNAME