Securing Spring Boot applications with JDBC Authentication

This tutorial will teach you how to secure your Spring Boot applications using a JDBC Datasource and the H2 Database. We will create a sample REST Controller with two method endpoints, each one available to a distinct Role.

Let’s begin from our REST Controller which contains the following methods:

@RestController
public class CustomerController {
  @Autowired CustomerRepository repository;

  @RequestMapping(
      path = "/",
      method = RequestMethod.GET,
      produces = {"application/json"})
  public List<Customer> findAll() {
    return repository.getData();
  }

  @PostMapping(path = "/", consumes = "application/json", produces = "application/json")
  public ResponseEntity<Customer> addCustomer(@RequestBody Customer customer) throws Exception {
    repository.save(customer);
    return new ResponseEntity<Customer>(customer, HttpStatus.CREATED);
  }
}

And here is the SpringSecurityConfig which extends the WebSecurityConfigurerAdapter. Within the configAuthentication we specify that we will be using jdbcAuthentication. The password on the Database will be stored using a BCryptPasswordEncoder. You can learn how to encrypt password using BCryptPasswordEncoder in this tutorial: Using BCryptPasswordEncoder to encrypt your passwords

In the configure method we state the Roles allowed for each method:

@Configuration @EnableAutoConfiguration public class SpringSecurityConfig extends WebSecurityConfigurerAdapter {
  @Autowired DataSource dataSource;
  @Autowired public void configAuthentication(AuthenticationManagerBuilder auth) throws Exception {
    auth.jdbcAuthentication().passwordEncoder(new BCryptPasswordEncoder()).dataSource(dataSource);
  }
  @Override protected void configure(HttpSecurity http) throws Exception {
    //HTTP Basic authentication 
    http.httpBasic().and().authorizeRequests().antMatchers(HttpMethod.GET, "/").hasRole("USER").antMatchers(HttpMethod.POST, "/").hasRole("ADMIN").and().csrf().disable().formLogin().disable();
  }
}

As you can see, we haven’t specified any Table/Relation where username, password and roles are stored. As a matter of fact, we will be using the default Table and field names, which are:

how to secure your Spring Boot applications using a JDBC Datasource and the H2 Database

Therefore, we will add two sql scripts in the resources folder. Schema.sql to create the above tables and data.sql to insert some users:

Here is schema.sql:

create table USERS(       username varchar(128) not null primary key,       password varchar(512) not null,       enabled boolean not null);  create table AUTHORITIES (       username varchar(128) not null,       authority varchar(128) not null);  create unique index idx_auth_username on authorities (username,authority); 

And data.sql:

insert into users (username, password, enabled) values ('adam', '$2a$10$Ptvs9mLVFuEOXC4ckHi1weHGLQ7bGwigKQR5Uk9/aONvmKYPnRl8m', true); insert into authorities (username, authority) values ('adam', 'ROLE_USER');  insert into users (username, password, enabled) values ('jeff', '$2a$10$SRzxfUhAPr3wDJwcIJ/b3ePcJlmaJWOZMafN0dDmAw0ispxdXFi6K', true); insert into authorities (username, authority) values ('jeff', 'ROLE_ADMIN'); 

The password for the above users are “password1” and “password2”. They have been encrypted using a BCryptPasswordEncoder.

Since we will be using H2 Database for this example, in the application.properties file we will configure the Datasource as follows:

spring.datasource.url = jdbc:h2:mem:testdb;DB_CLOSE_ON_EXIT=FALSE spring.datasource.username = sa spring.datasource.password = spring.datasource.driverClassName=org.h2.Driver spring.datasource.initialization-mode=always 

Finally, these dependencies are needed in the pom.xml file:

<?xml version="1.0" encoding="UTF-8"?><project>
       
   <dependencies>
               
      <dependency>
                      
         <groupId>org.springframework.boot</groupId>
                      
         <artifactId>spring-boot-starter-web</artifactId>
                  
      </dependency>
                
      <dependency>
                      
         <groupId>org.springframework.boot</groupId>
                      
         <artifactId>spring-boot-starter-test</artifactId>
                      
         <scope>test</scope>
                  
      </dependency>
                
      <dependency>
                      
         <groupId>org.springframework.boot</groupId>
                      
         <artifactId>spring-boot-starter-jdbc</artifactId>
                  
      </dependency>
                
      <dependency>
                      
         <groupId>org.springframework.boot</groupId>
                      
         <artifactId>spring-boot-starter-security</artifactId>
                  
      </dependency>
                
      <dependency>
                      
         <groupId>org.springframework.security</groupId>
                      
         <artifactId>spring-security-test</artifactId>
                      
         <scope>test</scope>
                  
      </dependency>
               
      <dependency>
                      
         <groupId>com.h2database</groupId>
                      
         <artifactId>h2</artifactId>
                      
         <scope>runtime</scope>
                  
      </dependency>
           
   </dependencies>
    
</project>

Writing a Test class

Let’s write a test for the above endpoint using TestRestTemplate:

@RunWith(SpringRunner.class) @SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.DEFINED_PORT) public class DemoApplicationTests {
  private static final ObjectMapper om = new ObjectMapper();

  //@WithMockUser is not working with TestRestTemplate     

  @Autowired private TestRestTemplate restTemplate;
  @Test public void testCustomerList() throws Exception {
    ResponseEntity < String > response = restTemplate.withBasicAuth("adam", "password1").getForEntity("/", String.class);
    printJSON(response);
    //Verify user is authorized and content is JSON  
    assertEquals(MediaType.APPLICATION_JSON_UTF8, response.getHeaders().getContentType());
    assertEquals(HttpStatus.OK, response.getStatusCode());
    Customer c = new Customer(3, "Adam");
    ResponseEntity < String > result = restTemplate.withBasicAuth("adam", "password1").postForEntity("/", c, String.class);
    //Verify user is unauthorized         

    Assert.assertEquals(403, result.getStatusCodeValue());
    //Verify user is authorized         

    result = restTemplate.withBasicAuth("jeff", "password2").postForEntity("/", c, String.class);
    //Verify request succeed         

    Assert.assertEquals(201, result.getStatusCodeValue());
  }
  private static void printJSON(Object object) {
    String result;
    try {
      result = om.writerWithDefaultPrettyPrinter().writeValueAsString(object);
      System.out.println(result);
    } catch (JsonProcessingException e) {
      e.printStackTrace();
    }
  }
}

In this test we will check that:

  • User “adam” is authorized to use the GET Endpoint
  • User “adam” is not authorized to use the POST Endpoint
  • User “jeff” is authorized to use the POST Endpoint

You can run it with:

mvn clean install 

Great. You have just learnt how to secure your Spring Boot applications using JDBC with H2 Database. If you want to switch to another database, just adapt the sql script for its dialect and insert the appropriate dependencies in pom.xml and the Datasource configuration in application.properties.

The source code for this tutorial is available here: https://github.com/fmarchioni/masterspringboot/tree/master/security/rest-jdbc-security