Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Spring Boot With Spring Security and JDBC (Part 2)

DZone's Guide to

Spring Boot With Spring Security and JDBC (Part 2)

The second in this in depth series on getting familiar with two key Spring frameworks and their interaction with databases.

Free Resource

Read why times series is the fastest growing database category.

In a previous post, we implemented security based on the default table schemas that Spring Security issues requests.

Considering users and roles, application developers use a schema that fits their needs. Spring gives us the ability to specify the queries needed in order to retrieve information such as username, password and roles.

Our custom tables will be pretty different from the tables of the first example.

drop table if exists Custom_Users;
create table Custom_Users(id bigint auto_increment, username varchar(255), password varchar(255));
insert into Custom_Users(username,password) values('TestUser','TestPass');

drop table if exists Custom_Roles;
create table Custom_Roles(username varchar(255),authority  varchar(255), UNIQUE(username,authority));
insert into Custom_Roles(username,authority) values('TestUser','superadmin');

In order to use these tables with Spring Security, we must pass the queries that Spring Security will use in order to retrieve the security information needed.

To do so, we will create a security configuration that will set up the queries needed.

package com.gkatzioura.spring.security.config;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Profile;
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;


/**
 * Created by gkatzioura on 9/20/16.
 */
@EnableWebSecurity
@Profile("customquery")
public class CustomQuerySecurityConfig extends WebSecurityConfigurerAdapter {

    @Autowired
    private DataSource dataSource;

    @Autowired
    public void configAuthentication(AuthenticationManagerBuilder auth) throws Exception {
        auth.jdbcAuthentication().dataSource(dataSource)
                .usersByUsernameQuery("SELECT username,password,1 FROM Custom_Users where username=?")
                .authoritiesByUsernameQuery("SELECT username,authority FROM Custom_Roles where username=?");
    }

    @Override
    protected void configure(HttpSecurity http) throws Exception {

        http.authorizeRequests()
                .antMatchers("/public").permitAll()
                .anyRequest().authenticated()
                .and()
                .formLogin()
                .permitAll()
                .and()
                .logout()
                .permitAll();
    }

}

We use Spring profiles. Our spring profile would be “customquery,” therefore, the CustomQuerySecurityConfig would be bound to the “customquery” profile.

In order to run, for convenience, we have to change the default profile in our build.gradle file.

group 'com.gkatzioura'
version '1.0-SNAPSHOT'

buildscript {
    repositories {
        mavenCentral()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:1.4.0.RELEASE")
    }
}

apply plugin: 'java'
apply plugin: 'idea'
apply plugin: 'spring-boot'

sourceCompatibility = 1.8

repositories {
    mavenCentral()
}

dependencies {
    compile("org.springframework.boot:spring-boot-starter-web")
    compile("org.thymeleaf:thymeleaf-spring4")
    compile("org.springframework.boot:spring-boot-starter-security")
    compile("org.springframework:spring-jdbc")
    compile("com.h2database:h2:1.4.192")
    compile("org.slf4j:slf4j-api:1.6.6")
    compile("ch.qos.logback:logback-core:1.1.7")
    compile("ch.qos.logback:logback-classic:1.1.7")
    testCompile "junit:junit:4.11"
}

bootRun {
    systemProperty "spring.profiles.active", "customquery"
}

To run the application issue:

gradle bootRun

You can find the source code on GitHub.

Learn how to get 20x more performance than Elastic by moving to a Time Series database.

Topics:
spring ,security ,spring boot ,spring security

Published at DZone with permission of Emmanouil Gkatziouras, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}