Enterprise Java

Spring boot with Spring Security and jdbc Part 2

On 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.
public class CustomQuerySecurityConfig extends WebSecurityConfigurerAdapter {

    private DataSource dataSource;

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

    protected void configure(HttpSecurity http) throws Exception {



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 reasons we have to change the default profile in our build.gradle file.

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

buildscript {
    repositories {
    dependencies {

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

sourceCompatibility = 1.8

repositories {

dependencies {
    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

Reference: Spring boot with Spring Security and jdbc Part 2 from our JCG partner Emmanouil Gkatziouras at the gkatzioura blog.

Emmanouil Gkatziouras

He is a versatile software engineer with experience in a wide variety of applications/services.He is enthusiastic about new projects, embracing new technologies, and getting to know people in the field of software.
Notify of

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Inline Feedbacks
View all comments
Back to top button