(翻译)Spring Security-2.0.x参考文档“安全数据库表结构”

安全数据库表结构

可以为框架采用不同的数据库结构，这个附录为所有功能提供了一种参考形式。 你只要为需要的功能部分提供对应的表结构。

这些DDL语句都是对应于HSQLDB数据库的。 你可以把它们当作一个指南，参照它，在你使用的数据库中定义表结构。
A.1. User表

UserDetailsService的标准JDBC实现，需要从这些表里读取用户的密码，帐号信息（可用或禁用）和权限（角色）列表。

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

A.1.1. 组权限

Spring Security 2.0支持了权限分组

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


A.2. 持久登陆（Remember-Me）表

这个表用来保存安全性更高的持久登陆remember-me实现所需要的数据。 如果你直接或通过命名空间使用了JdbcTokenRepositoryImpl，你就会需要这些表结构。

create table persistent_logins (
username varchar(64) not null,
series varchar(64) primary key,
token varchar(64) not null,
last_used timestamp not null);

A.3. ACL表

这些表对应Spring Security的ACL实现。

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