In the tutorial, JavaSampleApproach will show you how to create a Spring Security JDBC Authentication with SpringBoot + MySQL + Bootstrap.
Contents
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-security</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-thymeleaf</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> |
2. Create Controller
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
<!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml" xmlns:th="http://www.thymeleaf.org" xmlns:sec="http://www.thymeleaf.org/thymeleaf-extras-springsecurity3"> <head> <title>Security with Spring Boot</title> </head> <body> <h1>Hello, This is Home page!</h1> <a style="color: blue" th:href="@{/user}">User Page</a> <br /> <a style="color: blue" th:href="@{/admin}">Admin Page</a> </body> </html> |
user.html
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml" xmlns:th="http://www.thymeleaf.org" xmlns:sec="http://www.thymeleaf.org/thymeleaf-extras-springsecurity3"> <head> <title>Welcome Security with Spring Boot!</title> </head> <body> <h1>Hello, the page is for Users!</h1> <a style="color: blue" th:href="@{/}">Home</a> <form th:action="@{/logout}" method="post"> <input type="submit" value="Sign Out" /> </form> </body> </html> |
admin.html
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml" xmlns:th="http://www.thymeleaf.org" xmlns:sec="http://www.thymeleaf.org/thymeleaf-extras-springsecurity3"> <head> <title>Welcome Security with Spring Boot!</title> </head> <body> <h1>Hello, the page is for Admin!</h1> <a style="color: blue" th:href="@{/}">Home</a> <form th:action="@{/logout}" method="post"> <input type="submit" value="Sign Out" /> </form> </body> </html> |
login.html
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
<!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml" xmlns:th="http://www.thymeleaf.org" xmlns:sec="http://www.thymeleaf.org/thymeleaf-extras-springsecurity3"> <head> <title>Welcome Security with Spring Boot!</title> <meta charset="utf-8"/> <meta name="viewport" content="width=device-width, initial-scale=1"/> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"/> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script> </head> <body class="container" style="margin:50px"> <div class="row col-sm-6" style="border: 1px ridge #003312; padding:20px; float: none; margin: 0 auto;"> <h5 class="text-center" style="font-size: 25px">Sign In</h5> <div th:if="${param.error}"> <p style="color: red">UserName or PassWord is wrong. Please check again!</p> </div> <div th:if="${param.logout}"> <h1 style="color: blue">Logged out.</h1> </div> <form th:action="@{/login}" method="post"> <div class="form-group"> <label for="username">User Name: </label> <input type="text" class="form-control" id="username" placeholder="Enter UserName" name="username"/> </div> <div class="form-group"> <label for="password">Password: </label> <input type="password" class="form-control" id="password" placeholder="Enter Password" name="password"/> </div> <button type="submit" class="btn btn-primary btn-block">Submit</button> </form> </div> </body> </html> |
403.html
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml" xmlns:th="http://www.thymeleaf.org" xmlns:sec="http://www.thymeleaf.org/thymeleaf-extras-springsecurity3"> <head> <title>Security with Spring Boot</title> </head> <body> <h1>Access is Denied!</h1> <a style="color: blue" th:href="@{/}">Home</a> <form th:action="@{/logout}" method="post"> <input type="submit" value="Sign Out" /> </form> </body> </html> |
4. Configure Database
Open application.properties, configure database properties:
1 2 3 |
spring.datasource.url=jdbc:mysql://localhost:3306/testdb spring.datasource.username=root spring.datasource.password=12345 |
Create user table with 3 columns:
1 2 3 4 5 |
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:
1 2 3 4 5 6 7 8 |
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:
1 2 3 4 5 6 |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
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
Last updated on February 18, 2018.
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
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());