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.
user
- to hold the user data.role
- to hold the data of roles that a user can have.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
- Configure Spring Security with Spring boot
- Configure JDBC Authetication using MYSQL Query
- Authenticate User with Custom UserDetailsService
- Implement Role Hierarchy with In-Memory Authentication
- How to list the User Authorities in Controller,Filter and Services
- Disable Session Creation for Stateless Authentication
Download from Github
No comments: