Spring Security using both username or email

Question

I'm using Spring Security in my Spring MVC app.

JdbcUserDetailsManager is initialized with the following query for authentication:

select username, password, enabled from user where username = ?

And authorities are being loaded here:

select u.username, a.authority from user u join authority a on u.userId = a.userId where username = ?

I would like to make it so that users can login with both username and email. Is there a way to modify these two queries to achieve that ? Or is there an even better solution ?

Answer 1

Unfortunatelly there is no easy way doing this just by changing the queries. The problem is that spring security expects that the users-by-username-query and authorities-by-username-query have a single parameter (username) so if your query contain two parameters like

username = ? or email = ?

the query will fail.

What you can do, is to implement your own UserDetailsService that will perform the query (or queries) to search user by username or email and then use this implementation as authentication-provider in your spring security configuration like

  <authentication-manager>
    <authentication-provider user-service-ref='myUserDetailsService'/>
  </authentication-manager>

  <beans:bean id="myUserDetailsService" class="xxx.yyy.UserDetailsServiceImpl">
  </beans:bean>

Answer 2

I had the same problem, and after trying with a lot of different queries, with procedures... I found that this works:

public void configAuthentication(AuthenticationManagerBuilder auth)
        throws Exception {
    // Codificación del hash
    PasswordEncoder pe = new BCryptPasswordEncoder();

    String userByMailQuery = "SELECT mail, password, enabled FROM user_ WHERE mail = ?;";
    String userByUsernameQuery = "SELECT mail, password, enabled FROM user_ WHERE username=?";
    String roleByMailQuery = "SELECT mail, authority FROM role WHERE mail =?;";

    auth.jdbcAuthentication().dataSource(dataSource).passwordEncoder(pe)
            .usersByUsernameQuery(userByMailQuery)
            .authoritiesByUsernameQuery(roleByMailQuery);

    auth.jdbcAuthentication().dataSource(dataSource).passwordEncoder(pe)
            .usersByUsernameQuery(userByUsernameQuery)
            .authoritiesByUsernameQuery(roleByMailQuery);

}

Its just repeat the configuration with the two queries.