Harvest SQL query help

Document ID : KB000108565
Last Modified Date : 08/08/2018
Show Technical Document Details
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
Instructions:
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:
User-added image
 
These levels of access are described in the Administrator Guide, in the “Controlling Object Access” chapter:
 
ProjectView*View project and lifecycle, view views, and view packages, package groups, and review requests.
ProjectUpdate*Edit project, edit, and view lifecycle (states and processes), maintain baseline.
ProjectSecureGrant access rights to project.
ProjectUseView 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:
User-added 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