Spring Security using Database Authentication

Tools and Technologies used
1)Eclipse IDE Mars Release (4.5.0)
2)Java 8
3)Spring framework 4.2.0
4)Spring security 3.2
5)Tomcat 8

Follow steps from the Spring MVC project link to setup a spring maven hello world project.

Create the spring controller class

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
package com.kb.controllers;
 
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
 
@Controller
public class SpringSecurityHelloWorldControllerDatabase {
      @RequestMapping("/public/pages")
    public String helloWorld(Model model) {
        model.addAttribute("message", "Hi   user, welcome to Public Hello World page");
 
        return "/helloWorld";
    }
     
    @RequestMapping("/secured/pages")
    public String helloWorldSecured(Model model) {
        model.addAttribute("message", "Hi user,  Great !! , welcome to Secured Hello World page");
        return "/secured/securedHelloWorld";
    }
    
 
}
package com.kb.controllers;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;

@Controller
public class SpringSecurityHelloWorldControllerDatabase {
	  @RequestMapping("/public/pages")
    public String helloWorld(Model model) {
        model.addAttribute("message", "Hi   user, welcome to Public Hello World page");
 
        return "/helloWorld";
    }
     
    @RequestMapping("/secured/pages")
    public String helloWorldSecured(Model model) {
        model.addAttribute("message", "Hi user,  Great !! , welcome to Secured Hello World page");
        return "/secured/securedHelloWorld";
    }
	

}

Create the below java class

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
package com.kb;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
 
public class DBUtilsToCreateRoleTables {
    
    private DataSource dataSource;
 
    public DataSource getDataSource() {
        return dataSource;
    }
 
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }
    
    public void createRoleTables(){
        DataSource dataSource = getDataSource();
        Connection connection =null;
        Statement statement =null;
        try {
            System.out.println("inside createRoleTables method");
             connection = dataSource.getConnection();
             statement = connection.createStatement();
             System.out.println("connection - "+connection);
             System.out.println("statement - "+statement);
            statement.executeUpdate("DROP TABLE USERS IF EXISTS");
            statement.executeUpdate("CREATE TABLE USERS(USER_ID INTEGER,USERNAME VARCHAR(50),PASSWORD VARCHAR(50),ENABLED BOOLEAN);");
            statement.executeUpdate("INSERT INTO USERS VALUES(1,'kb','1234',TRUE);");
            statement.executeUpdate("DROP TABLE USERS_ROLES IF EXISTS");
            statement.executeUpdate("CREATE TABLE USERS_ROLES(USER_ROLE_ID INTEGER,USER_ID INTEGER,ROLE VARCHAR(50));");
            statement.executeUpdate("INSERT INTO USERS_ROLES VALUES(1,1,'ROLE_ADMIN');");
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            try {
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            
        }
    }
 
}
package com.kb;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;

public class DBUtilsToCreateRoleTables {
	
	private DataSource dataSource;

	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	
	public void createRoleTables(){
		DataSource dataSource = getDataSource();
		Connection connection =null;
		Statement statement =null;
		try {
			System.out.println("inside createRoleTables method");
			 connection = dataSource.getConnection();
			 statement = connection.createStatement();
			 System.out.println("connection - "+connection);
			 System.out.println("statement - "+statement);
			statement.executeUpdate("DROP TABLE USERS IF EXISTS");
			statement.executeUpdate("CREATE TABLE USERS(USER_ID INTEGER,USERNAME VARCHAR(50),PASSWORD VARCHAR(50),ENABLED BOOLEAN);");
			statement.executeUpdate("INSERT INTO USERS VALUES(1,'kb','1234',TRUE);");
			statement.executeUpdate("DROP TABLE USERS_ROLES IF EXISTS");
			statement.executeUpdate("CREATE TABLE USERS_ROLES(USER_ROLE_ID INTEGER,USER_ID INTEGER,ROLE VARCHAR(50));");
			statement.executeUpdate("INSERT INTO USERS_ROLES VALUES(1,1,'ROLE_ADMIN');");
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			try {
				statement.close();
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
	}

}

This java class is used to create the tables required for the authorization of users.
We will have 2 tables a) USERS table b) USERS_ROLES table
Mapping is done between these tables with user_id of users table as the foreign key in the user-roles table.
Tables created in the DB and the values inserted are as below

Create Spring-security.xml as below

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<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.2.xsd
          http://www.springframework.org/schema/security
          http://www.springframework.org/schema/security/spring-security-3.2.xsd">
           
    <http auto-config='true'>
      <intercept-url pattern="/secured/*" access="ROLE_ADMIN" />
    </http>
       
    <authentication-manager>
      <authentication-provider>
        <jdbc-user-service data-source-ref="dataSource"  
        users-by-username-query="Select username,password,enabled from users where username=?" 
        authorities-by-username-query="select us.username,ur.role from users us,
        users_roles ur where us.user_id=ur.user_id and us.username=?"/>          
      </authentication-provider>
    </authentication-manager>   
           
</beans:beans>
<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.2.xsd
          http://www.springframework.org/schema/security
          http://www.springframework.org/schema/security/spring-security-3.2.xsd">
           
    <http auto-config='true'>
      <intercept-url pattern="/secured/*" access="ROLE_ADMIN" />
    </http>
       
    <authentication-manager>
      <authentication-provider>
        <jdbc-user-service data-source-ref="dataSource"  
        users-by-username-query="Select username,password,enabled from users where username=?" 
        authorities-by-username-query="select us.username,ur.role from users us,
        users_roles ur where us.user_id=ur.user_id and us.username=?"/>          
      </authentication-provider>
    </authentication-manager>   
           
</beans:beans>

I have implemented DB call with the query to authenticate the logged in user.
Now jdbc-user-service element provides the way to authenticate the user from the credentials stored in the DB.
data-source-ref references to a bean named dataSource which is defined in the spring bean definition file , which contains the DB details.
And the query defined inside is used to verify whether the user with Role in DB has an access to secured page or not.

Define the data source bean in the spring configuration file

Spring-mvc.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:mvc="http://www.springframework.org/schema/mvc"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context-4.0.xsd
        http://www.springframework.org/schema/mvc
        http://www.springframework.org/schema/mvc/spring-mvc-4.0.xsd">
 
    <context:component-scan base-package="com.kb.*" />
    <mvc:annotation-driven />
    <context:property-placeholder location="classpath:database.properties" />
    <bean id="viewResolver"
        class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <property name="prefix" value="/WEB-INF/pages/" />
        <property name="suffix" value=".jsp" />
    </bean>
 
    <bean id="dataSource"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="${driverClassName}" />
        <property name="url" value="${url}" />
        <property name="username" value="${username}" />
        <property name="password" value="${password}" />
    </bean>
 
    <bean id="dbUtil" class="com.kb.DBUtilsToCreateRoleTables"
        init-method="createRoleTables">
        <property name="dataSource" ref="dataSource" />
    </bean>
</beans>
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:mvc="http://www.springframework.org/schema/mvc"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context-4.0.xsd
        http://www.springframework.org/schema/mvc
        http://www.springframework.org/schema/mvc/spring-mvc-4.0.xsd">

	<context:component-scan base-package="com.kb.*" />
	<mvc:annotation-driven />
	<context:property-placeholder location="classpath:database.properties" />
	<bean id="viewResolver"
		class="org.springframework.web.servlet.view.InternalResourceViewResolver">
		<property name="prefix" value="/WEB-INF/pages/" />
		<property name="suffix" value=".jsp" />
	</bean>

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

	<bean id="dbUtil" class="com.kb.DBUtilsToCreateRoleTables"
		init-method="createRoleTables">
		<property name="dataSource" ref="dataSource" />
	</bean>
</beans>

Note : createRoleTables() method is made as init method so that it will be executed at the loading of spring configuration file and creates the Tables and data.

Create database.properties file with below details

1
2
3
4
driverClassName=org.hsqldb.jdbcDriver
url=jdbc:hsqldb:mem://personDb
username=kb
password=1234
driverClassName=org.hsqldb.jdbcDriver
url=jdbc:hsqldb:mem://personDb
username=kb
password=1234

Let’s create public hello world jsp page

1
2
3
4
5
6
7
8
9
10
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<h3>Hello World!</h3>
<h4>${message}</h4>
</body>
</html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<h3>Hello World!</h3>
<h4>${message}</h4>
</body>
</html>

Let’s create secured hello world jsp page

1
2
3
4
5
6
7
8
9
10
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<h3>Hello World!</h3>
<h4>${message}</h4>
</body>
</html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<h3>Hello World!</h3>
<h4>${message}</h4>
</body>
</html>

Build the project ->Right click on project and select maven-install

Deployment of our application
Now go to the target folder of project and copy the war file spring_security_database.war
E:\workspace\Spring-security-ws\spring_security_database\target\spring_security_database.war

Paste it in the tomcat’s webapps folder
E:\workspace\Spring-security-ws\spring_security_database\target

Start the server

Right click on server and click on start as shown below

Open browser and type http://localhost:8080/ and see whether server window opens
Now access the below url
http://localhost:8080/spring_security_database/public/pages

Now try to access secured url
http://localhost:8080/spring_security_database/secured/pages
You should get default spring login form

Notice here that secured page is not accessible directly, we need to provide valid credentials to access this page.
Spring has displayed its own login form, please enter valid user name and password and then access secured pages.
Enter bad credentials and see the output

Enter valid credentials which are as below
Username – kb
Password – 1234

and now we accessed the secured page

Download this project springSecurityDatabase.zip

About the Author

Karibasappa G C (KB)
Founder of javainsimpleway.com
I love Java and open source technologies and very much passionate about software development.
I like to share my knowledge with others especially on technology 🙂
I have given all the examples as simple as possible to understand for the beginners.
All the code posted on my blog is developed,compiled and tested in my development environment.
If you find any mistakes or bugs, Please drop an email to kb.knowledge.sharing@gmail.com

Connect with me on Facebook for more updates

Share this article on