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 !!