Home  >  Spring  >  Spring Security

Spring MVC Security + JDBC + UserDetailsService + Database Authentication

By Arvind Rai, November 28, 2019
On this page we will walk through the Spring MVC Security JDBC authentication example with custom UserDetailsService and database tables using Java configuration. The UserDetailsService provides a method loadUserByUsername() in which we pass username obtained from login page and then it returns UserDetails. We need to create a class by implementing UserDetailsService and override loadUserByUsername() method. With the help of this custom UserDetailsService implementation, we are able to use custom table structure for our Spring Security authentication using JDBC. In our example, we are using a JDBC property file to get credentials. We create DataSource bean in Java configuration file. We will create a DAO to access user information using JDBC and SQL queries. Find the complete example step-by-step.

Software Used in Example

In our example, we are using software as follows.
1. Java 8
2. Tomcat 8
3. Spring 4
4. Eclipse
5. MySQL
6. Gradle

Project Structure in Eclipse

Find the project structure in Eclipse.
Spring MVC Security JDBC Authentication Example with Custom UserDetailsService and Database Tables using Java Configuration

Using Spring Boot in Gradle to Resolve JAR Dependencies

Find the Gradle file.
apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'war'
archivesBaseName = 'cp'
version = '1' 
repositories {
    maven { url "https://repo.spring.io/libs-release" }
dependencies {
    compile 'org.springframework.boot:spring-boot-starter-web:1.2.3.RELEASE'
    compile 'org.springframework.boot:spring-boot-starter-security:1.2.3.RELEASE'
    compile 'org.springframework.boot:spring-boot-starter-jdbc:1.2.3.RELEASE'
    compile 'mysql:mysql-connector-java:5.1.31'
    compile 'commons-dbcp:commons-dbcp:1.4'

Custom Database Tables for Spring Security Authentication

In our example we will use custom database tables. We can manage user, password and role according to our own way. Just for the example I am using two tables. Find the schema and data inserted into it. Password is in SHA-1 encoded.
Database Schema
-- Dumping database structure for concretepage
USE `concretepage`;

-- Dumping structure for table concretepage.comp_authorities
CREATE TABLE IF NOT EXISTS `comp_authorities` (
  `username` varchar(50) NOT NULL,
  `authority` varchar(50) NOT NULL,
  UNIQUE KEY `ix_auth_username` (`username`,`authority`),
  CONSTRAINT `fk_authorities_users` FOREIGN KEY (`username`) REFERENCES `comp_users` (`username`)

-- Dumping data for table concretepage.comp_authorities: ~2 rows (approximately)
INSERT INTO `comp_authorities` (`username`, `authority`) VALUES
	('krishna', 'ROLE_ADMIN'),
	('sudama', 'ROLE_USER');

-- Dumping structure for table concretepage.comp_users
  `username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `enabled` tinyint(1) NOT NULL,
  PRIMARY KEY (`username`)

-- Dumping data for table concretepage.comp_users: ~2 rows (approximately)
INSERT INTO `comp_users` (`username`, `password`, `enabled`) VALUES
	('krishna', '21a4ed0a0cf607e77e93bf7604e2bb1ad07757c5', 1),
	('sudama', '904752ad9c4ae4186c4b4897321c517de0618702', 1); 
There are two users with username/password as krishna/k123 and sudama/s123.

JDBC Property File

Find the property file which contains credentials to access database.

DAO for JDBC Authentication

Find the DAO class which provides a method to return user information for the given username. While accessing user information we create SQL query for our custom tables. From the DAO class, UserDetailsService needs username, password and role for the given username and to provide these value, we are free to use any table structure , ORM or SQL queries.
package com.concretepage.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import com.concretepage.bean.UserInfo;
public class UserDAO {
    private JdbcTemplate jdbcTemplate;
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    public UserInfo getUserInfo(String username){
    	String sql = "SELECT u.username name, u.password pass, a.authority role FROM "+
    			     "comp_users u INNER JOIN comp_authorities a on u.username=a.username WHERE "+
    			     "u.enabled =1 and u.username = ?";
    	UserInfo userInfo = (UserInfo)jdbcTemplate.queryForObject(sql, new Object[]{username},
    		new RowMapper<UserInfo>() {
	            public UserInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
	                UserInfo user = new UserInfo();
	                return user;
    	return userInfo;
package com.concretepage.bean;
public class UserInfo {
	private String username;
	private String password;
	private String role;
	public String getUsername() {
		return username;
	public void setUsername(String username) {
		this.username = username;
	public String getPassword() {
		return password;
	public void setPassword(String password) {
		this.password = password;
	public String getRole() {
		return role;
	public void setRole(String role) {
		this.role = role;

Custom UserDetailsService | Override loadUserByUsername() Method

To use custom UserDetailsService, we will create a class and implement its method loadUserByUsername(String username) which returns UserDetails. In login page, we enter username and password, and while authentication, spring security calls this method passing username obtained from login page.
package com.concretepage.service;
import java.util.Arrays;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.authority.SimpleGrantedAuthority;
import org.springframework.security.core.userdetails.User;
import org.springframework.security.core.userdetails.UserDetails;
import org.springframework.security.core.userdetails.UserDetailsService;
import org.springframework.security.core.userdetails.UsernameNotFoundException;
import org.springframework.stereotype.Service;
import com.concretepage.bean.UserInfo;
import com.concretepage.dao.UserDAO;
public class AuthenticationService implements UserDetailsService {
	private UserDAO userDAO;
	public UserDetails loadUserByUsername(String username)
			throws UsernameNotFoundException {
		UserInfo userInfo = userDAO.getUserInfo(username);
		GrantedAuthority authority = new SimpleGrantedAuthority(userInfo.getRole());
		UserDetails userDetails = (UserDetails)new User(userInfo.getUsername(), 
				userInfo.getPassword(), Arrays.asList(authority));
		return userDetails;

Java Configuration for Spring Security and UserDetailsService

In Spring Security java configuration, we are creating a bean for DataSource which will be used to connect to database. We are implementing a method configureGlobal() which has the argument AuthenticationManagerBuilder using which we call a method userDetailsService() to assign the instance of custom UserDetailsService. We are using SHA-1 password encoding for authentication.
package com.concretepage.config;  
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.security.authentication.encoding.ShaPasswordEncoder;
import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder;
import org.springframework.security.config.annotation.method.configuration.EnableGlobalMethodSecurity;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
import com.concretepage.service.AuthenticationService;
public class SecurityConfig extends WebSecurityConfigurerAdapter {
        Environment env;
	AuthenticationService authenticationService;
	protected void configure(HttpSecurity http) throws Exception {
	public void configureGlobal(AuthenticationManagerBuilder auth) throws Exception {
            ShaPasswordEncoder encoder = new ShaPasswordEncoder();
	public DataSource getDataSource() {
	    BasicDataSource dataSource = new BasicDataSource();
	    return dataSource;
package com.concretepage.config;  
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
@Import({ SecurityConfig.class })
public class AppConfig {  
package com.concretepage.config;
import org.springframework.security.web.context.AbstractSecurityWebApplicationInitializer;
public class SecurityInitializer extends AbstractSecurityWebApplicationInitializer {
package com.concretepage.config;
import org.springframework.web.servlet.support.AbstractAnnotationConfigDispatcherServletInitializer;
public class WebAppInitializer extends AbstractAnnotationConfigDispatcherServletInitializer  {
	protected Class<?>[] getRootConfigClasses() {
       	   return new Class[] { AppConfig.class };
        protected Class<?>[] getServletConfigClasses() {
           return null;
        protected String[] getServletMappings() {
           return new String[]{"/"};

Create Controller and Service Class

Find the controller.
package com.concretepage;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.core.Authentication;
import org.springframework.security.core.GrantedAuthority;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import com.concretepage.service.IInfoService;
public class InfoController {
	private  IInfoService service;
	public @ResponseBody String userInfo(Authentication authentication) {
		String msg = "";
		for (GrantedAuthority authority : authentication.getAuthorities()) {
		     String role = authority.getAuthority();
                     msg+=service.getMsg()+ authentication.getName()+", You have "+ role;
		return msg;
We are creating a service class which has a method with @PreAuthorize authorization annotation. We are accessing this method after user logged-in. IInfoService.java
package com.concretepage.service;
import org.springframework.security.access.prepost.PreAuthorize;
public interface IInfoService {
	public String getMsg();
package com.concretepage.service;
import org.springframework.stereotype.Service;
public class InfoService implements IInfoService {
	public String getMsg() {
		return "Hello ";

Check Output

To check the output access the URL http://localhost:8080/cp-1/info.
Spring MVC Security JDBC Authentication Example with Custom UserDetailsService and Database Tables using Java Configuration
Enter username/password as krishna/k123, we will get the response as follows.
Spring MVC Security JDBC Authentication Example with Custom UserDetailsService and Database Tables using Java Configuration

We are done now. Happy Spring Learning!

Download Complete Source Code


©2020 concretepage.com | Privacy Policy | Contact Us