In the tutorial, JavaSampleApproach will show you how to create a Spring Security JDBC Authentication with SpringBoot + MySQL + Bootstrap.

I. Technologies

– Apache Maven 3.5.2
– Spring Tool Suite – Version 3.9.0.RELEASE
– Spring Boot – 1.5.10.RELEASE
– Bootstrap
– MySQL

II. Goal

We create a Spring MVC Web Application as below:

With 5 urls:

– ‘/’: access with everyone.

– ‘/user’: must authenticate and be accessed with user ROLE {USER, ADMIN}

– ‘/admin’: accessed by user with role Admin

– ‘/login’: login page

– ‘/403’: HTTP Error 403 Forbidden

We create 2 MySQL tables for 2 users (username/password):
– jack/jack has 2 roles {USER, ADMIN}
– peter/peter has 1 role USER

-> jack/jack can access both pages {user.html, admin.html}. While peter/peter just accesses 1 page user.html.

III. Implementation

Step to do
– Create Spring Boot project
– Create Controller
– Create View Pages
– Configure Database
– Configure WebSecurity

1. Create Spring Boot project

Use SpringToolSuite to create a SpringBoot project with below dependencies:

	org.springframework.boot
	spring-boot-starter-jdbc


	org.springframework.boot
	spring-boot-starter-security


	org.springframework.boot
	spring-boot-starter-thymeleaf


	org.springframework.boot
	spring-boot-starter-web


	mysql
	mysql-connector-java
	runtime

2. Create Controller

package com.javasampleapproach.springsecurity.jdbcauthentication.controller;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
 
@Controller
public class WebController {
   
    @RequestMapping(value="/")
    public String home(){
        return "home";
    }
   
    @RequestMapping(value="/user")
    public String user(){
        return "user";
    }
  
    @RequestMapping(value="/admin")
    public String admin(){
        return "admin";
    }
   
    @RequestMapping(value="/login")
    public String login(){
        return "login";
    }
   
    @RequestMapping(value="/403")
    public String Error403(){
        return "403";
    }
}

3. Create View Pages

home.html

	
Hello, This is Home page!
	User Page
	

	Admin Page

user.html

	
Hello, the page is for Users!
	Home
	

		
	

admin.html

	
Hello, the page is for Admin!
	Home
	

		
	

login.html

	
	
	
	
	
	


	

		
Sign In
		

			
UserName or PassWord is wrong. Please
				check again!
	
		
		

			
Logged out.
		
		

			

				User Name: 
				
			
			

				Password: 
				
			
			Submit
		
	

403.html

	
Access is Denied!
	Home
	

		
	

4. Configure Database

Open application.properties, configure database properties:

spring.datasource.url=jdbc:mysql://localhost:3306/testdb
spring.datasource.username=root
spring.datasource.password=12345

Create user table with 3 columns:

CREATE  TABLE testdb.users (
  username VARCHAR(20) NOT NULL ,
  password VARCHAR(20) NOT NULL ,
  enabled TINYINT NOT NULL DEFAULT 1 ,
  PRIMARY KEY (username));

Create user_roles table:

CREATE TABLE testdb.user_roles (
  user_role_id int(11) NOT NULL AUTO_INCREMENT,
  username varchar(20) NOT NULL,
  role varchar(20) 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 testdb.users (username));

Insert data to 2 tables:

INSERT INTO testdb.users(username,password,enabled) VALUES ('jack','jack', true);
INSERT INTO testdb.users(username,password,enabled) VALUES ('peter','peter', true);
 
INSERT INTO testdb.user_roles (username, role) VALUES ('jack', 'ROLE_USER');
INSERT INTO testdb.user_roles (username, role) VALUES ('jack', 'ROLE_ADMIN');
INSERT INTO testdb.user_roles (username, role) VALUES ('peter', 'ROLE_USER');

5. Configure WebSecurity

Create a SecurityConfig class that extends WebSecurityConfigurerAdapter.
Then override method onfigAuthentication(AuthenticationManagerBuilder auth) to setup SQL queries for users & roles. And override configure(HttpSecurity http) to customize http requests.

package com.javasampleapproach.springsecurity.jdbcauthentication.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.context.annotation.Configuration;
import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
 
@Configuration
@EnableAutoConfiguration
public class SecurityConfig extends WebSecurityConfigurerAdapter {
 
	@Autowired
	DataSource dataSource;
 
	@Autowired
	public void configAuthentication(AuthenticationManagerBuilder auth) throws Exception {
		auth.jdbcAuthentication().dataSource(dataSource)
				.usersByUsernameQuery("select username,password, enabled from users where username=?")
				.authoritiesByUsernameQuery("select username, role from user_roles where username=?");
	}
 
	@Override
	protected void configure(HttpSecurity http) throws Exception {
		http.authorizeRequests().antMatchers("/", "/home").permitAll().antMatchers("/admin").hasRole("ADMIN")
				.anyRequest().authenticated().and().formLogin().loginPage("/login").permitAll().and().logout()
				.permitAll();
		http.exceptionHandling().accessDeniedPage("/403");
	}
}

IV. SourceCode

SpringSecurityJDBCAuthenticationMySQL