Close

Spring Boot - JDBC Authentication With H2 Console

[Updated: Jun 7, 2018, Created: Jun 7, 2018]

In the last example we saw how to enable JDBC security using H2 database. In following example we will see how to configure H2 web console when security is enabled.

Example

src/main/resources/application.properties

spring.h2.console.enabled=true

Configuration class

@SpringBootApplication
public class ExampleMain {

  @Bean
  public WebSecurityConfigurerAdapter webSecurityConfig(DataSource dataSource) {
      return new WebSecurityConfigurerAdapter() {
          @Override
          protected void configure(HttpSecurity http) throws Exception {
              http.authorizeRequests()
                  .antMatchers("/h2-console/**").hasRole("ADMIN")//allow h2 console access to admins only
                  .anyRequest().authenticated()//all other urls can be access by any authenticated role
                  .and().formLogin()//enable form login instead of basic login
                  .and().csrf().ignoringAntMatchers("/h2-console/**")//don't apply CSRF protection to /h2-console
                  .and().headers().frameOptions().sameOrigin();//allow use of frame to same origin urls
          }

          @Override
          protected void configure(AuthenticationManagerBuilder builder) throws Exception {
              builder.jdbcAuthentication()
                     .dataSource(dataSource);
          }
      };
  }

  public static void main(String[] args) {
      SpringApplication.run(ExampleMain.class);
  }
}

In above configuration we have allowed access to the URIs starting with /h2-console/ to the ADMIN role only. We have also disabled CSRF protection for /h2-console/** and allowed frame use (H2 console uses <frame></frame>) if the request come from the same origin (i.e. localhost:8080 in our example). See also X-Frame-Options.

SQL scripts

src/main/resources/schema.sql

create table users(
	username varchar_ignorecase(50) not null primary key,
	password varchar_ignorecase(200) 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)
);

src/main/resources/data.sql

insert into users (username, password, enabled) values ('bob', '{noop}123', true);
insert into authorities (username, authority) values ('bob', 'ROLE_USER');

insert into users (username, password, enabled) values ('sara', '{noop}234', true);
insert into authorities (username, authority) values ('sara', 'ROLE_ADMIN');

In data.sql script we used {noop} to indicate that no password encoder (NoOpPasswordEncoder) should be used as we also did not specify any password encoder in our configuration above.

MVC controller

@Controller
public class AppController {

  @RequestMapping("/**")
  public String handler(ModelMap model, HttpServletRequest request) {
      Authentication auth = SecurityContextHolder.getContext()
                                                 .getAuthentication();
      model.addAttribute("uri", request.getRequestURI());
      model.addAttribute("user", auth.getName());
      model.addAttribute("roles", auth.getAuthorities());
      return "app";
  }
}

Thymeleaf view

src/main/resources/templates/app.html

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml"
      xmlns:th="http://www.thymeleaf.org">

<body>
<h2>Spring Secured App</h2>
<p>app content ......... at uri <span th:text="${uri}"/></p>
<p>User: <span th:text="${user}"/></p>
<p>Roles: <span th:text="${roles}"/></p>
<br/>
<form action="/logout" method="post">
    <input type="hidden"
           th:name="${_csrf.parameterName}"
           th:value="${_csrf.token}"/>
    <input type="submit" value="Logout">
</form>
</body>
</html>

Running example

To try examples, run spring-boot maven plugin (configured in pom.xml of example project below):

mvn spring-boot:run

Or run the main method class from IDE.

Output

Accessing localhost:8080/

Entring username=bob and password=123 and clicking on 'Login' button:

Accessing /h2-console

That's expected because 'bob' does not have 'ADMIN' role. /h2-console access is only allowed for ADMIN per our configuration.

Let's login with username=sara and password=234 who has the ADMIN role.

Accessing /h2-console

Clicking on 'Connect' button and querying USERS table:

Example Project

Dependencies and Technologies Used:

  • Spring Boot 2.0.2.RELEASE
    Corresponding Spring Version 5.0.6.RELEASE
  • spring-boot-starter-security : Starter for using Spring Security.
    Uses org.springframework.security:spring-security-web version 5.0.5.RELEASE
  • spring-boot-starter-web : Starter for building web, including RESTful, applications using Spring MVC. Uses Tomcat as the default embedded container.
  • spring-boot-starter-thymeleaf : Starter for building MVC web applications using Thymeleaf views.
    Uses org.thymeleaf:thymeleaf-spring5 version 3.0.9.RELEASE
  • spring-boot-starter-jdbc : Starter for using JDBC with the HikariCP connection pool.
    Uses org.springframework:spring-jdbc version 5.0.6.RELEASE
    Uses com.zaxxer:HikariCP version 2.7.9
  • h2 1.4.197: H2 Database Engine.
  • JDK 1.8
  • Maven 3.3.9

JDBC Security With H2 Console Select All Download
  • boot-sec-jdbc-authentication-with-h2-console
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • ExampleMain.java
          • resources
            • templates

    See Also