How to use Spring Security JDBC Authentication with MySQL & Spring Boot

In the tutorial, JavaSampleApproach uses MySQL & Spring Boot to guide you how to develop a Spring Security JDBC for Authentication.

Related Articles:
How to use Spring Security JDBC Authentication with PostgreSQL & Spring Boot
Spring Security – Config Security for Web MVC by Spring Boot
Spring Security – Customize Login Handler
Spring Security Customize Logout Handler
Spring Security – Customize Authentication Provider


I. Technologies

– Java 1.8
– Maven 3.3.9
– Spring Boot
– Spring Tool Suite – Version 3.8.1.RELEASE
– MySQL

II. Overview
1. Project Structure

spring security jdbc authentication mysql - project structure

2. URLs of Web Application

We create a MVC Web Application with 6 url:
– “/”, “/home”: access with everyone.
– “/welcome”: must authenticate and be accessed with user ROLE: USER or ADMIN.
– “/admin”: accessed by user with Role: Admin.
– “/login”: login page.
– “/403”: HTTP Error 403 Forbidden.

3. Step to do

– Create a Spring Boot project
– Create Controller
– Create View Pages
– Configure Database
– Configure WebSecurity
– Run & Enjoy Results

III. Practices
1. Create a Spring Boot project

Open Spring Tool Suite, File->New->Spring Starter Project: New Spring Starter Project is open, input info about project.
Press Next button, add needed dependencies:
– For Security, choose Core -> select: Security
– For Template Engines, choose Template Engines->select: Thymeleaf.
– For Database MySql, choose SQL->select: MySQL & JDBC
– For Web MVC, choose Web -> select: Web

– Press Finish -> Spring Boot Project will create successfully.
Open pom.xml and check dependencies:

2. Create Controller

Create a simple MVC Controller with 6 url:
– “/”, “home” -> return: home.html page
– “/welcome” -> return: welcome.html page
– “/admin” -> return: admin.html page
– “/login” -> return: login.html page
– “/403” -> return 403.html page

3. Create View Pages

Create home.html page
Home page has a Button that navigates to Welcome Page.

Create WelcomePage: welcome.html
Welcome page is a protect by security, So we need login before go to Welcome.

– Create Admin Page: admin.html
Admin Page is accessed with Admin users.

Spring provides a default login pages, but we can customize a login.html page as below:

Create an access denied pages: 403.html
If an user try to access url but Not has permission, our web app will redirect to 403.html for notified message.

4. Configure Database

Open application.properties, configure database properties:

Create User table with 3 columns: username, password and enabled(used to active a user account).

Create user_roles:

Insert data to 2 tables:

We had created 2 active accounts: jack/jack with role: USER/ADMIN & peter/peter with role: USER

Users table:
spring security jdbc authentication mysql - user table
User_Role table:
spring security jdbc authentication mysql - user_role table

5. Configure WebSecurity

– Create a SecurityConfig class that extends – WebSecurityConfigurerAdapter
– Override method: configAuthentication(AuthenticationManagerBuilder auth) to setup SQL queries for users & roles.
Override configure(HttpSecurity http) to customize http requests.

6. Run & Enjoy Results

Build & Run the project with Spring Boot App mode.
– Login with both accounts: Jack/Jack & Peter/Peter can access Welcome Page:
spring security jdbc authentication - welcome page
– Login with Jack/Jack account, can access Admin Page beacause Jack has ADMIN role.
spring security jdbc authentication - admin page
– But with Peter/Peter account, can NOT access Admin Page beacause Peter just has USER role only.
spring security jdbc authentication - denied page

IV. Sourcecode

SpringSecurityAuthenticationMySQL


Related Posts


Got Something To Say:

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

*