Spring MVC security with jdbc database authentication example using XML configuration

STEP 1:- To perform database authentication, you have to create tables to store the users and roles detail. Here are the MySQL scripts to create users and user_roles tables.
  • Table: users
CREATE  TABLE users (
  username VARCHAR(60) NOT NULL ,
  password VARCHAR(60) NOT NULL ,
  enabled TINYINT NOT NULL DEFAULT 1 ,
  PRIMARY KEY (username));

  • Table: user_roles
CREATE TABLE user_roles (
  user_role_id int(11) NOT NULL AUTO_INCREMENT,
  username varchar(60) NOT NULL,
  role varchar(60) NOT NULL,
  PRIMARY KEY (user_role_id),
  UNIQUE KEY uni_username_role (role,username),
  KEY fk_username_idx (username),
  CONSTRAINT fk_username FOREIGN KEY (username) REFERENCES users (username));


  • here is insert script
insert into users values('user','123456',1)
insert into users values('apiuser','123456',1)
insert into users values('admin','123456',1)

insert into user_roles values('user','ROLE_USER')
insert into user_roles values('admin','ROLE_USER')
insert into user_roles values('admin','ROLE_ADMIN')
insert into user_roles values('admin','ROLE_API')
insert into user_roles values('apiuser','ROLE_USER')
insert into user_roles values('apiuser','ROLE_API')

STEP 2:- Open Eclipse and Create Dynamic Web Project named SpringSecurityDBAuthXMLConfig

STEP 3:- Make sure you use Target Runtime as Apache Tomcat 7.0. 

STEP 4:- copy below jars to WEB-INF/lib folder.
  • commons-logging-1.2.jar
  • mysql-connector-java-5.1.38.jar
  • spring-aop-4.1.4.RELEASE.jar
  • spring-beans-4.1.4.RELEASE.jar
  • spring-context-4.1.4.RELEASE.jar
  • spring-core-4.1.4.RELEASE.jar
  • spring-dao-2.0.8.jar
  • spring-expression-4.1.4.RELEASE.jar
  • spring-jdbc-4.1.4.RELEASE.jar
  • spring-security-config-4.0.2.RELEASE.jar
  • spring-security-core-4.0.2.RELEASE.jar
  • spring-security-web-4.0.2.RELEASE.jar
  • spring-web-4.1.4.RELEASE.jar
  • spring-webmvc-4.1.4.RELEASE.jar
STEP 5:- Create Spring Configuration Bean file. /WebContent/WEB-INF/dispatcher-servlet.xml
<beans xmlns="http://www.springframework.org/schema/beans"
 xmlns:mvc="http://www.springframework.org/schema/mvc" 
 xmlns:context="http://www.springframework.org/schema/context"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="
 http://www.springframework.org/schema/beans 
 http://www.springframework.org/schema/beans/spring-beans.xsd
 http://www.springframework.org/schema/mvc 
 http://www.springframework.org/schema/mvc/spring-mvc.xsd
 http://www.springframework.org/schema/context 
 http://www.springframework.org/schema/context/spring-context.xsd">
 
 <context:component-scan base-package="com.tutorialsdesk.controller" />
 
 <bean id="viewResolver"
 class="org.springframework.web.servlet.view.UrlBasedViewResolver">
 <property name="viewClass"
 value="org.springframework.web.servlet.view.JstlView" />
 <property name="prefix" value="/WEB-INF/views/" />
 <property name="suffix" value=".jsp" />
 </bean>
 
 <mvc:annotation-driven/>
 
</beans>

STEP 6:- Create Spring security configuration file. /WebContent/WEB-INF/spring-security.xml
<beans:beans xmlns="http://www.springframework.org/schema/security"
 xmlns:beans="http://www.springframework.org/schema/beans"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://www.springframework.org/schema/beans 
 http://www.springframework.org/schema/beans/spring-beans.xsd
 http://www.springframework.org/schema/security 
 http://www.springframework.org/schema/security/spring-security.xsd">
 
 <http auto-config="true" >
 
 <intercept-url pattern="/" access="permitAll" />
 
 <intercept-url pattern="/home" access="permitAll" />
 
 <intercept-url pattern="/admin**"
access="hasRole('ADMIN')" />
 
 <intercept-url pattern="/api**" access="hasRole('ADMIN')
or hasRole('API')" />
 
 <!-- access denied page -->
 <access-denied-handler error-page="/Access_Denied" />
 
 <form-login 
 login-processing-url="/login"
 login-page="/login" 
 default-target-url="/home" 
 username-parameter="username"
 password-parameter="password"
 authentication-failure-url="/login?error"/>
 <!-- enable csrf protection -->
 <csrf/>
 
 </http>
 
 <!-- Select users and user_roles from database -->
 <authentication-manager>
 <authentication-provider>
 <jdbc-user-service data-source-ref="dataSource"
 users-by-username-query=
 "select username,password, enabled from users where username=?"
 authorities-by-username-query=
 "select username, role from user_roles where username =? " />
 </authentication-provider>
 </authentication-manager>
</beans:beans>

STEP 7 :- Create Spring datasource configuration files in /WebContent/WEB-INF/application-context.xml file as below :-
<beans xmlns="http://www.springframework.org/schema/beans"
 xmlns:mvc="http://www.springframework.org/schema/mvc" 
 xmlns:context="http://www.springframework.org/schema/context"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="
 http://www.springframework.org/schema/beans 
 http://www.springframework.org/schema/beans/spring-beans.xsd
 http://www.springframework.org/schema/mvc 
 http://www.springframework.org/schema/mvc/spring-mvc.xsd
 http://www.springframework.org/schema/context 
 http://www.springframework.org/schema/context/spring-context.xsd">
 
 <bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
 <property name="driverClassName" value="${db.driver}" />
 <property name="url" value="${db.jdbcurl}" />
 <property name="username" value="${db.username}" />
 <property name="password" value="${db.password}" />
 </bean>
 
 <context:property-placeholder location="/WEB-INF/db.properties" />
 
</beans>

STEP 8 :- Create db properties files in /WebContent/WEB-INF/db.properties file as below :-
db.driver=com.mysql.jdbc.Driver
db.jdbcurl=jdbc:mysql://localhost:3306/test
db.username=root
db.password=password

STEP 9 :- Map Spring configuration files in /WebContent/WEB-INF/web.xml file as below :-

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://java.sun.com/xml/ns/javaee"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" version="3.0">
 <display-name>SpringSecurityDBAuthXMLConfig</display-name>
 <servlet>
 <servlet-name>dispatcher</servlet-name>
 <servlet-class>
 org.springframework.web.servlet.DispatcherServlet
 </servlet-class>
 <load-on-startup>1</load-on-startup>
 </servlet>
 <servlet-mapping>
 <servlet-name>dispatcher</servlet-name>
 <url-pattern>/</url-pattern>
 </servlet-mapping>
 <context-param>
 <param-name>contextConfigLocation</param-name>
 <param-value>
 /WEB-INF/spring-security.xml
 /WEB-INF/application-context.xml
 </param-value>
 </context-param>
 <listener>
 <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
 </listener>
 <filter>
 <filter-name>springSecurityFilterChain</filter-name>
 <filter-class>org.springframework.web.filter.DelegatingFilterProxy</filter-class>
 </filter>
 <filter-mapping>
 <filter-name>springSecurityFilterChain</filter-name>
 <url-pattern>/*</url-pattern>
 </filter-mapping>
</web-app>

STEP 10 :- Create Controller Class.
  • Package: com.tutorialsdesk.controller
  • Filename: HelloWorldController.java
package com.tutorialsdesk.controller;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.security.core.Authentication;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.security.core.userdetails.UserDetails;
import
org.springframework.security.web.authentication.logout.SecurityContextLogoutHandler;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;

@Controller
public class HelloWorldController {

 @RequestMapping(value = { "/", "/login" }, method =
RequestMethod.GET)
 public String loginPage(ModelMap model, @RequestParam(value = "error",
required = false) String error) {
 
 if (error != null) {
 model.addAttribute("error", "Invalid username and password!");
 }
 return "login";
 }
 
 @RequestMapping(value = { "/home" }, method = RequestMethod.GET)
 public String homePage(ModelMap model) {
 model.addAttribute("greeting", "Hi, Welcome to mysite. ");
 return "welcome";
 }
 
 @RequestMapping(value = "/admin", method = RequestMethod.GET)
 public String adminPage(ModelMap model) {
 model.addAttribute("user", getPrincipal());
 return "admin";
 }
 
 @RequestMapping(value = "/api", method = RequestMethod.GET)
 public String dbaPage(ModelMap model) {
 model.addAttribute("user", getPrincipal());
 return "api";
 }
 
 @RequestMapping(value="/logout", method = RequestMethod.GET)
 public String logoutPage (ModelMap model,HttpServletRequest request,
HttpServletResponse response) {
 Authentication auth = SecurityContextHolder.getContext().getAuthentication();
 if (auth != null){ 
 new SecurityContextLogoutHandler().logout(request, response, auth);
 }
 model.addAttribute("msg", "You've been logged out
successfully.");
 return "login";
 }
 
 @RequestMapping(value = "/Access_Denied", method = RequestMethod.GET)
 public String accessDeniedPage(ModelMap model) {
 model.addAttribute("user", getPrincipal());
 return "accessDenied";
 }
 
 private String getPrincipal(){
 String userName = null;
 Object principal =
SecurityContextHolder.getContext().getAuthentication().getPrincipal();
 
 if (principal instanceof UserDetails) {
 userName = ((UserDetails)principal).getUsername();
 } else {
 userName = principal.toString();
 }
 return userName;
 }
}

STEP 11 :- Create jsp files in /WebContent/WEB-INF/views folder
  • Filename: login.jsp
<%@ taglib prefix="c"
uri="http://java.sun.com/jsp/jstl/core"%>
<%@page session="true"%>
<html>
<head>
<title>Login Page</title>
<style>
.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: 300px;
 padding: 20px;
 margin: 100px auto;
 background: #fff;
 -webkit-border-radius: 2px;
 -moz-border-radius: 2px;
 border: 1px solid #000;
}
</style>
</head>
<body onload='document.loginForm.username.focus();'>

 <h1>Spring Security Custom Login Form</h1>

 <div id="login-box">

 <h2>Login with Username and Password</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='/login' />" method='POST'>

 <table>
 <tr>
 <td>User:</td>
 <td><input type='text' name='username'></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>

  • Filename: welcome.jsp
<%@ page language="java" 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>HelloWorld page</title>
</head>
<body>
 Greeting : ${greeting}
 This is a welcome page.
 <br/>
 <br/>
 <br/>
<a href="<c:url value="/admin" />">Admin
Page</a> ( Only Admin user can access this )
<br/>
<br/>
<a href="<c:url value="/api" />">API Page</a>
( Admin or API user can access this )

</body>
</html>

  • Filename: admin.jsp
<%@ page language="java" 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>HelloWorld Admin page</title>
</head>
<body>
 Dear <strong>${user}</strong>, Welcome to Admin Page.
 
 
<form action="logout" method="post">
 <input type="submit" value="Logout" />
 <input type="hidden" name="${_csrf.parameterName}"
value="${_csrf.token}"/>
</form>
</body>
</html>

  • Filename: api.jsp
<%@ page language="java" 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>DBA page</title>
</head>
<body>
 Dear <strong>${user}</strong>, Welcome to API Page.
 
 <form action="logout" method="post">
 <input type="submit" value="Logout" />
 <input type="hidden" name="${_csrf.parameterName}"
value="${_csrf.token}"/>
</form>
</body>
</html>

  • Filename: accessDenied.jsp
<%@ page language="java" 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>AccessDenied page</title>
</head>
<body>
 Dear <strong>${user}</strong>, You are not authorized to access this page
 <form action="logout" method="post">
 <input type="submit" value="Logout" />
 <input type="hidden" name="${_csrf.parameterName}"
value="${_csrf.token}"/>
</form>
 
</body>
</html>

STEP 12 :- Run your project enter below URL in your browser

http://localhost:8080/SpringSecurityDBAuthXMLConfig/

Keep visiting TutorialsDesk for more tutorials and practical programming examples on Spring MVC. Hope we are able to explain you Spring MVC security with jdbc database authentication example using XML configuration Example, if you have any questions or suggestions please write to us using contact us form. 

Please share us on social media if you like the tutorial.