JDBC Based Authentication
The example, Spring Boot Security form based JDBC authentication, will show you how to use custom login form with Spring’s j_spring_security_check to authenticate a user. You may also look into form based jdbc authentication on Spring MVC framework. The similar example I will implement here but using Spring Boot framework. In the previous example I had used Resource Bundle that will also help you to make your application internationalization.
Prerequisites
Java 1.8/19, Gradle 5.4.1, Spring Boot 2.1.6/3.1.4, Maven 3.8.5, MySQL 8.1.0
Go through the below sections to implement Spring Boot Security form based JDBC authentication.
Project Setup
Create a maven or gradle based project in your favorite IDE or tool. The project name is given as spring-boot-security-form-based-jdbc-authentication.
Once the gradle or maven project gets created, open build.gradle script or pom.xml file in editor and update it to include the required dependencies.
I have added Spring Boot web starter, security starter for our web application with security implementation.
For spring boot version 3.x.x use the following pom.xml file. I am going to use Thymeleaf for view technology. So I am going to create HTML pages for spring boot 3.
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.roytuts</groupId>
<artifactId>spring-boot-security-form-based-jdbc-authentication</artifactId>
<version>0.0.1-SNAPSHOT</version>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>19</maven.compiler.source>
<maven.compiler.target>19</maven.compiler.target>
</properties>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>3.1.4</version>
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-security</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.thymeleaf.extras</groupId>
<artifactId>thymeleaf-extras-springsecurity6</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
For spring boot 2.x.x you can use the following build.gradle script or you can build the pom.xml file yourself.
I have also added jasper and jstl as I will use jsp pages as a view technology in our application.
I have included here H2 in-memory database to make our application quickly build. You can also use other database server for your application.
buildscript {
ext {
springBootVersion = '2.1.6.RELEASE'
}
repositories {
mavenLocal()
mavenCentral()
}
dependencies {
classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
}
}
apply plugin: 'java'
apply plugin: 'org.springframework.boot'
sourceCompatibility = 1.8
targetCompatibility = 1.8
repositories {
mavenLocal()
mavenCentral()
}
dependencies {
implementation("org.springframework.boot:spring-boot-starter-web:${springBootVersion}")
implementation("org.springframework.boot:spring-boot-starter-security:${springBootVersion}")
implementation("org.springframework:spring-jdbc:5.1.8.RELEASE")
implementation('org.apache.tomcat.embed:tomcat-embed-jasper:9.0.22')
implementation('javax.servlet:jstl:1.2')
runtime("com.h2database:h2:1.4.196")
}View Resolvers
The following view resolver is not required for spring boot version 3.x.x. The database settings are required for creating the datasource instance in the application.properties file.
#datasource
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/roytuts
spring.datasource.username=root
spring.datasource.password=root
Add application.properties file under src/main/resources to configure view resolvers. You can also create bean as a view resolver but specifying in the application.properties is fair enough for the Spring Boot application.
So I will put our jsp files under webapp/views folder and the jsp file’s extension is .jsp.
spring.mvc.view.prefix=/views/
spring.mvc.view.suffix=.jspSQL Scripts
For spring boot version 3.x.x you can use the following DDL to create tables in MySQL database server:
CREATE TABLE IF NOT EXISTS `user` (
`user_name` varchar(30) NOT NULL,
`user_pass` varchar(255) NOT NULL,
`enable` tinyint NOT NULL DEFAULT '1',
PRIMARY KEY (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE IF NOT EXISTS `user_role` (
`user_name` varchar(30) NOT NULL,
`user_role` varchar(15) NOT NULL,
KEY `user_name` (`user_name`),
CONSTRAINT `user_role_ibfk_1` FOREIGN KEY (`user_name`) REFERENCES `user` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `user` (`user_name`, `user_pass`, `enable`) VALUES
('admin', '$2a$10$dl8TemMlPH7Z/mpBurCX8O4lu0FoWbXnhsHTYXVsmgXyzagn..8rK', 1);
INSERT INTO `user_role` (`user_name`, `user_role`) VALUES
('admin', 'ROLE_ADMIN');The following SQL scripts you need to keep under class path folder for spring boot 2.x.x version.
In our application I am going to authenticate users using Spring JDBC. So I need to create SQL scripts for creating tables and inserting some data to test our application.
Table – user
The below table stores user information. The SQL script user.sql is kept under src/main/resources folder.
/*Table structure for table `user` */
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`user_name` varchar(30) NOT NULL,
`user_pass` varchar(255) NOT NULL,
`enable` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`user_name`)
);
/*Data for the table `user` */
insert into `user`(`user_name`,`user_pass`,`enable`) values ('admin','$2a$10$dl8TemMlPH7Z/mpBurCX8O4lu0FoWbXnhsHTYXVsmgXyzagn..8rK',1);Table – user_role
The below table stores user information. The SQL script user_role.sql is kept under src/main/resources folder.
/*Table structure for table `user_role` */
DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role` (
`user_name` varchar(30) NOT NULL,
`user_role` varchar(15) NOT NULL,
FOREIGN KEY (`user_name`) REFERENCES `user` (`user_name`)
);
/*Data for the table `user_role` */
insert into `user_role`(`user_name`,`user_role`) values ('admin','ROLE_ADMIN');Database Config Class
The following database configuration class is not required for spring boot version 3.x.x.
I need to create datasource for Spring JDBC API to work with the database. Note that I don’t need to pass database credentials to connect to the database as Spring Boot will connect to default instance with default credentials of H2 database.
@Configuration
public class DatabaseConfig {
@Bean
public DataSource dataSource() {
EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();
EmbeddedDatabase db = builder.setType(EmbeddedDatabaseType.H2) // .H2 or .DERBY, etc.
.addScript("user.sql").addScript("user-role.sql").build();
return db;
}
}Security Config Class
The following security configuration is required for spring boot 3.x.x. For spring boot 3 you don’t need to annotate class with @EnableWebSecurity or related annotations, you only need to use @Configuration annotation.
@Configuration
public class SecurityConfig {
@Autowired
private DataSource dataSource;
@Autowired
private PasswordEncoder passwordEncoder;
@Autowired
public void configureGlobal(AuthenticationManagerBuilder auth) throws Exception {
final String sqlUserName = "select u.user_name, u.user_pass, u.enable from user u where u.user_name = ?";
final String sqlAuthorities = "select ur.user_name, ur.user_role from user_role ur where ur.user_name = ?";
auth.jdbcAuthentication().dataSource(dataSource).usersByUsernameQuery(sqlUserName)
.authoritiesByUsernameQuery(sqlAuthorities).passwordEncoder(passwordEncoder);
}
@Bean
public SecurityFilterChain securityFilterChain(HttpSecurity http) throws Exception {
http.authorizeHttpRequests(auth -> auth
// ignore home, login, error pages and css files
.requestMatchers("/", "/login", "/css/**", "/error**").permitAll().requestMatchers("/admin")
.hasRole("ADMIN").anyRequest().authenticated()); // check for admin url with ADMIN role
// allow users to authenticate with form based login
http.formLogin(form -> form.loginPage("/login") // specifies custom login page
.permitAll().usernameParameter("username") // overrides spring's default j_username with username
// parameter
.passwordParameter("password") // overrides spring's default j_password with password parameter
.loginProcessingUrl("/j_spring_security_check") // login processing url
.defaultSuccessUrl("/admin") // default target url which will be shown after successful login
.failureUrl("/login?error")); // authenticate failure url
http.logout((logout) -> logout.permitAll());
return http.build();
}
}You need to create bean for password encoder in a separate class otherwise you will see circular dependency exception:
@Configuration
public class PasswordEncryptorConfig {
@Bean
public PasswordEncoder passwordEncoder() {
return new BCryptPasswordEncoder();
}
}For spring boot version 2.x.x use the following security configurations.
I need to create Java config class to configure Spring Security for our form based JDBC authentication.
I have put comments after each line in the below Spring Security configuration class to understand it clearly.
The below WebSecurityConfig class, which is responsible for all security configurations, extends WebSecurityConfigurerAdapter and overrides configure(HttpSecurity http) method and authenticationManager() methods.
I have ignored security for static resources such as js, css, images etc that have path /static**, root path /, /login, /error.
I have overridden method configure(HttpSecurity http) to apply security for all URLs including the URL which is having pattern /admin with role as ADMIN.
Here, I have implemented JDBC authentication unlike the application Spring Boot Security form based authentication, where I have used in-memory authentication. You can also apply LDAP or any other third party API to authenticate your application users.
I have used PasswordEncoder because plain text password is not acceptable in current version of Spring Security and you will get below exception if you do not use PasswordEncoder.
java.lang.IllegalArgumentException: There is no PasswordEncoder mapped for the id "null"
at org.springframework.security.crypto.password.DelegatingPasswordEncoder$UnmappedIdPasswordEncoder.matches(DelegatingPasswordEncoder.java:244)As the passwords are in encrypted format in the below class, so you won’t find it easier until I tell you. The password for admin is admin.
@Configuration
@EnableWebSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter {
@Autowired
private DataSource dataSource;
@Autowired
public void configureGlobal(AuthenticationManagerBuilder auth) throws Exception {
final String sqlUserName = "select u.user_name, u.user_pass, u.enable from user u where u.user_name = ?";
final String sqlAuthorities = "select ur.user_name, ur.user_role from user_role ur where ur.user_name = ?";
auth.jdbcAuthentication().dataSource(dataSource).usersByUsernameQuery(sqlUserName)
.authoritiesByUsernameQuery(sqlAuthorities).passwordEncoder(passwordEncoder());
}
@Override
protected void configure(HttpSecurity http) throws Exception {
http// ,
.authorizeRequests()// , authorize request
.antMatchers("/", "/login", "/static/**", "/error**").permitAll().anyRequest().authenticated()// ,
// ignore
// /,login
// page,static
// resources, error
// pages
.antMatchers("/admin")// Ensures that request with "/admin" to
// our application requires the user to
// be authenticated
.access("hasRole('ADMIN')")// Any URL that starts with
// "/admin" will
// be restricted to users who have the
// role "ROLE_ADMIN",
.and()// ,
.formLogin()// Allows users to authenticate with form based
// login,
.loginPage("/login")// specifies the location of the log in
// page,
.loginProcessingUrl("/j_spring_security_check")// login
// processing
// URL,
.defaultSuccessUrl("/admin")// default-target-url,
.failureUrl("/login?error")// authentication-failure-url,
.usernameParameter("username")// overrides Spring's default
// j_username with
// username-parameter,
.passwordParameter("password");// overrides Spring's default
// j_password with
// password-parameter
}
@Bean
public PasswordEncoder passwordEncoder() {
return new BCryptPasswordEncoder();
}
}REST Controller
Now create below REST Controller class to test Spring Boot Security form based authentication.
I have defined few end-points in the below REST controller class, where the path / and /login do not need to be authenticated and only /admin needs to be authenticated.
@Controller
public class SpringSecurityController {
@GetMapping("/")
public String defaultPage(Model model) {
model.addAttribute("msg", "Welcome to Spring Security");
return "index" // "home" for spring boot 3;
}
@GetMapping("/login")
public String loginPage(Model model, @RequestParam(value = "error", required = false) String error,
@RequestParam(value = "logout", required = false) String logout) {
if (error != null) {
model.addAttribute("error", "Invalid Credentials");
}
if (logout != null) {
model.addAttribute("msg", "You have been successfully logged out");
}
return "login";
}
@GetMapping("/logout")
public String logoutPage(Model model, HttpServletRequest request) {
request.getSession().invalidate();
return "redirect:/login?logout";
}
@GetMapping("/admin")
public String adminPage(Model model) {
model.addAttribute("title", "Administrator Control Panel");
model.addAttribute("message", "This page demonstrates how to use Spring security");
return "admin";
}
}View Files
For spring boot 3.x.x version, the view files are kept under src/main/resources/templates folder.
I will create three view files, i.e., jsp pages for path – /, /login and /admin.
Use the following JSP pages for spring boot version 2.x.x and HTML pages for spring boot version 3.x.x.
Path – /
Create home.html page under src/main/resources/templates folder.
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:th="https://www.thymeleaf.org">
<head>
<title>Spring Security Basic - Form Based Authentication</title>
</head>
<body>
<div align="center">
<h1>Home Page</h1>
<a th:href="@{/admin}">Go to Administrator page</a>
</div>
</body>
</html>Create index.jsp file under src/main/webapp/views folder with the below content.
It will just show you home page that has a link to go to the Admin page.
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Spring Security Basic - Form Based JDBC Authentication</title>
</head>
<body>
<div align="center">
<h1>Home Page</h1>
<a href="${pageContext.request.contextPath}/admin">Go to Administrator page</a>
</div>
</body>
</html>Path – /login
Create login.html page under src/main/resources/templates folder.
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:th="https://www.thymeleaf.org">
<head>
<title>Spring Security Basic - Form Based Authentication</title>
<link href="/css/style.css" rel="stylesheet" type="text/css" />
<!--<style th:inline="css">
.error {
padding: 15px;
margin-bottom: 20px;
border: 1px solid transparent;
border-radius: 4px;
color: #a94442;
background-color: #f2dede;
border-color: #ebccd1;
}
.msg {
padding: 15px;
margin-bottom: 20px;
border: 1px solid transparent;
border-radius: 4px;
color: #31708f;
background-color: #d9edf7;
border-color: #bce8f1;
}
#login-box {
width: 500px;
padding: 20px;
margin: 50px auto;
background: #fff;
-webkit-border-radius: 2px;
-moz-border-radius: 2px;
border: 1px solid #000;
}
</style>-->
</head>
<body>
<div id="login-box">
<h2>Login Here</h2>
<div th:if="${error}" th:text="${error}"></div>
<div th:if="${msg}" th:text="${msg}"></div>
<form th:action="@{/j_spring_security_check}" method="post">
<div>
<input type="text" name="username" placeholder="Username"/>
</div>
<div>
<input type="password" name="password" placeholder="Password"/>
</div>
<input type="submit" value="Log in" />
</form>
</div>
</body>
</html>Create a file login.jsp under src/main/webapp/views folder with below content.
This page shows you login form when you click on Go to Administrator page link on home page.
In this page you need to put admin/admin as credentials to go to the admin page otherwise you will get login error.
<%@ page language="java" session="true"
contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<html>
<head>
<title>Spring Security Basic - Form Based JDBC Authentication</title>
<link rel="stylesheet" type="text/css"
href="<c:url value="/static/css/style.css"/>" />
</head>
<body>
<div id="login-box">
<h2>Login Here</h2>
<c:if test="${not empty error}">
<div class="error">${error}</div>
</c:if>
<c:if test="${not empty msg}">
<div class="msg">${msg}</div>
</c:if>
<form name='loginForm'
action="<c:url value='j_spring_security_check' />" method='POST'>
<table>
<tr>
<td>User:</td>
<td><input type='text' name='username' value=''></td>
</tr>
<tr>
<td>Password:</td>
<td><input type='password' name='password' /></td>
</tr>
<tr>
<td colspan='2'><input name="submit" type="submit"
value="Submit" /></td>
</tr>
</table>
<input type="hidden" name="${_csrf.parameterName}"
value="${_csrf.token}" />
</form>
</div>
</body>
</html>Path – /admin
Create admin.html page under src/main/resources/templates folder.
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:th="https://www.thymeleaf.org"
xmlns:sec="https://www.thymeleaf.org/thymeleaf-extras-springsecurity6">
<head>
<title>Spring Security Basic - Form Based Authentication</title>
</head>
<body>
<div align="center">
<h1 th:inline="text" th:text="${title}"></h1>
<h2 th:inline="text" th:text="${message}"></h2>
<h3>
<span>Welcome </span>
<span sec:authentication="name"></span> | <a th:href="@{/logout}">Logout</a>
</h3>
</div>
</body>
</html>Create a file admin.jsp page under src/main/webapp/views folder with below content.
This page shows admin page once you successfully logged in to the application using login page.
In this page you will see your username and logout link with some message. On clicking on the logout link you will be redirected to the login page with successful message.
<%@ page language="java" session="true"
contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Spring Security Basic - Form Based JDBC Authentication</title>
</head>
<body>
<div align="center">
<h1>${title}</h1>
<h2>${message}</h2>
<c:if test="${pageContext.request.userPrincipal.name != null}">
<h2>
Welcome
: ${pageContext.request.userPrincipal.name} | <a
href="<c:url value='logout'/>">Logout</a>
</h2>
</c:if>
</div>
</body>
</html>Applying Style
For spring boot version 3.x.x, keep the style.css file under src/main/resources/static/css folder.
Now I apply some basic style for our pages. So create below style.css file under src/main/webapp/static/css folder for spring boot version 2.x.x.
.error {
padding: 15px;
margin-bottom: 20px;
border: 1px solid transparent;
border-radius: 4px;
color: #a94442;
background-color: #f2dede;
border-color: #ebccd1;
}
.msg {
padding: 15px;
margin-bottom: 20px;
border: 1px solid transparent;
border-radius: 4px;
color: #31708f;
background-color: #d9edf7;
border-color: #bce8f1;
}
#login-box {
width: 500px;
padding: 20px;
margin: 50px auto;
background: #fff;
-webkit-border-radius: 2px;
-moz-border-radius: 2px;
border: 1px solid #000;
}Spring Boot Main Class
Creating a main would be sufficient to deploy our application into the Tomcat server. This is a great advantage that you just need to let Spring know that it is your Spring Boot Application using @SpringBootApplication and main class.
@SpringBootApplication
public class SpringSecurityFormBasedJdbcAuthApp {
public static void main(String[] args) {
SpringApplication.run(SpringSecurityFormBasedJdbcAuthApp.class, args);
}
}Deploying the Application
Just run the above main class to deploy the application into embedded Tomcat server. The server will start on default port 8080.
Testing the Form Based Auth Using JDBC
Please refer to the same section in the example Spring Boot Security form based authentication. You will see the similar output.