Account Entitlement Mapping via OOTB DB Connector

[Aashish-Handa]

Hi Experts,

We have a requirement where we need to integrate a DB instance with Saviynt using the OOTB DB connector, where the DB doesnt have in built roles/entitlements of there own and are providing accesses to the users via Flags turned as 0 or 1 as values in the table.

The ask here is to configure the reconciliation in Saviynt such that there is a sync between the target DB and Saviynt wrt to accounts and entitlements. We tried using a query inside the accountimportmapping json on the connector but are getting errors executing it.

Can someone please suggest what we are doing wrong, and/or is there another way to achieve this use case?

The mapping is as follows:

<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >
<![CDATA[SELECT
USER_NUMBER AS accountname,
'xyz' AS securitysystem,
'xyz' AS endpoint,
DELETED AS status,
'Roles' AS entitlementtype,
'Standard Edit Access (OMS/PJM/OFS)' AS entitlementvalue
FROM
xyz.USER
WHERE
STANDARD_FLAG = 1
UNION
SELECT
USER_NUMBER AS accountname,
'xyz' AS securitysystem,
'xyz' AS endpoint,
DELETED AS status,
'Roles' AS entitlementtype,
'Credit' AS entitlementvalue
FROM
xyz.USER
WHERE
FINANCE_FLAG = 1
UNION
SELECT
USER_NUMBER AS accountname,
'xyz' AS securitysystem,
'xyz' AS endpoint,
DELETED AS status,
'Roles' AS entitlementtype,
'Restricted (Admin)' AS entitlementvalue
FROM
xyz.USER
WHERE
ADMIN_FLAG = 1
UNION
SELECT
USER_NUMBER AS accountname,
'xyz' AS securitysystem,
'xyz' AS endpoint,
DELETED AS status,
'Roles' AS entitlementtype,
'Restricted (FW Admin)' AS entitlementvalue
FROM
xyz.USER
WHERE
FWA_FLAG = 1
UNION
SELECT
USER_NUMBER AS accountname,
'xyz' AS securitysystem,
'xyz' AS endpoint,
DELETED AS status,
'Roles' AS entitlementtype,
'HCFS' AS entitlementvalue
FROM
xyz.USER
WHERE
HCFS_FLAG = 1
UNION
SELECT
USER_NUMBER AS accountname,
'xyz' AS securitysystem,
'xyz' AS endpoint,
DELETED AS status,
'Roles' AS entitlementtype,
'Read Only' AS entitlementvalue
FROM
xyz.USER]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="Suspend" deleteaccountentitlement="true" ifusernotexists="noaction">
<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"/>
</mapper>
</dataMapping>

Error Snippet:

Thanks

[rushikeshvartak]

Do 2 changes 

• in Query instead of Deleted it should 1 as status
• in mapper
• <mapfield saviyntproperty="accounts.status" sourceproperty="status" type="number"/>

[Aashish-Handa]

Wouldn't using 1 instead of DELETED make all the users to a certain status?

[rushikeshvartak]

Yes you can use case when then in that case 

[NM]

@Aashish-Handait does but as soon as you delete the account on application end ..they will automatically be marked as suspended