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
