HowToDoInJava

  • Python
  • Java
  • Spring Boot
  • Dark Mode
Home / Spring Security / Spring Security – JDBC User Service Example

Spring Security – JDBC User Service Example

In previous post related to spring 3 security demo application using default user service in configuration file, we learned about securing an application behind a login page. In that post, username and passwords were stored in application-security.xml file itself. Now its time to take these authentication parameters out of configuration and store them in database.

There are two ways of doing this i.e. using custom user service implementation or spring provided jdbc user service. In this post, I am showing the way to use second approach i.e. jdbc user service.

Sections in this post:

Create tables in database
Update security configuration to use jdbc user service
Test the application

Background information

Apart from other inbuilt user details services inside spring container, JDBC user detail implementation is one of it. It is configured using tag “jdbc-user-service“.

Just like other spring features, this also comes with default implementation ready to use e.g.

	<authentication-manager alias="authenticationManager">
		<authentication-provider>
			<jdbc-user-service data-source-ref="dataSource" />
		</authentication-provider>
	</authentication-manager>

Above been definition will use the default tables in configured database. It assumes to be:

 create table users(
      username varchar_ignorecase(50) not null primary key,
      password varchar_ignorecase(50) not null,
      enabled boolean not null);

  create table authorities (
      username varchar_ignorecase(50) not null,
      authority varchar_ignorecase(50) not null,
      constraint fk_authorities_users foreign key(username) references users(username));
      create unique index ix_auth_username on authorities (username,authority);

BUT, in my example, I have created two tables according to my choice and have decided to feed data to jdbc user service using sql queries.

Create tables in database

So lets create our own tables in database:

-- ----------------------------
-- Table structure for `tbl_users`
-- ----------------------------
DROP TABLE IF EXISTS `tbl_users`;
CREATE TABLE `tbl_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `password` varchar(20) NOT NULL,
  `enabled` int(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Table for storing the role and username mapping.

-- ----------------------------
-- Table structure for `tbl_user_role`
-- ----------------------------
DROP TABLE IF EXISTS `tbl_user_role`;
CREATE TABLE `tbl_user_role` (
  `userid` int(11) NOT NULL,
  `rolename` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

These tables have all required information like username, password, enabled status and assigned role. JDBC user service require these values only for a username provided as parameter.These values will be fetched with following sql queries:

SELECT USERNAME, PASSWORD, CASE ENABLED WHEN 1 THEN 'true' ELSE 'false' END 'ENABLED' FROM TBL_USERS WHERE USERNAME=?;

+----------+----------+---------+
| USERNAME | PASSWORD | ENABLED |
+----------+----------+---------+
| lokesh   | password | true    |
+----------+----------+---------+

Query to select role name.

SELECT u.USERNAME, r.ROLENAME
FROM TBL_USERS u, TBL_USER_ROLE r
WHERE u.ID = r.USERID
AND u.USERNAME=?;

+----------+-----------+
| USERNAME | ROLENAME  |
+----------+-----------+
| lokesh   | ROLE_USER |
+----------+-----------+

Update security configuration to use jdbc user service

This can be done as follows:

	<authentication-manager alias="authenticationManager">
		<authentication-provider>
			<jdbc-user-service data-source-ref="dataSource"
 
		   users-by-username-query="
		     SELECT USERNAME, PASSWORD, CASE ENABLED WHEN 1 THEN 'true' ELSE 'false' END 'ENABLED' 
		     FROM TBL_USERS 
		     WHERE USERNAME=?;"
 
		   authorities-by-username-query="
		    SELECT u.USERNAME, r.ROLENAME 
			FROM TBL_USERS u, TBL_USER_ROLE r
			WHERE u.ID = r.USERID
			AND u.USERNAME=?;"
 
			/>
		</authentication-provider>
	</authentication-manager>

JDBC user service implementation provide these two attributes to match username with password and then username with granted role or authority.

  1. users-by-username-query
  2. authorities-by-username-query

Test the application

Now build and run above application again in application server. It will behave exactly the way authentication/ authorization worked for xml configuration in previous tutorial.

Download sourcecode

Happy Learning !!

Share this:

  • Twitter
  • Facebook
  • LinkedIn
  • Reddit

About Lokesh Gupta

A family guy with fun loving nature. Love computers, programming and solving everyday problems. Find me on Facebook and Twitter.

Feedback, Discussion and Comments

  1. Akshay

    August 5, 2019

    How do we get access to dataSource?

  2. dty

    April 4, 2019

    please add

     
    <http>
      <!-- ... -->
      <csrf disabled="true"/>
    </http>
    
  3. ayman

    January 7, 2018

    how it authenticate or varify the password?

  4. Syam

    November 4, 2016

    Here How to get the USERNAME value if user failed to login?

  5. Tushar Goel

    July 17, 2016

    How to handle a case when user assigned multiple roles?

  6. eriks

    November 24, 2015

      It's works!! thanks dear, for saving my day! 
  7. Kartik

    July 31, 2015

    How do I get user ID ?

    • Lokesh Gupta

      August 1, 2015

      You can execute a sql query separately using username/email in where clause. For auth purpose, you don’t need user id. right?

  8. Partha

    September 4, 2014

    what is the use of enabled

    • Lokesh Gupta

      September 4, 2014

      Whether user is active or not.

  9. ML

    August 18, 2014

    Excellent example, thank you. How would one do this with encryption on the password field for MSSQL server?

  10. Mike

    December 3, 2013

    How about implementing Remember me with persistent tokens (persistent_logins table in database)?
    Thanks

    • Lokesh Gupta

      December 3, 2013

      I will try

  11. Anonymous

    May 15, 2013

    Good, thanks for the example

Comments are closed on this article!

Search Tutorials

Spring Security Tutorial

  • Security – Introduction
  • Security – Method Level Security
  • Security – Siteminder
  • Security – Login Form
  • Security – JSP taglibs
  • Security – jdbc-user-service
  • Security – UserDetailsService
  • Security – Basic Auth
  • Security – Junit Tests
  • @PreAuthorize and @Secured

Meta Links

  • About Me
  • Contact Us
  • Privacy policy
  • Advertise
  • Guest and Sponsored Posts

Recommended Reading

  • 10 Life Lessons
  • Secure Hash Algorithms
  • How Web Servers work?
  • How Java I/O Works Internally?
  • Best Way to Learn Java
  • Java Best Practices Guide
  • Microservices Tutorial
  • REST API Tutorial
  • How to Start New Blog

Copyright © 2020 · HowToDoInjava.com · All Rights Reserved. | Sitemap

  • Java 15 New Features
  • Sealed Classes and Interfaces
  • EdDSA (Ed25519 / Ed448)