Skip to main content

SpringSecurity : Configure JDBC Authetication using MYSQL Query

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
Labels:

Comments

Post a Comment