Oracel DB Accounts Import

[r-m]

Hello,

I'm trying to connect to an Oracle Database using the DB connector. In the ACCOUNTSIMPORT xml we have:

"<dataMapping>
<before-import></before-import>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >
<![CDATA[
select users.name, 'Privilege' as entitlementtype, privileges_tbl.privilege entitlementvalue, 'Oracle DB' as securitysystem, 'Oracle Endpoint' as endpoint
from SYS.user$ users, SYS.DBA_SYS_PRIVS privileges_tbl
where users.name = privileges_tbl.grantee
UNION
select users.name, 'Role' as entitlementtype, role_tbl.granted_role entitlementvalue, 'Oracle DB' as securitysystem, 'Oracle Endpoint' as endpoint
from SYS.user$ users, SYS.DBA_ROLE_PRIVS role_tbl
where users.name = role_tbl.grantee
]]>
</sql-query>

<mapper description="This is the mapping field for SAviynt Field name" ifusernotexists="noaction" CHECKRULES="false">
<mapfield saviyntproperty="accounts.name" sourceproperty="name" type="character"></mapfield>
<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.entitlementvalue" sourceproperty="entitlementvalue" type="character"></mapfield>
</mapper>
<after-import description="EMAIL,BATCH,SQL">
</after-import>
</dataMapping>"

The connections is successful but the Accounts import job fails with to the following error: "java.sql.SQLException: ORA-00933: SQL command not properly ended". 

I believe this happens because Saviynt is adding "Limit 0,10000" at the end of the SQL query which is used in ACCOUNTSIMPORT (visible in the logs attached). It is my understanding that the Limit function is not available in Oracle, which generates the error. 

We have tested the SQL query above, as is, directly in the database and it works as expected, returning an error only if "Limit 0,10000" is added.  How can i stop Saviynt from adding this limit to the query?

Thank you!

[rushikeshvartak]

Use attached 

<dataMapping>
	<before-import></before-import>
	<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >
		<![CDATA[
select users.name, 'Privilege' as entitlementtype, privileges_tbl.privilege entitlementvalue, 'Oracle DB' as securitysystem, 'Oracle Endpoint' as endpoint
from SYS.user\$ users, SYS.DBA_SYS_PRIVS privileges_tbl
where users.name = privileges_tbl.grantee
UNION
select users.name, 'Role' as entitlementtype, role_tbl.granted_role entitlementvalue, 'Oracle DB' as securitysystem, 'Oracle Endpoint' as endpoint
from SYS.user\$ users, SYS.DBA_ROLE_PRIVS role_tbl
where users.name = role_tbl.grantee
]]>
	</sql-query>
	<mapper description="This is the mapping field for SAviynt Field name" ifusernotexists="noaction" CHECKRULES="false">
		<mapfield saviyntproperty="accounts.name" sourceproperty="name" type="character"></mapfield>
		<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.entitlementvalue" sourceproperty="entitlementvalue" type="character"></mapfield>
	</mapper>
	<after-import description="EMAIL,BATCH,SQL"></after-import>
</dataMapping>"

[r-m]

Hi, 

I tried the provided xml and got the following error:"java.sql.SQLException: ORA-00903: invalid table name".

Thank you,

[rushikeshvartak]

Try two slash 

SYS.user\\$

[r-m]

Hi,

Still the same error ☹️ 
"java.sql.SQLException: ORA-00903: invalid table name"

Thank you

[rushikeshvartak]

Try 4 \\\\ or table name in quote 

[r-m]

Both options gave the "java.sql.SQLException: ORA-00903: invalid table name" error.

[rushikeshvartak]

Use dba_users instead of sys.user$

[r-m]

Hi, 

I do not have access to that specific table but i tried using all_users instead so that i could check that it is not the use of SYS.user$ that causes the issue.

I got the same behavior that i had detailed in the initial post. The Limit function is still added to the query and the job fails with error: "java.sql.SQLException: ORA-00933: SQL command not properly ended".

On your side, does this happen when you run a DB Connector with the ACCOUNTSIMPORT xml that I initially provided, does Saviynt add the limit function?

Thank you.

[sk]

We have some db integrations and one of it is Oracle. But I don't think we have seen such behaviour of adding LIMIT condition to query at the end. But we are on v2021.x version, Not sure if there are any settings or is it version related issue. I haven't came across any such setting which limits the # of records while import in v2021.x

I would suggest checking externalconfig.properties to see if there is any parameter which limits the accounts import. If you didn't find one then better to open a support ticket unless some other member provides you a solution through this forum.

[sk]

Do you have anything set in STATUS_THRESHOLD_CONFIG?

[r-m]

Currently i have the following in STATUS_THRESHOLD_CONFIG:

{
"statusAndThresholdConfig": {
"statusColumn":
"customproperty30",
"activeStatus": ["512","active"],
"accountThresholdValue": 3000,
"deleteLinks": false,
"correlateInactiveAccounts": false,
"inactivateAccountsNotInFile": false}
}

I've tried removing and changing the accountThresholdValue but it had no impact on the "Limit" function appearing on the query.