Spring Security - JDBC Authentication

Spring JDBC Authentication

In my previous tutorials, I have shown in-memory authentications Spring Security Form based Authentication – XML Configuration , Spring Security Form based Authentication – Annotations but in this tutorial I will show you how to authenticate user using Spring JDBC and Spring MVC web application to secure pages. I will create spring mvc based web application and I will configure Spring Security to protect a page for external access.

Spring Security allows to you to integrate security features with JEE web application easily, it takes care about all incoming HTTP requests via servlet filter, and implements “user defined” security checking.

Prerequisites

Java 1.8+, Tomcat 9, Servlet 4.0.1, Spring 5.7.4, Maven 3.8.5

Project Setup

You can create a maven based project in your favorite IDE or tool. The minimal dependency artifacts required for Spring Security are spring-security-web and spring-security-config.

<?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-security-jdbc-authentication</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>war</packaging>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<maven.compiler.source>11</maven.compiler.source>
		<maven.compiler.target>11</maven.compiler.target>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-webmvc</artifactId>
			<version>5.3.23</version>
		</dependency>
		<!-- Spring Security -->
		<dependency>
			<groupId>org.springframework.security</groupId>
			<artifactId>spring-security-web</artifactId>
			<version>5.7.4</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.security</groupId>
			<artifactId>spring-security-config</artifactId>
			<version>5.7.4</version>
		</dependency>

		<!-- Servlet -->
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>javax.servlet-api</artifactId>
			<version>4.0.1</version>
			<scope>provided</scope>
		</dependency>

		<dependency>
			<groupId>javax.servlet.jsp</groupId>
			<artifactId>javax.servlet.jsp-api</artifactId>
			<version>2.3.1</version>
			<scope>provided</scope>
		</dependency>

		<!-- jstl -->
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>jstl</artifactId>
			<version>1.2</version>
		</dependency>

		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-orm</artifactId>
			<version>5.3.23</version>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.30</version>
		</dependency>
	</dependencies>

	<build>
		<finalName>spring-security-jdbc-authentication</finalName>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.8.1</version>
			</plugin>

			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-war-plugin</artifactId>
				<version>3.3.2</version>
				<configuration>
					<failOnMissingWebXml>false</failOnMissingWebXml>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

In the above pom.xml file you notice additional plugin for failOnMissingWebXml configuration is required because, I am going to write Java code purely based on annotations, so I will delete the web.xml (if any) file from WEB-INF directory.

I have added JDBC dependency and MySQL Java connector for querying MySQL database.

Deployment Descriptor Configurations

Ensure you delete the web.xml (if any) file from WEB-INF directory. Instead I will create below two classes which are equivalent to whatever were there in web.xml file.

public class MvcWebApplicationInitializer extends AbstractAnnotationConfigDispatcherServletInitializer {

	@Override
	protected Class<?>[] getRootConfigClasses() {
		return new Class[] { WebSecurityConfig.class, WebMvcConfig.class };
	}

	@Override
	protected Class<?>[] getServletConfigClasses() {
		return null;
	}

	@Override
	protected String[] getServletMappings() {
		return new String[] { "/" };
	}

}

The above class declared Spring MVC DispatcherServlet, that acts as a front controller to handle incoming request and response for the URL pattern "/". This is equivalent to declaring DispatcherServlet in web.xml file in my tutorial Spring Security Form based Authentication – XML Configuration

I have also loaded config classes WebSecurityConfig.class and WebMvcConfic.class, that are equivalent to security.xml and controllers.xml configurations in my tutorial Spring Security Form based Authentication – XML Configuration

public class SecurityWebApplicationInitializer extends AbstractSecurityWebApplicationInitializer {

}

The above class is equivalent to add the filter declaration DelegatingFilterProxy to your web.xml file.

This provides a hook into the Spring Security web infrastructure. DelegatingFilterProxy is a Spring Framework class which delegates to a filter implementation which is defined as a Spring bean in your application context. In this case, the bean is named springSecurityFilterChain, which is an internal infrastructure bean created by the namespace to handle web security. Note that you should not use this bean name yourself.

Security Configurations

Create WebSecurityConfig.java file that is equivalent to security.xml file under src/main/resources directory in my tutorial Spring Security Form based Authentication – XML Configuration.

@Configuration
@EnableWebSecurity
public class WebSecurityConfig {

	@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(new BCryptPasswordEncoder());
	}

	@Bean
	public SecurityFilterChain filterChain(HttpSecurity http) throws Exception {
		http// ,
				.authorizeRequests()// ,
				.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

		return http.build();
	}

}

I am using Bcrypt password encoder because plain text in password field is prohibited and it is also a security risk.

Spring Web MVC Configurations

Create WebMvcConfig.java file that is equivalent to controllers.xml file under src/main/resources directory in my tutorial Spring Security Form based Authentication – XML Configuration.

@EnableWebMvc
@Configuration
@PropertySource(value = { "classpath:messages.properties", "classpath:jdbc.properties" })
@ComponentScan(basePackages = "com.roytuts.spring.security.jdbc.authentication.controllers")
public class WebMvcConfig implements WebMvcConfigurer {

	@Autowired
	private Environment environment;

	@Bean
	public ViewResolver getViewResolver() {
		InternalResourceViewResolver viewResolver = new InternalResourceViewResolver("/views/", ".jsp");
		return viewResolver;
	}

	@Override
	public void addResourceHandlers(final ResourceHandlerRegistry registry) {
		registry.addResourceHandler("/static/**").addResourceLocations("/static/");
	}

	@Bean("messageSource")
	public MessageSource messageSource() {
		ResourceBundleMessageSource messageSource = new ResourceBundleMessageSource();
		messageSource.setBasename("messages");
		messageSource.setDefaultEncoding("UTF-8");
		return messageSource;
	}

	@Bean(name = "dataSource")
	public DriverManagerDataSource dataSource() {
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName(environment.getRequiredProperty("jdbc.driverClassName"));
		dataSource.setUrl(environment.getRequiredProperty("jdbc.url"));
		dataSource.setUsername(environment.getRequiredProperty("jdbc.username"));
		dataSource.setPassword(environment.getRequiredProperty("jdbc.password"));
		return dataSource;
	}

}

The annotation @EnableWebMvc is equivalent to <mvc:annotation-driven /> to work with annotations in Spring MVC.

The addResourceHandlers() acts in the similar way as <mvc:resources location="/static/" mapping="/static/**" /> to load static resources from static directory.

The annotation @ComponentScan is equivalent to <context:component-scan/> to load all annotation-driven controllers from the given base package.

I have used Environment variable from Spring to use the key/value pairs from properties file. I have defined a DataSource bean for querying database in the application.

Spring I18N Support

I have also declared view resolver bean and message resource for i18n supports.

Create messages.properties file with below content and put it under src/main/resources folder:

page.title=Spring Security JDBC Authentication
page.home.heading=Home Page
page.login.heading=Login Here
page.admin.heading=Administrator Control Panel
page.admin.message=This page demonstrates how to use Spring security.
page.goto.admin=Go to Administrator page
login.failure.reason=Invalid credentials
welcome.msg=Welcome
logout.text=Logout
logout.msg.success=You have been successfully logged out.

DataSource Properties

Create a jdbc.properties file with the below content and put it under src/main/resources folder. Make sure to update the values according to your database configurations.

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost/roytuts
jdbc.username=root
jdbc.password=root

Spring Controller

The following controller class will handle user requests and responses. Based on the navigation or user’s action an appropriate page will be displayed to the end user.

@Controller
@RequestMapping("/")
public class SpringSecurityController implements MessageSourceAware {
	private MessageSource messageSource;

	@Override
	public void setMessageSource(MessageSource messageSource) {
		this.messageSource = messageSource;
	}

	@RequestMapping("/")
	public String defaultPage(Model model) {
		model.addAttribute("msg", "Welcome to Spring Security");
		return "index";
	}

	@RequestMapping("/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", messageSource.getMessage("login.failure.reason", null, Locale.US));
		}
		if (logout != null) {
			model.addAttribute("msg", messageSource.getMessage("logout.msg.success", null, Locale.US));
		}
		return "login";
	}

	@RequestMapping("/logout")
	public String logoutPage(Model model, HttpServletRequest request) {
		request.getSession().invalidate();
		return "redirect:/login?logout";
	}

	@RequestMapping("/admin")
	public String adminPage(Model model) {
		model.addAttribute("title", messageSource.getMessage("page.admin.heading", null, Locale.US));
		model.addAttribute("message", messageSource.getMessage("page.admin.message", null, Locale.US));
		return "admin";
	}

}

Style

I am going to apply some basic style, so create below style.css file and put it under webapp/static/css directory.

.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;
}

JSP Views

Below is the index.jsp file and put it under webapp/views folder and see how keys are used to fetch corresponding value from messages.properties file. This index.jsp file is not secured and is accessible directly.

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title><fmt:bundle basename="messages">
		<fmt:message key="page.title" />
	</fmt:bundle></title>
</head>
<body>
	<div align="center">
		<h1>
			<fmt:bundle basename="messages">
				<fmt:message key="page.home.heading" />
			</fmt:bundle>
		</h1>
		<a href="${pageContext.request.contextPath}/admin"><fmt:bundle
				basename="messages">
				<fmt:message key="page.goto.admin" />
			</fmt:bundle></a>
	</div>
</body>
</html>

Below admin.jsp file in webapp/views directory is secured and user must login before viewing the content of this file. When you try to access the admin.jsp file then you will automatically be redirected to the login.jsp file.

<%@ 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"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jstl/fmt"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title><fmt:bundle basename="messages">
		<fmt:message key="page.title" />
	</fmt:bundle></title>
</head>
<body>
	<div align="center">
		<h1>${title}</h1>
		<h2>${message}</h2>
		<c:if test="${pageContext.request.userPrincipal.name != null}">
			<h2>
				<fmt:bundle basename="messages">
					<fmt:message key="welcome.msg" />
				</fmt:bundle>
				: ${pageContext.request.userPrincipal.name} | <a
					href="<c:url value='logout'/>"><fmt:bundle basename="messages">
						<fmt:message key="logout.text" />
					</fmt:bundle></a>
			</h2>
		</c:if>
	</div>
</body>
</html>

The content of the login.jsp page under webapp/views folder is given below.

<%@ 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"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jstl/fmt"%>
<html>
<head>
<title><fmt:bundle basename="messages">
		<fmt:message key="page.title" />
	</fmt:bundle></title>
<link rel="stylesheet" type="text/css"
	href="<c:url value="/static/css/style.css"/>" />
</head>
<body>
	<div id="login-box">
		<h2>
			<fmt:bundle basename="messages">
				<fmt:message key="page.login.heading" />
			</fmt:bundle>
		</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>

MySQL Table and Data

Create below tables in MySQL database. The password is encrypted using Bcrypt password encoder algorithm. The decrypted value of the encrypted password is roy.

USE `roytuts`;
/*Table structure for table `user` */
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `user_name` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_pass` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `enable` tinyint NOT NULL DEFAULT '1',
  PRIMARY KEY (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*Data for the table `user` */
insert  into `user`(`user_name`,`user_pass`,`enable`) values ('roy','$2a$12$PN0MjtyNGWW.AjWdIuYKxe4.4Grjs4K7oanuAnt/WSDSvFlUc3eQi',1);

/*Table structure for table `user_role` */
DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role` (
  `user_name` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_role` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL,
  KEY `fk_user` (`user_name`),
  CONSTRAINT `fk_user` FOREIGN KEY (`user_name`) REFERENCES `user` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*Data for the table `user_role` */
insert  into `user_role`(`user_name`,`user_role`) values ('roy','ROLE_ADMIN');

Testing Spring Security JDBC Authentication

When you deploy the application in Tomcat server and run the application you will see different output in the browser.

When you access the URL http://localhost:8080/spring-security-jdbc-authentication/, the page will appear should have the following content:

If you do not enter credentials or invalid credentials, you will see the following error page:

Once you enter the correct credentials (roy/roy):

You will see the following page:

Clicking on the Logout link will take you to the login page again:

Hope you got an idea how to build Spring Security JDBC based authentication system.

Source Code

Download

Roy Tutorials

Spring Security – JDBC Authentication