Spring Security Using Username or Email

Question

I am using Spring Security in my Spring MVC application.

JdbcUserDetailsManager initialized with the following authentication request:

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

And here the permissions are loaded:

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

I would like to make it possible for users to log in with a username and email. Is there a way to modify these two queries to achieve this? Or is there an even better solution?

Answer 1

Unfortunately, there is no easy way to do this by simply changing the queries. The problem is that spring security assumes that requesting a user by username and requesting authority by username have one parameter (username), so if your request contains two parameters, for example

 username = ? or email = ? 

The request will fail.

What you can do is implement your own UserDetailsService , which will execute a request (or requests) to search for a user by username or email, and then use this implementation as an authentication provider in your spring Security Configuration, for example

  <authentication-manager> <authentication-provider user-service-ref='myUserDetailsService'/> </authentication-manager> <beans:bean id="myUserDetailsService" class="xxx.yyy.UserDetailsServiceImpl"> </beans:bean> 

Answer 2

If I understood this correctly, the problem is that you want to search for the username entered by the user in two different database columns.

Of course you can do this by setting up a 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 like 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 = ?) 

Answer 3

I had the same problem, and after trying with many different requests, 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); } 

Just repeat the configuration with two queries.

Answer 4

You can define user requests in the <jdbc-user-service> in the 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 a class that implements org.springframework.security.core.userdetails.UserDetailsService , and configure the application to use as an authentication source. Inside the UserDetails user service, you can perform the queries necessary to get the user from the database.

Answer 5

Here is a workaround I discovered. Essentially, I combine the username and email address with the separator character between them (for example, “ jsmith~[e-mail] ”), and check if the parameter matches the left side of the separator or whether the separator matches the right side:

 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 a delimiter character (such as ~) may exist in the username or email, use the non-standard delimiter character (for example, X'9C ') instead.