Announcement Announcement Module
Collapse
No announcement yet.
Database authentication: resultset parsing error Page Title Module
Move Remove Collapse
This topic is closed
X
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Database authentication: resultset parsing error

    InMemory authentication worked fine, but when I try to enable database authentication on DB2 I get a resultset parsing error:

    Code:
    14:41:15,465 DEBUG [DataSourceUtils] Opening JDBC Connection
    14:41:15,475 DEBUG [DataSourceUtils] Closing JDBC Connection
    14:41:15,475 DEBUG [SQLErrorCodesFactory] Database product name cached for DataSource [org.jboss.resource.adapter.jdbc.WrapperData
    Source@c6c084]: name is 'DB2*'
    14:41:15,475 DEBUG [SQLErrorCodesFactory] SQL error codes for 'DB2*' found
    14:41:15,475 DEBUG [SQLErrorCodeSQLExceptionTranslator] Unable to translate SQLException with errorCode '-99999', will now try the
     fallback translator
    14:41:15,475 DEBUG [DataSourceUtils] Closing JDBC Connection
    14:41:15,475 DEBUG [AbstractProcessingFilter] Updated ContextHolder to contain null Authentication
    14:41:15,475 DEBUG [AbstractProcessingFilter] Authentication request failed: net.sf.acegisecurity.AuthenticationServiceException:
    executing PreparedStatementCallback: encountered SQLException [Invalid argument: parameter index 3 is out of range.]; nested excep
    tion is com.ibm.db2.jcc.c.SqlException: Invalid argument: parameter index 3 is out of range.; nested exception is org.springframew
    ork.jdbc.UncategorizedSQLException: executing PreparedStatementCallback: encountered SQLException [Invalid argument: parameter ind
    ex 3 is out of range.]; nested exception is com.ibm.db2.jcc.c.SqlException: Invalid argument: parameter index 3 is out of range.
    14:41:15,475 DEBUG [SecurityEnforcementFilter] Chain processed normally
    14:41:15,475 DEBUG [HttpSessionContextIntegrationFilter] Context stored to HttpSession: 'net.sf.acegisecurity.context.security.SecureContextImpl@11f846b: Null authentication'
    14:41:15,475 DEBUG [HttpSessionContextIntegrationFilter] ContextHolder set to null as request processing completed
    14:41:15,485 DEBUG [HttpSessionContextIntegrationFilter] Obtained from ACEGI_SECURITY_CONTEXT a valid Context and set to ContextHo
    lder: 'net.sf.acegisecurity.context.security.SecureContextImpl@11f846b: Null authentication'
    14:41:15,485 DEBUG [PathBasedFilterInvocationDefinitionMap] Converted URL to lowercase, from: '/login.jsp?failed=true'; to: '/login.jsp?failed=true'
    Code:
    <bean id="authenticationDao" class="net.sf.acegisecurity.providers.dao.jdbc.JdbcDaoImpl">
    		<property name="dataSource"><ref bean="dataSource"/></property>
    		<property name="usersByUsernameQuery">
    			<value>SELECT loginName username, password FROM support.user WHERE loginName=?</value>
    		</property>
    		<property name="authoritiesByUsernameQuery">
    			<value>SELECT u.loginName username, r.code authority, u.online enabled FROM support.user u, support.role r WHERE u.role_id=r.id AND u.loginName=?</value>
    		</property>
    	</bean>
    I override the default queries and let the column names match the original. I validated the queries against DB2 with success.
    Anybody any idea what is going wrong?

  • #2
    Does DB2 work OK with the stock-standard SQL shipped with Acegi Security? You might need to subclass JdbcDaoImpl and override its inner classes to modify the schema. Looks like your query parameters aren't matching what your database schema is expecting.

    Comment


    • #3
      Subclassing JdbcDaoImpl

      I've tried it with default tables as well, with no success. Maybe it's me...
      However, I looked in the API docs for info about subclassing JdbcDaoImpl, but I couldn't find much. Is there documentation of some kind that describes f.e. what object type to return, ...? Thanks in advance

      Comment


      • #4
        DB2

        I got it working now. I found 2 problems in my setup while browsing through the Acegi source-code:

        1) I added the authority prefix (ROLE_) in the DB2 column, so it should be MANAGER instead of ROLE_MANAGER in the database.

        2) After I subclassed JdbcDaoImpl for setting custom MappingSqlQuery objects, I noticed that the conversion of the "enabled" column to boolean was the critical point since this column is defined as 'Integer'. When I hardcoded the value to true, it worked.

        3) Overriding the constructors was necesarry.

        Here is my custom code:

        Code:
        public class DB2JdbcDaoImpl extends JdbcDaoImpl &#123;
        
          protected void initMappingSqlQueries&#40;&#41; &#123;
            setUsersByUsernameMapping&#40;new MyUserMapper&#40;getDataSource&#40;&#41;&#41;&#41;;
            setAuthoritiesByUsernameMapping&#40;new MyAuthorityMapper&#40;getDataSource&#40;&#41;&#41;&#41;;
          &#125;
        
          protected class MyUserMapper extends MappingSqlQuery &#123;
        
            public MyUserMapper&#40;DataSource ds&#41; &#123;
              super&#40;ds, getUsersByUsernameQuery&#40;&#41;&#41;;
              declareParameter&#40;new SqlParameter&#40;Types.VARCHAR&#41;&#41;;
              compile&#40;&#41;;
            &#125;
            
            protected Object mapRow&#40;ResultSet rs, int idx&#41; throws SQLException &#123;
              String username = rs.getString&#40;1&#41;;
              String password = rs.getString&#40;2&#41;;
              boolean enabled = true;
              UserDetails user = new User&#40;username, password, enabled, true, true, true,
                  new GrantedAuthority&#91;&#93; &#123; new GrantedAuthorityImpl&#40;"HOLDER"&#41; &#125;&#41;;
              return user;
            &#125;
          &#125;
        
          protected class MyAuthorityMapper extends MappingSqlQuery &#123;
            
            public MyAuthorityMapper&#40;DataSource ds&#41; &#123;
              super&#40;ds, getAuthoritiesByUsernameQuery&#40;&#41;&#41;;
              declareParameter&#40;new SqlParameter&#40;Types.VARCHAR&#41;&#41;;
              compile&#40;&#41;;
            &#125;
            
            protected Object mapRow&#40;ResultSet rs, int rownum&#41; throws SQLException &#123;
              String roleName = getRolePrefix&#40;&#41; + rs.getString&#40;2&#41;;
              GrantedAuthorityImpl authority = new GrantedAuthorityImpl&#40;roleName&#41;;
              return authority;
            &#125;
          &#125;
        &#125;
        Code:
        <bean id="authenticationDao" class="be.emd.support.portal.security.DB2JdbcDaoImpl">
        		<property name="dataSource"><ref bean="dataSource"/></property>
        		<property name="rolePrefix" value="ROLE_"/>
        		<property name="usersByUsernameQuery">
        			<value>SELECT loginName username, password FROM support.user WHERE loginName=?</value>
        		</property>
        		<property name="authoritiesByUsernameQuery">
        			<value>SELECT u.loginName username, r.code authority, u.online enabled FROM support.user u, support.role r WHERE u.role_id=r.id AND u.loginName=?</value>
        		</property>
        </bean>

        Comment

        Working...
        X