Spring Security – JDBC Authentication – SpringBoot + MySQL + Bootstrap

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:

SpringSecurityJDBCAuthenticationMySQL - project structure

With 5 urls:

– ‘/’: access with everyone.

SpringSecurityJDBCAuthenticationMySQL

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

SpringSecurityJDBCAuthenticationMySQL

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

SpringSecurityJDBCAuthenticationMySQL

– ‘/login’: login page

SpringSecurityJDBCAuthenticationMySQL

– ‘/403’: HTTP Error 403 Forbidden

SpringSecurityJDBCAuthenticationMySQL

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

SpringSecurityJDBCAuthenticationMySQL

-> 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



Security with Spring Boot


	

Hello, This is Home page!

User Page
Admin Page%MINIFYHTML87714da0685ab5f745912c3acadfe41d16%
user.html



Welcome Security with Spring Boot!


	

Hello, the page is for Users!

Home
admin.html



Welcome Security with Spring Boot!


	

Hello, the page is for Admin!

Home
login.html



	Welcome Security with Spring Boot!
	
	
	%MINIFYHTML87714da0685ab5f745912c3acadfe41d17%%MINIFYHTML87714da0685ab5f745912c3acadfe41d18%

	
Sign In

UserName or PassWord is wrong. Please check again!

Logged out.

403.html



Security with Spring Boot


	

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



By grokonez | February 10, 2017.

Last updated on February 18, 2018.



Related Posts


14 thoughts on “Spring Security – JDBC Authentication – SpringBoot + MySQL + Bootstrap”

  1. Hi,
    my output after trial this tutorial always give the result below:

    java.lang.IllegalArgumentException: There is no PasswordEncoder mapped for the id “null”
    at org.springframework.security.crypto.password.DelegatingPasswordEncoder$Unmapped

    may you can explain what configure should be to fix this?

    thank you

    1. adding .passwordEncoder(new BCryptPasswordEncoder()); at the end of auth maybe help you,
      sample:
      auth.jdbcAuthentication().dataSource(dataSource)
      .usersByUsernameQuery(“select username,password, enabled from users where username=?”)
      .authoritiesByUsernameQuery(“select username, role from user_roles where username=?”). .passwordEncoder(new BCryptPasswordEncoder());

    2. Please configure password encoder using BCryptPasswordEncoder as shown below:

      @Override
          protected void configure(AuthenticationManagerBuilder auth) throws Exception {
              auth.jdbcAuthentication().dataSource(dataSource).passwordEncoder(new BCryptPasswordEncoder())
                      .usersByUsernameQuery("select username, password, enabled from security_user where username=?")
                      .authoritiesByUsernameQuery("select username,role from role where username=?");
          }
      
      1. Hi,
        I have tried your solution but it’s still working.
        (WARN 3534 — [io-8080-exec-10] o.s.s.c.bcrypt.BCryptPasswordEncoder : Encoded password does not look like BCrypt). it seem it not match format that I store in MySQL (password: 1).

        1. Hi all,

          If you want to Bcrypt passwork, just define a new Bean BCryptPasswordEncoder
          Then use configure it with authenticationManagerBuilder

          All in class SecurityConfig extends WebSecurityConfigurerAdapter

          Example code:

          @Configuration
          @EnableWebSecurity
          public class WebSecurityConfig extends WebSecurityConfigurerAdapter {
              @Override
              public void configure(AuthenticationManagerBuilder authenticationManagerBuilder) throws Exception {
                  authenticationManagerBuilder
                          .userDetailsService(userDetailsService)
                          .passwordEncoder(passwordEncoder());
              }
          
              @Bean
              public PasswordEncoder passwordEncoder() {
                  return new BCryptPasswordEncoder();
              }
          

          You can see more at Spring-Security-JWT post ->

          Spring Security JWT Authentication – SpringBoot + Spring JPA + MySQL

          Regards

          1. Thanks Grokonez! it works.

            @Override
                protected void configure(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=?")
                            .passwordEncoder(passwordEncoder());
            }
            @Bean
                public PasswordEncoder passwordEncoder() {
                    return new BCryptPasswordEncoder();
            }
            

            And make sure you store password like this format “$2a$10$EblZqNptyYvcLm/VwDCVAuBjzZOI7khzdyGPBr08PpIi0na624b8.” (123456)

  2. what should i do?
    java.lang.IllegalArgumentException: There is no PasswordEncoder mapped for the id “null”

    1. add to your code:

      @Bean
      BCryptPasswordEncoder passwordEncoder() {
      return new BCryptPasswordEncoder();
      }

  3. Hi,

    I have been trying to add JDBC based authentication (replacing the inMemory authentication) and I came across similar steps in various articles that I read. My problem is that it gives me “Could not get JDBC connection ” everytime! My question is that do you add the datasource properties in application.properties only or elsewhere? Coz when I see your code, the case for datasource is different. “dataSource” and “datasource”. Infact, Ive put same case in my code but still im getting the JDBC connection not found error! Please help..

  4. Thank you so much! It really works for me! Perfect! I spent few days trying to setup JWT authorization and your tutorial is the BEST!

  5. Hi. I am trying to use this project but everytime I try to login the application crashes and I get this error in the console: “Unable to load authentication plugin ‘caching_sha2_password’.”. What is the solution to this problem?

  6. i’m getting Encoded password does not look like BCrypt even i added .passwordEncoder(passwordEncoder()); and the bean of .passwordEncoder , and add and remove the id {bcrypt} and {noop} what is the problem

Got Something To Say:

Your email address will not be published. Required fields are marked *

*