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.


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;

Step 4 : Add queries to be fired for user authentication

  1. @Override  
  2. protected void configure(AuthenticationManagerBuilder auth) throws Exception {  
  3.     auth.jdbcAuthentication()  
  4.             .usersByUsernameQuery("select username,password,enabled from user where username=?")  
  5.             .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=?")  
  6.             .dataSource(dataSource);  
  7.     auth.userDetailsService(userDetailsService());  
  8. }  


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

  1. package com.ekiras.ss.config;  
  2.   
  3. import org.springframework.beans.factory.annotation.Autowired;  
  4. import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder;  
  5. import org.springframework.security.config.annotation.web.builders.HttpSecurity;  
  6. import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;  
  7. import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;  
  8.   
  9. import javax.sql.DataSource;  
  10.   
  11. /** 
  12.  * @author ekansh 
  13.  * @since 30/3/16 
  14.  */  
  15. @EnableWebSecurity  
  16. public class SpringSecurityConfigurer extends WebSecurityConfigurerAdapter{  
  17.   
  18.     @Autowired  
  19.     private DataSource dataSource;  
  20.   
  21.     @Override  
  22.     protected void configure(AuthenticationManagerBuilder auth) throws Exception {  
  23.         auth.jdbcAuthentication()  
  24.                 .usersByUsernameQuery("select username,password,enabled from user where username=?")  
  25.                 .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=?")  
  26.                 .dataSource(dataSource);  
  27.         auth.userDetailsService(userDetailsService());  
  28.     }  
  29.   
  30.   
  31.     @Override  
  32.     protected void configure(HttpSecurity http) throws Exception {  
  33.         http  
  34.             .authorizeRequests()  
  35.                 .antMatchers("/admin/**").hasAuthority("ADMIN")  
  36.                 .antMatchers("/user/**").hasAuthority("USER")  
  37.                 .anyRequest().fullyAuthenticated()  
  38.                 .and()  
  39.             .formLogin()  
  40.             .and()  
  41.             .logout()  
  42.         ;  
  43.     }  
  44. }  


No comments :

Post a Comment