000038974 - Form Control Type 'User Picker' with the User filter option generates a SQL Exception in RSA Identity Governance & Lifecycle

Document created by RSA Customer Support Employee on Jun 8, 2020
Version 1Show Document
  • View in full screen mode

Article Content

Article Number000038974
Applies ToRSA Product Set: RSA Identity Governance & Lifecycle
RSA Version/Condition: 7.0.2, 7.1.0, 7.1.1
 
IssueRequest Form Fields that use the User Picker Control Type fail when a User Filter is defined. In the user interface go to Requests > Configuration > Request Forms tab > {Request Form name} > Fields tab.

EXAMPLE

In the example below, the Control Type has been set to User Picker and the User Filter has been set to filter on users with the First Name of John
 
 


User-added image

User-added image



When you run the form and click on the User Test question to obtain a list of users with the first name John, the form either returns no results or returns no results and a SQL Exception depending on the Table Options chosen:
 
In the first screenshot, the First Name column is not part of the table display and no results are displayed.

 

User-added image



In the second screenshot, the column name that is being used in the user filter (First Name field) has been added to the table display resulting in the following SQL exception in the user interface:

 

Error - com.aveksa.server.filter.FilterException:Exception while executing filter query



User-added image


The following errors are logged to the aveksaServer.log file ($AVEKSA_HOME/wildfly/standalone/log/aveksaServer.log):
 
06/08/2020 09:08:23.766 WARN  (default task-14) [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Error: 904, SQLState: 42000
06/08/2020 09:08:23.766 ERROR (default task-14) [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] ORA-00904: "THIS_"."First Name": invalid identifier

06/08/2020 09:08:23.767 ERROR (default task-14) [com.aveksa.server.filter.ObjectListFilter] Exception while executing filter query
com.aveksa.server.db.PersistenceException:
n/a
        at com.aveksa.server.db.persistence.PersistenceServiceProvider.executeCriteriaQuery(PersistenceServiceProvider.java:2067)
        at com.aveksa.server.db.persistence.PersistenceServiceProvider.executeCriteriaQuery(PersistenceServiceProvider.java:2018)
        at com.aveksa.server.db.PersistenceManager.executeCriteriaQuery(PersistenceManager.java:327)
        at com.aveksa.server.filter.ObjectListFilter.executeCriteria(ObjectListFilter.java:109)
        at com.aveksa.server.filter.ObjectListFilter.getFilteredObjects(ObjectListFilter.java:80)
        at com.aveksa.gui.objects.ObjectStore.getUsers(ObjectStore.java:2322)
        at com.aveksa.gui.components.objectPicker.single.user.UserPickerTable.getObjectList(UserPickerTable.java:29)
        at com.aveksa.gui.components.objectPicker.single.user.FilteredUserPickerTable.getObjectList(FilteredUserPickerTable.java:49)
        at com.aveksa.gui.components.table.core.DefaultTableModel.getObjects(DefaultTableModel.java:140)
        at com.aveksa.gui.components.table.core.TableModel.refreshObjects(TableModel.java:2176)
        at com.aveksa.gui.components.table.core.TableModel.handlePaging(TableModel.java:591)
        at com.aveksa.gui.components.table.core.TableModel.handleRequest(TableModel.java:492)
        at com.aveksa.gui.components.table.TableManager.handleRequest(TableManager.java:66)
        at com.aveksa.gui.core.MainManager.handleRequest(MainManager.java:190)
        at com.aveksa.gui.core.MainManager.doGet(MainManager.java:129)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:687)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
        at io.undertow.servlet.handlers.ServletHandler.handleRequest(ServletHandler.java:85)
        at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:129)
        at com.aveksa.gui.core.filters.LoginFilter.doFilter(LoginFilter.java:62)
        at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
        at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
        at com.aveksa.gui.util.security.XSSFilter.doFilter(XSSFilter.java:20)
        at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
        at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
        at io.undertow.servlet.handlers.FilterHandler.handleRequest(FilterHandler.java:84)
        at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62)
        at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36)
        at org.wildfly.extension.undertow.security.SecurityContextAssociationHandler.handleRequest(SecurityContextAssociationHandler.java:78)
        at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
        at io.undertow.servlet.handlers.security.SSLInformationAssociationHandler.handleRequest(SSLInformationAssociationHandler.java:131)
        at io.undertow.servlet.handlers.security.ServletAuthenticationCallHandler.handleRequest(ServletAuthenticationCallHandler.java:57)
        at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
        at io.undertow.security.handlers.AuthenticationConstraintHandler.handleRequest(AuthenticationConstraintHandler.java:53)
        at io.undertow.security.handlers.AbstractConfidentialityHandler.handleRequest(AbstractConfidentialityHandler.java:46)
        at io.undertow.servlet.handlers.security.ServletConfidentialityConstraintHandler.handleRequest(ServletConfidentialityConstraintHandler.java:64)
        at io.undertow.servlet.handlers.security.ServletSecurityConstraintHandler.handleRequest(ServletSecurityConstraintHandler.java:59)
        at io.undertow.security.handlers.AuthenticationMechanismsHandler.handleRequest(AuthenticationMechanismsHandler.java:60)
        at io.undertow.servlet.handlers.security.CachedAuthenticatedSessionHandler.handleRequest(CachedAuthenticatedSessionHandler.java:77)
        at io.undertow.security.handlers.NotificationReceiverHandler.handleRequest(NotificationReceiverHandler.java:50)
        at io.undertow.security.handlers.AbstractSecurityContextAssociationHandler.handleRequest(AbstractSecurityContextAssociationHandler.java:43)
        at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
        at org.wildfly.extension.undertow.security.jacc.JACCContextIdHandler.handleRequest(JACCContextIdHandler.java:61)
        at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
        at org.wildfly.extension.undertow.deployment.RewriteCorrectingHandlerWrappers$PostWrapper$1.handleRequest(RewriteCorrectingHandlerWrappers.java:71)
        at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
        at org.wildfly.extension.undertow.deployment.RewriteCorrectingHandlerWrappers$PreWrapper$1.handleRequest(RewriteCorrectingHandlerWrappers.java:52)
        at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
        at io.undertow.servlet.handlers.ServletInitialHandler.handleFirstRequest(ServletInitialHandler.java:292)
        at io.undertow.servlet.handlers.ServletInitialHandler.access$100(ServletInitialHandler.java:81)
        at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:138)
        at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:135)
        at io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:48)
        at io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43)
        at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
         at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
        at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
        at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
        at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
        at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:272)
        at io.undertow.servlet.handlers.ServletInitialHandler.access$000(ServletInitialHandler.java:81)
        at io.undertow.servlet.handlers.ServletInitialHandler$1$1.run(ServletInitialHandler.java:110)
        at java.security.AccessController.doPrivileged(Native Method)
        at io.undertow.servlet.handlers.ServletInitialHandler$1.handleRequest(ServletInitialHandler.java:107)
        at io.undertow.server.Connectors.executeRootHandler(Connectors.java:202)
        at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:805)
        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: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
        at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79)
        at org.hibernate.loader.Loader.getResultSet(Loader.java:2117)
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1900)
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1876)
        at org.hibernate.loader.Loader.doQuery(Loader.java:919)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336)
        at org.hibernate.loader.Loader.doList(Loader.java:2617)
        at org.hibernate.loader.Loader.doList(Loader.java:2600)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2429)
        at org.hibernate.loader.Loader.list(Loader.java:2424)
        at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:109)
        at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1774)
        at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:363)
        at com.aveksa.server.db.persistence.PersistenceServiceProvider.executeCriteriaQuery(PersistenceServiceProvider.java:2062)
        ... 68 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "THIS_"."First Name": invalid identifier

        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.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:226)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:59)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:747)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:904)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1082)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3780)
        at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1343)
        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3822)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1165)
        at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)
                ... 81 more
Caused by: Error : 904, Position : 1844, Sql = select this_."ID" as ID1_315_0_, this_."USER_ID" as USER_ID2_315_0_, this_."IDC_ID" as IDC_ID3_315_0_, this_."IDC_GROUP_ID" as IDC_GROUP_ID4_315_0_, this_."CREATION_DATE" as CREATION_DATE5_315_0_, this_."DELETION_DATE" as DELETION_DATE6_315_0_, this_."TERMINATION_DATE" as TERMINATION_DATE7_315_0_, this_."EMAIL_ADDRESS" as EMAIL_ADDRESS8_315_0_, this_."NAME" as NAME9_315_0_, this_."FIRST_NAME" as FIRST_NAME10_315_0_, this_."LAST_NAME" as LAST_NAME11_315_0_, this_."SUPERVISOR_ID" as SUPERVISOR_ID12_315_0_, this_."DEPARTMENT" as DEPARTMENT13_315_0_, this_."TITLE" as TITLE14_315_0_, this_."IS_TERMINATED" as IS_TERMINATED15_315_0_, this_."IS_DELETED" as IS_DELETED16_315_0_, this_."JOB_STATUS" as JOB_STATUS17_315_0_, this_."BUSINESS_UNIT_ID" as BUSINESS_UNIT_ID18_315_0_, this_."SUPERVISOR_NAME" as SUPERVISOR_NAME19_315_0_, this_."CERTIFICATION_DATE" as CERTIFICATION_DAT20_315_0_, this_."VIOLATION_COUNT" as VIOLATION_COUNT21_315_0_, this_."EXCEPTION_COUNT" as EXCEPTION_COUNT22_315_0_, this_."AVAILABILITY_STATUS" as AVAILABILITY_STAT23_315_0_, this_."JML_STATUS" as JML_STATUS24_315_0_, this_."BACKUP_SUPERVISOR" as BACKUP_SUPERVISOR25_315_0_, this_."BACKUP_SUPERVISOR_NAME" as BACKUP_SUPERVISOR26_315_0_, this_."CUS_ATTR_USER_CAS_1" as CUS_ATTR_USER_CAS27_315_0_, this_."CUS_ATTR_USER_CAS_2" as CUS_ATTR_USER_CAS28_315_0_, this_."CUS_ATTR_USER_CAS_3" as CUS_ATTR_USER_CAS29_315_0_, this_."CUS_ATTR_USER_CAS_4" as CUS_ATTR_USER_CAS30_315_0_, this_."CUS_ATTR_USER_CAS_4_NAME" as CUS_ATTR_USER_CAS31_315_0_, this_."CUS_ATTR_USER_CAS_5" as CUS_ATTR_USER_CAS32_315_0_, this_."CUS_ATTR_USER_CAS_5_NAME" as CUS_ATTR_USER_CAS33_315_0_, this_."CUS_ATTR_USER_CAS_6" as CUS_ATTR_USER_CAS34_315_0_, this_."CUS_ATTR_USER_CAS_6_NAME" as CUS_ATTR_USER_CAS35_315_0_, this_."UNIQUE_ID" as UNIQUE_ID36_315_0_ from V_MASTER_ENTERPRISE_USERS this_ where (this_."First Name"='John') and ( this_.termination_date is null and this_.deletion_date is null ) order by this_."NAME" asc fetch first :1  rows only, OriginalSql = select this_."ID" as ID1_315_0_, this_."USER_ID" as USER_ID2_315_0_, this_."IDC_ID" as IDC_ID3_315_0_, this_."IDC_GROUP_ID" as IDC_GROUP_ID4_315_0_, this_."CREATION_DATE" as CREATION_DATE5_315_0_, this_."DELETION_DATE" as DELETION_DATE6_315_0_, this_."TERMINATION_DATE" as TERMINATION_DATE7_315_0_, this_."EMAIL_ADDRESS" as EMAIL_ADDRESS8_315_0_, this_."NAME" as NAME9_315_0_, this_."FIRST_NAME" as FIRST_NAME10_315_0_, this_."LAST_NAME" as LAST_NAME11_315_0_, this_."SUPERVISOR_ID" as SUPERVISOR_ID12_315_0_, this_."DEPARTMENT" as DEPARTMENT13_315_0_, this_."TITLE" as TITLE14_315_0_, this_."IS_TERMINATED" as IS_TERMINATED15_315_0_, this_."IS_DELETED" as IS_DELETED16_315_0_, this_."JOB_STATUS" as JOB_STATUS17_315_0_, this_."BUSINESS_UNIT_ID" as BUSINESS_UNIT_ID18_315_0_, this_."SUPERVISOR_NAME" as SUPERVISOR_NAME19_315_0_, this_."CERTIFICATION_DATE" as CERTIFICATION_DAT20_315_0_, this_."VIOLATION_COUNT" as VIOLATION_COUNT21_315_0_, this_."EXCEPTION_COUNT" as EXCEPTION_COUNT22_315_0_, this_."AVAILABILITY_STATUS" as AVAILABILITY_STAT23_315_0_, this_."JML_STATUS" as JML_STATUS24_315_0_, this_."BACKUP_SUPERVISOR" as BACKUP_SUPERVISOR25_315_0_, this_."BACKUP_SUPERVISOR_NAME" as BACKUP_SUPERVISOR26_315_0_, this_."CUS_ATTR_USER_CAS_1" as CUS_ATTR_USER_CAS27_315_0_, this_."CUS_ATTR_USER_CAS_2" as CUS_ATTR_USER_CAS28_315_0_, this_."CUS_ATTR_USER_CAS_3" as CUS_ATTR_USER_CAS29_315_0_, this_."CUS_ATTR_USER_CAS_4" as CUS_ATTR_USER_CAS30_315_0_, this_."CUS_ATTR_USER_CAS_4_NAME" as CUS_ATTR_USER_CAS31_315_0_, this_."CUS_ATTR_USER_CAS_5" as CUS_ATTR_USER_CAS32_315_0_, this_."CUS_ATTR_USER_CAS_5_NAME" as CUS_ATTR_USER_CAS33_315_0_, this_."CUS_ATTR_USER_CAS_6" as CUS_ATTR_USER_CAS34_315_0_, this_."CUS_ATTR_USER_CAS_6_NAME" as CUS_ATTR_USER_CAS35_315_0_, this_."UNIQUE_ID" as UNIQUE_ID36_315_0_ from V_MASTER_ENTERPRISE_USERS this_ where (this_."First Name"='John') and ( this_.termination_date is null and this_.deletion_date is null ) order by this_."NAME" asc fetch first ? rows only, Error Msg = ORA-00904: "THIS_"."First Name": invalid identifier

        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)
        ... 97 more
06/08/2020 09:08:23.768 ERROR (default task-14) [com.aveksa.gui.objects.ObjectStore]
com.aveksa.server.filter.FilterException: Exception while executing filter query


 

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.)
 
CauseThe error:
 
ORA-00904: "THIS_"."First Name": invalid identifier'


indicates an invalid table column is used in the SQL query to filter the users.

This is a known issue reported in engineering ticket ACM-94292.
 
ResolutionThis issue is resolved in the following RSA Identity Governance & Lifecycle versions and/or patch levels:
  • RSA Identity Governance & Lifecycle 7.0.2 P12
  • RSA Identity Governance & Lifecycle 7.1.0 P06
  • RSA Identity Governance & Lifecycle 7.1.1 P01
WorkaroundAs a workaround, the SQL query generated when defining the User Filter may be modified by using the Advanced option to use a valid column name. The column names for user picker fields are the columns defined in the T_MASTER_ENTERPRISE_USERS table in the AVUSER schema. In the example above, the column name would be FIRST_NAME. The workaround would be to:
  1. In the user interface go to Requests > Configuration > Request Forms tab > {Request Form name} > Fields tab > {Field name} > Edit button
  2. Click User Filter to edit the user filter.
  3. Click the Advanced button on the bottom left of the screen to get to the Advanced query option.
  4. Under the Where Clause option, remove all double quotes and set the column name to a valid column name from the T_MASTER_ENTERPRISE_USERS table. In this case FIRST_NAME. 

Change From:



users."First Name" like '%John%'


To:



users.FIRST_NAME like '%John%'


  1. Click OK to save the changes.
  2. Run the Request Form again to verify the results.

Attachments

    Outcomes