Spring Security: Authentication and Authorization Using Database

Concept Overview

In the previous tutorial we saw an introduction to Spring security and how to achieve spring security using XML configuration. Spring security provides an ability for declarative authentication and authorization. To achieve this it is possible to store the list of users and their roles in the database. The database information can then be wired in the security beans. Although the default implementation expects a particular table structure, it is possible to specify another structure and then wire the authentication query in the spring xml.

Sample Program Overview

We demonstrate Spring security using a database by a simple login example. The user is presented with a login page, once the user enters his username and password, the application queries the database to validate the user and then presents a success or failure page dependending on the result of the validation.

Required Libraries
  • aopalliance-1.0.jar
  • aspectjweaver-1.6.10.jar
  • commons-logging-1.1.1.jar
  • embeddedwebserver.jar
  • hsqldb.jar
  • org.springframework.web.servlet.jar
  • servlet-api-2.5.jar
  • spring-aop-3.0.7.RELEASE.jar
  • spring-asm-3.0.7.RELEASE.jar
  • spring-beans-3.0.7.RELEASE.jar
  • spring-context-3.0.7.RELEASE.jar
  • spring-core-3.0.7.RELEASE.jar
  • spring-expression-3.0.7.RELEASE.jar
  • spring-jdbc-3.0.7.RELEASE.jar
  • spring-security-config-3.1.4.RELEASE.jar
  • spring-security-core-3.1.4.RELEASE.jar
  • spring-security-web-3.1.4.RELEASE.jar
  • spring-tx-3.0.7.RELEASE.jar
  • spring-web-3.0.7.RELEASE.jar

Code Package Structure

Interaction Flow

  1. User accesses a URL on a web application
  2. The web application refers to web.xml
  3. The web.xml matches the URL pattern
  4. The control is redirected to DispatcherServlet in Spring framework
  5. Spring framework finds that the all URLs are secured and hence displays login page to the user
  6. The user enters his login name and password
  7. Spring configuration file knows that it has authenticate against the database and hence it accesses the database
  8. Spring performs the authentication and authorization against the database and if the credentials are successful then it redirects to the original accessed URL

Source Code

Create the DbUtil class (see sample code below).

Note:This class is necessary only to create the necessary tables for authentication and authorization and seed the necessary data.

Create the necessary table for user authentication (see lines 27 below).

The table
is used for authentication and contains the following columns:

Column Name Purpose
USER_ID Primary Key
USERNAME The user name used to login
PASSWORD The password to be used
ENABLED Specify whether the user’s account is active or not

Create a username ‘alpha’ and password ‘pass1’ (see line 28 below).

Also, create the necessary table for authorization (see line 31 below).

The table
is used for authorization and contains the following columns:

Column Name Purpose
USER_ROLE_ID Primary Key
USER_ID The id of user in USER_AUTHENTICATION table. Foreign Key.
ROLE The role for the user

Create an admin role and associate it with the user named ‘alpha’ (see line 32 below)

Create the main_page.jsp as shown below. This page is accessed by the end user and is displayed after successful login.

Note that this is the only JSP page required for this sample. No explicit coding is required to display Spring’s in-built login page.

Create the web.xml file as shown below.

Register Spring’s DispatcherServlet used to register handlers for processing the web request (see lines 29-38 below).

Define filter-mapping and filter for DelegatingFilterProxy (see lines 15-23 below). This filter shall delegate the call to a class that implements
and is registered as Spring bean.

Note: In this example we do not have to specifically create a class that implements
. This is automatically available to us when we configure our Spring configuration file using
in springsecurity-servlet.xml file
described later

Also configure that ContextLoaderListener (see lines 25-27 below).

Finally provide the location of Spring’s configuration file in web.xml (see lines 10-13 below).

Create the Spring configuration file as shown below.

Create a data source to connect to the in-memory hsqldb database (see lines 11-17 below).

Initialize the database by using DbUtil class
(described earlier)
(see lines 1-22 below).

Configure Spring security using
tag (see lines 24-26 below).

Specify that all URLs should be intercepted by Spring security (see
attribute in line 25 below).

Also specify that access should be restricted only to those users who have the role
attribute on line 25 below).

Specify the security related settings (see lines 28-35 below).

Specify the data source to be used by Spring Security framework for looking up to the database for authentication and authorization (see line 31 below).

Construct the query to be executed by Spring security for user authentication (see line 32 below). Note that this was the same table created in DbUtil.java
(described earlier)

Construct the query to be executed by Spring security for user authorization (see line 33 below). Note that this was the same table created in DbUtil.java
(described earlier)

This demonstrates the usage of specifying authentication and authorization information in Spring XML file.

Running Sample Program

This sample program has been packaged as a jar installer which will copy the source code (along with all necessary dependencies)on your machine and automatically run the program for you as shown in the steps below. As this sample program contains Java Server Pages (JSPs), you will need Java Development Kit (JDK preferably 1.5 or higher) on your machine so that the JSPs can be complied locally. Note that no other setup is required on your machine! Also please ensure that the port 8080 is not being used by any other program on your machine.

Download And Automatically Run Sample Program
  • Save the springsecurityusingdb-installer.jar on your machine
  • Execute/Run the jar using Java Runtime Environment

  • (Alternatively you can go the folder containing the springsecurityusingdb-installer.jar and execute the jar using
    java -jar springsecurityusingdb-installer.jar

  • You will see a wizard as shown below. Enter the location of Java Development Kit (JDK) and Click ‘Next’ button.
  • You will see a wizard page as shown below
  • Enter the location of the directory where you want the program to install and run (say, C:\Temp)
  • The installer will copy the program on your machine and automatically start the inbuilt webserver on your machine.
  • Go to the URL http://localhost:8080/springsecurityusingdb/jsp/main_page.jsp. A login page will be displayed as shown below
  • Enter some invalid credentials (e.g. user as ‘beta’ and password as ‘abcd’) as shown below
  • Click the Login button. The login page will be displayed with a ‘bad credentials’ message as shown below
  • Now enter some the credentials (e.g. user as ‘alpha’ and password as ‘pass1’) as shown below
  • Click the Login button. The user will be authenticated successfully and the main page will successfully displayed to him based on his authorizations as shown below
  • This demonstrates the successful execution of the sample program on your machine
  • Browsing the Program

    This source code for this program is downloaded in the folder specified by you (say, C:\Temp) as an eclipse project called
    . All the required libraries have also been downloaded and placed in the same location. You can open this project from Eclipe IDE and directly browse the source code. See below for details of the project structure.

    Redeploying this sample program in a different web server

    The WAR file for this example is available as springsecurityusingxml.war in the download folder specified by you earlier (e.g. C:\Temp). The path for the WAR file is <DOWNLOAD_FOLDER_PATH>/springsecurityusingdb/dist/springsecurityusingdb.war.

    This WAR file can be deployed in any webserver of your choice and example can be executed.

    5 thoughts on “Spring Security: Authentication and Authorization Using Database

    • November 15, 2016 at 4:13 am

      I inspired a lot by the way you present the concept.It would be better if you explain the configuration file.Because i am not able to see configuration xml file here.

    • November 16, 2016 at 10:17 am

      Very good. I will want see all configuration in java code, but not xml file.

    • November 27, 2016 at 7:12 am

      it is very easy to understand from your presentation. thank you

    • March 8, 2017 at 8:24 am

      how can i download It ?

    • January 25, 2018 at 11:31 am

      How to download springsecurityusingdb-installer.jar ?


    Leave a Reply

    Your email address will not be published. Required fields are marked *

    This site uses Akismet to reduce spam. Learn how your comment data is processed.