Appendix A. Security Database Schema

Table of Contents

User Schema
Group Authorities
Persistent Login (Remember-Me) Schema
ACL Schema

There are various database schema used by the framework and this appendix provides a single reference point to them all. You only need to provide the tables for the areas of functonality you require.

DDL statements are given for the HSQLDB database. You can use these as a guideline for defining the schema for the database you are using.

User Schema

The standard JDBC implementation of the UserDetailsService requires tables to load the password, account status (enabled or disabled) and a list of authorities (roles) for the user.

  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);;

Group Authorities

Spring Security 2.0 introduced support for group authorities

create table groups (
  id bigint generated by default as identity(start with 0) primary key, 
  group_name varchar_ignorecase(50) not null);

create table group_authorities (
  group_id bigint not null, 
  authority varchar(50) not null, 
  constraint fk_group_authorities_group foreign key(group_id) references groups(id));

create table group_members (
  id bigint generated by default as identity(start with 0) primary key, 
  username varchar(50) not null, 
  group_id bigint not null, 
  constraint fk_group_members_group foreign key(group_id) references groups(id));