Null entitlement is getting created when users don't have entitlements in snowflake JDBC integration

[sk]

Team,

We did integrate snowflake using JDBC connector and during account import job we noticed an issue which is if user is not part of any entitlement our query returns null value for entitlement column but in saviynt we see it is creating an entitlement as null. We did similar integration for other type of DBs where we didn't see such issue and working as expected even if user didn't have any entitlement. Trying to understand if we missed any configuration or if it is an issue

ACCOUNTSIMPORT JSON:

<dataMapping>

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

<![CDATA[select u.name as accountname,

'Snowflake_DEV' as securitysystem,

'Snowflake_DEV' as endpoint,

        'ROLE' as entitlementtype,

gtu.role as entitlementvalue,

case

when u.disabled=false then '1'

when u.disabled=true then '2'

end as status,

u.display_name as displayname,

u.user_id as accountid

from users u left join grants_to_users gtu on u.name = gtu.grantee_name and gtu.deleted_on is null

where u.deleted_on is null;]]>

</sql-query>

<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="Suspend" deleteaccountentitlement="true" ifusernotexists="noaction" addOnlyMode="false">

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

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

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

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

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

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

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

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

</mapper>

</dataMapping>

Sample Output:

Null Entitlement in Saviynt

Accounts in NULL Entitlement

[sk · Accepted solution]

Able to fix by modifying the query as below, Since we don't have entitlement type creation enabled through import able to fix with below solution.

<dataMapping>

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

<![CDATA[select u.name as accountname,

'Snowflake_DEV' as securitysystem,

'Snowflake_DEV' as endpoint,

 case
when gtu.role IS NOT NULL then 'ROLE'
end as entitlementtype,

gtu.role as entitlementvalue,

case

when u.disabled=false then '1'

when u.disabled=true then '2'

end as status,

u.display_name as displayname,

u.user_id as accountid

from users u left join grants_to_users gtu on u.name = gtu.grantee_name and gtu.deleted_on is null

where u.deleted_on is null;]]>

</sql-query>

<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="Suspend" deleteaccountentitlement="true" ifusernotexists="noaction" addOnlyMode="false">

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

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

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

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

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

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

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

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

</mapper>

</dataMapping>