Finding all access permissions for a user group

Products

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

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

This query works with SQL Server and with Oracle

Additional Information

The above query works with Oracle and with SQL Server.  You can also use the "hsql" command line utility to run this query without first logging in to SQL Server Management Studio or Sqlplus.  If you want to use hsql to run this query, here are a couple of tips:

• Use the attached txt file version of the query rather than copying and pasting from above.  This is to prevent extra formatting characters controlling how the resolution is displayed from being included in the query.
• Include the "-t" option to produce a tab-delimited result that can then be imported into Excel.

In order to add "ENVISACTIVE" to the result so that you can know which projects are active and which are inactive or lifecycle templates, this adjustment to the query will work:

 

SELECT TYPE, LVL, ENVIRONMENTNAME, ENVISACTIVE, STATENAME, PROCESSNAME, USERGROUPNAME
FROM (
SELECT 'HARVEST' AS TYPE, 'SECURE' AS LVL, NULL AS ENVIRONMENTNAME, NULL AS ENVISACTIVE, 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 ENVISACTIVE, 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 ENVISACTIVE, 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 ENVISACTIVE, 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 ENVISACTIVE, 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 ENVISACTIVE, 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 ENVISACTIVE, 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 ENVISACTIVE, 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 ENVISACTIVE, 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, HARENVIRONMENT.ENVISACTIVE, 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, HARENVIRONMENT.ENVISACTIVE, 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, HARENVIRONMENT.ENVISACTIVE, 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, HARENVIRONMENT.ENVISACTIVE, 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, HARENVIRONMENT.ENVISACTIVE, 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, HARENVIRONMENT.ENVISACTIVE, 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, HARENVIRONMENT.ENVISACTIVE, 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