How to list all access permissions for a group across projects, states and processes
book
Article ID: 126294
calendar_today
Updated On:
Products
CA Harvest Software Change Manager - OpenMake Meister
Issue/Introduction
The haccess command line utility provides a form of this, but not for all 3 categories in one report. Also, in the Administrator Tool, there is a "User Groups" report that will provide a list of all users for each user group.
Is there any kind of a SQL query that will give us a list of users, what user groups they are in and the Harvest projects that those user groups have access to?
Environment
CA Harvest SCM - all versions. These queries should work for both Oracle and SQL Server
Resolution
For a listing of all access permissions by user group across all projects, states and processes, this SQL should provide the answer:
SELECT * FROM ( SELECT 'Project' AS "LEVEL", HARENVIRONMENT.ENVIRONMENTNAME, ' ' AS STATENAME, ' ' AS PROCESSNAME, ' ' AS PROCESSTYPE, HARUSERGROUP.USERGROUPNAME, HARENVIRONMENTACCESS.SECUREACCESS AS "PROJECT SECURE", HARENVIRONMENTACCESS.UPDATEACCESS AS "PROJECT UPDATE", HARENVIRONMENTACCESS.EXECUTEACCESS AS "PROJECT USE", HARENVIRONMENTACCESS.VIEWACCESS AS "PROJECT VIEW", ' ' AS "STATE UPDATE", ' ' AS "STATE UPDATEPACKAGE", ' ' AS "PROCESS EXECUTE" FROM HARENVIRONMENT INNER JOIN HARENVIRONMENTACCESS ON HARENVIRONMENT.ENVOBJID = HARENVIRONMENTACCESS.ENVOBJID INNER JOIN HARUSERGROUP ON HARENVIRONMENTACCESS.USRGRPOBJID = HARUSERGROUP.USRGRPOBJID INNER JOIN HARUSERSINGROUP ON HARUSERGROUP.USRGRPOBJID = HARUSERSINGROUP.USRGRPOBJID UNION SELECT 'State' AS "LEVEL", HARENVIRONMENT.ENVIRONMENTNAME, HARSTATE.STATENAME, ' ' AS PROCESSNAME, ' ' AS PROCESSTYPE, HARUSERGROUP.USERGROUPNAME, ' ' AS "PROJECT SECURE", ' ' AS "PROJECT UPDATE", ' ' AS "PROJECT USE", ' ' AS "PROJECT VIEW", HARSTATEACCESS.UPDATEACCESS AS "STATE UPDATE", HARSTATEACCESS.UPDATEPKGACCESS AS "STATE UPDATEPACKAGE", ' ' AS "PROCESS EXECUTE" FROM HARENVIRONMENT INNER JOIN HARSTATE ON HARENVIRONMENT.ENVOBJID = HARSTATE.ENVOBJID INNER JOIN HARSTATEACCESS ON HARSTATE.STATEOBJID = HARSTATEACCESS.STATEOBJID INNER JOIN HARUSERGROUP ON HARSTATEACCESS.USRGRPOBJID = HARUSERGROUP.USRGRPOBJID INNER JOIN HARUSERSINGROUP ON HARUSERGROUP.USRGRPOBJID = HARUSERSINGROUP.USRGRPOBJID WHERE ((UPDATEACCESS = 'Y') OR (UPDATEPKGACCESS = 'Y')) UNION SELECT 'Process' AS "LEVEL", HARENVIRONMENT.ENVIRONMENTNAME, HARSTATE.STATENAME, HARSTATEPROCESS.PROCESSNAME, HARSTATEPROCESS.PROCESSTYPE, HARUSERGROUP.USERGROUPNAME, ' ' AS "PROJECT SECURE", ' ' AS "PROJECT UPDATE", ' ' AS "PROJECT USE", ' ' AS "PROJECT VIEW", ' ' AS "STATE UPDATE", ' ' AS "STATE UPDATEPACKAGE", HARSTATEPROCESSACCESS.EXECUTEACCESS AS "PROCESS EXECUTE" 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 INNER JOIN HARUSERSINGROUP ON HARUSERGROUP.USRGRPOBJID = HARUSERSINGROUP.USRGRPOBJID WHERE HARSTATEPROCESSACCESS.EXECUTEACCESS = 'Y' ) ALLRESULTS ORDER BY USERGROUPNAME, ENVIRONMENTNAME, STATENAME, PROCESSNAME
For the same by individual user and group, this SQL should provide the answer:
SELECT * FROM ( SELECT HARENVIRONMENT.ENVIRONMENTNAME, ' ' AS STATENAME, ' ' AS PROCESSNAME, HARUSERGROUP.USERGROUPNAME, HARUSER.USERNAME, HARUSER.REALNAME, HARUSER.LASTLOGIN, HARUSERDATA.ACCOUNTDISABLED, HARUSERDATA.ACCOUNTLOCKED, CASE WHEN HARUSERDATA.ACCOUNTDISABLED = 'N' THEN 'A' WHEN HARUSERDATA.ACCOUNTDISABLED = 'A' THEN 'I' END AS STATUS_IND, 'PROJECT' AS "TYPE", HARENVIRONMENTACCESS.SECUREACCESS AS "PROJECT SECURE", HARENVIRONMENTACCESS.UPDATEACCESS AS "PROJECT UPDATE", HARENVIRONMENTACCESS.EXECUTEACCESS AS "PROJECT USE", HARENVIRONMENTACCESS.VIEWACCESS AS "PROJECT VIEW", ' ' AS "STATE UPDATE", ' ' AS "STATE UPDATEPACKAGE", ' ' AS "PROCESS EXECUTE" FROM HARENVIRONMENT INNER JOIN HARENVIRONMENTACCESS ON HARENVIRONMENT.ENVOBJID = HARENVIRONMENTACCESS.ENVOBJID INNER JOIN HARUSERGROUP ON HARENVIRONMENTACCESS.USRGRPOBJID = HARUSERGROUP.USRGRPOBJID INNER JOIN HARUSERSINGROUP ON HARUSERGROUP.USRGRPOBJID = HARUSERSINGROUP.USRGRPOBJID INNER JOIN HARUSER ON HARUSER.USROBJID = HARUSERSINGROUP.USROBJID INNER JOIN HARUSERDATA ON HARUSER.USROBJID = HARUSERDATA.USROBJID WHERE (HARENVIRONMENTACCESS.SECUREACCESS = 'Y') OR (HARENVIRONMENTACCESS.UPDATEACCESS = 'Y') OR (HARENVIRONMENTACCESS.VIEWACCESS = 'Y') OR (HARENVIRONMENTACCESS.EXECUTEACCESS = 'Y') UNION SELECT HARENVIRONMENT.ENVIRONMENTNAME, HARSTATE.STATENAME, ' ' AS PROCESSNAME, HARUSERGROUP.USERGROUPNAME, HARUSER.USERNAME, HARUSER.REALNAME, HARUSER.LASTLOGIN, HARUSERDATA.ACCOUNTDISABLED, HARUSERDATA.ACCOUNTLOCKED, CASE WHEN HARUSERDATA.ACCOUNTDISABLED = 'N' THEN 'A' WHEN HARUSERDATA.ACCOUNTDISABLED = 'A' THEN 'I' END AS STATUS_IND, 'STATE' AS "TYPE", ' ' AS "PROJECT SECURE", ' ' AS "PROJECT UPDATE", ' ' AS "PROJECT USE", ' ' AS "PROJECT VIEW", HARSTATEACCESS.UPDATEACCESS AS "STATE UPDATE", HARSTATEACCESS.UPDATEPKGACCESS AS "STATE UPDATEPACKAGE", ' ' AS "PROCESS EXECUTE" FROM HARENVIRONMENT INNER JOIN HARSTATE ON HARENVIRONMENT.ENVOBJID = HARSTATE.ENVOBJID INNER JOIN HARSTATEACCESS ON HARSTATE.STATEOBJID = HARSTATEACCESS.STATEOBJID INNER JOIN HARUSERGROUP ON HARSTATEACCESS.USRGRPOBJID = HARUSERGROUP.USRGRPOBJID INNER JOIN HARUSERSINGROUP ON HARUSERGROUP.USRGRPOBJID = HARUSERSINGROUP.USRGRPOBJID INNER JOIN HARUSER ON HARUSER.USROBJID = HARUSERSINGROUP.USROBJID INNER JOIN HARUSERDATA ON HARUSER.USROBJID = HARUSERDATA.USROBJID WHERE ((UPDATEACCESS = 'Y') OR (UPDATEPKGACCESS = 'Y')) UNION SELECT HARENVIRONMENT.ENVIRONMENTNAME, HARSTATE.STATENAME, HARSTATEPROCESS.PROCESSNAME, HARUSERGROUP.USERGROUPNAME, HARUSER.USERNAME, HARUSER.REALNAME, HARUSER.LASTLOGIN, HARUSERDATA.ACCOUNTDISABLED, HARUSERDATA.ACCOUNTLOCKED, CASE WHEN HARUSERDATA.ACCOUNTDISABLED = 'N' THEN 'A' WHEN HARUSERDATA.ACCOUNTDISABLED = 'A' THEN 'I' END AS STATUS_IND, 'PROCESS' AS "TYPE", ' ' AS "PROJECT SECURE", ' ' AS "PROJECT UPDATE", ' ' AS "PROJECT USE", ' ' AS "PROJECT VIEW", ' ' AS "STATE UPDATE", ' ' AS "STATE UPDATEPACKAGE", HARSTATEPROCESSACCESS.EXECUTEACCESS AS "PROCESS EXECUTE" 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 INNER JOIN HARUSERSINGROUP ON HARUSERGROUP.USRGRPOBJID = HARUSERSINGROUP.USRGRPOBJID INNER JOIN HARUSER ON HARUSER.USROBJID = HARUSERSINGROUP.USROBJID INNER JOIN HARUSERDATA ON HARUSER.USROBJID = HARUSERDATA.USROBJID WHERE HARSTATEPROCESSACCESS.EXECUTEACCESS = 'Y' ) ALLRESULTS ORDER BY USERNAME, USERGROUPNAME, ENVIRONMENTNAME, STATENAME, PROCESSNAME