How to learn all access permissions granted to a user group

Issue/Introduction:

We are performing an audit and would like to get a listing of all access permissions granted to a specific user group across all projects, states and processes. How can this be done?

Environment:

CA Harvest SCM all versions and platforms

Resolution:

This query will give you all access permissions – everything but access to folders and files in the repository for a specified user group. You would specify which user group 2 lines up from the bottom

SELECT TYPE, LVL, ENVIRONMENTNAME, STATENAME, PROCESSNAME, USERGROUPNAME
FROM (
SELECT 'HARVEST' AS TYPE, 'SECURE' AS LVL, NULL AS ENVIRONMENTNAME, NULL AS STATENAME, NULL AS PROCESSNAME, HARUSERGROUP.USERGROUPNAME
FROM HARHARVEST
INNER JOIN HARUSERGROUP ON HARUSERGROUP.USRGRPOBJID = HARHARVEST.USRGRPOBJID
WHERE HARHARVEST.SECUREHARVEST = 'Y'
UNION
SELECT 'HARVEST' AS TYPE, 'ADMIN-USER' AS LVL, NULL AS ENVIRONMENTNAME, NULL AS STATENAME, NULL AS PROCESSNAME, HARUSERGROUP.USERGROUPNAME
FROM HARHARVEST
INNER JOIN HARUSERGROUP ON HARUSERGROUP.USRGRPOBJID = HARHARVEST.USRGRPOBJID
WHERE HARHARVEST.ADMINUSER = 'Y'
UNION
SELECT 'HARVEST' AS TYPE, 'VIEW-USER' AS LVL, NULL AS ENVIRONMENTNAME, NULL AS STATENAME, NULL AS PROCESSNAME, HARUSERGROUP.USERGROUPNAME
FROM HARHARVEST
INNER JOIN HARUSERGROUP ON HARUSERGROUP.USRGRPOBJID = HARHARVEST.USRGRPOBJID
WHERE HARHARVEST.VIEWUSER = 'Y'
UNION
SELECT 'HARVEST' AS TYPE, 'ADMIN-ENVIRONMENT' AS LVL, NULL AS ENVIRONMENTNAME, NULL AS STATENAME, NULL AS PROCESSNAME, HARUSERGROUP.USERGROUPNAME
FROM HARHARVEST
INNER JOIN HARUSERGROUP ON HARUSERGROUP.USRGRPOBJID = HARHARVEST.USRGRPOBJID
WHERE HARHARVEST.ADMINENVIRONMENT = 'Y'
UNION
SELECT 'HARVEST' AS TYPE, 'VIEW-ENVIRONMENT' AS LVL, NULL AS ENVIRONMENTNAME, NULL AS STATENAME, NULL AS PROCESSNAME, HARUSERGROUP.USERGROUPNAME
FROM HARHARVEST
INNER JOIN HARUSERGROUP ON HARUSERGROUP.USRGRPOBJID = HARHARVEST.USRGRPOBJID
WHERE HARHARVEST.VIEWENVIRONMENT = 'Y'
UNION
SELECT 'HARVEST' AS TYPE, 'ADMIN-REPOSITORY' AS LVL, NULL AS ENVIRONMENTNAME, NULL AS STATENAME, NULL AS PROCESSNAME, HARUSERGROUP.USERGROUPNAME
FROM HARHARVEST
INNER JOIN HARUSERGROUP ON HARUSERGROUP.USRGRPOBJID = HARHARVEST.USRGRPOBJID
WHERE HARHARVEST.ADMINREPOSITORY = 'Y'
UNION
SELECT 'HARVEST' AS TYPE, 'VIEW-REPOSITORY' AS LVL, NULL AS ENVIRONMENTNAME, NULL AS STATENAME, NULL AS PROCESSNAME, HARUSERGROUP.USERGROUPNAME
FROM HARHARVEST
INNER JOIN HARUSERGROUP ON HARUSERGROUP.USRGRPOBJID = HARHARVEST.USRGRPOBJID
WHERE HARHARVEST.VIEWREPOSITORY = 'Y'
UNION
SELECT 'HARVEST' AS TYPE, 'ADMIN-FORMTYPE' AS LVL, NULL AS ENVIRONMENTNAME, NULL AS STATENAME, NULL AS PROCESSNAME, HARUSERGROUP.USERGROUPNAME
FROM HARHARVEST
INNER JOIN HARUSERGROUP ON HARUSERGROUP.USRGRPOBJID = HARHARVEST.USRGRPOBJID
WHERE HARHARVEST.ADMINFORMTYPE = 'Y'
UNION
SELECT 'HARVEST' AS TYPE, 'VIEW-FORMTYPE' AS LVL, NULL AS ENVIRONMENTNAME, NULL AS STATENAME, NULL AS PROCESSNAME, HARUSERGROUP.USERGROUPNAME
FROM HARHARVEST
INNER JOIN HARUSERGROUP ON HARUSERGROUP.USRGRPOBJID = HARHARVEST.USRGRPOBJID
WHERE HARHARVEST.VIEWFORMTYPE = 'Y'
UNION
SELECT 'PROJECT' AS TYPE, 'SECURE' AS LVL, HARENVIRONMENT.ENVIRONMENTNAME, NULL AS STATENAME, NULL AS PROCESSNAME, HARUSERGROUP.USERGROUPNAME
FROM HARENVIRONMENT
INNER JOIN HARENVIRONMENTACCESS ON HARENVIRONMENT.ENVOBJID = HARENVIRONMENTACCESS.ENVOBJID
INNER JOIN HARUSERGROUP ON HARENVIRONMENTACCESS.USRGRPOBJID = HARUSERGROUP.USRGRPOBJID
WHERE HARENVIRONMENTACCESS.SECUREACCESS = 'Y'
UNION
SELECT 'PROJECT' AS TYPE, 'UPDATE' AS LVL, HARENVIRONMENT.ENVIRONMENTNAME, NULL AS STATENAME, NULL AS PROCESSNAME, HARUSERGROUP.USERGROUPNAME
FROM HARENVIRONMENT
INNER JOIN HARENVIRONMENTACCESS ON HARENVIRONMENT.ENVOBJID = HARENVIRONMENTACCESS.ENVOBJID
INNER JOIN HARUSERGROUP ON HARENVIRONMENTACCESS.USRGRPOBJID = HARUSERGROUP.USRGRPOBJID
WHERE HARENVIRONMENTACCESS.UPDATEACCESS = 'Y'
UNION
SELECT 'PROJECT' AS TYPE, 'USE' AS LVL, HARENVIRONMENT.ENVIRONMENTNAME, NULL AS STATENAME, NULL AS PROCESSNAME, HARUSERGROUP.USERGROUPNAME
FROM HARENVIRONMENT
INNER JOIN HARENVIRONMENTACCESS ON HARENVIRONMENT.ENVOBJID = HARENVIRONMENTACCESS.ENVOBJID
INNER JOIN HARUSERGROUP ON HARENVIRONMENTACCESS.USRGRPOBJID = HARUSERGROUP.USRGRPOBJID
WHERE HARENVIRONMENTACCESS.EXECUTEACCESS = 'Y'
UNION
SELECT 'PROJECT' AS TYPE, 'VIEW' AS LVL, HARENVIRONMENT.ENVIRONMENTNAME, NULL AS STATENAME, NULL AS PROCESSNAME, HARUSERGROUP.USERGROUPNAME
FROM HARENVIRONMENT
INNER JOIN HARENVIRONMENTACCESS ON HARENVIRONMENT.ENVOBJID = HARENVIRONMENTACCESS.ENVOBJID
INNER JOIN HARUSERGROUP ON HARENVIRONMENTACCESS.USRGRPOBJID = HARUSERGROUP.USRGRPOBJID
WHERE HARENVIRONMENTACCESS.VIEWACCESS = 'Y'
UNION
SELECT 'STATE' as TYPE, 'UPDATE' as LVL, HARENVIRONMENT.ENVIRONMENTNAME, HARSTATE.STATENAME, NULL AS PROCESSNAME, HARUSERGROUP.USERGROUPNAME
FROM HARENVIRONMENT
INNER JOIN HARSTATE ON HARENVIRONMENT.ENVOBJID = HARSTATE.ENVOBJID
INNER JOIN HARSTATEACCESS ON HARSTATE.STATEOBJID = HARSTATEACCESS.STATEOBJID
INNER JOIN HARUSERGROUP ON HARUSERGROUP.USRGRPOBJID = HARSTATEACCESS.USRGRPOBJID
WHERE HARSTATEACCESS.UPDATEACCESS = 'Y'
UNION
SELECT 'STATE' as TYPE, 'UPDATE' as LVL, HARENVIRONMENT.ENVIRONMENTNAME, HARSTATE.STATENAME, NULL AS PROCESSNAME, HARUSERGROUP.USERGROUPNAME
FROM HARENVIRONMENT
INNER JOIN HARSTATE ON HARENVIRONMENT.ENVOBJID = HARSTATE.ENVOBJID
INNER JOIN HARSTATEACCESS ON HARSTATE.STATEOBJID = HARSTATEACCESS.STATEOBJID
INNER JOIN HARUSERGROUP ON HARUSERGROUP.USRGRPOBJID = HARSTATEACCESS.USRGRPOBJID
WHERE HARSTATEACCESS.UPDATEPKGACCESS = 'Y'
UNION
SELECT 'PROCESS' as TYPE, 'EXECUTE' as LVL, HARENVIRONMENT.ENVIRONMENTNAME, HARSTATE.STATENAME, HARSTATEPROCESS.PROCESSNAME, HARUSERGROUP.USERGROUPNAME
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
WHERE HARSTATEPROCESSACCESS.EXECUTEACCESS = 'Y'
) ALLACCESS
WHERE ALLACCESS.USERGROUPNAME = 'Public' /* <--- User Group name goes here */
ORDER BY ALLACCESS.ENVIRONMENTNAME, ALLACCESS.STATENAME, ALLACCESS.PROCESSNAME, ALLACCESS.TYPE, ALLACCESS.LVL

Additional Information:

I have tested this in both Oracle and SQL Server, and it works for both types of database.

The Harvest command that you can use to execute queries against the database is the “HSQL” command. Here is where you can read more about it: https://docops.ca.com/ca-harvest-scm/13-0/en/command-reference/get-started-with-ca-harvest-scm-commands/hsql-command-rdb-query