Harvest SQL query help

Products

• CA Harvest Software Change Manager - OpenMake Meister

Issue/Introduction

We have annual audit requirement where in we need to remove some users from particular group using below query 

Query: 
SELECT HARENVIRONMENT.ENVIRONMENTNAME AS Project, 
HARUSERGROUP.USERGROUPNAME AS "Group", 
HARUSER.USERNAME AS username, 
HARUSER.REALNAME AS RealName, 
HARUSER.LASTLOGIN, 
HARUSERDATA.ACCOUNTDISABLED, 
HARUSERDATA.ACCOUNTLOCKED, 
case 
when HARUSERDATA.ACCOUNTDISABLED='N' then 'A' 
when HARUSERDATA.ACCOUNTDISABLED='A' then 'I' 
end as STATUS_IND 
FROM HARENVIRONMENT 
INNER JOIN HARENVIRONMENTACCESS 
ON HARENVIRONMENT.ENVOBJID = HARENVIRONMENTACCESS.ENVOBJID 
INNER JOIN HARUSERGROUP 
ON HARUSERGROUP.USRGRPOBJID = HARENVIRONMENTACCESS.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 HARENVIRONMENT.ENVISACTIVE = 'Y' 
ORDER BY username, "Group", Project 

The issue with above query is : 
For example, revoking ‘Public’ removes a user entirely from the Harvest app because it appears to be the ‘birthright’ access group. While we will leave this group in for now (and add additional verbiage to ensure a reviewer understands that revoking ‘Public’ removes the user and any other groups that they may have opted to maintain), perhaps this group should be excluded in the future (the query would need to exclude it). If then all groups are removed for a user during a review, the user would be removed from the system by default (removing ‘Pubic’ at that time, unless the application has a separate inactive process that looks at users in ‘Public’ only that have not logged in after some preset time and removes them automatically). 

Can you please help us with updating the query where it will exclude public group as it is completely removing the user . we should be able to remove user from only required groups and we need query to generate report excluding public. 

Environment

CA Harvest SCM all versions and platforms

Resolution

The simple answer would be to add it to your “where” clause, like this:
 
WHERE HARENVIRONMENT.ENVISACTIVE = 'Y'
AND HARUSERGROUP.USERGROUPNAME != 'Public'
 
But, if your goal is to prevent certain user from ANY type of access to the project, you would need to, instead, remove the “Public” user group from the project’s access list.
 
Remember that at the Project level there are 4 types of access that can be granted:

<Please see attached file for image>

 
These levels of access are described in the Administrator Guide, in the “Controlling Object Access” chapter:
 

Project	View*	View project and lifecycle, view views, and view packages, package groups, and review requests.
Project	Update*	Edit project, edit, and view lifecycle (states and processes), maintain baseline.
Project	Secure	Grant access rights to project.
Project	Use	View project and lifecycle (states and processes), view views and view packages, view package groups, view review requests, create package groups, create, delete, and update review requests, execute processes (not a sufficient but a necessary requirement for process execution).

 
Now, the second thing to know about your query is how the HARENVIRONMENTACCESS table is built:

<Please see attached file for image>

 
What you’ll find for each combination of project and user group is one row in this table.  The 4 “access” columns represent the 4 types of access with a “Y” or “N” specifying which user group has which type of access.  After you revoke all access for a group (all 4 columns have been set to “N”), the record for that group does not go away.  It remains in the table with all 4 access columns set to “N”.  This will record the fact that at some time in the past this group did have access to this project.  So, to get an accurate picture of who has access to a particular project, this must be taken into account as well.
 
Given all this I recommend that you: 

1. Use the Administrator Tool to remove the “Public” user group from all Project access lists
2. Adjust your query to look more like this:

SELECT HARENVIRONMENT.ENVIRONMENTNAME AS Project,
  HARUSERGROUP.USERGROUPNAME AS "Group",
  HARUSER.USERNAME AS username,
  HARUSER.REALNAME AS RealName,
  HARUSER.LASTLOGIN,
  HARUSERDATA.ACCOUNTDISABLED,
  HARUSERDATA.ACCOUNTLOCKED,
  CASE
    WHEN HARUSERDATA.ACCOUNTDISABLED = 'N' THEN 'A'
    WHEN HARUSERDATA.ACCOUNTDISABLED = 'A' THEN 'I'
  END AS STATUS_IND,
  HARENVIRONMENTACCESS.SECUREACCESS,
  HARENVIRONMENTACCESS.UPDATEACCESS,
  HARENVIRONMENTACCESS.VIEWACCESS,
  HARENVIRONMENTACCESS.EXECUTEACCESS
FROM HARENVIRONMENT
INNER JOIN HARENVIRONMENTACCESS ON HARENVIRONMENT.ENVOBJID = HARENVIRONMENTACCESS.ENVOBJID
INNER JOIN HARUSERGROUP ON HARUSERGROUP.USRGRPOBJID = HARENVIRONMENTACCESS.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 HARENVIRONMENT.ENVISACTIVE = 'Y'
AND   SECUREACCESS||UPDATEACCESS||VIEWACCESS||EXECUTEACCESS LIKE '%Y%'
ORDER BY username, "Group", Project

 
More details on Controlling Object Access in Harvest can be found here: https://docops.ca.com/ca-harvest-scm/13-0/en/administrating/control-object-access