ENTITLEMENTVALUEIMPORT failing

[SDBeltran]

I'm trying to upload Entitlements (database roles) into an endpoint (database). Some of them have already been loaded when the DB accounts were loaded, by means of ACCOUNTSIMPORT/AccountsImportFullJob, but not all of the roles have been assigned to DB Users accounts so, need to upload those remaining DB roles as Entitlements into the Endpoint.

I've been reading the documentation and in forums however I'm still not able to upload them. I'm using the below ENTITLEMENTVALUEIMPORT XML:

<dataMapping>
  <sql-query description="Import_DB_roles_query">
    <![CDATA[select 'WDB' as securitysystem, 'WDB' as endpoint, 'Roles' as entitlementtype, role as entitlementvalue from dba_roles where role like 'APP%' and role not like '%RENEW%';]]>
  </sql-query>
  <mapper description="This is the mapping field for Saviynt Field name" deleteentitlementowner="false" createentitlementtype="true">
    <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"/>
  </mapper>
</dataMapping>

Below the output of the log:

2024-03-27T15:46:44-05:00-ecm-worker-saviynt.EntitlementValueImportService-quartzScheduler_Worker-9-hhgdq-DEBUG-RESULT LIST FROM SOURCE DATABASE select 'WDB' as applicationname, 'WDB' as endpoint, 'Roles' as entitlementtype, role as entitlementvalue from dba_roles where role like 'APP%' and role not like '%RENEW%';

2024-03-27T15:46:44-05:00-ecm-worker--null-hhgdq--27-Mar-2024 20:46:44.615 WARNING [quartzScheduler_Worker-9] groovy.sql.Sql$AbstractQueryCommand.execute Failed to execute: select 'WEYY' as applicationname, 'WEYY' as endpoint, 'Roles' as entitlementtype, role as entitlementvalue from dba_roles where role like 'APP%' and role not like '%RENEW%'; because: ORA-00933: SQL command not properly ended

2024-03-27T15:46:42-05:00-ecm-worker-saviynt.EntitlementValueImportService-quartzScheduler_Worker-9-hhgdq-DEBUG-EntitlementValueImport-Connection: WDB

2024-03-27T15:46:42-05:00-ecm-worker-provisoning.DBProvisioningService-quartzScheduler_Worker-9-hhgdq-DEBUG-using passed external connection :: WDB

2024-03-27T15:46:42-05:00-ecm-worker-provisoning.DBProvisioningService-quartzScheduler_Worker-9-hhgdq-DEBUG-url before binding .. jdbc:oracle:thin:@1.1.1.1:1526/WDB

2024-03-27T15:46:32-05:00-ecm-domain.JobcontrolController-http-nio-8080-exec-146-zcnqn-DEBUG-qry = SELECT il.IMPORTLOGID as IMPORTLOGID,il.logDataAsXML as logDataAsXML,ecmimp.jobid as jobID,ecmimp.jobname as JOBNAME,ecmimp.jobStartDate as JOBSTARTDATE,ecmimp.coments as COMMENTS,ecmimp.jobEndDate as JOBENDDATE,il.filename as FILENAME,ecmimp.SAVRESPONSE as SAVRESPONSE,ecmimp.systemName as SYSTEMNAME,ecmimp.externalconnection as EXTERNALCONNECTION,ecmimp.updateUser as UPDATEUSER,ecmimp.triggername as TRIGGERNAME,ecmimp.jobgroup as JOBGROUP,ecmimp.triggerType as TRIGGERTYPE FROM EcmImportJob ecmimp left join ImportLog il on(ecmimp.jobid=il.jobid) where 1=1 AND (ecmimp.jobname = 'EntitlementValueImportJob' OR ecmimp.jobname ='ENTITLEMENTVALUEIMPORTJOB' ) AND ecmimp.triggername = 'Orcl_WDB_Roles_Import' AND ecmimp.jobgroup = 'DATABASE' ORDER BY ecmimp.jobid DESC limit 0,15

2024-03-27T15:46:32-05:00-ecm-domain.JobcontrolController-http-nio-8080-exec-146-zcnqn-DEBUG-extraQry = AND (ecmimp.jobname = 'EntitlementValueImportJob' OR ecmimp.jobname ='ENTITLEMENTVALUEIMPORTJOB' ) AND ecmimp.triggername = 'Orcl_WDB_Roles_Import' AND ecmimp.jobgroup = 'DATABASE'

I know the SQL statement is correct and fine, I've ran it in the actual DB and it does run and get the appropriate data. I actually, just to test, used the accounts import statement -which I know Saviynt used it to import the accounts and it did work because the accounts are there so it's good-, and got the same result.

I'm obviously missing something basic here and it's not in the SQL query, I may be missing an option, a parameter... thanks for your help!

[rushikeshvartak]

<dataMapping>
  <sql-query description="Import_DB_roles_query">
    <![CDATA[select 'WDB' as securitysystem, 'WDB' as endpoint, 'Roles' as entitlementtype, role as entitlementvalue 1 as state from dba_roles where role like 'APP%' and role not like '%RENEW%']]>
  </sql-query>
  <mapper description="This is the mapping field for Saviynt Field name" deleteentitlementowner="false" createentitlementtype="true">
    <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="entitlementvalues.status" sourceproperty="state" type="number"/>
  </mapper>
</dataMapping>

[CR]

try below

<dataMapping>
<before-import ></before-import>
<sql-query description="Import_DB_roles_query">
<![CDATA[select 'WDB' as securitysystem, 'WDB' as EndpointName, 'Roles' as entitlementtype, role as entitlementvalue from dba_roles where role like 'APP%' and role not like '%RENEW%']]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" deleteentitlementowner="false" createentitlementtype="true" systems ="'WDB'" >
<mapfield type="character" sourceproperty="securitysystem" saviyntproperty="securitysystems.systemname" ></mapfield>
<mapfield type="character" sourceproperty="EndpointName" saviyntproperty="endpoints.endpointname" ></mapfield>
<mapfield type="character" sourceproperty="Application" saviyntproperty="entitlementvalues.customproperty7" ></mapfield>
<mapfield type="character" sourceproperty="entitlementtype" saviyntproperty="entitlementtypes.entitlementname" ></mapfield>
<mapfield type="character" sourceproperty="entitlementvalue" saviyntproperty="entitlementvalues.entitlement_value" ></mapfield>
</mapper>
<after-import description="EMAIL,BATCH,SQL" ></after-import>
</dataMapping>

[SDBeltran]

Thank you guys for your response... none of them worked. Any more thoughts?

[CR]

here you endpoointname and security system name shud be same as per xml, not displayname

[Amar_Simha]

@SDBeltran : Could you try below.

<dataMapping>
<before-import></before-import>
	<sql-query description="Import_DB_roles_query">
		<![CDATA[SELECT DISTINCT 'WDB' as securitysystem, 'WDB' as endpoint, 'Roles' as entitlementtype, role as entitlementvalue, 1 as status from dba_roles where role like 'APP%' and role not like '%RENEW%']]>
	</sql-query>
	<mapper description="This is the mapping field for Saviynt Field name" deleteentitlementowner="false" createentitlementtype="true">
		<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="securitysystem" type="character"></mapfield>
		<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpoint" type="character"></mapfield>
		<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="entitlementtype" type="character"></mapfield>
		<mapfield saviyntproperty="entitlementvalues.entitlement_value" sourceproperty="entitlementvalue" type="character"></mapfield>
	<mapfield saviyntproperty="entitlementvalues.status" sourceproperty="status" type="number"></mapfield>
		</mapper>
		<after-import description="EMAIL,BATCH,SQL">
		</after-import>
</dataMapping>

[SDBeltran]

Found that this is similar to issue reported in forum: https://forums.saviynt.com/t5/identity-governance/entitlementvalueimportjob-got-failed/td-p/78162  anybody would have any update?

[This post has been edited by a Moderator to fix the url.]

[rushikeshvartak]

Use sample xml from below post

https://forums.saviynt.com/t5/identity-governance/oracle-db-entitlement-import-duplicate-entry/m-p/1...

[SDBeltran]

Thanks @rushikeshvartak! I'm basically using the same XML -- I was not using entitlementID to begin with... I do notice that post was from more than a year ago, I wonder what version that was. Any way, I made a few changes, just to make sure, and I'm still getting an error:

No such property: for class: groovy.sql.GroovyRowResult Possible solutions: class, empty

I should mention that, the orignal error was: because: ORA-00933: SQL command not properly ended

However, after several trial/error attempts, I was able to pinpoint that the error was a semicolon in the SQL statement -- oracle does like the semicolon at the end of the statements. I do want to point out that the accounts were uploaded using a query that does contain semicolon in the XML code, so, that's the first red flag: ACCOUNTSIMPORT allows semicolon in the XML but ENTITLEMENTVALUEIMPORT throws an error if used. Second red flag: I'm getting the above mentioned error (No such property...) when the query runs fine in the database, but Saviyant is throwing a java error when the job is executed.

[rushikeshvartak]

Does any value contains colon ?

[SDBeltran]

I'm sorry... was having issues logging in to this forum... answering your question... no, no column contains a colon at all.

[rushikeshvartak · Accepted solution]

I will suggest to start xml build from scratch from samples . You have some typos

[SDBeltran]

You were right! I had exactly two typos - one typo in one word, but that word was used twice in the XML. I'm done! That did it. Thank you guys!!!