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.

· Database Zone ·
Free Resource

RavenDB vs MongoDB: Which is Better? This White Paper compares the two leading NoSQL Document Databases on 9 features to find out which is the best solution for your next project.  

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.

Get comfortable using NoSQL in a free, self-directed learning course provided by RavenDB. Learn to create fully-functional real-world programs on NoSQL Databases. Register today.

Topics:
spring ,security ,spring boot ,spring security

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}