When removing a user from a large number of Roles, the Change Request creation fails with an 'ORA-01704: string literal too long' error in RSA Identity Governance & Lifecycle

[#1]

Article Number

000039078

Applies To

RSA Product Set: RSA Identity Governance & Lifecycle
RSA Version/Condition: 7.1.0, 7.1.1, 7.2.0
 

Issue

When removing a user from a large number of Roles in bulk (whether through a Rule or an explicit request), the change request fails to be created.

The following error is logged to the aveksaServer.log file ($AVEKSA_HOME/wildfly/standalone/log/aveksaServer.log😞
 

05/26/2020 10:53:21.748 INFO  (CR-Creation-1) [com.aveksa.server.db.persistence.PersistenceServiceProvider] executeCallableStatement giving up after hitting SQLException: ORA-01704: string literal too long
ORA-06512: at "AVUSER.ROLE_MANAGEMENT_PKG", line 6164
ORA-06512: at line 1

05/26/2020 10:53:21.750 WARN  (CR-Creation-1) [org.hibernate.engine.jdbc.spi.SqlExceptionHelper]
SQL Error: 1704, SQLState: 42000
05/26/2020 10:53:21.750 ERROR (CR-Creation-1) [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] ORA-01704: string literal too long
ORA-06512: at "AVUSER.ROLE_MANAGEMENT_PKG", line 6164
ORA-06512: at line 1

05/26/2020 10:53:21.763 ERROR (CR-Creation-1) [com.aveksa.server.core.concurrent.ConcurrentUtils] Task encountered an error.
java.lang.RuntimeException: com.aveksa.server.db.PersistenceException: java.sql.SQLSyntaxErrorException: ORA-01704: string literal too long
ORA-06512: at "AVUSER.ROLE_MANAGEMENT_PKG", line 6164
ORA-06512: at line 1        

     at com.aveksa.server.core.globalroleset.RoleManagementServiceProvider.getExclusiveEntsForRoleMembers(RoleManagementServiceProvider.java:1356)        
     at com.aveksa.server.core.globalroleset.RoleManagementServiceProvider.getExclusiveEntitlementsForMember(RoleManagementServiceProvider.java:1199)        
     at com.aveksa.server.core.cr.UserChangeRequestData.createIndirectsForRemoveRole(UserChangeRequestData.java:337)           
     at com.aveksa.server.core.cr.UserChangeRequestData.getIndirectChangeDatas(UserChangeRequestData.java:256)        
     at com.aveksa.server.core.cr.ChangeRequestCreationHandler.getInDirectChangeItems(ChangeRequestCreationHandler.java:475)        
     at com.aveksa.server.core.cr.ChangeRequestCreationHandler.setIndirectItems(ChangeRequestCreationHandler.java:107)        
     at com.aveksa.server.core.cr.ChangeRequestCreationHandler.createChangeRequests(ChangeRequestCreationHandler.java:81)    
     at com.aveksa.server.core.cr.concurrent.CRCreationTask.call(CRCreationTask.java:39)        
    at com.aveksa.server.core.cr.concurrent.CRCreationTask.call(CRCreationTask.java:19)        
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)        
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)        
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)        
    at java.lang.Thread.run(Thread.java:748) Caused by: com.aveksa.server.db.PersistenceException: java.sql.SQLSyntaxErrorException: ORA-01704: string literal too long
ORA-06512: at "AVUSER.ROLE_MANAGEMENT_PKG", line 6164
ORA-06512: at line 1        

     at com.aveksa.server.db.persistence.PersistenceServiceProvider.callDBProcWithOutParams(PersistenceServiceProvider.java:2607)     
     at com.aveksa.server.db.persistence.PersistenceServiceProvider.callFunctionWithOutParams(PersistenceServiceProvider.java:2636)        
     at com.aveksa.server.db.PersistenceManager.callFunctionWithOutParams(PersistenceManager.java:383)        
     at com.aveksa.server.core.globalroleset.RoleManagementServiceProvider.getExclusiveEntsForRoleMembers(RoleManagementServiceProvider.java:1343)         ... 12 more Caused by: java.sql.SQLSyntaxErrorException: ORA-01704: string literal too long
ORA-06512: at "AVUSER.ROLE_MANAGEMENT_PKG", line 6164
ORA-06512: at line 1  
     
     at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)        
     at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)        
     at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054)        
     at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)        
     at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)        
     at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)        
     at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:223)        
     at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:56)        
     at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:907)        
     at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1119)        
     at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3780)        
     at oracle.jdbc.driver.T4CCallableStatement.executeInternal(T4CCallableStatement.java:1300)        
     at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3887)        
     at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4230)        
     at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1079)        
     at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:442)        
     at com.aveksa.server.db.persistence.work.PersistenceServiceBaseWork.executeCallableStatement(PersistenceServiceBaseWork.java:36)        
     at com.aveksa.server.db.persistence.work.ScalarFunctionCallWork.execute(ScalarFunctionCallWork.java:87)        
     at org.hibernate.jdbc.WorkExecutor.executeWork(WorkExecutor.java:37)        
     at org.hibernate.internal.SessionImpl$3.accept(SessionImpl.java:2134)        
     at org.hibernate.internal.SessionImpl$3.accept(SessionImpl.java:2131)        
     at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.coordinateWork(JdbcCoordinatorImpl.java:332)        
     at org.hibernate.internal.SessionImpl.doWork(SessionImpl.java:2153)        
     at org.hibernate.internal.SessionImpl.doWork(SessionImpl.java:2138)        
     at com.aveksa.server.db.persistence.PersistenceServiceProvider.callDBProcWithOutParams(PersistenceServiceProvider.java:2573)         ... 15 more Caused by: Error : 1704, Position : 0, Sql = BEGIN :1 := Role_Management_Pkg.Get_Exclusive_Ents_For_Member(:2 ,:3 ,:4 ,:5 ); END;, OriginalSql = {? = call Role_Management_Pkg.Get_Exclusive_Ents_For_Member(?,?,?,?)}, Error Msg = ORA-01704: string literal too long
ORA-06512: at "AVUSER.ROLE_MANAGEMENT_PKG", line 6164
ORA-06512: at line 1
       
     at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)         ... 39 more

Please refer to RSA Knowledge Base Article 000030327 --Artifacts to gather in RSA Identity Governance & Lifecycle to find the location of the aveksaServer.log file for your specific deployment if you are on a WildFly cluster or a non-WildFly platform. The aveksaServer.log may also be downloaded from the RSA Identity Governance & Lifecycle user interface (Admin > System > Server Nodes tab > under Logs).
 

Cause

This is a known issue reported in engineering ticket ACM-105662.

The error:
 

 ORA-01704: string literal too long

indicates that a size limitation has been exceeded. In this case the string variable used to hold the comma separated list of Role IDs to be removed in the Change Request exceeds a size limitation of 4000 characters.
 

Resolution

This issue is being investigated by the Engineering team in order to provide a permanent resolution in a future release.
 

Workaround

Split the change request into multiple change requests such that the number of role IDs per request does not exceed 4000 characters when joined together with commas.