Spring Security Form Login Using Database

Posted by Kanhaiya
In this tutorial, we show you how to use database to perform the form-based login authentication in Spring Security.

Note
The last form-based login example will be reused, but the user details are move from XML file to database.

Technologies used :

• Spring 3.0.5.RELEASE
• Spring Security 3.0.5.RELEASE
• Spring JDBC 3.0.5.RELEASE
• MySQL 5.1

1. Database Tables
In database, you need to create two tables to store user details and user role details, one to many relationship, one user can contains many roles.

A simple and standard table design for user role relationship. And, you are allow to add extra columns for extra functionality. In additional, the table name and column name are not fixed, you can rename to whatever name.

drop table if exists `users`;
CREATE TABLE `users` (
  `USER_ID` INT(10) UNSIGNED NOT NULL,
  `USERNAME` VARCHAR(45) NOT NULL,
  `PASSWORD` VARCHAR(45) NOT NULL,
  `ENABLED` TINYINT(1) NOT NULL,
  PRIMARY KEY (`USER_ID`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

drop table if exists `user_roles`;
CREATE TABLE `user_roles` (
  `USER_ROLE_ID` INT(10) UNSIGNED NOT NULL,
  `USER_ID` INT(10) UNSIGNED NOT NULL,
  `AUTHORITY` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`USER_ROLE_ID`),
  KEY `FK_user_roles` (`USER_ID`),
  CONSTRAINT `FK_user_roles` FOREIGN KEY (`USER_ID`) REFERENCES `users` (`USER_ID`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

Insert data for testing, now an user “kanha” is created and contains role named “ROLE_USER“.

INSERT INTO testdb.users (USER_ID, USERNAME,PASSWORD, ENABLED)
VALUES (100, 'kanha', '123456', TRUE);
 
INSERT INTO testdb.user_roles (USER_ROLE_ID, USER_ID, AUTHORITY)
VALUES (1, 100, 'ROLE_USER');

2. Spring JDBC
Create a data source bean, and connect to database via Spring JDBC.

3. Spring Security
In Spring security configuration file, use “jdbc-user-service” tag, and define your query to get the data from database.

4.Run this application
See screenshots of this tutorial.

1. If username or password is incorrect.


2. If username or password is correct.



Download Source Code
SrcCodes: Spring-Security-Login-db-Example.zip


References:
Spring Security form-based login example (user details in XML file)
Spring Security
Spring Security documentation