User authentication and authorization in Spring Security using MySQL Database

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 Configuring Spring Security in Spring 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 app-config.xml file to configure datasource.

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

# database properties

You have to modify according to your database information. We have to put following changes to our app-config.xml:

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

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

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

            <password-encoder hash="md5"/>
            <jdbc-user-service data-source-ref="dataSource"/>

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.

Deploy the war file in tomcat 6 and hit the url in browser, you will get following login page:

Custom Login Form in Spring Security

Custom Login Form in Spring Security

If you enter any wrong username and password, it will show following screen:

Custom Login Form Errors in Spring Security

Custom Login Form Errors in Spring Security

And if you enter “admin” as username and password, it will serve you the welcome page:

Spring Security Secure Page with Logout option

Spring Security Secure Page with Logout option


You can download source code of this tutorial from following links:

Source: Download

Related Posts:

Leave a comment ?


  1. Nice article and very informative as well.

  2. Thanks for the article. Do you happen to have a tutorial where user groups are being used?

Leave a Comment Cancel reply

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>