Spring Security JDBC authentication without authorization

Question

I do not need Admin role, so I need to perform authentication only.

@Configuration
@EnableWebSecurity
protected static class WebSecurityConfig extends WebSecurityConfigurerAdapter {

    @Autowired
    private DataSource dataSource;

    @Autowired
    private MyAuthenticationSuccessHandler authenticationSuccessHandler;


    @Override
    protected void configure(HttpSecurity http) throws Exception {
        http.authorizeRequests().anyRequest().authenticated().and().formLogin().loginPage("/login")
                .successHandler(authenticationSuccessHandler).failureUrl("/login?error").permitAll().and().logout()
                .permitAll();
        http.csrf().disable();

    }

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

        auth.jdbcAuthentication().dataSource(dataSource)
                .usersByUsernameQuery("select login, password, enabled from users where login=?");

    }

}

My problem is, that when I try to run it, I get

org.springframework.security.authentication.InternalAuthenticationServiceException: PreparedStatementCallback; bad SQL grammar [select username,authority from authorities where username = ?]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'phonebook.authorities' doesn't exist

, which is kind of logical, because I don't have .authoritiesByUsernameQuery() method applied. The question is how can I overcome it? How can I assign default role to all my users without need to query database? How can I login from database using only login and password, and no role?

Answer 1

Option 1 is to set a "dummy" query with a static role:

public void configure(AuthenticationManagerBuilder auth) throws Exception {
    auth.jdbcAuthentication().dataSource(dataSource)
            .usersByUsernameQuery("select login, password, enabled from users where login=?")
            .authoritiesByUsernameQuery("select login, 'ROLE_USER' from users where login=?");
}

Option 2 if you want to optimize away the second query:

public void configure(AuthenticationManagerBuilder auth) throws Exception {
    auth.userDetailsService(userDetailsService);
}

where you must implement the UserDetailsService interface.