How to learn all access permissions granted to a user group
Article ID: 109995
Updated On:
Products
CA Harvest Software Change Manager - OpenMake Meister
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
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
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
Feedback
Yes
No
Powered by
