Announcing the SAVIYNT KNOWLEDGE EXCHANGE unifying the Saviynt forums, documentation, training, and more in a single search tool across platforms. Click HERE to read the Announcement.

Oracel DB Accounts Import

r-m
New Contributor
New Contributor

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!

11 REPLIES 11

rushikeshvartak
All-Star
All-Star

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>"

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Hi, 

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

rm_0-1671212635078.png

Thank you,

 

Try two slash 

SYS.user\\$

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Hi,

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

Thank you

 

Try 4 \\\\ or table name in quote 


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

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

Use dba_users instead of sys.user$


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

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.

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

sk_0-1671836856805.png

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.


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

sk
All-Star
All-Star

Do you have anything set in STATUS_THRESHOLD_CONFIG?


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

r-m
New Contributor
New Contributor

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.