on Jun 8, 2020Article Content
| Article Number | 000038974 |
| Applies To | RSA Product Set: RSA Identity Governance & Lifecycle RSA Version/Condition: 7.0.2, 7.1.0, 7.1.1 |
| Issue | Request 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.   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.  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  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.) |
| Cause | The 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. |
| Resolution | This issue is resolved in the following RSA Identity Governance & Lifecycle versions and/or patch levels:
|
| Workaround | As 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:
Change From:
To:
|