Spring Security: Exploring JDBC Authentication

1. Overview

In this short tutorial, we’ll explore the capabilities offered by Spring to perform JDBC Authentication using an existing DataSource configuration.

In our Authentication with a Database-backed UserDetailsService post, we analyzed one approach to achieve this, by implementing the UserDetailService interface ourselves.

This time, we’ll make use of the AuthenticationManagerBuilder#jdbcAuthentication directive to analyze the pros and cons of this simpler approach.

2. Using an Embedded H2 Connection

First of all, we’ll analyze how we can achieve authentication using an embedded H2 database.

This is easy to achieve because most of the Spring Boot’s autoconfiguration are prepared out-of-the-box for this scenario.

2.1. Dependencies and Database Configuration

Let’s start by following the instructions of our previous Spring Boot With H2 Database post to:

1. Include the corresponding spring-boot-starter-data-jpa and h2 dependencies
2. Configure the database connection with application properties
3. Enable the H2 console

2.2. Configuring JDBC Authentication

We’ll use Spring Security’s AuthenticationManagerBuilder configuration helper to configure JDBC Authentication:

@Autowired
private DataSource dataSource;

@Autowired
public void configureGlobal(AuthenticationManagerBuilder auth)
  throws Exception {
    auth.jdbcAuthentication()
      .dataSource(dataSource)
      .withDefaultSchema()
      .withUser(User.withUsername("user")
        .password(passwordEncoder().encode("pass"))
        .roles("USER"));
}

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

As we can see, we’re using the autoconfigured DataSource. The withDefaultSchema directive adds a database script that will populate the default schema, allowing users and authorities to be stored.

This basic user schema is documented in the Spring Security Appendix.

Finally, we’re creating an entry in the database with a default user programmatically.

2.3. Verifying the Configuration

Let’s create a very simple endpoint to retrieve the authenticated Principal information:

@RestController
@RequestMapping("/principal")
public class UserController {

    @GetMapping
    public Principal retrievePrincipal(Principal principal) {
        return principal;
    }
}

In addition, we’ll secure this endpoint, whilst permitting access to the H2 console:

@Configuration
public class SecurityConfiguration {

    @Bean
    public SecurityFilterChain filterChain(HttpSecurity httpSecurity) throws Exception {
        httpSecurity.authorizeHttpRequests(authorizationManagerRequestMatcherRegistry ->
                        authorizationManagerRequestMatcherRegistry
                                .requestMatchers(PathRequest.toH2Console()).permitAll().anyRequest().authenticated())
                .formLogin(AbstractAuthenticationFilterConfigurer::permitAll);

        httpSecurity.csrf(httpSecurityCsrfConfigurer -> httpSecurityCsrfConfigurer.ignoringRequestMatchers(PathRequest.toH2Console()));

        httpSecurity.headers(httpSecurityHeadersConfigurer ->
                        httpSecurityHeadersConfigurer.frameOptions(HeadersConfigurer.FrameOptionsConfig::sameOrigin));
        return httpSecurity.build();
    }

}

Note: here we’re reproducing the former security configuration implemented by Spring Boot, but in a real-life scenario, we probably won’t enable the H2 console at all.

Now we’ll run the application and browse the H2 console. We can verify that Spring is creating two tables in our embedded database: users and authorities.

Their structure corresponds to the structure defined in the Spring Security Appendix we mentioned before.

Finally, let’s authenticate and request the /principal endpoint to see the related information, including the user details.

2.4. Under the Hood

At the beginning of this post, we presented a link to a tutorial that explained how we can customize database-backed authentication implementing the UserDetailsService interface; we strongly recommend having a look at that post if we want to understand how things work under the hood.

In this case, we’re relying on an implementation of this same interface provided by Spring Security; the JdbcDaoImpl.

If we explore this class, we’ll see the UserDetails implementation it uses, and the mechanisms to retrieve user information from the database.

This works pretty well for this simple scenario, but it has some drawbacks if we want to customize the database schema, or even if we want to use a different database vendor.

Let’s see what happens if we change the configuration to use a different JDBC service.

3. Adapting the Schema for a Different Database

In this section, we’ll configure authentication on our project using a MySQL database.

As we’ll see next, in order to achieve this, we’ll need to avoid using the default schema and provide our own.

3.1. Dependencies and Database Configuration

For starters, let’s remove the h2 dependency and replace it for the corresponding MySQL library:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>

As always, we can look up the latest version of the library in Maven Central.

Now let’s re-set the application properties accordingly:

spring.datasource.url=
  jdbc:mysql://localhost:3306/jdbc_authentication
spring.datasource.username=root
spring.datasource.password=pass

3.2. Running the Default Configuration

Of course, these should be customized to connect to your running MySQL server. For testing purposes, here we’ll start a new instance using Docker:

docker run -p 3306:3306
  --name bael-mysql
  -e MYSQL_ROOT_PASSWORD=pass
  -e MYSQL_DATABASE=jdbc_authentication
  mysql:latest

Let’s run the project now to see if the default configuration is suitable for a MySQL database.

Actually, the application won’t be able to get started, because of an SQLSyntaxErrorException. This actually makes sense; as we said, most of the default autoconfiguration is suitable for an HSQLDB.

In this case, the DDL script provided with the withDefaultSchema directive uses a dialect not suitable for MySQL.

Therefore, we need to avoid using this schema and provide our own.

3.3. Adapting the Authentication Configuration

As we don’t want to use the default schema, we’ll have to remove the proper statement from the AuthenticationManagerBuilder configuration.

Also, since we’ll be providing our own SQL scripts, we can avoid trying to create the user programmatically:

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

Now let’s have a look at the database initialization scripts.

First, our schema.sql:

CREATE TABLE users (
  username VARCHAR(50) NOT NULL,
  password VARCHAR(100) NOT NULL,
  enabled TINYINT NOT NULL DEFAULT 1,
  PRIMARY KEY (username)
);
  
CREATE TABLE authorities (
  username VARCHAR(50) NOT NULL,
  authority VARCHAR(50) NOT NULL,
  FOREIGN KEY (username) REFERENCES users(username)
);

CREATE UNIQUE INDEX ix_auth_username
  on authorities (username,authority);

And then, our data.sql:

-- User user/pass
INSERT INTO users (username, password, enabled)
  values ('user',
    '$2a$10$8.UnVuG9HHgffUDAlk8qfOuVGkqRzgVymGe07xd00DMxs.AQubh4a',
    1);

INSERT INTO authorities (username, authority)
  values ('user', 'ROLE_USER');

Finally, we should modify some other application properties:

• Since we’re not expecting Hibernate to create the schema now, we should disable the ddl-auto property
• By default, Spring Boot initializes the data source only for embedded databases, which is not the case here:

spring.sql.init.mode=always
spring.jpa.hibernate.ddl-auto=none

As a result, we should now be able to start our application correctly, authenticating and retrieving the Principal data from the endpoint.

Also, note that the spring.sql.init.mode property was introduced in Spring Boot 2.5.0; for earlier versions, we need to use spring.datasource.initialization-mode.

4. Adapting the Queries for a Different Schema

Let’s go a step further. Imagine the default schema is just not suitable for our needs.

4.1. Changing the Default Schema

Imagine, for example, that we already have a database with a structure that slightly differs from the default one:

CREATE TABLE bael_users (
  name VARCHAR(50) NOT NULL,
  email VARCHAR(50) NOT NULL,
  password VARCHAR(100) NOT NULL,
  enabled TINYINT NOT NULL DEFAULT 1,
  PRIMARY KEY (email)
);
  
CREATE TABLE authorities (
  email VARCHAR(50) NOT NULL,
  authority VARCHAR(50) NOT NULL,
  FOREIGN KEY (email) REFERENCES bael_users(email)
);

CREATE UNIQUE INDEX ix_auth_email on authorities (email,authority);

Finally, our data.sql script will be adapted to this change too:

-- User [email protected]/pass
INSERT INTO bael_users (name, email, password, enabled)
  values ('user',
    '[email protected]',
    '$2a$10$8.UnVuG9HHgffUDAlk8qfOuVGkqRzgVymGe07xd00DMxs.AQubh4a',
    1);

INSERT INTO authorities (email, authority)
  values ('[email protected]', 'ROLE_USER');

4.2. Running the Application with the New Schema

Let’s launch our application. It initializes correctly, which makes sense since our schema is correct.

Now, if we try to log in, we’ll find an error is prompted when presenting the credentials.

Spring Security is still looking for a username field in the database. Lucky for us, the JDBC Authentication configuration offers the possibility of customizing the queries used to retrieve user details in the authentication process.

4.3. Customizing the Search Queries

Adapting the queries is quite easy. We simply have to provide our own SQL statements when configuring the AuthenticationManagerBuilder:

@Autowired
public void configureGlobal(AuthenticationManagerBuilder auth) 
  throws Exception {
    auth.jdbcAuthentication()
      .dataSource(dataSource)
      .usersByUsernameQuery("select email,password,enabled "
        + "from bael_users "
        + "where email = ?")
      .authoritiesByUsernameQuery("select email,authority "
        + "from authorities "
        + "where email = ?");
}

We can launch the application once more, and access the /principal endpoint using the new credentials.

5. Conclusion

As we can see, this approach is much simpler than having to create our own UserDetailService implementation, which implies an arduous process; creating entities and classes implementing the UserDetail interface and adding repositories to our project.

The drawback is, of course, the little flexibility it offers when our database or our logic differs from the default strategy provided by the Spring Security solution.