users-by-username-query returns no results

问题:

I am trying to get authentication roles from DB. Tables are there, and here is my context:

 <!-- Configures in-memory implementation of the UserDetailsService implementation -->
<security:authentication-manager alias="authenticationManager">
    <security:authentication-provider>
    <security:jdbc-user-service data-source-ref="dataSource"
       users-by-username-query="select * from users where email=?" 
       authorities-by-username-query="select u.email, ur.authority from users u, usersroles ur where u.id = ur.userid and u.email = ?" 
    />
    </security:authentication-provider>
</security:authentication-manager>

The user is authenticated by email. When doing this query in DB console, it returns one obejct, however when I look into logs, I find:

16:18:55,517 DEBUG JdbcTemplate:637 - Executing prepared SQL query
16:18:55,518 DEBUG JdbcTemplate:572 - Executing prepared SQL statement [select * from users where email=?]
16:18:55,519 DEBUG DataSourceUtils:110 - Fetching JDBC Connection from DataSource
16:18:55,519 DEBUG DriverManagerDataSource:162 - Creating new JDBC DriverManager Connection to [jdbc:h2:file:../lib/Money]
16:18:55,582 DEBUG DataSourceUtils:327 - Returning JDBC Connection to DataSource
16:18:55,607 DEBUG JdbcUserDetailsManager:154 - Query returned no results for user ''
16:18:55,610 DEBUG DaoAuthenticationProvider:134 - User '' not found
16:18:55,610 DEBUG UsernamePasswordAuthenticationFilter:346 - Authentication request failed: org.springframework.security.authentication.BadCredentialsException: Bad credentials

What am I doing wrong?

User and password are sent from frontend code. Everything is fine in request I guess. I send a simple JSON there with username and password. I gues there's something wrong with autenticationManager. Here is the code:

    <!-- Configures a custom login filter bean -->
<bean id="loginFilter" class="org.springframework.security.web.authentication.UsernamePasswordAuthenticationFilter">
    <property name="authenticationManager" ref="authenticationManager"/>
    <property name="authenticationFailureHandler" ref="restAuthenticationFailureHandler"/>
    <property name="authenticationSuccessHandler" ref="restAuthenticationSuccessHandler"/>
    <property name="filterProcessesUrl" value="/api/login/"/>
    <property name="usernameParameter" value="username"/>
    <property name="passwordParameter" value="password"/>
    <property name="postOnly" value="true"/>
</bean>

回答1:

Try to write the user by username query so that it return 3 parameter in this order:

• username
• password
• enabled

For example:

 <security:jdbc-user-service data-source-ref="dataSource"
   users-by-username-query="SELECT username, password, true FROM users WHERE email=?" 
   authorities-by-username-query="select u.email, ur.authority from users u, usersroles ur where u.id = ur.userid and u.email = ?" 
/>

回答2:

Configure Like that.

<beans:beans xmlns="http://www.springframework.org/schema/security"
    xmlns:beans="http://www.springframework.org/schema/beans" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
    http://www.springframework.org/schema/security
    http://www.springframework.org/schema/security/spring-security-3.2.xsd">

    <!-- enable use-expressions -->
    <http auto-config="true" use-expressions="true">

        <intercept-url pattern="/admin**" access="hasRole('ROLE_ADMIN')" />

        <!-- access denied page -->
        <access-denied-handler error-page="/403" />

        <form-login 
            login-page="/login" 
            default-target-url="/welcome" 
            authentication-failure-url="/login?error" 
            username-parameter="username"
            password-parameter="password" />
        <logout logout-success-url="/login?logout"  />
        <!-- enable csrf protection -->
        <csrf/>
    </http>

    <!-- Select users and user_roles from database -->
    <authentication-manager>
      <authentication-provider>
        <jdbc-user-service data-source-ref="dataSource"
          users-by-username-query=
            "select username,password, enabled from users where username=?"
          authorities-by-username-query=
            "select username, role from user_roles where username =?  " />
      </authentication-provider>
    </authentication-manager>

</beans:beans>