Spring Security using both username or email

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 ?

Solution 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>

Solution 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.

Solution 3:

If I understood this correctly, then the problem is that you want to lookup username entered by the user in two different DB columns.

Sure, you can do that by customizing UserDetailsService.

public class CustomJdbcDaoImpl extends JdbcDaoImpl {

    @Override
    protected List<GrantedAuthority> loadUserAuthorities(String username) {
    return getJdbcTemplate().query(getAuthoritiesByUsernameQuery(), new String[] {username, username}, new RowMapper<GrantedAuthority>() {
            public GrantedAuthority mapRow(ResultSet rs, int rowNum) throws SQLException {
              .......
            }
        });
    }

    @Override
    protected List<UserDetails> loadUsersByUsername(String username) {
        return getJdbcTemplate().query(getUsersByUsernameQuery(), new String[] {username, username}, new RowMapper<UserDetails>() {
            public UserDetails mapRow(ResultSet rs, int rowNum) throws SQLException {
                 .......
            }
        });
}

Your bean configuration for this class will look something like this.

<beans:bean id="customUserDetailsService" class="com.xxx.CustomJdbcDaoImpl">
    <beans:property name="dataSource" ref="dataSource"/>
    <beans:property name="usersByUsernameQuery">
        <beans:value> YOUR_QUERY_HERE</beans:value>
    </beans:property>
    <beans:property name="authoritiesByUsernameQuery">
        <beans:value> YOUR_QUERY_HERE</beans:value>
    </beans:property>
</beans:bean>

Your queries will look something similar to this

select username, password, enabled from user where (username = ? or email = ?)
select u.username, a.authority from user u join authority a on u.userId = a.userId where (username = ? or email = ?)

Solution 4:

You can use your UserDetailesService.and config like the below code.

@EnableWebSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter {

    @Autowired
    private MyUserDetailsService userDetailsService;

    @Override
    protected void configure(AuthenticationManagerBuilder auth) throws Exception {
        auth.userDetailsService(userDetailsService);
    }
}

The point is that you don't need to return the user with the same username and you can get user-email and return user with the username. The code will be like the code below.

@Service
public class MyUserDetailsService implements UserDetailsService {

    @Override
    public UserDetails loadUserByUsername(String usernameOrEmail) throws UsernameNotFoundException {
        var user = /** here search user via jpa or jdbc by username or email **/;
        if(user == null ) 
            throw new UsernameNotFoundExeption();
        else return new UserDetail(user); // You can implement your user from UserDerail interface or create one;
    }

}

tip* UserDetail is an interface and you can create one or use Spring Default.

Solution 5:

You can define custom queries in <jdbc-user-service> tag in users-by-username-query and authorities-by-username-query attributes respectively.

<jdbc-user-service data-source-ref="" users-by-username-query="" authorities-by-username-query=""/>

Update

You can create class which implements org.springframework.security.core.userdetails.UserDetailsService and configure your application to use it as an authentication source. Inside your custom UserDetails service you can execute queries that you need to obtain user from database.