How to learn all access permissions granted to a user group

Document ID : KB000109995
Last Modified Date : 08/08/2018
Show Technical Document Details
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
Instructions:
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