Spring Security Login Example with Database

By Arvind Rai, November 26, 2019

On this page, we will learn Spring Security login application with database. We configure <authentication-manager> for datasource and to fetch username and password from database. We need to choose a database and configure the connection parameter for datasource and create tables to store the user related information.

Spring Security Table Schema

Find the table details which will be used to store user roles, username and password.
users

CREATE TABLE `users` (
	`username` VARCHAR(50) NOT NULL,
	`password` VARCHAR(50) NOT NULL,
	`enabled` TINYINT(1) NOT NULL,
	PRIMARY KEY (`username`)
)

users table contains the username and password. The column enabled in user table can have 0 or 1. Here 0 means user is disabled and 1 means user is enabled.
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`)
)

The 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

Find the Spring Security configuration file to authenticate and authorize a user.
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 configure <jdbc-user-service> for select query inside <authentication-provider>. Find its attributes to understand it.
data-source-ref: datasource is assigned to it.
users-by-username-query: SQL query that will select username and password.
authorities-by-username-query: SQL query that will select username and authority