Home  >  Spring Security

Spring Security Login Example with Database

By Arvind Rai, December 19, 2013
In this page, we will learn login application using database in spring security. We can configure authentication-manager to get username and password from database. We will choose a database and configure the connections parameter to get datasource and create some tables to store the user related information.

Spring Security Table Schema

Find the two table details which will be used to store user roles, username and password.
Table Schema: users
CREATE TABLE `users` (
	`username` VARCHAR(50) NOT NULL,
	`password` VARCHAR(50) NOT NULL,
	`enabled` TINYINT(1) NOT NULL,
	PRIMARY KEY (`username`)
) 
User table contains the username and password. The column enabled in user table can have 0 or 1. 0 means user is disabled and 1 means user is enabled.
Table Schema: authorities
CREATE TABLE `authorities` (
	`username` VARCHAR(50) NOT NULL,
	`authority` VARCHAR(50) NOT NULL,
	UNIQUE INDEX `ix_auth_username` (`username`, `authority`),
	CONSTRAINT `fk_authorities_users` FOREIGN KEY (`username`) REFERENCES `users` (`username`)
) 
Authorities table will store username and associated authority. Authority can be like ROLE_USER, ROLE_ADMIN, ROLE_SUPERWISER etc.

Spring Security Database Authentication and Authorization

To authenticate and authorize we need to do below configuration.
security-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<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.1.xsd">
	<http auto-config="true">
		<intercept-url pattern="/login" access="ROLE_USER" />
		<logout logout-success-url="/login" />
	</http>
	<authentication-manager>
          <authentication-provider>
             <jdbc-user-service data-source-ref="dataSource" authorities-by-username-query="SELECT username, authority FROM authorities WHERE username = ?"
                users-by-username-query="SELECT username, password, enabled FROM users WHERE username = ?"/>
          </authentication-provider>
        </authentication-manager>
        <beans:bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
            <beans:property name="driverClassName" value="com.mysql.jdbc.Driver"/>
            <beans:property name="url" value="jdbc:mysql://localhost:3306/test"/>
            <beans:property name="username" value="root"/>
            <beans:property name="password" value=""/>
        </beans:bean>
</beans:beans> 
Create the datasource for any database. And this datasource will be assigned to authentication-manager. We need to configure jdbc-user-service. Find its attributes to understand it.
data-source-ref
datasource is assigned to it.
users-by-username-query
a sql query that will select username and password.
authorities-by-username-query
a sql query that will select username and authority

Output UI

Spring Security Login Example with Database

Download Source Code

POSTED BY
ARVIND RAI
ARVIND RAI
FIND MORE TUTORILAS








Copyright ©2017 concretepage.com, all rights reserved |Privacy Policy | Contact Us