Finding which permissions are granted to each user

Products

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

Issue/Introduction

It was noticed users that were members of more than one user group in Harvest.  Why this would be the case and is there a report detailing all the permissions that have been granted to each user?

Environment

Harvest Software Change Manager all versions and platforms

Cause

It is possible for a user to be a member of more than one group.  Harvest provides access levels and permissions based on user groups, so to cover all the needs of a particular user, it might be possible that membership in more than one group would be needed to accommodate those requirements.  

In the Harvest Administrator Tool, you can see which users belong to a particular user group by looking at the "User Groups" tab in the properties window for the user.  The same is true in reverse for user groups.  You can see the users assigned to a group by looking at the properties window for the user group and going to the "Users" tab.  These two places are also where you can make any necessary updates to users and their group memberships.

In the Harvest Administrator Tool, you can get a listing of all access permissions for a project by right clicking on the project name and selecting "Access Report".  In the resulting report you will see listed all the various access levels for all the different objects within the project and which groups have been granted which types of access.  This listing can be exported to a txt or csv file by right clicking in the white space and selecting "Save As".

A description of the various access levels for the different types of objects within a Harvest project can be found here: Control Object Access

Resolution

Here is a SQL query that can produce a report of all access permissions that have been granted to each user in the system across all projects:

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