We have annual audit requirement where in we need to remove some users from particular group using below query
SELECT HARENVIRONMENT.ENVIRONMENTNAME AS Project,
HARUSERGROUP.USERGROUPNAME AS "Group",
HARUSER.USERNAME AS username,
HARUSER.REALNAME AS RealName,
when HARUSERDATA.ACCOUNTDISABLED='N' then 'A'
when HARUSERDATA.ACCOUNTDISABLED='A' then 'I'
end as STATUS_IND
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.
CA Harvest SCM all versions and platforms