Manager Sav role is getting removed and adding back

[sandeepgudipudi]

I created analytics to assign and revoke Manager sav role but if manager reportee is inactive the analytics query revoking access and adding back the access. Can you help with query even if reportee is inactive it should not revoke access.

Below is the query to revoke access

select distinct u1.USERNAME, u1.FIRSTNAME,u1.LASTNAME,u1.EMAIL, case when u1.STATUSKEY=1 then 'Active' when u1.STATUSKEY=0 then 'Inactive' end as UserStatus, u4.NAME,u5.ENDPOINTNAME,u6.ENTITLEMENTNAME,u7.ENTITLEMENT_VALUE, 'Deprovision Access' as Default_Action_For_Analytics, u7.ENTITLEMENT_VALUEKEY as entvaluekey, u4.ACCOUNTKEY as acctKey, u4.NAME as accName, u1.USERKEY as userKey from users u1 join user_accounts u3 on u1.USERKEY=u3.USERKEY join accounts u4 on u3.ACCOUNTKEY=u4.ACCOUNTKEY join endpoints u5 on (u4.ENDPOINTKEY=u5.ENDPOINTKEY and u5.ENDPOINTNAME ='Saviynt') join entitlement_types u6 on (u5.ENDPOINTKEY=u6.ENDPOINTKEY and u6.EntitlementName ='SAVRoles') join entitlement_values u7 on (u6.ENTITLEMENTTYPEKEY=u7.ENTITLEMENTTYPEKEY and u7.entitlement_Value ='ROLE_XXXX_MANAGER') where not exists (SELECT MANAGER FROM users where manager = u1.userkey) and u1.userkey in ( select usr1.USERKEY from user_savroles usr1 join savroles usr2 on (usr1.ROLEKEY=usr2.ROLEKEY and usr2.RoleName = 'ROLE_XXXX_MANAGER') );

[sudeshjaiswal]

Hello @sandeepgudipudi,

It’s currently revoking access when there are no active reportees for a manager. If you want to keep the access even if the reportee is inactive, you should remove or modify this condition.

Thanks.

[rushikeshvartak]

• Does it adding access back using query or via user update rule ?

[sandeepgudipudi]

its adding back via query

[rushikeshvartak]

Query does not have action defined  Default_Action_For_Analytics  as provision access. i dont think its added by report. Check source in artasks table 

[sandeepgudipudi]

There is a separate query for provision and deprovision manager sav roles

Problem statement: If manager reportee is inactive the revoke query removes manager sav role and assign query adding back the sav role. Even though manager reportee is inactive still he is a manager and it query should not revoke sav role... it should revoke sav role if manager is inactive only

Query to assign Manager sav role

select distinct u1.USERNAME, u1.FIRSTNAME,u1.LASTNAME,u1.EMAIL, case when u1.STATUSKEY=1 then 'Active' when u1.STATUSKEY=0 then 'Inactive' end as UserStatus, u4.NAME,u5.ENDPOINTNAME,u6.ENTITLEMENTNAME,u7.ENTITLEMENT_VALUE, 'Provision Access' as Default_Action_For_Analytics, u7.ENTITLEMENT_VALUEKEY as entvaluekey, u4.ACCOUNTKEY as acctKey, u4.NAME as accName, u1.USERKEY as userKey from users u1 join user_accounts u3 on u1.USERKEY=u3.USERKEY join accounts u4 on u3.ACCOUNTKEY=u4.ACCOUNTKEY join endpoints u5 on u4.ENDPOINTKEY=u5.ENDPOINTKEY join entitlement_types u6 on u5.ENDPOINTKEY=u6.ENDPOINTKEY join entitlement_values u7 on u6.ENTITLEMENTTYPEKEY=u7.ENTITLEMENTTYPEKEY where u1.userkey in (SELECT DISTINCT MANAGER FROM users) and not exists ( select distinct usr1.USERKEY from user_savroles usr1 join savroles usr2 on usr1.ROLEKEY=usr2.ROLEKEY where usr2.ROLENAME='ROLE_XXXX_MANAGER' and usr1.USERKEY=u1.USERKEY ) and u5.ENDPOINTNAME='Saviynt' and u6.ENTITLEMENTNAME='SAVRoles' and u7.ENTITLEMENT_VALUE='ROLE_XXXX_MANAGER' and u1.statuskey=1 and u7.status=1;

query to revoke access

select distinct u1.USERNAME, u1.FIRSTNAME,u1.LASTNAME,u1.EMAIL, case when u1.STATUSKEY=1 then 'Active' when u1.STATUSKEY=0 then 'Inactive' end as UserStatus, u4.NAME,u5.ENDPOINTNAME,u6.ENTITLEMENTNAME,u7.ENTITLEMENT_VALUE, 'Deprovision Access' as Default_Action_For_Analytics, u7.ENTITLEMENT_VALUEKEY as entvaluekey, u4.ACCOUNTKEY as acctKey, u4.NAME as accName, u1.USERKEY as userKey from users u1 join user_accounts u3 on u1.USERKEY=u3.USERKEY join accounts u4 on u3.ACCOUNTKEY=u4.ACCOUNTKEY join endpoints u5 on (u4.ENDPOINTKEY=u5.ENDPOINTKEY and u5.ENDPOINTNAME ='Saviynt') join entitlement_types u6 on (u5.ENDPOINTKEY=u6.ENDPOINTKEY and u6.EntitlementName ='SAVRoles') join entitlement_values u7 on (u6.ENTITLEMENTTYPEKEY=u7.ENTITLEMENTTYPEKEY and u7.entitlement_Value ='ROLE_XXXX_MANAGER') where not exists (SELECT MANAGER FROM users where manager = u1.userkey) and u1.userkey in ( select usr1.USERKEY from user_savroles usr1 join savroles usr2 on (usr1.ROLEKEY=usr2.ROLEKEY and usr2.RoleName = 'ROLE_XXXX_MANAGER') );

[rushikeshvartak]

Query to Assign Manager "Saviynt" Role

The current query assigns the "Saviynt" role to active managers who do not already have this role. This query seems correct as it stands, but let's ensure it is clear and accurate:

SELECT DISTINCT u1.USERNAME, u1.FIRSTNAME, u1.LASTNAME, u1.EMAIL, CASE WHEN u1.STATUSKEY = 1 THEN 'Active' WHEN u1.STATUSKEY = 0 THEN 'Inactive' END AS UserStatus, u4.NAME, u5.ENDPOINTNAME, u6.ENTITLEMENTNAME, u7.ENTITLEMENT_VALUE, 'Provision Access' AS Default_Action_For_Analytics, u7.ENTITLEMENT_VALUEKEY AS entvaluekey, u4.ACCOUNTKEY AS acctKey, u4.NAME AS accName, u1.USERKEY AS userKey FROM users u1 JOIN user_accounts u3 ON u1.USERKEY = u3.USERKEY JOIN accounts u4 ON u3.ACCOUNTKEY = u4.ACCOUNTKEY JOIN endpoints u5 ON u4.ENDPOINTKEY = u5.ENDPOINTKEY JOIN entitlement_types u6 ON u5.ENDPOINTKEY = u6.ENDPOINTKEY JOIN entitlement_values u7 ON u6.ENTITLEMENTTYPEKEY = u7.ENTITLEMENTTYPEKEY WHERE u1.USERKEY IN (SELECT DISTINCT MANAGER FROM users) AND NOT EXISTS ( SELECT DISTINCT usr1.USERKEY FROM user_savroles usr1 JOIN savroles usr2 ON usr1.ROLEKEY = usr2.ROLEKEY WHERE usr2.ROLENAME = 'ROLE_XXXX_MANAGER' AND usr1.USERKEY = u1.USERKEY ) AND u5.ENDPOINTNAME = 'Saviynt' AND u6.ENTITLEMENTNAME = 'SAVRoles' AND u7.ENTITLEMENT_VALUE = 'ROLE_XXXX_MANAGER' AND u1.STATUSKEY = 1 AND u7.STATUS = 1;

Query to Revoke Access

We need to modify the revoke query to ensure it only revokes the role if the manager themselves is inactive. Here's the revised query:

SELECT DISTINCT u1.USERNAME, u1.FIRSTNAME, u1.LASTNAME, u1.EMAIL, CASE WHEN u1.STATUSKEY = 1 THEN 'Active' WHEN u1.STATUSKEY = 0 THEN 'Inactive' END AS UserStatus, u4.NAME, u5.ENDPOINTNAME, u6.ENTITLEMENTNAME, u7.ENTITLEMENT_VALUE, 'Deprovision Access' AS Default_Action_For_Analytics, u7.ENTITLEMENT_VALUEKEY AS entvaluekey, u4.ACCOUNTKEY AS acctKey, u4.NAME AS accName, u1.USERKEY AS userKey FROM users u1 JOIN user_accounts u3 ON u1.USERKEY = u3.USERKEY JOIN accounts u4 ON u3.ACCOUNTKEY = u4.ACCOUNTKEY JOIN endpoints u5 ON (u4.ENDPOINTKEY = u5.ENDPOINTKEY AND u5.ENDPOINTNAME = 'Saviynt') JOIN entitlement_types u6 ON (u5.ENDPOINTKEY = u6.ENDPOINTKEY AND u6.ENTITLEMENTNAME = 'SAVRoles') JOIN entitlement_values u7 ON (u6.ENTITLEMENTTYPEKEY = u7.ENTITLEMENTTYPEKEY AND u7.ENTITLEMENT_VALUE = 'ROLE_XXXX_MANAGER') WHERE NOT EXISTS ( SELECT 1 FROM users u2 WHERE u2.MANAGER = u1.USERKEY ) AND u1.STATUSKEY = 0 AND u1.USERKEY IN ( SELECT usr1.USERKEY FROM user_savroles usr1 JOIN savroles usr2 ON (usr1.ROLEKEY = usr2.ROLEKEY AND usr2.ROLENAME = 'ROLE_XXXX_MANAGER') );

Key Changes and Rationale

1. Revoke Query Adjustment: The WHERE clause now includes AND u1.STATUSKEY = 0 to ensure we only consider revoking the role if the manager themselves is inactive.
2. Removing Dependency on Reportee Status: The NOT EXISTS subquery in the revoke query checks if the manager has any reportees, but the main condition for revoking the role is based on the manager's status, not the reportee's status.