Spring Security User Login Example With JDBC

In this article, we will learn how to use JDBC and Spring Security to implement user authentication.

We will use an embedded H2 database to store the user credentials.

We will also authenticate the username and password entered in the login form against the available user records in the H2 database.

Version details:

Java 8
Spring boot version 2.4.0
Maven build tool.
H2 in-memory database.
Spring security.

Create a application
Setup the database configurations
- Creating the tables and inserting some users
- Add connection configurations
Setting up security configurations
Add a controller
Testing the application
Conclusion

Create a application

Create a Spring boot application with the required dependencies.

Also, add the below maven dependencies to the application’s pom.xml file.

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-security</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

We have added security, web, and JDBC spring boot starter dependencies to our application.

We have also added the H2 dependency that provides the required in-memory H2 database.

Setup the database configurations

We need to set up the required tables to store the user details in the H2 database.

We create the required tables for Spring Security and insert a few user details during the application startup.

Creating the tables and inserting some users

Create a schema.sql file under the src/main/resources/ directory of the application and add the below DDL scripts.

The script will create the Users and Authorities tables in the H2 database that contains user information.

CREATE TABLE IF NOT EXISTS USERS (
 `id` INT NOT NULL AUTO_INCREMENT,
 `username` VARCHAR(45) NOT NULL,
 `password` VARCHAR(45) NOT NULL,
 `enabled` INT NOT NULL,
 PRIMARY KEY(`id`));
 
 CREATE TABLE IF NOT EXISTS AUTHORITIES (
  `id` INT NOt NULL AUTO_INCREMENT,
  `username` VARCHAR(45) NOT NULL,
  `authority` VARCHAR(45) NOT NULL,
  PRIMARY KEY(`id`));

Finally, create a data.sql file under the /src/main/resources directory and add the below content.

INSERT INTO authorities VALUES(NULL, 'arun', 'read');
INSERT INTO users VALUES(NULL, 'arun', '12345', '1');
INSERT INTO authorities VALUES(NULL, 'sara', 'read');
INSERT INTO users VALUES(NULL, 'sara', 'abcd', '1');
INSERT INTO authorities VALUES(NULL, 'john', 'read');
INSERT INTO users VALUES(NULL, 'john', '1111', '1');

We are adding some user and authority records and adding them to the created tables.

Add connection configurations

Add the below configuration properties to the application.properties under the directory src/main/resources directory.

spring.datasource.url=jdbc:h2:~/spring;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.data-username=sa
spring.datasource.data-password=
spring.datasource.initialization-mode=always

Setting up security configurations

Create a Spring configuration java class with the name AuthConfig, as shown below.

@Configuration
public class AuthConfig {

    @Bean
    public PasswordEncoder passwordEncoder() {
        return NoOpPasswordEncoder.getInstance();
    }

    @Bean
    public UserDetailsService userDetailsService(DataSource dataSource) {
        String userByUserNameQuery = "SELECT username, password, enabled from USERS where username =?";
        String authoritiesByUserQuery = "SELECT username, authority from AUTHORITIES where username =?";
        var userDetailsManager = new JdbcUserDetailsManager(dataSource);
        userDetailsManager.setUsersByUsernameQuery(userByUserNameQuery);
        userDetailsManager.setAuthoritiesByUsernameQuery(authoritiesByUserQuery);
        return userDetailsManager;
    }
}

We have defined a UserDetailsService bean. We have also created an instance of the JdbcUserDetailsManager provided by the spring security and initialized it with the data source.

Then, we have customized the select user and authorities queries passing our customized SQL queries.

The above implementation is a simple example that shows how simple it is to customize the configuration provided by Spring security.

We have also defined a password encoder bean, which is necessary as we have customized the UserDetailsService configuration(Do not use the NoOpPasswordEncoder encoder in the production environment).

Add a controller

Create a controller class with the name HelloController, and also define a simple REST endpoint that returns a “hello” message.

@RestController
public class HelloController {
	
	@GetMapping("/")
	public String getMessage() {
		return "Hello!!";
	}
}

Testing the application

Run the Spring boot application. By default, the Tomcat server runs on the URL: http://localhost:8080

Spring security displays a login page, as shown below.

Finally, Input the correct user credentials that we inserted in the tables and click on the Login button. We successfully log in to the application as shown below.

Conclusion

In conclusion, In this post, we learned how to use spring security with JDBC and implement simple user authentication.

We also learned how Spring security provides built-in support for JDBC basic user authentication and how easy it is to customize the authentication configuration.

Finally, the example code is available on GitHub.

Table of Contents