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 ?

Vichyssoise

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>

Tabor

I've looked at the class but have a bit of trouble figuring out which methods should I overwrite. Any pointers? – Vichyssoise

I think I see it now: loadUserByUsername(String username) – Vichyssoise

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.

Malaise

Answer 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 = ?)

Unworldly

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

Shavon

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

Hemipterous

That's exactly what I've been doing already and I've included both of them in my question. The issue is that I have to customize them to include both username and email. – Vichyssoise

You have not mentioned that you know how to configure them. And you are asking "Is there a way to modify these two queries to achieve that ?". – Hemipterous

Answer 6

You can config UserDetailesService class like this.

public class UserDetailsServiceImpl implements UserDetailsService{

    @Autowired
    private UserRepository userRepository;
    
    @Override
    public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
        User user = this.userRepository.getUserByEmailOrUserName(username); //for fetch user
        if(user==null) {
            throw new UsernameNotFoundException("User doesn't exists");
        }
        UserDetailsImpl customUserDetails = new UserDetailsImpl(user);
        return customUserDetails;
    }

}

Your queries will look something similar to this

select * from user where email = ? or username = ?

UserRepository class for fetch user data

@Repository
public interface UserRepository extends JpaRepository<User, Integer>{

    @Query("from user where email = :u or username = :u")
    public User getUserByEmailOrUserName(@Param("u") String username);
    
}

You can also add phone number while doing login.

Shimkus

Answer 7

Here is a workaround I discovered. Basically I'm concatenating username and email address with a delimiter character in between (for example '[email protected]'), and checking to see if the parameter matches the left side of the delimiter or if matches the right side of the delimiter:

select username, password, enabled 
  from users 
 where ? in (substring_index(concat(username, '~',email),'~', 1), 
             substring_index(concat(username, '~',email),'~', -1))

If you are concerned that the delimiter character (such as ~) might exist within the username or email, use a non-standard delimiter character instead (for example, X'9C')

Zampino

Answer 8

you can change your config as follows :

@Bean
    public UserDetailsService userDetailsService() {
        return input -> repository.findByUsername(input)
                .orElseGet(() -> repository.findByEmail(input)
                        .orElseThrow(() -> new UsernameNotFoundException("account not found")));
    }

Impoverish

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Wellbalanced