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.
The standard JDBC implementation of the
UserDetailsService (JdbcDaoImpl)
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);
Spring Security 2.0 introduced support for group authorities in
JdbcDaoImpl. The table structure if groups are enabled is as
follows:
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));
Remember that these tables are only required if you are using the provided JDBC
UserDetailsService implementation. If you write your
own or choose to implement AuthenticationProvider
without a UserDetailsService, then you have complete
freedom over how you store the data, as long as the interface contract is
satisfied.
This table is used to store data used by the more secure persistent token remember-me
implementation. If you are using JdbcTokenRepositoryImpl either
directly or through the namespace, then you will need this table.
create table persistent_logins ( username varchar(64) not null, series varchar(64) primary key, token varchar(64) not null, last_used timestamp not null);
There are four tables used by the Spring Security ACL implementation.
acl_sid stores the security identities recognised by the
ACL system. These can be unique principals or authorities which may apply to
multiple principals.
acl_class defines the domain object types to which ACLs
apply. The class column stores the Java class name of the
object.
acl_object_identity stores the object identity definitions
of specific domai objects.
acl_entry stores the ACL permissions which apply to a
specific object identity and security identity.
It is assumed that the database will auto-generate the primary keys for each of the
identities. The JdbcMutableAclService has to be able to retrieve
these when it has created a new row in the acl_sid or
acl_class tables. It has two properties which define the SQL needed
to retrieve these values classIdentityQuery and
sidIdentityQuery. Both of these default to call
identity()
The default schema works with the embedded HSQLDB database that is used in unit tests within the framework.
create table acl_sid (
id bigint generated by default as identity(start with 100) not null primary key,
principal boolean not null,
sid varchar_ignorecase(100) not null,
constraint unique_uk_1 unique(sid,principal) );
create table acl_class (
id bigint generated by default as identity(start with 100) not null primary key,
class varchar_ignorecase(100) not null,
constraint unique_uk_2 unique(class) );
create table acl_object_identity (
id bigint generated by default as identity(start with 100) not null primary key,
object_id_class bigint not null,
object_id_identity bigint not null,
parent_object bigint,
owner_sid bigint not null,
entries_inheriting boolean not null,
constraint unique_uk_3 unique(object_id_class,object_id_identity),
constraint foreign_fk_1 foreign key(parent_object)references acl_object_identity(id),
constraint foreign_fk_2 foreign key(object_id_class)references acl_class(id),
constraint foreign_fk_3 foreign key(owner_sid)references acl_sid(id) );
create table acl_entry (
id bigint generated by default as identity(start with 100) not null primary key,
acl_object_identity bigint not null,ace_order int not null,sid bigint not null,
mask integer not null,granting boolean not null,audit_success boolean not null,
audit_failure boolean not null,
constraint unique_uk_4 unique(acl_object_identity,ace_order),
constraint foreign_fk_4 foreign key(acl_object_identity)
references acl_object_identity(id),
constraint foreign_fk_5 foreign key(sid) references acl_sid(id) );
create table acl_sid(
id bigserial not null primary key,
principal boolean not null,
sid varchar(100) not null,
constraint unique_uk_1 unique(sid,principal));
create table acl_class(
id bigserial not null primary key,
class varchar(100) not null,
constraint unique_uk_2 unique(class));
create table acl_object_identity(
id bigserial primary key,
object_id_class bigint not null,
object_id_identity bigint not null,
parent_object bigint,
owner_sid bigint,
entries_inheriting boolean not null,
constraint unique_uk_3 unique(object_id_class,object_id_identity),
constraint foreign_fk_1 foreign key(parent_object) references acl_object_identity(id),
constraint foreign_fk_2 foreign key(object_id_class) references acl_class(id),
constraint foreign_fk_3 foreign key(owner_sid) references acl_sid(id));
create table acl_entry(
id bigserial primary key,
acl_object_identity bigint not null,
ace_order int not null,
sid bigint not null,
mask integer not null,
granting boolean not null,
audit_success boolean not null,
audit_failure boolean not null,
constraint unique_uk_4 unique(acl_object_identity,ace_order),
constraint foreign_fk_4 foreign key(acl_object_identity)
references acl_object_identity(id),
constraint foreign_fk_5 foreign key(sid) references acl_sid(id));
You will have to set the classIdentityQuery and
sidIdentityQuery properties of
JdbcMutableAclService to the following values,
respectively:
select currval(pg_get_serial_sequence('acl_class',
'id'))
select currval(pg_get_serial_sequence('acl_sid',
'id'))