Image description
Kudos 👏 to reach till here.
In the previous article, we were implementing in-memory authentication in Spring security. But what if we need to connect to a datasource instead of an in-memory.
In this article, we’ll fo throught JDBC authentication.

Most of the concept is same as before, the only difference is setting up a jdbc authentication in the configure method.

Below is the code —

@EnableWebSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter {
  @Override 
  protected void configure (AuthenticationManagerBuilder auth) {
    auth.jdbcAuthentication();
  }
  @Override
  protected void configure (HttpSecurity http) {
    http.authorizeRequests()
        .antMatchers("/admin").hasRole("ADMIN").
        .antMatchers("/user").hasAnyRole("ADMIN","USER").
        .antMatchers("/**").permitAll().
        .hasAnyRole();
  }
}

Now, we need to setup our authentication to tell Spring Security to lookup the user and password in the database. To do so, we need a bean for data source. By default, Spring security provides DataSource class which we can autowire in our project.

@Autowired
DataSource datasource;
@Override 
  protected void configure (AuthenticationManagerBuilder auth) {
    auth.jdbcAuthentication()
        .dataSource(dataSource);
  }

But how does the spring knows the configuration of the data source.
We must configure it somewhere or else if we’re using H2 which is in-memory database, then spring by default creates a datasource.

For now, lets assume we have H2 in our class path.

@Autowired
DataSource datasource;
@Override 
  protected void configure (AuthenticationManagerBuilder auth) {
    auth.jdbcAuthentication()
        .dataSource(dataSource);
        .dataSource(dataSource)
        .withDefaultSchema()
        .withUser(
        user.withUserName("user")
            .password("pass")
            .roles("USER")
            .withUserName("admin")
            .password("pass")
            .roles("ADMIN")
        );
  }

In the above code, we’re using a default schema and creating the users.
When a client sends a request along with a password, the authentication configuration creates these 2 users and checks do the user in request matches to these. Hence authenticating the request.

Now, what if we already have a schema or we don’t want to use the default schema and you don’t want to put user creation logic in the code?
Create your own schema in resources -> schema.sql

schema.sql :

create table users(
 username varchar_ignorecase(50) not null primary key,
 password varchar_ignorecase(50) not null,
 enabled boolean not null
);

create table authorities (
 username varchar_ignorecase(50) not null,
 authority varchar_ignorecase(50) not null,
 constraint fk_authorities_users foreign key(username) references users(username)
);
create unique index ix_auth_username on authorities (username,authority);

data.sql :

INSERT INTO users (username, password, enabled) VALUES 
('user', 'pass', true),
('admin', 'pass', true);

INSERT INTO authorities (username, authority) VALUES 
('user', 'ROLE_USER'),
('admin', 'ROLE_ADMIN');
Important notes:

The password should ideally be encoded (BCrypt is recommended) rather than stored in plain text like this
Spring Security expects the authorities/roles to be prefixed with “ROLE_” by default
The enabled column must be set to true for the users to be able to authenticate
Below is the complete code for configuration class:

@EnableWebSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter {
  @Autowired
  DataSource dataSource;

  @Override 
  protected void configure(AuthenticationManagerBuilder auth) throws Exception {
    auth.jdbcAuthentication()
        .dataSource(dataSource)
        .passwordEncoder(passwordEncoder());
  }

  @Override
    protected void configure (HttpSecurity http) {
      http.authorizeRequests()
        .antMatchers("/admin").hasRole("ADMIN").
        .antMatchers("/user").hasAnyRole("ADMIN","USER").
        .antMatchers("/**").permitAll().
        .hasAnyRole();
  }

  @Bean
  public PasswordEncoder passwordEncoder() {
    return new BCryptPasswordEncoder();
  }
}

When you autowire DataSource and use it in auth.jdbcAuthentication(), Spring Security automatically uses this DataSource to query the database for user authentication.

By default, it looks for tables: users (with username, password, enabled columns) , authorities (with username, authority columns)

If your schema is different, you can customize the queries.

auth.jdbcAuthentication()
        .dataSource(dataSource)
        .usersByUsernameQuery("Select username, password, enables"
        + "from custom_users where username = ?")
        .authoritiesByUsernameQuery("Select username, authority "
        + "from authorities where username = ?");

If you’re using MySQL, you need to define the DataSource in application.properties:

spring.datasource.url=jdbc:mysql://localhost:3306/spring_security_db
spring.datasource.username=root
spring.datasource.password=password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

Great 👏

You’ve now got a good idea about spring security and jdbc authentication.

Next, let’s dive deep into JWT and implementing it using Spring security.
Thanks for reading.