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
Project Structure in Eclipse
Using Spring Boot in Gradle to Resolve JAR Dependencies
Custom Database Tables for Spring Security Authentication
Custom UserDetailsService | Override loadUserByUsername() Method
JDBC Property File
DAO for JDBC Authentication
Java Configuration for Spring Security and UserDetailsService
Create Controller and Service Class
Check Output

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.
build.gradle

apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'war'
archivesBaseName = 'cp'
version = '1' 
repositories {
    maven { url "https://repo.spring.io/libs-release" }
    mavenLocal()
    mavenCentral()
}
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
CREATE DATABASE IF NOT EXISTS `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`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- 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
CREATE TABLE IF NOT EXISTS `comp_users` (
  `username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `enabled` tinyint(1) NOT NULL,
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- 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.
jdbc.properties

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

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.
UserDAO.java

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;
@Repository
public class UserDAO {
    private JdbcTemplate jdbcTemplate;
    @Autowired
    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();
	                user.setUsername(rs.getString("name"));
	                user.setPassword(rs.getString("pass"));
	                user.setRole(rs.getString("role"));
	                return user;
	            }
        });
    	return userInfo;
    }
}

UserInfo.java

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.
AuthenticationService.java

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;
@Service
public class AuthenticationService implements UserDetailsService {
	@Autowired
	private UserDAO userDAO;
	@Override
	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.
SecurityConfig.java

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;
@Configuration
@ComponentScan("com.concretepage")
@EnableWebSecurity
@EnableGlobalMethodSecurity(prePostEnabled=true)
@PropertySource("classpath:jdbc.properties")
public class SecurityConfig extends WebSecurityConfigurerAdapter {
	@Autowired
        Environment env;
	@Autowired
	AuthenticationService authenticationService;
	@Override
	protected void configure(HttpSecurity http) throws Exception {
		http.authorizeRequests().antMatchers("/info/**").hasAnyRole("ADMIN","USER").
		and().formLogin();
	}
        @Autowired
	public void configureGlobal(AuthenticationManagerBuilder auth) throws Exception {
            ShaPasswordEncoder encoder = new ShaPasswordEncoder();
            auth.userDetailsService(authenticationService).passwordEncoder(encoder);
	}
        @Bean
	public DataSource getDataSource() {
	    BasicDataSource dataSource = new BasicDataSource();
	    dataSource.setDriverClassName(env.getProperty("jdbc.driverClassName"));
	    dataSource.setUrl(env.getProperty("jdbc.url"));
	    dataSource.setUsername(env.getProperty("jdbc.username"));
	    dataSource.setPassword(env.getProperty("jdbc.password"));
	    return dataSource;
	}
}

AppConfig.java

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;
@Configuration 
@ComponentScan("com.concretepage") 
@EnableWebMvc   
@Import({ SecurityConfig.class })
public class AppConfig {  
}

SecurityInitializer.java

package com.concretepage.config;
import org.springframework.security.web.context.AbstractSecurityWebApplicationInitializer;
public class SecurityInitializer extends AbstractSecurityWebApplicationInitializer {
}

WebAppInitializer.java

package com.concretepage.config;
import org.springframework.web.servlet.support.AbstractAnnotationConfigDispatcherServletInitializer;
public class WebAppInitializer extends AbstractAnnotationConfigDispatcherServletInitializer  {
	@Override
	protected Class<?>[] getRootConfigClasses() {
       	   return new Class[] { AppConfig.class };
	}
	@Override
        protected Class<?>[] getServletConfigClasses() {
           return null;
        }
        @Override
        protected String[] getServletMappings() {
           return new String[]{"/"};
        } 
}

Create Controller and Service Class

Find the controller.
InfoController.java

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;
@Controller
@RequestMapping
public class InfoController {
	@Autowired
	private  IInfoService service;
	@RequestMapping("/info")
	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 {
	@PreAuthorize("hasAnyRole('ROLE_ADMIN','ROLE_USER')")
	public String getMsg();
}

InfoService.java

package com.concretepage.service;
import org.springframework.stereotype.Service;
@Service
public class InfoService implements IInfoService {
	public String getMsg() {
		return "Hello ";
	}
}