spring

Spring security using JDBC authentication

In this tutorial, i will show you how to perform authenticate with Spring security using JDBC authentication.
We need following tools and techniques:

  • Eclipse 4.3
  • JDK 7
  • Maven 3
  • Spring security 3.2.3
  • Spring MVC 4.0.4
  • Mysql 5.6

We reuse code of the post Spring security quick start tutorial and enhance with follow features

  • JDBC authentication
  • Logout handler

 1. Dependencies

To using JDBC authentication we need add dependencies for mysql , spring-jdbc

<dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>${org.springframework-version}</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.30</version>
    </dependency>

 2. Scripts database

Run below script on your mysql database

CREATE DATABASE TUTORIAL;
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Tables structure
-- ----------------------------
DROP TABLE IF EXISTS `roles`;
CREATE TABLE `roles` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `description` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `create_date` datetime DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `roles_users`; CREATE TABLE `roles_users` ( `role_id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, PRIMARY KEY (`role_id`,`user_id`), CONSTRAINT `FK_USERS` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`), CONSTRAINT `FK_ROLES` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Data
-- ----------------------------
INSERT INTO `roles` (`id`, `description`, `name`, `status`) VALUES (1, 'Adminstrator of application', 'ADMIN', 'Active');
INSERT INTO `roles` (`id`, `description`, `name`, `status`) VALUES (2, 'User role', 'USER', 'Active');
INSERT INTO `users` (`id`, `create_date`, `password`, `username`) VALUES (1, '2014-5-7 07:49:58', 'devjav', 'admin');
INSERT INTO `users` (`id`, `create_date`, `password`, `username`) VALUES (2, '2014-5-7 08:42:04', 'devjav', 'devjav');
INSERT INTO `roles_users` (`role_id`, `user_id`) VALUES (1, 1);
INSERT INTO `roles_users` (`role_id`, `user_id`) VALUES (2, 2);

 3. Spring security configuration

We update spring security configuration file security.xml as below

<?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:security="http://www.springframework.org/schema/security"
  xsi:schemaLocation="http://www.springframework.org/schema/security http://www.springframework.org/schema/security/spring-security-3.2.xsd
    http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
  <security:http pattern="/resources/**" security="none" />
  <security:http auto-config="true" use-expressions="true">
    <security:intercept-url pattern="/" access="permitAll" />
    <security:intercept-url pattern="/login.do" access="permitAll" />
    <security:intercept-url pattern="/accessdenied.do" access="permitAll" />
    <security:intercept-url pattern="/home.do" access="hasRole('ROLE_USER')" />
    <security:intercept-url pattern="/admin/**" access="hasRole('ROLE_ADMIN')" />
    <security:form-login login-page="/login.do" login-processing-url="/dologin.do"
      username-parameter="username" password-parameter="password"
      authentication-failure-url="/login.do?error=true" default-target-url="/home.do"
      always-use-default-target="true" />
    <security:access-denied-handler error-page="/accessdenied.do"/>
    <security:logout logout-success-url="/login.do" logout-url="/logout.do"/>
  </security:http>
  <security:authentication-manager>
    <security:authentication-provider>
      <!-- <security:user-service>
        <security:user name="devjav" password="devjav" authorities="ROLE_USER" />
        <security:user name="admin" password="devjav" authorities="ROLE_ADMIN" />
      </security:user-service>
       -->
      <security:jdbc-user-service data-source-ref="dataSource" role-prefix="ROLE_"
      users-by-username-query=
        "select username,password, 1 from users where username=?"
      authorities-by-username-query=
        "select a.username, b.name from users a,roles b, roles_users c where a.username =? and a.id=c.user_id and b.id=c.role_id " />
    </security:authentication-provider>
  </security:authentication-manager>
</beans>

I comment define In-memory credentials and add define jdbc-user-service

  • data-source-ref: reference to jdbc datasource define
  • role-prefix: cause our roles in database does not have ROLE_ prefix in name of roles but when define spring security we need that so must define role_prefix
  • users-by-username-query: query to get information about users, i default 1 cause spring security need that field as status of user but i don’t have that field on database.
  • authorities-by-username-query: define query to get out username and role name.

I also define logout handler for logout of web application.

  • logout-success-url: url broswer will redirect to after logout
  • logout-url: logout url entry point

4.Spring configuration

We update spring configuration file root-context.xml to define datasource

<?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.xsd">
  <bean id="dataSource"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost:3306/tutorial" />
    <property name="username" value="root" />
    <property name="password" value="admin" />
  </bean>
</beans>

5. User home page

We update user home page to add logout menu.

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form"%>
<%@ taglib prefix="spring" uri="http://www.springframework.org/tags"%>
<html>
<head>
  <title>Home</title>
  <link rel="stylesheet"
  href='<spring:url value="/resources/css/bootstrap.min.css"/>' />
  <link rel="stylesheet"
  href='<spring:url value="/resources/css/custom.css"/>' />
</head>
<body>
 <div class="navbar navbar-inverse navbar-fixed-top" role="navigation">
      <div class="container">
        <div class="navbar-header">
          <button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
            <span class="sr-only">Toggle navigation</span>
            <span class="icon-bar"></span>
            <span class="icon-bar"></span>
            <span class="icon-bar"></span>
          </button>
          <a class="navbar-brand" href="#">DEVJAV</a>
        </div>
        <div class="collapse navbar-collapse">
          <ul class="nav navbar-nav">
            <li class="active"><a href="#">Home</a></li>
          </ul>
          <ul class="nav navbar-nav">
            <li class="active"><a href="<spring:url value="/logout.do"/>">Logout</a></li>
          </ul>
        </div>
        <!--/.nav-collapse -->
      </div>
    </div>

    <div class="container">

      <div class="starter-template">
        <h1>User View</h1>
        <p class="lead">This is user home page</p>
      </div>

    </div><!-- /.container -->
</body>
</html>

6. Source code

You can download source code on this link

One thought on “Spring security using JDBC authentication”

  1. Pingback: Java Dev world

Leave a Reply