How to learn all access permissions granted to a user group
book
Article ID: 109995
calendar_today
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
Additional Information
I have tested this in both Oracle and SQL Server, and it works for both types of database.