How to list all access permissions for a group across projects, states and processes
Article ID: 126294
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?
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
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
Feedback
Yes
No
Powered by
