Simple web application with Spring Security: Part 10

In this part we add a database backend and add users and authorities to the database instead of using the user-service configuration element of spring security.

First we must pick a database: for now we will use a simple lightweight HSQLDB. We need to add dependencies into our lib folder:

  1. download latest hsqldb.jar (hsqldb-1.8.0.7.jar) and put into lib folder

Next we need to create a dataAccessContext.xml file that will be responsible for defining beans used for accessing the database.

dataAccessContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="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-2.5.xsd">

	<!-- business stuff below -->
	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="org.hsqldb.jdbcDriver" />
		<property name="url" value="jdbc:hsqldb:mem:test" />
		<property name="username" value="sa" />
		<property name="password" value="" />
	</bean>

	<bean id="dataSourcePopulator" class="com.heraclitus.service.HsqldbSchemaAndDataPopulator">
		<property name="dataSource" ref="dataSource" />
	</bean>
</beans>

Things to note:

  1. Created bean datasource that is configured to connect to in-memory hsqldb database
  2. Created bean dataSourcePopulator that is responsible for creating the schema and inserting data into the in-memory database on initialization of the bean.

The dataSourcePopulator file: HsqldbSchemaAndDataPopulator.java

package com.heraclitus.service;

import javax.sql.DataSource;

import org.springframework.beans.factory.InitializingBean;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.Assert;

/**
 * I am responsible for populating the configured datasource
 */
public class HsqldbSchemaAndDataPopulator implements InitializingBean {

    private JdbcTemplate template;

    /**
     *
     */
    public void afterPropertiesSet() throws Exception {
        Assert.notNull(template, "dataSource required");

        // add tables to represent admin core-domain instances.
        template
                .execute("CREATE TABLE USERS(USERNAME VARCHAR_IGNORECASE(50) NOT NULL PRIMARY KEY,"
                        + "PASSWORD VARCHAR_IGNORECASE(50) NOT NULL,"
                        + "ENABLED BOOLEAN NOT NULL);");
        template
                .execute("CREATE TABLE AUTHORITIES(USERNAME VARCHAR_IGNORECASE(50) NOT NULL,AUTHORITY VARCHAR_IGNORECASE(50) NOT NULL,CONSTRAINT FK_AUTHORITIES_USERS FOREIGN KEY(USERNAME) REFERENCES USERS(USERNAME));");
        template
                .execute("CREATE UNIQUE INDEX IX_AUTH_USERNAME ON AUTHORITIES(USERNAME,AUTHORITY);");

        // add tables to represent bug tracking domain instances.
        // TODO - add project start and end date
        template
                .execute("CREATE TABLE PROJECTS(ID BIGINT NOT NULL PRIMARY KEY, NAME VARCHAR_IGNORECASE(50) NOT NULL, DESCRIPTION VARCHAR_IGNORECASE(200) NOT NULL);");

        // insert data here
        template
                .execute("INSERT INTO USERS VALUES('disabled','disabled',FALSE);");
        template.execute("INSERT INTO USERS VALUES('admin','admin',TRUE);");
        template
                .execute("INSERT INTO USERS VALUES('username','password',TRUE);");
        template.execute("INSERT INTO USERS VALUES('test','test',TRUE);");

        template
                .execute("INSERT INTO AUTHORITIES VALUES('admin','ROLE_USER');");
        template
                .execute("INSERT INTO AUTHORITIES VALUES('admin','ROLE_ADMIN');");

        template
                .execute("INSERT INTO AUTHORITIES VALUES('username','ROLE_USER');");

        template.execute("INSERT INTO AUTHORITIES VALUES('test','ROLE_USER');");

        template
                .execute("INSERT INTO projects VALUES (1, 'Test Project', 'A description not longer than 200 chars of what project is.');");
        template
                .execute("INSERT INTO projects VALUES (2, 'Test Project 2', 'Smaller description of project here.');");
    }

    public void setDataSource(final DataSource dataSource) {
        this.template = new JdbcTemplate(dataSource);
    }
}

Next we must update our web.xml file to load the dataAccessContext.xml file when the web application context loads up.

web.xml

<context-param>
	<param-name>contextConfigLocation</param-name>
	<param-value>
	    WEB-INF\dataAccessContext.xml
            WEB-INF\applicationContext-security.xml
        </param-value>
</context-param>

Next update your applicationContext-security.xml file

<authentication-provider>
        <jdbc-user-service id="userDetailsService" data-source-ref="dataSource" />
</authentication-provider>

Things to note:

  1. This configures spring security to use its JdbcDaoImpl class against the given dataSource (hsqldb)
  2. Thus it expects to be able to load user and authority details from two tables on the database named USERS and AUTHORITIES (the code that creates the schema and populates it is in HsqldbSchemaAndDataPopulator.java

That should be enough to convert your application to have a backend and to allow successful logins as before, to test, build, deploy and run all your acceptance tests on it.

Getting the code

The code for this part is tagged and available for viewing online at: http://code.google.com/p/spring-security-series/source/browse/#svn/tags/SpringSecuritySeriesWAR-Part10

SVN Url: https://spring-security-series.googlecode.com/svn/tags/SpringSecuritySeriesWAR-Part10

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: