Spring Boot Security MySQL Database Integration Example

By | 2018-11-11T10:12:53+00:00 October 28th, 2018|Spring Boot|0 Comments

In this tutorials, we are going to show how to secure spring boot rest services with MySQL database integration.

Spring Boot Security MySQL Database Integration:

In the previous example, we have discussed spring boot in-memory security where the user validation happened at in-memory, as part of this Spring Boot Security MySQL Database Integration the user validation takes place in the MySQL database.

Technology Used:

  • Spring Boot 2.0.6
  • Spring Boot WEB
  • Spring Security 2.0.6
  • Spring Boot Data JPA
  • MySQL 5.1.47
  • Java 8

Spring Boot Security MySQL Example:

As part of this example, I am going to create a simple spring boot rest service which provides two different rest endpoints, one is – to say hello to you and another one is secured rest endpoint which provides all item details.

Preparing Database Tables:

Here we need 3 different tables such as USER, ROLE and USER_ROLE. The relationship between USER and ROLE is many to many because one USER can have multiple ROLES and one ROLE can be assigned to multiple USERS.

USER:

OTP.USER
CREATE TABLE `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `active` int(11) DEFAULT NULL,
  `lastname` varchar(255) NOT NULL,
  `firstname` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

ROLE:

OTP.ROLE
CREATE TABLE `role` (
  `role_id` int(11) NOT NULL AUTO_INCREMENT,
  `role_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

USER_ROLE:

As per the normalization we need to have this intermediate table to represent the many to many relationships.

OTP.USER_ROLE
CREATE TABLE `user_role` (
  `user_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  PRIMARY KEY (`user_id`,`role_id`),
  UNIQUE KEY `UK_it77eq964jhfqtu54081ebtio` (`role_id`),
  CONSTRAINT `FK859n2jvi8ivhui0rl0esws6o` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`),
  CONSTRAINT `FKa68196081fvovjhkek5m97n3y` FOREIGN KEY (`role_id`) REFERENCES `role` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Insert Data:

User and Role data
-- creating user
insert into OTP.USER values(1,1,'shekhar','chandra','12345');
-- creating ADMIN role
insert into OTP.ROLE values(1,'ADMIN');
-- Mapping ADMIN role id (1) to shekhar user userid (1)
insert into OTP.USER_ROLE values(1,1);

Application Structure:

Spring Boot Security MySQL Database Project

Application Dependencies:

pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.onlinetutorialspoint</groupId>
  <artifactId>SpringBoot-Security-Mysql-Example</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>SpringBoot-Security-Mysql-Example</name>
  <description>Spring Boot Security MySQL Database Integration Example</description>

  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.0.6.RELEASE</version>
    <relativePath/> <!-- lookup parent from repository -->
  </parent>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    <java.version>1.8</java.version>
  </properties>

  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jpa</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-web</artifactId>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <scope>runtime</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework.security</groupId>
      <artifactId>spring-security-test</artifactId>
      <scope>test</scope>
    </dependency>
  </dependencies>
  <build>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
      </plugin>
    </plugins>
  </build>
</project>

Database configuration properties.

application.properties
server.port=8080
spring.datasource.driver-class-name: com.mysql.jdbc.Driver
spring.datasource.url: jdbc:mysql://localhost:3306/otp
spring.datasource.username: root
spring.datasource.password: 12345

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

Item Model: Carrying Items.

Item.java
package com.onlinetutorialspoint.model;

import java.io.Serializable;

public class Item implements Serializable {
    private Integer id;
    private String name;
    private String category;

    public Item() {
    }

    public Item(Integer id, String name, String category) {
        this.id = id;
        this.name = name;
        this.category = category;
    }
    // Getters and Setters
}

Creating JPA Entities:

User.java representing USER table.

User.java
package com.onlinetutorialspoint.model;

import javax.persistence.*;

@Entity
@Table(name="role")
public class Role {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "role_id")
    private int id;

    @Column(name = "role_name")
    private String roleName;

    public Role() {
    }

    public Role(int id, String roleName) {
        this.id = id;
        this.roleName = roleName;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }
}

Role.java representing ROLE table.

Role.java
package com.onlinetutorialspoint.model;

import javax.persistence.*;

@Entity
@Table(name="role")
public class Role {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "role_id")
    private int id;

    @Column(name = "role_name")
    private String roleName;

    public Role() {
    }

    public Role(int id, String roleName) {
        this.id = id;
        this.roleName = roleName;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }
}

Creating JPA Repository.

UserRepository.java
package com.onlinetutorialspoint.repos;

import com.onlinetutorialspoint.model.User;
import org.springframework.data.jpa.repository.JpaRepository;

import java.util.Optional;

public interface UsersRepository extends JpaRepository<User, Integer> {
    Optional<User> findByFirstName(String firstname);
}

Spring Security UserdetailService Implementation.

UserDetailsServiceImpl.java
package com.onlinetutorialspoint.service;

import com.onlinetutorialspoint.model.User;
import com.onlinetutorialspoint.model.UserDetailsImpl;
import com.onlinetutorialspoint.repos.UsersRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.core.userdetails.UserDetails;
import org.springframework.security.core.userdetails.UserDetailsService;
import org.springframework.security.core.userdetails.UsernameNotFoundException;
import org.springframework.stereotype.Service;

import java.util.Optional;

@Service
public class UserDetailsServiceImpl implements UserDetailsService {

    @Autowired
    private UsersRepository usersRepository;
    @Override
    public UserDetails loadUserByUsername(String userName) throws UsernameNotFoundException {
        Optional<User> optionalUser = usersRepository.findByFirstName(userName);
        return Optional.ofNullable(optionalUser).orElseThrow(()->new UsernameNotFoundException("Username Not Found"))
               .map(UserDetailsImpl::new).get();
    }
}

Spring Security UserDetails implementation.

UserDetailsImpl.java
package com.onlinetutorialspoint.model;

import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.authority.SimpleGrantedAuthority;
import org.springframework.security.core.userdetails.UserDetails;

import java.util.Collection;
import java.util.stream.Collectors;

public class UserDetailsImpl extends User implements UserDetails {

    public UserDetailsImpl(User user) {
        super(user);
    }

    @Override
    public Collection<? extends GrantedAuthority> getAuthorities() {
        return getRoles()
                .stream()
                .map(role-> new SimpleGrantedAuthority("ROLE_"+role.getRoleName()))
                .collect(Collectors.toList());
    }

    @Override
    public String getPassword() {
        return super.getPassword();
    }

    @Override
    public String getUsername() {
        return super.getFirstName();
    }

    @Override
    public boolean isAccountNonExpired() {
        return true;
    }

    @Override
    public boolean isAccountNonLocked() {
        return true;
    }

    @Override
    public boolean isCredentialsNonExpired() {
        return true;
    }

    @Override
    public boolean isEnabled() {
        return true;
    }
}

Spring Security Configuration

SecureConfig.java
package com.onlinetutorialspoint.configuration;

import com.onlinetutorialspoint.repos.UsersRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder;
import org.springframework.security.config.annotation.method.configuration.EnableGlobalMethodSecurity;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
import org.springframework.security.core.userdetails.UserDetailsService;
import org.springframework.security.crypto.password.PasswordEncoder;

@EnableGlobalMethodSecurity(prePostEnabled = true)
@Configuration
@EnableWebSecurity
@EnableJpaRepositories(basePackageClasses = UsersRepository.class)
public class SecureConfig extends WebSecurityConfigurerAdapter {

    @Autowired
    UserDetailsService userDetailsService;
    @Override
    protected void configure(AuthenticationManagerBuilder auth) throws Exception {
        auth.userDetailsService(userDetailsService)
                .passwordEncoder(getPasswordEncoder());
    }

    @Override
    protected void configure(HttpSecurity http) throws Exception {

        http.csrf().disable();
        http.authorizeRequests()
                .antMatchers("**/getAllItems").authenticated()
                .anyRequest().permitAll()
                .and().formLogin().permitAll();
    }

    private PasswordEncoder getPasswordEncoder() {
        return new PasswordEncoder() {
            @Override
            public String encode(CharSequence charSequence) {
                return charSequence.toString();
            }

            @Override
            public boolean matches(CharSequence charSequence, String s) {
                return true;
            }
        };
    }
}

Creating the Rest Controller to provide /hello and /getAllItems rest endpoints.

ItemController.java
package com.onlinetutorialspoint.controller;

import com.onlinetutorialspoint.model.Item;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.util.UriComponentsBuilder;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

@RestController
public class ItemController {

    @Autowired
    ItemController itemService;

    public static List<Item> items;
    static{
        items = new ArrayList<>(Arrays.asList(new Item(1,"Spring Boot in Action","Books"),
                new Item(2,"Java 8 in Action","Books"),
                new Item(3,"Data Structures","Books"),
                new Item(4,"Spring Boot Security","Books")));
    }

    @PreAuthorize("hasAnyRole('ADMIN')")
    @RequestMapping("/getAllItems")
    @ResponseBody
    public ResponseEntity<List<Item>> getAllItems() {
        //Reading all items (ADMIN only can access this)
        List<Item> items = this.items;
        System.out.println("Reading items: "+items);
        return new ResponseEntity<List<Item>>(items, HttpStatus.OK);
    }

    @RequestMapping("/hello")
    public String sayHello(){
        return "Hello User!";
    }

}

Main class.

SpringBootSecurityMysqlExampleApplication.java

package com.onlinetutorialspoint;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SpringBootSecurityMysqlExampleApplication {

	public static void main(String[] args) {
		SpringApplication.run(SpringBootSecurityMysqlExampleApplication.class, args);
	}
}

Run It

Terminal
mvn spring-boot:run

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.0.6.RELEASE)

2018-10-28 12:52:26.892  INFO 13656 --- [           main] pringBootSecurityMysqlExampleApplication : Starting SpringBootSecurityMysqlExampleApplication on DESKTOP-RN4SMHT with PID 13656 (E:\work\SpringBoot-Security-Mysql-Example\target\classes started by Lenovo in E:\work\SpringBoot-Security-Mysql-Example)
2018-10-28 12:52:26.911  INFO 13656 --- [           main] pringBootSecurityMysqlExampleApplication : No active profile set, falling back to default profiles: default
2018-10-28 12:52:27.142  INFO 13656 --- [           main] ConfigServletWebServerApplicationContext : Refreshing org.springframework.boot.web.servlet.context.AnnotationConfigServletWebServerApplicationContext@14dd7b39: startup date [Sun Oct 28 12:52:27 IST 2018]; root of context hierarchy
.....
.....

Access the application:

Accessing /hello (unsecured) endpoint

Spring Boot Security MySQL Database Normal User

Accessing /getAllItems secured endpoint — An ADMIN can access this endpoint with valid user credentials.

As soon as we access the localhost:8080/getAllItems endpoint, the spring automatically redirects our request to the login page as it is a secured service. Here we have to provide our valid user credentials then only it will allow us to access the /getAllItems endpoint.

Note: We haven’t created this login form at all. Spring has given us to provide the login details.

Let’s provide invalid user credentials and see what will happen.

Database ADMIN User Invalid

Here I provided invalid username like shekhar123 and click on Login button, as a result, we should get the below error response saying login attempt was not successful.

Spring Boot Security MySQL Database ADMIN User Invalid Error

Let’s provide valid user credentials and see the result.

Spring Boot Security MySQL Database ADMIN User

Now we can able to see all items provided by rest endpoint.

Spring Boot Security MySQL Database ADMIN User Results

References:

Happy Learning 🙂

Download Example

About the Author:

Hi Folks, you have reach this so far, that shows you like what you are learning. Then why don't you support us to improve for bettor tutorials by leaving your valuable comments and why not you keep in touch with us for latest updates on your favorite blog @ facebook , twitter , Or Google+ ,

Leave A Comment