Spring Security Login Example with Database
November 26, 2019
<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`) )
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>
<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
Output
