Spring Boot Security Authentication - Configuring Credentials in database (2024)

In previous article, we learned about Spring Boot Security Basic Authentication and Spring Boot Security Digest Authentication. In this tutorial, will configure user credential in database.

Project Structure:

Create Tables:

Following 2 tables need to be created to store credentials and authorities.

1. users: The users table stores credentials.
2. authorities: It store user authorities.

This script(schema.sql) will be executed as soon as the spring boot application starts running.

Create Schema:

create table users(
	username VARCHAR(50) not null primary key,
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	password VARCHAR(200) not null,
	email VARCHAR(100) not null,
	enabled boolean not null
);

create table authorities (
	username VARCHAR(50) not null,
	authority VARCHAR(50) not null,
	constraint fk_authorities_users foreign key(username) references users(username)
);

create unique index ix_auth_username on authorities (username,authority);

Insert Data into table:

data.sql will insert data into user and authorities. Storing password in {noop}, indicates that we are storing plain password without any encryption. And if you see in WebSecurityConfiguration, we are using createDelegatingPasswordEncoder, which will internally call NoOpPasswordEncoder when will retrieve password from database.

INSERT into users(username,password,email,enabled) values ('user','{noop}userPass','user@gmail.com',true);
INSERT into users(username,password,email,enabled) values ('admin','{noop}adminPass','admin@gmail.com',true);

INSERT into authorities values ('user','USER');
INSERT into authorities values ('admin','ADMIN');

Configure Dependencies

Include spring security starter , mysql-connector-java and jpa dependencies in the 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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.3.4.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.techgeeknext</groupId>
	<artifactId>websecurity</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>websecurity</name>
	<description>Spring boot security authentication project</description>

	<properties>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<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>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</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-data-jpa</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
			<exclusions>
				<exclusion>
					<groupId>org.junit.vintage</groupId>
					<artifactId>junit-vintage-engine</artifactId>
				</exclusion>
			</exclusions>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

Configure Data Source Properties

server:
  port: 8083

spring:
  datasource:
    url: jdbc:mysql://localhost/SecuritySchema?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
    username: root
    password: root
    platform: mysql
    initialization-mode: always

  # Hibernate ddl auto (create, create-drop, validate, update)
  jpa:
  hibernate.ddl-auto: create-drop

Configure Spring Security Authentication & Authorization

package com.techgeeknext.websecurity.config;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.security.authentication.AuthenticationManager;
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.EnableWebSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
import org.springframework.security.crypto.factory.PasswordEncoderFactories;
import org.springframework.security.crypto.password.DelegatingPasswordEncoder;
import org.springframework.security.crypto.password.PasswordEncoder;

import javax.sql.DataSource;

@Configuration
@EnableWebSecurity
public class WebSecurityConfiguration extends WebSecurityConfigurerAdapter {

    @Override
    protected void configure(HttpSecurity http) throws Exception {
        http.authorizeRequests()
                .antMatchers("/hello/user/*").hasAnyRole("USER","ADMIN")
                .anyRequest().authenticated()
                .and()
                .formLogin().permitAll()
                .and()
                .logout().permitAll()
                .and()
                .exceptionHandling().accessDeniedPage("/403")
        ;

        http.csrf().disable();
    }


    @Bean
    public PasswordEncoder passwordEncoder() {
        //it'll delegate to NoOpPasswordEncoder
        return (DelegatingPasswordEncoder) PasswordEncoderFactories.createDelegatingPasswordEncoder();
    }

    @Autowired
    private DataSource dataSource;

    @Override
    @Bean
    public AuthenticationManager authenticationManagerBean() throws Exception{
       return  super.authenticationManagerBean();
    }

 @Autowired
	public void globalUserDetails(AuthenticationManagerBuilder auth) throws Exception {
		auth.jdbcAuthentication()
		.dataSource(dataSource).passwordEncoder(passwordEncoder())
        // set credential from database
		.usersByUsernameQuery("select username,password,enabled from users where username=?")
        //set authorities from database
		.authoritiesByUsernameQuery("select username,authority from authorities where username=?");
	}
}

REST Controller:

This is a simple Hello REST controller.

package com.techgeeknext.websecurity.controller;

import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.security.Principal;

@RestController
public class HelloController {

	@GetMapping("/hello/user")
	public String sayHello(@RequestParam(required = false,defaultValue = "TechGeekNext User") String name, Principal principal) {
		return name+" : You are logged in as <b><u>"+principal.getName()+"</b></u>";
	}
}

Testing Digest Authentication

1. Enter valid credential with User Authorities, using http://localhost:8083/hello/user?name=TechGeekUser rest api.

Enter Username: user and Password: userPass

After login, you can see user has been login through User Authorities.

2. Enter valid credential with Admin Authorities
   Enter Username: admin and Password: adminPass
   
   After login, you can see user has been login through Admin Authorities.
   

Download Source Code

The full source code for this article can be found on below.
Download it here - Spring Boot Security - Configuring Credentials in database