Create Database Schema and tables

First we will create a Database Schema as shown in the image below.

Sql schema

We have to create 3 Tables in database.

  1. user - to hold the user data.
  2. role - to hold the data of roles that a user can have.
  3. user_roles - to hold the mapping of user and roles.

Configure JDBC Authetication using MYSQL Query.

Step 1 : Add the Dependencies

 compile('org.springframework.boot:spring-boot-starter-data-jpa')
runtime('mysql:mysql-connector-java')

Step 2 : Add the Datasource properties

spring.jpa.hibernate.ddl-auto=update

spring.datasource.url=jdbc:mysql://localhost/demo_ss
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.maxActive=10
spring.datasource.max-idle=4
spring.datasource.min-idle=2
spring.datasource.test-while-idle=true
spring.datasource.test-on-borrow=true
spring.datasource.validation-query=SELECT 1
spring.datasource.time-between-eviction-runs-millis=60000
spring.datasource.min-evictable-idle-time-millis=300000
spring.jpa.properties.hibernate.current_session_context_class=org.springframework.orm.hibernate4.SpringSessionContext

Step 3 : Add the Datasource Bean to SpringSecurity Configurer class

    @Autowired
private DataSource dataSource;

Add queries to be fired for user authentication

    @Override
protected void configure(AuthenticationManagerBuilder auth) throws Exception {
auth.jdbcAuthentication()
.usersByUsernameQuery("select username,password,enabled from user where username=?")
.authoritiesByUsernameQuery("select u.username,r.role from user u inner join user_roles ur on(u.id=ur.user_id) inner join role r on(ur.role_id=r.id) where u.username=?")
.dataSource(dataSource);
auth.userDetailsService(userDetailsService());
}

Following code will demonstrate how to configure spring security to authenticate the user using a simple mysql query.

package com.ekiras.ss.config;

import org.springframework.beans.factory.annotation.Autowired;
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 javax.sql.DataSource;

/**
* @author ekiras
*/

@EnableWebSecurity
public class SpringSecurityConfigurer extends WebSecurityConfigurerAdapter{

@Autowired
private DataSource dataSource;

@Override
protected void configure(AuthenticationManagerBuilder auth) throws Exception {
auth.jdbcAuthentication()
.usersByUsernameQuery("select username,password,enabled from user where username=?")
.authoritiesByUsernameQuery("select u.username,r.role from user u inner join user_roles ur on(u.id=ur.user_id) inner join role r on(ur.role_id=r.id) where u.username=?")
.dataSource(dataSource);
auth.userDetailsService(userDetailsService());
}


@Override
protected void configure(HttpSecurity http) throws Exception {
http
.authorizeRequests()
.antMatchers("/admin/**").hasAuthority("ADMIN")
.antMatchers("/user/**").hasAuthority("USER")
.anyRequest().fullyAuthenticated()
.and()
.formLogin()
.and()
.logout()
;
}
}

Also Read


    Download from Github