Oracle DB connector access mapping showing error as data

[sudheera]

Getting below error in logs for db connection in access import. and entitlement values are not getting mapped to account when there is ','.

logs:

WARNING [quartzScheduler_Worker-10] groovy.sql.Sql.withBatch Error during batch execution: Data truncation: Data too long for column 'ENTITLEMENT_VALUE' at row 1"
WARNING [quartzScheduler_Worker-10] groovy.sql.Sql.handleError Rolling back due to: Data truncation: Data too long for column 'ENTITLEMENT_VALUE' at row 1"
"saviynt.AccountsImportService","quartzScheduler_Worker-10-vg5mj","ERROR","Error processing records"
"","null-vg5mj","","java.sql.BatchUpdateException: Data truncation: Data too long for column 'ENTITLEMENT_VALUE' at row 1 at com.mysql.cj.util.Util.handleNewInstance(Util.java:192) at com.mysql.cj.util.Util.getInstance(Util.java:167) at com.mysql.cj.util.Util.getInstance(Util.java:174) at com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:224) at com.mysql.cj.jdbc.StatementImpl.executeBatchInternal(StatementImpl.java:893) at com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:796) at com.saviynt.AccountsImportService$_createAndExecuteInsertQuery_closure17.doCall(AccountsImportService.groovy:629) at com.saviynt.AccountsImportService.createAndExecuteInsertQuery(AccountsImportService.groovy:628) at com.saviynt.AccountsImportService.importData(AccountsImportService.groovy:401) at AccountsImportFullJob.execute(AccountsImportFullJob.groovy:58) at org.quartz.core.JobRunShell.run(JobRunShell.java:199) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546)Caused by: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Data too long for column 'ENTITLEMENT_VALUE' at row 1 at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:104) at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1335) at com.mysql.cj.jdbc.StatementImpl.executeBatchInternal(StatementImpl.java:859) ... 7 more"
"saviynt.AccountsImportService","quartzScheduler_Worker-10-vg5mj","DEBUG","Updating Account-Entitlement mapping using - UPDATE ACCOUNT_ENTITLEMENTS1 SET JOB_ID=370632,SAVACCESS='' WHERE ACCOUNTKEY = 64510988 AND ENTITLEMENT_VALUEKEY = 213061 "
"saviynt.AccountsImportService","quartzScheduler_Worker-10-vg5mj","ERROR","Entitlement Value not found"

Accountimport json:

<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name">
<![CDATA[select 'eNPP' as securitysystem,'eNPP' as endpoint,
'Access-Role' as entitlementtype, '1' as status,
NPUG.USERID AS SSOID, NPUG.DESCRIPTION
from
(select
NPIT_USER_GROUP_10.USERID ,SUBSTR(SYS_CONNECT_BY_PATH(NPIT_USER_GROUP_10.DESCRIPTION, ','), 2) DESCRIPTION,
CONNECT_BY_ISLEAF islf
from
(select NPIT_USER_GROUP.USERID, NPIT_GROUP.DESCRIPTION, row_number() over(partition by NPIT_USER_GROUP.USERID order by NPIT_USER_GROUP.GROUP_ID) rn
from NPIT_USER_GROUP, NPIT_GROUP where NPIT_USER_GROUP.GROUP_ID = NPIT_GROUP.GROUP_ID) NPIT_USER_GROUP_10
start with NPIT_USER_GROUP_10.rn = 1 connect by NPIT_USER_GROUP_10.USERID = prior NPIT_USER_GROUP_10.USERID and NPIT_USER_GROUP_10.rn = prior NPIT_USER_GROUP_10.rn + 1
) NPUG, NPIT_SSO_USER NSU, NPIT_USERS NU
Where NPUG.islf = 1 AND NPUG.USERID = NSU.USERID AND NSU.USERID = NU.USERID AND NU.INACTIVE = 0 order by NPUG.USERID
]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="Suspend" deleteaccountentitlement="false" ifusernotexists="create" dateformat="timestamp">
<mapfield saviyntproperty="accounts.name" sourceproperty="SSOID" type="character"/>
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="securitysystem" type="character"/>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpoint" type="character"/>
<mapfield saviyntproperty="accounts.CustomProperty31" sourceproperty="DESCRIPTION" type="character"/>
<mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="DESCRIPTION" type="character"/>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="entitlementtype" type="character"/>
<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="character"/>


</mapper>
</dataMapping>

customproperty31 value stored as :

CustomProperty31='COE/ P&L Representative,Read Only Access,Program Manager'

[NM]

Hi @sudheera instead of using cp31 use any other property and give it a try.

[rushikeshvartak]

• Entitlement value name should not exceed 255 characters 
• Are you sure below is correct mapping ?
• <mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="DESCRIPTION" type="character"/>
• Please share sample output of query in text /csv format

[sudheera]

Hi,

Yes , desription value is fetching the ent from target DB.

for the account which has single ent as "Read Only Access", it is updating the entitlement heirarchy.

but for the account which has multiple ent as "COE/ P&L Representative,Read Only Access,Program Manager" (where the ent are returned as  seperated by comma from query), it is not updating the entitlement heirarchy.

How can we seperate values with comma in the returned value from query and map the ent to account?

[rushikeshvartak]

Sample.

Adjust as per your needs

SELECT
    'eNPP' AS securitysystem,
    'eNPP' AS endpoint,
    'Access-Role' AS entitlementtype,
    '1' AS status,
    NPUG.USERID AS SSOID,
    REGEXP_SUBSTR(NPUG.DESCRIPTION, '[^,]+', 1, LEVEL) AS entitlement
FROM (
    SELECT
        NPIT_USER_GROUP_10.USERID,
        SUBSTR(SYS_CONNECT_BY_PATH(NPIT_USER_GROUP_10.DESCRIPTION, ','), 2) AS DESCRIPTION,
        CONNECT_BY_ISLEAF AS islf
    FROM (
        SELECT
            NPIT_USER_GROUP.USERID,
            NPIT_GROUP.DESCRIPTION,
            ROW_NUMBER() OVER (PARTITION BY NPIT_USER_GROUP.USERID ORDER BY NPIT_USER_GROUP.GROUP_ID) AS rn
        FROM NPIT_USER_GROUP
        JOIN NPIT_GROUP ON NPIT_USER_GROUP.GROUP_ID = NPIT_GROUP.GROUP_ID
    ) NPIT_USER_GROUP_10
    START WITH NPIT_USER_GROUP_10.rn = 1
    CONNECT BY NPIT_USER_GROUP_10.USERID = PRIOR NPIT_USER_GROUP_10.USERID
    AND NPIT_USER_GROUP_10.rn = PRIOR NPIT_USER_GROUP_10.rn + 1
) NPUG
JOIN NPIT_SSO_USER NSU ON NPUG.USERID = NSU.USERID
JOIN NPIT_USERS NU ON NSU.USERID = NU.USERID
WHERE NPUG.islf = 1
AND NU.INACTIVE = 0
CONNECT BY REGEXP_SUBSTR(NPUG.DESCRIPTION, '[^,]+', 1, LEVEL) IS NOT NULL
ORDER BY NPUG.USERID;