Spring Security - JDBC Authentication with remote Database running in server mode

[Updated: Aug 20, 2017, Created: Aug 18, 2017]

This example demonstrates how to implement Spring JDBC authentication with remote database server. Even though we are going to use H2 database, these steps should work for any database with related JDBC driver and valid server URL..

Downloading H2 database and running in server mode

  1. Download H2 database from here (the zip file). Extract the zip file (say at location c:\). The main folder h2-xxx will be created (h2-2017-06-10 in my case). Run the following from command line:


    That will open the h2 console in browser. Apply the settings as shown:

    Click on 'Connect' button. It will take you the schema user interface.

  2. Now copy/paste the following script (User Schema and insert statements) in the 'SQL Statement' section as shown:


    create table users(
    	username varchar_ignorecase(50) not null primary key,
    	password varchar_ignorecase(50) not null,
    	enabled boolean not null
    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)
    create unique index ix_auth_username on authorities (username,authority);
    insert into USERS (USERNAME, PASSWORD, ENABLED) values ('tina', 222, true);
    insert into USERS (USERNAME, PASSWORD, ENABLED) values ('tom', 333, true);
    insert into USERS (USERNAME, PASSWORD, ENABLED) values ('jake', 111, true);
    insert into AUTHORITIES (USERNAME, AUTHORITY) values ('tina', 'ROLE_ADMIN');
    insert into AUTHORITIES (USERNAME, AUTHORITY) values ('tom', 'ROLE_USER');
    insert into AUTHORITIES (USERNAME, AUTHORITY) values ('jake', 'ROLE_VISITOR');

    Paste the script here and click on the run button:

    Above standard sql statements should work for all databases, if not then you may need to tweak it a little for your database.

    You can play a little here to see whether AUTHORITIES and USERS tables are created and populated as expected.

Creating example application

Java Config class

public class AppConfig extends WebSecurityConfigurerAdapter {

  public DataSource appDataSource() {
      DriverManagerDataSource ds = new DriverManagerDataSource();
      return ds;

  protected void configure(HttpSecurity http) throws Exception {

  public void configure(AuthenticationManagerBuilder builder)
          throws Exception {

  public ViewResolver viewResolver() {
      InternalResourceViewResolver viewResolver = new InternalResourceViewResolver();
      return viewResolver;

Depending on what database you are using, you have to specify the related JDBC driver and correct URL for your database.

The Controller

public class MyController {

  public String handleRequest2(HttpServletRequest request, Model model) {
      Authentication auth = SecurityContextHolder.getContext()
      model.addAttribute("uri", request.getRequestURI())
           .addAttribute("user", auth.getName())
           .addAttribute("roles", auth.getAuthorities())
           .addAttribute("time", LocalTime.now());
      return "my-page";

The JSP page


<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<html lang="en">
 <h2>Spring Security Example</h2>
 <p>Time: ${time}<br/>
    URI: ${uri} <br/>
    User :  ${user} <br/>
    roles:  ${roles} <br/>
  <form action="/logout" method="post">
     <input type="hidden"
  <input type="submit" value="Logout">

To try examples, run embedded tomcat (configured in pom.xml of example project below):

mvn tomcat7:run-war


Accessing 'http://localhost:8080/' from browser:

Submitting user/password (say jake/111):

Logging out:

Example Project

Dependencies and Technologies Used :

  • spring-jdbc 4.3.10.RELEASE: Spring JDBC.
  • h2 1.4.196: H2 Database Engine.
  • spring-security-web 4.2.3.RELEASE: spring-security-web.
  • spring-security-config 4.2.3.RELEASE: spring-security-config.
  • spring-webmvc 4.3.9.RELEASE: Spring Web MVC.
  • javax.servlet-api 3.1.0 Java Servlet API
  • jstl 1.2 javax.servlet:jstl
  • JDK 1.8
  • Maven 3.3.9

JDBC Database Server Authentication Example Select All Download
  • jdbc-database-server-authentication
    • src
      • main
        • java
          • com
            • logicbig
              • example
        • resources
        • webapp
          • WEB-INF
            • views

See Also