Automatic removal of user from usergroups after user is offboarded using Saviynt to Saviynt account.

[tejpusal]

When the user is offboarded, his status is becoming inactive and also he is part of user group.

considering user group as an entitlement, we tried to reconcile the usergroups with members of it into Saviynt to Saviynt End point.

Issues:-

1.We were able to reconcile usergroups as entitlements but the account which are part of the user group are not getting reconciled.

2. We tried to check with sav roles and other entitlements of Saviynt to Saviynt end point but all of them are populated with accounts.

Can you please help us resolve this issue or provide any alternate solution for our requirement.

[rushikeshvartak]

Can you provide more context and screenshot. Does User group members needs to be removed ?

[tejpusal]

Thanks for your reply Rushikesh,

The context is:-

When the user is offboarded or his state is inactive, all his entitlements should be removed. But if we consider user groups as entitlements, even they should be removed.

In order to do that, we have reconciled user groups as entitlements of Saviynt to Saviynt end point. The reconciliation was success but the accounts that are part of usergroups are not getting populated in Saviynt to Saviynt end point.

We have checked for other entitlements such as Sav roles but it is populating the accounts of the users that are having the sav roles.

You can see screenshot number 109 as user group and 108 as sav role

[rushikeshvartak]

This looks like your connector issue. Check JSON for REST Connector/ XML for DB connector of Sav4Sav

[tejpusal]

<dataMapping>

    <before-import>

    </before-import>

    <sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >

        <![CDATA[    select distinct u.username as accountname, 'SaviyntToSaviynt' as name, 'SaviyntToSaviynt' as endpointname,u.username as accountcn, 'Role' as attribute,r.ROLE_NAME  as entitlementvalue,u.username as username, u.statuskey as status, u.updatedate, u.username as objectguid from users u, roles r, role_owners rw where u.userkey=rw.USERKEY and r.ROLEKEY=rw.ROLEKEY and r.STATUS=1  union   select distinct u.username as accountname, 'SaviyntToSaviynt' as name, 'SaviyntToSaviynt' as endpointname,u.username as accountcn, 'Rule' as attribute, case when type=0 then concat( h.NAME,' TYPE:BUSINESS') when type=1 then concat( h.NAME,' TYPE:TECHNICAL') when type=2 then concat( h.NAME,' TYPE:USER UPDATE') when type=3 then concat( h.NAME,' TYPE:NEW USER') when type=4 then concat( h.NAME,' TYPE:REMOVE USER') when type=5 then concat( h.NAME,' TYPE:UPDATE ENTITLEMENT') when type=6 then concat( h.NAME,' TYPE:NEW ENTITLEMENT') when type=7 then concat( h.NAME,' TYPE:REMOVE ENTITLEMENT') when type=8 then concat( h.NAME,' TYPE:REQUEST') end as entitlementvalue, u.username as username, u.statuskey as status, u.updatedate, u.username as objectguid from hanarule h, users u, rule_owners ro where u.USERKEY = ro.USERKEY and h.HANARULEKEY=ro.RULEKEY and h.status=0  union   select distinct u.username as accountname, 'SaviyntToSaviynt' as name, 'SaviyntToSaviynt' as endpointname,u.username as accountcn, 'UserGroup' as attribute, ug.user_groupname as entitlementvalue,u.username as username, u.statuskey as status, u.updatedate,u.username as objectguid from users u, user_groups ug, usergroup_owners ugo where ug.usergroupkey=ugo.USERGROUPKEY and ugo.USERKEY =u.userkey  union   select distinct u.username as accountname, 'SaviyntToSaviynt' as name, 'SaviyntToSaviynt' as endpointname,u.username as accountcn, 'SAVRole' as attribute, s.rolename as entitlementvalue,u.username as username, u.statuskey as status, u.updatedate,u.username as objectguid from users u, user_savroles us, savroles s where u.userkey=us.userkey and us.rolekey=s.rolekey and s.statuskey=1 ;  ]]>

    </sql-query>

    <mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="delete" deleteaccountentitlement="true" addOnlyMode="FALSE"  ifusernotexists="noaction" incrementalcolumn="updatedate" systems="'SaviyntToSaviynt'">

    <mapfield saviyntproperty="accounts.name" sourceproperty="accountname" type="character"></mapfield>

    <mapfield saviyntproperty="securitysystems.systemname" sourceproperty="name" type="character"></mapfield>

    <mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpointname" type="character"></mapfield>

    <mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="attribute" type="character"></mapfield>

    <mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entitlementvalue" type="character"></mapfield>

    <mapfield saviyntproperty="users.username" sourceproperty="username" type="character"></mapfield>

  <mapfield saviyntproperty="accounts.status" sourceproperty="status" type="number"></mapfield>

<mapfield saviyntproperty="accounts.accountID" sourceproperty="accountcn" type="character"></mapfield>

<mapfield saviyntproperty="accounts.displayname" sourceproperty="objectguid" type="character"></mapfield>

    </mapper>

    <after-import description="EMAIL,BATCH,SQL">

    </after-import>

</dataMapping>

The above mentioned is our accountsimport XML and 

<dataMapping>
<before-import>
</before-import>
<sql-query description="This is the Source DB Query">
<![CDATA[ SELECT 'SaviyntToSaviynt' as system, 'SaviyntToSaviynt' as application, 'Role' as attribute, role_name as entitlementvalue,
'admin' as dataowner1, description as description, 1 as status from roles where status=1 union All SELECT 'SaviyntToSaviynt' as system, 'SaviyntToSaviynt' as application, 'Rule' as attribute,case when type=0 then concat(name,' TYPE:BUSINESS') when type=1 then concat(name,' TYPE:TECHNICAL') when type=2 then concat(name,' TYPE:USER UPDATE') when type=3 then concat(name,' TYPE:NEW USER') when type=4 then concat(name,' TYPE:REMOVE USER') when type=5 then concat(name,' TYPE:UPDATE ENTITLEMENT') when type=6 then concat(name,' TYPE:NEW ENTITLEMENT') when type=7 then concat(name,' TYPE:REMOVE ENTITLEMENT') when type=8 then concat(name,' TYPE:REQUEST') end as entitlementvalue, 'admin' as dataowner1, DESCRIPTION as description, 1 as status from hanarule where status=0 union All SELECT 'SaviyntToSaviynt' as system, 'SaviyntToSaviynt' as application, 'UserGroup' as attribute, USER_GROUPNAME as entitlementvalue,
'admin' as dataowner1, USER_GROUPDESCRIPTION as description, 1 as status from user_groups union All SELECT 'SaviyntToSaviynt' as system, 'SaviyntToSaviynt' as application, 'SAVRole' as attribute, rolename as entitlementvalue,
'admin' as dataowner1, roledescription as description, 1 as status from savroles where statuskey=1 ;
]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" deleteentitlementowner="false" systems="'SaviyntToSaviynt'" >
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="system" type="character"></mapfield>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="application" type="character"></mapfield>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="attribute" type="character"></mapfield>
<mapfield saviyntproperty="entitlementvalues.entitlement_value" sourceproperty="entitlementvalue" type="character"></mapfield>
<mapfield saviyntproperty="entitlementvalues.entowner1" sourceproperty="dataowner1" type="character"></mapfield>
<mapfield saviyntproperty="entitlementvalues.entitlement_glossary" sourceproperty="description" type="character"></mapfield>
<mapfield saviyntproperty="entitlementvalues.description" sourceproperty="description" type="character"></mapfield>
<mapfield saviyntproperty="entitlementvalues.status" sourceproperty="status" type="number"></mapfield>
\t</mapper>
<after-import description="EMAIL,BATCH,SQL">
</after-import>

</dataMapping>

This is our entitlementimport XML . We have tired to understand from our end as well. Any observation from your end is highly appreciated.

[rushikeshvartak]

Why your are not using user update rule to replace member / owner in user group during termination or remove/ deprovisioning access of saviynt application?

[tejpusal]

In order to do it, user group should be considered as an entitlement and while creating rules, we are not able to get removal of user from user groups as in our solution, we are not able to take user groups as entitlements

[rushikeshvartak]

Considering you are using sav4sav then you can revoke user group as entitlement which will also remove member from user group

[tejpusal]

We are actually using Sav4Sav and tried mostly every option possible in our dev version 24.2 and we cant head to prod with this as we cant afford messing it up. 

Please reach out to us by 6/10/2024 EOD. It is impacting our plan and we are unable to proceed with our architecture. 

[rushikeshvartak]

<dataMapping>

    <before-import>

    </before-import>

    <sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >

        <![CDATA[    select distinct u.username as accountname, 'SaviyntToSaviynt' as name, 'SaviyntToSaviynt' as endpointname,u.username as accountcn, 'Role' as attribute,r.ROLE_NAME  as entitlementvalue,u.username as username, u.statuskey as status, u.updatedate, u.username as objectguid from users u, roles r, role_owners rw where u.userkey=rw.USERKEY and r.ROLEKEY=rw.ROLEKEY and r.STATUS=1  union   select distinct u.username as accountname, 'SaviyntToSaviynt' as name, 'SaviyntToSaviynt' as endpointname,u.username as accountcn, 'Rule' as attribute, case when type=0 then concat( h.NAME,' TYPE:BUSINESS') when type=1 then concat( h.NAME,' TYPE:TECHNICAL') when type=2 then concat( h.NAME,' TYPE:USER UPDATE') when type=3 then concat( h.NAME,' TYPE:NEW USER') when type=4 then concat( h.NAME,' TYPE:REMOVE USER') when type=5 then concat( h.NAME,' TYPE:UPDATE ENTITLEMENT') when type=6 then concat( h.NAME,' TYPE:NEW ENTITLEMENT') when type=7 then concat( h.NAME,' TYPE:REMOVE ENTITLEMENT') when type=8 then concat( h.NAME,' TYPE:REQUEST') end as entitlementvalue, u.username as username, u.statuskey as status, u.updatedate, u.username as objectguid from hanarule h, users u, rule_owners ro where u.USERKEY = ro.USERKEY and h.HANARULEKEY=ro.RULEKEY and h.status=0  union   select distinct u.username as accountname, 'SaviyntToSaviynt' as name, 'SaviyntToSaviynt' as endpointname,u.username as accountcn, 'UserGroup' as attribute, ug.user_groupname as entitlementvalue,u.username as username, u.statuskey as status, u.updatedate,u.username as objectguid from users u, user_groups ug, usergroup_owners ugo where ug.usergroupkey=ugo.USERGROUPKEY and ugo.USERKEY =u.userkey  and u.statuskey=1 union   select distinct u.username as accountname, 'SaviyntToSaviynt' as name, 'SaviyntToSaviynt' as endpointname,u.username as accountcn, 'SAVRole' as attribute, s.rolename as entitlementvalue,u.username as username, u.statuskey as status, u.updatedate,u.username as objectguid from users u, user_savroles us, savroles s where u.userkey=us.userkey and us.rolekey=s.rolekey and s.statuskey=1 ;  ]]>

    </sql-query>

    <mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="delete" deleteaccountentitlement="true" addOnlyMode="FALSE"  ifusernotexists="noaction" incrementalcolumn="updatedate" systems="'SaviyntToSaviynt'">

    <mapfield saviyntproperty="accounts.name" sourceproperty="accountname" type="character"></mapfield>

    <mapfield saviyntproperty="securitysystems.systemname" sourceproperty="name" type="character"></mapfield>

    <mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpointname" type="character"></mapfield>

    <mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="attribute" type="character"></mapfield>

    <mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entitlementvalue" type="character"></mapfield>

    <mapfield saviyntproperty="users.username" sourceproperty="username" type="character"></mapfield>

  <mapfield saviyntproperty="accounts.status" sourceproperty="status" type="number"></mapfield>

<mapfield saviyntproperty="accounts.accountID" sourceproperty="accountcn" type="character"></mapfield>

<mapfield saviyntproperty="accounts.displayname" sourceproperty="objectguid" type="character"></mapfield>

    </mapper>

    <after-import description="EMAIL,BATCH,SQL">

    </after-import>

</dataMapping>

[pmahalle · Accepted solution]

@tejpusal ,

Try below xml in your accountimportxml field and check once.

<dataMapping>
<before-import></before-import>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >
<![CDATA[ select distinct u.username as accountname, 'SaviyntToSaviynt' as name, 'SaviyntToSaviynt' as endpointname,u.username as accountcn, 'Role' as attribute,r.ROLE_NAME as entitlementvalue,u.username as username, u.statuskey as status, u.updatedate, u.username as objectguid from users u, roles r, role_owners rw where u.userkey=rw.USERKEY and r.ROLEKEY=rw.ROLEKEY and r.STATUS=1 union select distinct u.username as accountname, 'SaviyntToSaviynt' as name, 'SaviyntToSaviynt' as endpointname,u.username as accountcn, 'Rule' as attribute, case when type=0 then concat( h.NAME,' TYPE:BUSINESS') when type=1 then concat( h.NAME,' TYPE:TECHNICAL') when type=2 then concat( h.NAME,' TYPE:USER UPDATE') when type=3 then concat( h.NAME,' TYPE:NEW USER') when type=4 then concat( h.NAME,' TYPE:REMOVE USER') when type=5 then concat( h.NAME,' TYPE:UPDATE ENTITLEMENT') when type=6 then concat( h.NAME,' TYPE:NEW ENTITLEMENT') when type=7 then concat( h.NAME,' TYPE:REMOVE ENTITLEMENT') when type=8 then concat( h.NAME,' TYPE:REQUEST') end as entitlementvalue, u.username as username, u.statuskey as status, u.updatedate, u.username as objectguid from hanarule h, users u, rule_owners ro where u.USERKEY = ro.USERKEY and h.HANARULEKEY=ro.RULEKEY and h.status=0 union select distinct u.username as accountname, 'SaviyntToSaviynt' as name, 'SaviyntToSaviynt' as endpointname,u.username as accountcn, 'UserGroup' as attribute, ug.user_groupname as entitlementvalue,u.username as username, u.statuskey as status, u.updatedate,u.username as objectguid from users u, user_groups ug, usergroup_users ugu where ug.usergroupkey=ugu.USER_GROUPKEY and ugu.userkey=u.userkey union select distinct u.username as accountname, 'SaviyntToSaviynt' as name, 'SaviyntToSaviynt' as endpointname,u.username as accountcn, 'SAVRole' as attribute, s.rolename as entitlementvalue,u.username as username, u.statuskey as status, u.updatedate,u.username as objectguid from users u, user_savroles us, savroles s where u.userkey=us.userkey and us.rolekey=s.rolekey and s.statuskey=1; ]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="delete" deleteaccountentitlement="true" addOnlyMode="FALSE" ifusernotexists="noaction" incrementalcolumn="updatedate" systems="'SaviyntToSaviynt'">
<mapfield saviyntproperty="accounts.name" sourceproperty="accountname" type="character"></mapfield>
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="name" type="character"></mapfield>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpointname" type="character"></mapfield>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="attribute" type="character"></mapfield>
<mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entitlementvalue" type="character"></mapfield>
<mapfield saviyntproperty="users.username" sourceproperty="username" type="character"></mapfield>
<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="number"></mapfield>
<mapfield saviyntproperty="accounts.accountID" sourceproperty="accountcn" type="character"></mapfield>
<mapfield saviyntproperty="accounts.displayname" sourceproperty="objectguid" type="character"></mapfield>
</mapper>
<after-import description="EMAIL,BATCH,SQL"></after-import>
</dataMapping>

[NM]

Hi @tejpusal , Are you not able to import user group all together or just the mapping between usergroup and users?