CODEWITHSUNDEEP
javaspring-security
xampo
xampo

Reputation: 399

Authentication of users from two database tables in Spring Security

I have two tables: User and Admin. Both of them I have a username, password and role. And I want to log in with either one form either for admin or user. I tried this:

@Autowired
    public void configAuthentication(AuthenticationManagerBuilder auth) throws Exception {
        auth.
                jdbcAuthentication().dataSource(dataSource)
                .usersByUsernameQuery("SELECT username,password FROM user WHERE username =?  UNION SELECT username,password FROM admin WHERE username =? ")
                .authoritiesByUsernameQuery("SELECT username,role FROM user WHERE username =?  UNION SELECT username,role FROM admin WHERE username =? ");
    }

, but I got exception:

org.springframework.security.authentication.InternalAuthenticationServiceException: PreparedStatementCallback; bad SQL grammar [SELECT username,password FROM user WHERE username =?  UNION SELECT username,password FROM admin WHERE username =? ]; nested exception is java.sql.SQLException: No value specified for parameter 2

So how can I log in to system with authentication two object type (User, Admin)?

Upvotes: 1

Views: 2820

Answers (1)

secondbreakfast
secondbreakfast

Reputation: 4384

Here is a very crude solution using a custom UserDetailsService.

@Service
public class CustomUserDetailsService implements UserDetailsService {

    @Autowired
    private UserRepository userRepository;
    @Autowired
    private AdminRepository adminRepository;

    @Override
    public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
        // first try loading from User table
        User user = userRepository.findByUsername(username);
        if (user != null) {
            return new CustomUserDetails(user.getUsername(), user.getPassword(), user.getRole());
        } else {
            // Not found in user table, so check admin
            Admin admin = adminRepository.findByUsername(username);
            if (admin != null) {
                return new CustomUserDetails(admin.getUsername(), admin.getPassword(), admin.getRole());
            }
        }
        throw new UsernameNotFoundException("User '" + username + "' not found");
    }

    public class CustomUserDetails implements UserDetails {

        private String username;
        private String password;
        private Collection<? extends GrantedAuthority> authorities;

        public CustomUserDetails() {
            super();
        }

        public CustomUserDetails(String username, String password, String role) {
            this.username = username;
            this.password = password;
            List<GrantedAuthority> grantedAuthorities = new ArrayList<>();
            grantedAuthorities.add(new SimpleGrantedAuthority(role));
            this.authorities = grantedAuthorities;
        }

        @Override
        public Collection<? extends GrantedAuthority> getAuthorities() {
            return authorities;
        }

        @Override
        public String getPassword() {
            return password;
        }

        @Override
        public String getUsername() {
            return username;
        }

        @Override
        public boolean isAccountNonExpired() {
            return true;
        }

        @Override
        public boolean isAccountNonLocked() {
            return true;
        }

        @Override
        public boolean isCredentialsNonExpired() {
            return true;
        }

        @Override
        public boolean isEnabled() {
            return true;
        }

    }

}

And the config

@EnableWebSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter {

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

                .authorizeRequests()
                .anyRequest().authenticated()
                .and().formLogin();
    }

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

    @Bean
    public UserDetailsService userDetailsService() {
        return new CustomUserDetailsService();
    }

    @Bean
    public DaoAuthenticationProvider authProvider() {
        DaoAuthenticationProvider authProvider = new DaoAuthenticationProvider();
        authProvider.setUserDetailsService(userDetailsService());
        // This assumes passwords are in plain text (but I hope they aren't!)
        authProvider.setPasswordEncoder(NoOpPasswordEncoder.getInstance());
        return authProvider;
    }

}

Upvotes: 3

Related Questions