Spring Boot Security - Database Authentication Example

In a previous post we had implemented Spring Boot Security - Creating a custom login page.
Till now we were making use of in memory configuration for authenticating users and associated roles. In this example we will authenticate users and roles against database tables.

Spring Boot Security - Table Of Contents
Spring Boot + Simple Security Configuration
Spring Boot Form Security Login Hello World Example
Spring Boot Security - Custom Login Page Example
Spring Boot Security - JDBC Authentication Example
Spring Boot Security - Creating Users Programmatically Using JdbcUserDetailsManager
Spring Boot Security - Password Encoding Using Bcrypt
Spring Boot Security - Enabling CSRF Protection
Spring Boot Security - Authentication Handler Example
Spring Boot Security - Introduction to OAuth
Spring Boot OAuth2 Part 1 - Getting The Authorization Code
Spring Boot OAuth2 Part 2 - Getting The Access Token And Using it to Fetch Data.

Lets Begin-

We will be modifying the code we developed in the previous Spring Boot Security - Creating a custom login page
Maven Project will be as follows-


By default spring security expects tables named users table for storing username, passwords and authorities table for storing the associated roles. In the schema-mysql.sql add these schemas and insert statements

DROP TABLE IF EXISTS employee;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS authorities;


CREATE TABLE employee (
  empId VARCHAR(10) NOT NULL,
  empName VARCHAR(100) NOT NULL
);

create table users (
    username varchar(50) not null primary key,
    password varchar(120) not null,
    enabled boolean not null
);

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

insert into users(username, password, enabled)values('javainuse','javainuse',true);
insert into authorities(username,authority)values('javainuse','ROLE_ADMIN');
 
insert into users(username, password, enabled)values('employee','employee',true);
insert into authorities(username,authority)values('javainuse','ROLE_USER');

Spring Boot JDBC runs this script before starting the application Finally modify the Spring Security configuration to switch to jdbc authentication.

package com.javainuse.config;

import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
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.builders.WebSecurity;
import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;

@Configuration
@EnableWebSecurity
public class EmployeeSecurityConfiguration extends WebSecurityConfigurerAdapter {

	@Autowired
    DataSource dataSource;

	//Enable jdbc authentication
    @Autowired
    public void configAuthentication(AuthenticationManagerBuilder auth) throws Exception {
        auth.jdbcAuthentication().dataSource(dataSource);
    }

	@Override
	public void configure(WebSecurity web) throws Exception {
		web.ignoring().antMatchers("/resources/**");
	}

	@Override
	protected void configure(HttpSecurity http) throws Exception {
		http.authorizeRequests().antMatchers("/").permitAll().antMatchers("/welcome").hasAnyRole("USER", "ADMIN")
				.antMatchers("/getEmployees").hasAnyRole("USER", "ADMIN").antMatchers("/addNewEmployee")
				.hasAnyRole("ADMIN").anyRequest().authenticated().and().formLogin().loginPage("/login").permitAll()
				.and().logout().permitAll();

		http.csrf().disable();
	}

    //remove this in memory authentication configuration
	// @Autowired
	//public void configureGlobal(AuthenticationManagerBuilder authenticationMgr) throws Exception {
	//	authenticationMgr.inMemoryAuthentication().withUser("admin").password("admin").authorities("ROLE_USER").and()
	//			.withUser("javainuse").password("javainuse").authorities("ROLE_USER", "ROLE_ADMIN");
	//} 

}

These are the only changes required.

• Go to localhost:8080/welcome, we will be redirected to the custom login page.
  
  
• Enter the user javainuse and password javainuse
  
  So our application is working good and getting correctly authenticated using database tables.

  Download Source Code

  Download it -
  Spring Boot Security - Database Authentication