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:

2. Create Controller

3. Create View Pages
home.html

user.html

admin.html

login.html

403.html

4. Configure Database

Open application.properties, configure database properties:

Create user table with 3 columns:

Create user_roles table:

Insert data to 2 tables:

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.

IV. SourceCode

SpringSecurityJDBCAuthenticationMySQL

By grokonez | February 10, 2017.

Last updated on February 18, 2018.



Related Posts


5 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. what should i do?
    java.lang.IllegalArgumentException: There is no PasswordEncoder mapped for the id “null”

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

Got Something To Say:

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

*