安全数据库表结构

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

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

A.1. User表

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

  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在JdbcDaoImpl中支持了权限分组。如果启用了权限分组功能，对应的数据库结果如下所示：

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。

acl_sid保存被ACL系统分配的安全标示符。它们可能是唯一的实体或可能分配给多个实体的权限。
acl_class定义ACL可以处理的实体类型。 class列保存了对象的Java类名。
acl_object_identity保存值得那个领域对象昂的对象标示定义。
acl_entry保存ACL权限，分配给一个特定的对象标示和安全标示。

假设数据库会自动生成主键作为每个标示。 JdbcMutableAclService必须可以获得这些，当创建了一个新的 acl_sid或acl_class表中的数据。它有两个属性可以定义需要的SQL来获得这些数据， classIdentityQuery和sidIdentityQuery。这两个属性的默认值是call identity()。

A.3.1. Hypersonic SQL

默认的表结构可以工作在内嵌的HSQLDB中，可以在框架内用作单元测试。

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

A.3.1.1. PostgreSQL

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

你需要把classIdentityQuery和 sidIdentityQuery两个 JdbcMutableAclService的属性设置成下面的值：

select currval(pg_get_serial_sequence('acl_class', 'id'))
select currval(pg_get_serial_sequence('acl_sid', 'id'))

Prev	Home	Next
Chapter 23. 替换验证身份	Sponsored by SpringSource	Appendix B. 安全命名空间