Spring Security with MySQL tables

User authentication and authorization in Spring Security using MySQL tables

It is always being desired to protect web application on the basis of the user details stored in some database Spring Security also provides facility for authenticating an user on the basis of database tables defined by Spring Security Framework i.e. the tables defined in the previous tutorial or on the basis of custom database tables.

Tools used :

• Spring MVC 3.0.3
• Spring JDBC 3.0.5
• Spring Security 3.0.5
• Eclipse Indigo 3.7
• Tomcat 6
• Jdk 1.6

In this tutorial, we will use database tables defined by Spring Security Framework for authentication and authorization of users. We will modify our Customizing Spring Security web MVC Application example to implement database based user authentication on the basis of table definitions defined in Spring Security.

Creating necessary tables in database

We will use MySQL database server for authenticating users. First of all, we have to create a schema in MySQL database. For user authentication and authorization we will create two tables as specified in the previous tutorial MySQL ddl tables definitions for Spring Security. As we will not use group level authorization in this example, so we will have to create only two tables “USERS” ans “AUTHORITIES” tables.


Run the following DDL  commands in MySQL command prompt:

create table users (
    username varchar(50) not null primary key,
    password varchar(50) not null,
    enabled boolean not null
) engine = InnoDb;

create table authorities (
    username varchar(50) not null,
    authority varchar(50) not null,
    foreign key (username) references users (username),
    unique index authorities_idx_1 (username, authority)
) engine = InnoDb;

 After that we have to create a user for login. Following DML insert commands will create a user with username “admin” and password “admin”:

 INSERT INTO users(`username`,`password`,`enabled`) VALUES( 'admin','21232f297a57a5a743894a0e4a801fc3',1);
INSERT INTO authorities(`username`,`authority`) VALUES('admin','ROLE_ADMIN');

Creating DataSource bean in Spring MVC

Our next work is to configure a datasource for the MySQL database. For this, we have to change our spring-servlet.xml file to configure datasource.

We will create a properties file (jdbc.properties) that will contain all database connection related information:


# database properties
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost/javapapers_demo
jdbc.username=root
jdbc.password=pass

You have to modify jdbc.properties according to your database information. We have to put following changes to our spring-servlet.xml:


<context:property-placeholder location="/WEB-INF/jdbc.properties" />

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">>
    <property name="driverClassName" value="${jdbc.driverClassName}" />
    <property name="url" value="${jdbc.url}" />
    <property name="username" value="${jdbc.username}" />
    <property name="password" value="${jdbc.password}" />
</bean>


<context:property-placeholder/> tag will help to import the properties defined in the jdbc.properties file. After that we have configured
org.springframework.jdbc.datasource.DriverManagerDataSource class provided by the spring jdbc to configure as dataSource. ${jdbc.driverClassName} is used to replace the property value from jdbc.properties file using Spring EL.


Changing Spring Security settings
After that, we will change Spring Security Configuration file to use database tables for user authentication:



<?xml version="1.0" encoding="UTF-8"?>
<b:beans xmlns="http://www.springframework.org/schema/security"
xmlns:b="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.xsd">

<http realm="Project Realm" auto-config="true">
    <intercept-url pattern="/auth/**" access="IS_AUTHENTICATED_ANONYMOUSLY"/>
    <intercept-url pattern="/**" access="ROLE_ADMIN"/>
    <form-login login-page="/auth/login.jsp" authentication-failure-url="/auth/login.jsp?login_error=1"/>
    <logout logout-success-url="/auth/login.jsp"/>
    <remember-me />
</http>

    <authentication-manager>
        <authentication-provider>
            <password-encoder hash="md5"/>
            <jdbc-user-service data-source-ref="dataSource"/>
        </authentication-provider>
    </authentication-manager>
</b:beans>


There is only one change in our security.xml file. Instead of using <user/> tag, we have used <jdbc-user-service/> tag and provided the reference of our configured datasource to its data-source-ref property.


That is all, now our Spring Security example will use database for user authentication.

Download Source Code with jars

Click here to download source code