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 :

  1. Spring 3.0.5.RELEASE
  2. Spring Security 3.0.5.RELEASE
  3. Spring JDBC 3.0.5.RELEASE
  4. MySQL 5.1

P.S We are using MySQL as database.

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.

P.S All scripts in MySQL.

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;
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 “mkyong” is created and contains role named “ROLE_USER“.

INSERT INTO mkyongdb.users (USER_ID, USERNAME,PASSWORD, ENABLED)
VALUES (100, 'mkyong', '123456', TRUE);
 
INSERT INTO mkyongdb.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.

<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">
 
   <bean id="dataSource"
	class="org.springframework.jdbc.datasource.DriverManagerDataSource">
 
	<property name="driverClassName" value="com.mysql.jdbc.Driver" />
	<property name="url" value="jdbc:mysql://localhost:3306/mkyongdb" />
	<property name="username" value="root" />
	<property name="password" value="password" />
   </bean>
 
</beans>

3. Spring Security

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

<beans:beans xmlns="http://www.springframework.org/schema/security"
	xmlns:beans="http://www.springframework.org/schema/beans" 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
	http://www.springframework.org/schema/security
	http://www.springframework.org/schema/security/spring-security-3.0.3.xsd">
 
	<http auto-config="true">
		<intercept-url pattern="/welcome*" access="ROLE_USER" />
		<form-login login-page="/login" default-target-url="/welcome"
			authentication-failure-url="/loginfailed" />
		<logout logout-success-url="/logout" />
	</http>
 
	<authentication-manager>
	   <authentication-provider>
		<jdbc-user-service data-source-ref="dataSource"
 
		   users-by-username-query="
		      select username,password, enabled 
		      from users where username=?" 
 
		   authorities-by-username-query="
		      select u.username, ur.authority from users u, user_roles ur 
		      where u.user_id = ur.user_id and u.username =?  " 
 
		/>
	   </authentication-provider>
	</authentication-manager>
 
</beans:beans>

4. Demo

See screenshots of this tutorial.

1. If username or password is incorrect.

login failed

2. If username or password is correct.

login-success

Download Source Code

Download it – Spring-Security-Login-Database-Example.zip (10 KB)

References

  1. Spring Security form-based login example (user details in XML file)
  2. Spring JDBC example
Tags :
Founder of Mkyong.com, love Java and open source stuffs. Follow him on Twitter, or befriend him on Facebook or Google Plus.
Here are some of my recommended Books

Related Posts

Popular Posts