Spring Security users-by-username-query with id_user

Question

I want to use spring security but I've never used it before.I want to retrieve user and roles from my tables (users,roles and user_roles).I have investigated about users-by-username-query. In all examples were same as the following example.

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

But I want to use id_user and id_role instead of username and role. Is it possible ? And must I change login page field names ? Thanks in advance

Answer 1

This is a common situation.

First, as I suggested in comments, use aliases to rename the fields which represent username, password and wether the user is enabled or not. Just like this:

users-by-username-query=
  "select user.user_login as username, user.user_pwd as password, user.user_enabled as enabled 
  from user where user.user_login=?"

Then, is usual too to have the authorities in a different table somehow related with the user table. This is a common case:

Where users are related with roles in a n-to-n relationship.

In this case, the authorities-by-username-query should be like this

 authorities-by-username-query=
        "SELECT users.name as username, roles.role as role 
        FROM users 
        INNER JOIN user_role ON users.id_user = user_role.id_user 
        INNER JOIN roles ON user_role.id_role = roles.id_role
        WHERE users.name = ?  "

As a test, for a set of tables containing this data:

users:

roles:

user_role

for a received username jlumietu the result should be this:

Finally, I've had some cases where I have extended org.springframework.security.authentication.dao.DaoAuthenticationProvider but I think that this is the simplest way of doing this kind of handling

Answer 2

For string boot 2 It's worked this way

package com.gkatzioura.spring.security.config;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;

import javax.sql.DataSource;


@Configuration
@EnableWebSecurity
public class PasswordEncodedSecurityConfig extends WebSecurityConfigurerAdapter {

    @Autowired
    private DataSource dataSource;

    @Override
    protected void configure(AuthenticationManagerBuilder auth) throws Exception {

        auth.jdbcAuthentication().dataSource(dataSource)
                //.passwordEncoder(new BCryptPasswordEncoder())
                .passwordEncoder(passwordEncoder())
                .usersByUsernameQuery("select username,password,enabled from users where username =?")
                .authoritiesByUsernameQuery(" select u.username, r.name from users u join user_role ur on u.id = ur.user_id " +
                        " join role r on ur.role_id = r.id where u.username =?");
    }

    @Override
    protected void configure(HttpSecurity http) throws Exception {

        http.authorizeRequests()
                .antMatchers("/public").permitAll()
                .anyRequest().authenticated()
                .and().formLogin().permitAll()
                .and().logout() .permitAll();
    }
    
    
    @Bean
    public PasswordEncoder passwordEncoder(){
        BCryptPasswordEncoder passwordEncoder = new BCryptPasswordEncoder();
        return passwordEncoder;
    }
    

}