Issues with JSF and Acegi with JdbcDaoImpl controller

[mickknutson]

I have created a custom JdbcDaoImpl Controller...

When I use the InMemoryDaoImpl authentication provider, it works fine.
When I run the queries on MySql, each of the queries works fine as well.

But when I use this JdbcDaoImpl controller with a username that _is_ in the database, I get:

13:18:19,439 INFO  [STDOUT] 13:18:19,439 DEBUG [SQLErrorCodesFactory] SQL error codes for 'MySQL' found
13:18:19,439 INFO  [STDOUT] 13:18:19,439 DEBUG [SQLErrorCodeSQLExceptionTranslator] Unable to translate SQLException with Error code '0', will now try the
fallback translator
13:18:19,439 INFO  [STDOUT] 13:18:19,439 DEBUG [XmlWebApplicationContext] Publishing event in context [Root WebApplicationContext]: org.acegisecurity.event
.authentication.AuthenticationFailureServiceExceptionEvent[source=org.acegisecurity.providers.UsernamePasswordAuthenticationToken@4be0a7a9: Username: mknut
son; Password: [PROTECTED]; Authenticated: false; Details: org.acegisecurity.ui.WebAuthenticationDetails@ffff6a82: RemoteIpAddress: 127.0.0.1; SessionId: 8
A6722D5B8165505C7569C7EA3196EA7; Not granted any authorities]
13:18:19,439 ERROR [STDERR] org.acegisecurity.AuthenticationServiceException: PreparedStatementCallback; uncategorized SQLException for SQL [
                SELECT username, password FROM user WHERE username=?
            ]; SQL state [S1009]; error code [0]; Column Index out of range, 3 > 2. ; nested exception is java.sql.SQLException: Column Index out of range,
 3 > 2. ; nested exception is org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [
                SELECT username, password FROM user WHERE username=?
            ]; SQL state [S1009]; error code [0]; Column Index out of range, 3 > 2. ; nested exception is java.sql.SQLException: Column Index out of range,
 3 > 2.
13:18:19,439 ERROR [STDERR] Caused by:
13:18:19,439 ERROR [STDERR] org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [
                SELECT username, password FROM user WHERE username=?
            ]; SQL state [S1009]; error code [0]; Column Index out of range, 3 > 2. ; nested exception is java.sql.SQLException: Column Index out of range,
 3 > 2.
13:18:19,439 ERROR [STDERR] Caused by:
13:18:19,439 ERROR [STDERR] java.sql.SQLException: Column Index out of range, 3 > 2.
13:18:19,439 ERROR [STDERR]     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
13:18:19,439 ERROR [STDERR]     at com.mysql.jdbc.ResultSet.checkColumnBounds(ResultSet.java:662)
13:18:19,439 ERROR [STDERR]     at com.mysql.jdbc.ResultSet.getBoolean(ResultSet.java:1448)
13:18:19,439 ERROR [STDERR]     at org.jboss.resource.adapter.jdbc.WrappedResultSet.getBoolean(WrappedResultSet.java:391)
13:18:19,439 ERROR [STDERR]     at org.acegisecurity.userdetails.jdbc.JdbcDaoImpl$UsersByUsernameMapping.mapRow(JdbcDaoImpl.java:236)
13:18:19,439 ERROR [STDERR]     at org.springframework.jdbc.object.MappingSqlQuery.mapRow(MappingSqlQuery.java:64)
13:18:19,439 ERROR [STDERR]     at org.springframework.jdbc.object.MappingSqlQueryWithParameters$RowMapperImpl.mapRow(MappingSqlQueryWithParameters.java:11
5)

If I try with a user that is _NOT_ in my database, I get:

14:09:25,962 INFO  [STDOUT] 14:09:25,962 INFO  [AuthenticationController] authenticate()
14:09:25,962 INFO  [STDOUT] 14:09:25,962 DEBUG [AuthenticationController] getAuthenticationManager()
14:09:25,962 INFO  [STDOUT] 14:09:25,962 DEBUG [ProviderManager] Authentication attempt using org.acegisecurity.providers.dao.DaoAuthenticationProvider
14:09:25,962 INFO  [STDOUT] 14:09:25,962 DEBUG [JdbcTemplate] Executing SQL query [
                SELECT username, password FROM user WHERE username=?
            ]
14:09:25,962 INFO  [STDOUT] 14:09:25,962 DEBUG [DataSourceUtils] Fetching JDBC Connection from DataSource
14:09:25,978 INFO  [STDOUT] 14:09:25,978 DEBUG [StatementCreatorUtils] Setting SQL statement parameter value: column index 1, parameter value [mickknutson]
, value class [java.lang.String], SQL type 12
14:09:25,978 INFO  [STDOUT] 14:09:25,978 DEBUG [DataSourceUtils] Returning JDBC Connection to DataSource
14:09:25,994 INFO  [STDOUT] 14:09:25,994 DEBUG [XmlWebApplicationContext] Publishing event in context [Root WebApplicationContext]: org.acegisecurity.event
.authentication.AuthenticationFailureBadCredentialsEvent[source=org.acegisecurity.providers.UsernamePasswordAuthenticationToken@653cb226: Username: mickknu
tson; Password: [PROTECTED]; Authenticated: false; Details: org.acegisecurity.ui.WebAuthenticationDetails@ffff6a82: RemoteIpAddress: 127.0.0.1; SessionId:
8A6722D5B8165505C7569C7EA3196EA7; Not granted any authorities]
14:09:25,994 ERROR [STDERR] org.acegisecurity.BadCredentialsException: Bad credentials
14:09:25,994 ERROR [STDERR]     at org.acegisecurity.providers.dao.AbstractUserDetailsAuthenticationProvider.authenticate(AbstractUserDetailsAuthentication
Provider.java:118)
14:09:25,994 ERROR [STDERR]     at org.acegisecurity.providers.ProviderManager.doAuthentication(ProviderManager.java:188)
14:09:25,994 ERROR [STDERR]     at org.acegisecurity.AbstractAuthenticationManager.authenticate(AbstractAuthenticationManager.java:45)
14:09:25,994 ERROR [STDERR]     at com.baselogic.tro.security.AuthenticationController.authenticate(AuthenticationController.java:98)

[mickknutson]

Here is my controller:

package com.baselogic.tro.security;

import com.baselogic.tro.user.UserManager;
import org.acegisecurity.Authentication;
import org.acegisecurity.AuthenticationManager;
import org.acegisecurity.context.HttpSessionContextIntegrationFilter;
import org.acegisecurity.context.SecurityContext;
import org.acegisecurity.context.SecurityContextHolder;
import org.acegisecurity.providers.UsernamePasswordAuthenticationToken;
import org.acegisecurity.ui.WebAuthenticationDetails;
import org.acegisecurity.ui.AbstractProcessingFilter;
import org.acegisecurity.ui.webapp.AuthenticationProcessingFilter;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Required;

import javax.faces.application.FacesMessage;
import javax.faces.context.FacesContext;
import javax.faces.event.ActionEvent;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

/**
 * Created by IntelliJ IDEA.
 * User: DJ MICK
 * Date: Nov 4, 2006
 * Time: 1:46:59 PM
 * To change this template use File | Settings | File Templates.
 */
public class AuthenticationController
        extends org.acegisecurity.userdetails.jdbc.JdbcDaoImpl {

    /**
     * Commons / Log4J Loggin object
     */
    private Log log = LogFactory.getLog(this.getClass());

    private UserManager userManager;


    public AuthenticationController() {
        log.info("AuthenticationController()");
    }

    public UserManager getUserManager() {
        log.info("getUserManager()");
        return userManager;
    }

    public void setUserManager(UserManager userManager) {
        log.info("setUserManager()");
        this.userManager = userManager;
    }


    private String _username;
    private String _password;

    // injected properties
    private AuthenticationManager _authenticationManager;

    public String getPassword() {
        return _password;
    }

    public void setPassword(String password) {
        _password = password;
    }

    public String getUsername() {
        return _username;
    }

    public void setUsername(String userName) {
        _username = userName;
    }

    @SuppressWarnings("unchecked")
    public String authenticate() {
        log.info("authenticate()");
        String outcome = "failure";

        try {
            final String userName = getUsername();
            final String password = getPassword();
            final UsernamePasswordAuthenticationToken authReq = new UsernamePasswordAuthenticationToken(
                    userName, password);

            final HttpServletRequest request = getRequest();
            authReq.setDetails(new WebAuthenticationDetails(request));

            final HttpSession session = request.getSession();
            session.setAttribute(
                    AuthenticationProcessingFilter.ACEGI_SECURITY_LAST_USERNAME_KEY,
                    userName);

            /* perform authentication */
            final Authentication auth = getAuthenticationManager().authenticate(authReq);
            log.info("Authentication"  + auth.toString());

            /* initialize the security context. */
            final SecurityContext secCtx = SecurityContextHolder.getContext();
            secCtx.setAuthentication(auth);
            session.setAttribute(HttpSessionContextIntegrationFilter.ACEGI_SECURITY_CONTEXT_KEY, secCtx);

            outcome = "success";

        } catch (Exception e) {
            outcome = "failure";
            Exception ex = (Exception) FacesContext.getCurrentInstance()
                    .getExternalContext()
                    .getSessionMap()
                    .get(AbstractProcessingFilter.ACEGI_SECURITY_LAST_EXCEPTION_KEY);
            if (ex != null)
                FacesContext.getCurrentInstance()
                        .addMessage(null,
                                new FacesMessage(
                                        FacesMessage.SEVERITY_ERROR, ex.getMessage(), ex.getMessage()));
            e.printStackTrace();
        }

        return outcome;
    }

    public void logout(ActionEvent e) {
        log.info("logout(ActionEvent)");
        final HttpServletRequest request = getRequest();
        request.getSession(false).removeAttribute(HttpSessionContextIntegrationFilter.ACEGI_SECURITY_CONTEXT_KEY);

        /* simulate the SecurityContextLogoutHandler
         */
        SecurityContextHolder.clearContext();

        request.getSession(false).invalidate();
    }

    private HttpServletRequest getRequest() {
        return (HttpServletRequest) FacesContext.getCurrentInstance().getExternalContext().getRequest();
    }

    public AuthenticationManager getAuthenticationManager() {
        log.trace("getAuthenticationManager()");
        return _authenticationManager;
    }

    @Required
    public void setAuthenticationManager(
            AuthenticationManager authenticationManager) {
        log.trace("setAuthenticationManager(AuthenticationManager)");
        _authenticationManager = authenticationManager;
    }


} // The End...

[mickknutson]

Here is my configuration:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
        "http://www.springframework.org/dtd/spring-beans.dtd">

<beans>

    <!-- Required (mk) -->
    <bean id="authenticationManager"
          class="org.acegisecurity.providers.ProviderManager">
        <property name="providers">
            <list>
                <ref local="daoAuthenticationProvider"/>
            </list>
        </property>
    </bean>

    <!-- Required (mk) -->
    <!--<bean id="daoAuthenticationProvider" class="org.acegisecurity.providers.dao.DaoAuthenticationProvider">
        <property name="userDetailsService">
            <bean class="org.acegisecurity.userdetails.memory.InMemoryDaoImpl">
                <property name="userMap">
                    <value>
                        mickknutson=mypassword,ROLE_ADMINISTRATOR,ROLE_USER
                        test123=test123,ROLE_USER
                    </value>
                </property>
            </bean>
        </property>
    </bean>-->

    <bean id="daoAuthenticationProvider" class="org.acegisecurity.providers.dao.DaoAuthenticationProvider">
        <property name="userDetailsService" ref="jdbcDaoImpl"/>
    </bean>

    <!-- specify the JDBC DAO Impl, note the reference to "dataSource" -->
    <!-- Required (mk) -->
    <bean id="jdbcDaoImpl" class="org.acegisecurity.userdetails.jdbc.JdbcDaoImpl">
        <property name="dataSource">
            <ref bean="dataSource"/>
        </property>
        <property name="usersByUsernameQuery">
            <value>
                SELECT username, password FROM user WHERE username=?
            </value>
        </property>
        <property name="authoritiesByUsernameQuery">
            <value>
                select u.username, r.role_name
                from user u, role r, user_role ur
                where u.username=?
                and u.username = ur.username
                and ur.role_name = r.role_name
            </value>
        </property>
    </bean>


    <bean id="authenticationProcessingFilter" class="org.acegisecurity.ui.webapp.AuthenticationProcessingFilter">
        <property name="filterProcessesUrl">
            <value>/j_acegi_security_check.jsp</value>
        </property>
        <property name="authenticationFailureUrl">
            <value>/views/common/logon.jsf?login_error=1</value>
        </property>
        <property name="defaultTargetUrl">
            <value>/views/secure/index.jsf</value>
        </property>
        <property name="authenticationManager">
            <ref bean="authenticationManager"/>
        </property>
    </bean>


    <!-- Required (mk) -->
    <bean id="filterChainProxy"
          class="org.acegisecurity.util.FilterChainProxy">
        <property name="filterInvocationDefinitionSource">
            <value>
                CONVERT_URL_TO_LOWERCASE_BEFORE_COMPARISON
                PATTERN_TYPE_APACHE_ANT
                /**=httpSessionContextIntegrationFilter,authenticationProcessingFilter,exceptionTranslationFilter,filterSecurityInterceptor
            </value>
        </property>
    </bean>

    <!-- Required (mk) -->
    <bean id="httpSessionContextIntegrationFilter"
          class="org.acegisecurity.context.HttpSessionContextIntegrationFilter">
        <property name="context">
            <value>org.acegisecurity.context.SecurityContextImpl</value>
        </property>
    </bean>

    <bean id="securityRequestFilter" class="org.acegisecurity.wrapper.SecurityContextHolderAwareRequestFilter"/>

    <bean id="exceptionTranslationFilter" class="org.acegisecurity.ui.ExceptionTranslationFilter">
        <property name="authenticationEntryPoint">
            <bean class="org.acegisecurity.ui.webapp.AuthenticationProcessingFilterEntryPoint">
                <property name="loginFormUrl">
                    <value>/views/common/logon.jsf</value>
                </property>
                <property name="forceHttps">
                    <value>false</value>
                </property>
            </bean>
        </property>
        <property name="accessDeniedHandler">
            <bean class="org.acegisecurity.ui.AccessDeniedHandlerImpl">
                <property name="errorPage">
                    <value>/views/common/logon.jsf?login_error=1</value>
                </property>
            </bean>
        </property>
    </bean>

    <!-- Required (mk) -->
    <bean id="filterSecurityInterceptor" class="org.acegisecurity.intercept.web.FilterSecurityInterceptor">
        <property name="authenticationManager">
            <ref bean="authenticationManager"/>
        </property>
        <property name="accessDecisionManager">
            <bean class="org.acegisecurity.vote.UnanimousBased">
                <property name="decisionVoters">
                    <list>
                        <bean class="org.acegisecurity.vote.RoleVoter">
                            <!--  Reset the role prefix to "", default is ROLE_ -->
                            <property name="rolePrefix">
                                <value></value>
                            </property>
                        </bean>
                    </list>
                </property>
            </bean>
        </property>
        <property name="objectDefinitionSource">
            <value>
                CONVERT_URL_TO_LOWERCASE_BEFORE_COMPARISON
                PATTERN_TYPE_APACHE_ANT

                /**/admin/**=ROLE_ADMINISTRATOR
                /**/secure/**=ROLE_USER
            </value>
        </property>
        <property name="observeOncePerRequest" value="false"/>
    </bean>

    <bean id="passwordEncoder"
          class="org.acegisecurity.providers.encoding.Md5PasswordEncoder"/>

</beans>

[karldmoore]

I would guess this is because it is expected there will be three parameters returned from the find user query e.g. username, password and enabled. Yours only seems to return two, hence the problem.

"SELECT username,password,enabled FROM users WHERE username = ?";

BTW, you are using programmatic authentication, yet you are using a web front end. Any reason? I would have thought it would be easier to let Acegi handle all that for you.

[mickknutson]

Ok, I am closer, but still have an issue....

I get this exception for both tro and mknutson

08:09:46,617 ERROR [STDERR] org.acegisecurity.DisabledException: User is disabled
08:09:46,617 ERROR [STDERR] at org.acegisecurity.providers.dao.AbstractUserDetail sAuthenticationProvider.authenticate(AbstractUserD etailsAuthentication
Provider.java:134)


Here is my Sql for the table:

CREATE TABLE `user` (
  `username` varchar(50) character set latin1 NOT NULL,
  `password` varchar(50) character set latin1 NOT NULL,
  `account_enabled` char(1) character set latin1 default '0',
  `account_expired` char(1) character set latin1 default '0',
  `account_locked` char(1) character set latin1 default '0',
  `credentials_expired` char(1) character set latin1 default '0',
  `first_name` varchar(50) character set latin1 default NULL,
  `last_name` varchar(50) character set latin1 default NULL,
  `email` varchar(50) character set latin1 default NULL,
  `address` varchar(50) character set latin1 default NULL,
  `address2` varchar(50) character set latin1 default NULL,
  `city` varchar(50) character set latin1 default NULL,
  `state` varchar(50) character set latin1 default NULL,
  `other_state` varchar(50) character set latin1 default NULL,
  `country` varchar(50) character set latin1 default NULL,
  `postal_code` varchar(10) character set latin1 default NULL,
  `last_update` timestamp NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
  `creation_date` datetime default NULL,
  PRIMARY KEY  (`username`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Here is my data from the table

INSERT INTO `role` VALUES ('ROLE_ADMINISTRATOR', 'Administrator');
INSERT INTO `role` VALUES ('ROLE_USER', null);
INSERT INTO `user` VALUES ('mknutson', 'mypassword', '0', '0', '0', '0', 'Mick', 'Knutson', 'email@email.com', '2875-F Northtowne Lane', '#374', 'Reno', 'NV', null, 'us', '94110', '2006-11-07 19:44:30', '2006-11-01 18:24:02');
INSERT INTO `user` VALUES ('tro', 'tro', '1', '1', '1', '1', 'Thump', 'User', 'thump@thumpradio.com', '725 Florida Street', '#5', 'San Francisco', 'CA', '', null, '94110', '2007-02-16 10:09:51', '2006-11-08 06:47:37');
INSERT INTO `user_role` VALUES ('mknutson', 'ROLE_USER');
INSERT INTO `user_role` VALUES ('tro', 'ROLE_USER');

here is the query I used to make this work:

SELECT username,password,account_enabled FROM user WHERE username = ?

[karldmoore]

I would presume the user you are trying to authenticate with is disabled e.g. account_enabled=0.

[mickknutson]

I would presume the user you are trying to authenticate with is disabled e.g. account_enabled=0.

my data shows that mknutson was disabled and I tried that user, but tro was enabled and that also failed with the same error.

[karldmoore]

It might be that you have defined the boolean columns as a character type. Have you tried a numeric type e.g something like bit or int. I would guess that characters are getting converted to false.

[mickknutson]

It might be that you have defined the boolean columns as a character type. Have you tried a numeric type e.g something like bit or int. I would guess that characters are getting converted to false.

That was it!!! Strange, but whatever.... at least it works now.

[karldmoore]

That was it!!! Strange, but whatever.... at least it works now.

Fantastic, glad it's working! I guess it always returns false if it can't convert the type to a boolean.