Spring security jdbcAuthentication does not work with default roles processing

Using

    @Autowired
    public void configureGlobalSecurity(AuthenticationManagerBuilder auth) throws Exception {
         auth.inMemoryAuthentication().withUser("dba").password("root123").roles("ADMIN","DBA");

my example works fine. For example for

      http.authorizeRequests()
        // ...
        .antMatchers("/db/**").access("hasRole('ADMIN') and hasRole('DBA')")
        .and().formLogin()
        .and().exceptionHandling().accessDeniedPage("/Access_Denied");

If I have changed inMemoryAuthentication to spring jdbc default - i got an role issue than.

    @Autowired
    public void configureGlobalSecurity(AuthenticationManagerBuilder auth) throws Exception {
         auth.jdbcAuthentication().dataSource(dataSource);

I sure I configured db and schema using spring recommendations (to be able to use default jdbc authentication).

In debug mode I can see result of loading from db in the

org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl
    #loadUserByUsername(username)[line 208]
    return createUserDetails(username, user, dbAuths);

It returns similar result with in memory configuration:

org.springframework.security.core.userdetails.User@183a3:
     Username: dba;
     Password: [PROTECTED];
     Enabled: true;
     AccountNonExpired: true;
     credentialsNonExpired: true;
     AccountNonLocked: true;
     Granted Authorities: ADMIN,DBA

As you can see it loads correspond Granted Authorities, but http request redirects me to .accessDeniedPage("/Access_Denied"). I confused because It should work for user like time before.

I do not use spring boot in my project. My logs does not contain any configuration of jdbc errors. I have spend a lot of time to investigate details and my ideas have just finished. Do you think I need add to build some cache libraries or something else?

There are 2 gotchas in play here.

The first is that when using hasRole('ADMIN') that first a check is done if it starts with the role prefix (for which the default is ROLE_) if not the passed in role is prefix with it (see also the reference guide). So in this case the actual authority checked is ROLE_ADMIN and not ADMIN as you expect/assume.

The second is that when using the in memory option the roles method does the same as mentioned here. It checks if the passed in roles start with the role prefix and if not adds it. So in your sample with the in memory one you end up with authorities ROLE_ADMIN and ROLE_DBA.

However in your JDBC option you have authorities ADMIN and DBA and hence the hasRole('ADMIN') check fails because ROLE_ADMIN isn't equal to ADMIN.

To fix you have several options.

1. Instead of hasRole use hasAuthority the latter doesn't add the role prefix and for the in memory option use authorities instead of roles.
2. In the JDBC option prefix the authorities in the database with ROLE_
3. Set the default role prefix to empty.

Using hasAuthority

First change the configuration of the in memory database to use authorities instead of roles.

auth.inMemoryAuthentication()
    .withUser("dba").password("root123")
    .authorities("ADMIN","DBA");

next change your expressions as well

.antMatchers("/db/**").access("hasAuthority('ADMIN') and hasAuthority('DBA')")

Prefix with ROLE_

In the script that inserts the authorities prefix the authorities with ROLE_.

Remove the default role prefix

This is a bit tricky and is extensivly described in [the migration guide].

There is no easy configuration option and requires a BeanPostProcessor.

public class DefaultRolesPrefixPostProcessor implements BeanPostProcessor, PriorityOrdered {

    @Override
    public Object postProcessAfterInitialization(Object bean, String beanName)
            throws BeansException {

        // remove this if you are not using JSR-250
        if(bean instanceof Jsr250MethodSecurityMetadataSource) {
            ((Jsr250MethodSecurityMetadataSource) bean).setDefaultRolePrefix(null);
        }

        if(bean instanceof DefaultMethodSecurityExpressionHandler) {
            ((DefaultMethodSecurityExpressionHandler) bean).setDefaultRolePrefix(null);
        }
        if(bean instanceof DefaultWebSecurityExpressionHandler) {
            ((DefaultWebSecurityExpressionHandler) bean).setDefaultRolePrefix(null);
        }
        if(bean instanceof SecurityContextHolderAwareRequestFilter) {
            ((SecurityContextHolderAwareRequestFilter)bean).setRolePrefix("");
        }
        return bean;
    }

    @Override
    public Object postProcessBeforeInitialization(Object bean, String beanName)
            throws BeansException {
        return bean;
    }

    @Override
    public int getOrder() {
        return PriorityOrdered.HIGHEST_PRECEDENCE;
    }
}