40. Security Database Schema安全数据库模式
该框架使用了各种数据库模式，本附录提供了所有这些模式的单一参考点。您只需要为您需要的功能领域提供表格。
DDL语句是为HSQLDB数据库提供的。您可以将这些作为定义您正在使用的数据库模式的指南。
40.1 User Schema
用户详细信息服务UserDetailsService 的标准JDBC实现需要表来加载用户的密码、帐户状态(启用或禁用)和权限(角色)列表。您需要调整此模式以匹配您正在使用的数据库方言。
1 create table users( 2 username varchar_ignorecase(50) not null primary key, 3 password varchar_ignorecase(50) not null, 4 enabled boolean not null 5 ); 6 7 create table authorities ( 8 username varchar_ignorecase(50) not null, 9 authority varchar_ignorecase(50) not null, 10 constraint fk_authorities_users foreign key(username) references users(username) 11 ); 12 create unique index ix_auth_username on authorities (username,authority);
40.1.1 Group Authorities
Spring Security 2.0引入了对JdbcDaoImpl中的组权限的支持。如果启用了组，则表结构如下。您需要调整此模式以匹配您正在使用的数据库方言。
1 create table groups ( 2 id bigint generated by default as identity(start with 0) primary key, 3 group_name varchar_ignorecase(50) not null 4 ); 5 6 create table group_authorities ( 7 group_id bigint not null, 8 authority varchar(50) not null, 9 constraint fk_group_authorities_group foreign key(group_id) references groups(id) 10 ); 11 12 create table group_members ( 13 id bigint generated by default as identity(start with 0) primary key, 14 username varchar(50) not null, 15 group_id bigint not null, 16 constraint fk_group_members_group foreign key(group_id) references groups(id) 17 );
请记住，只有在使用提供的JDBC用户详细信息服务UserDetailsService 实现时，这些表才是必需的。如果您自己编写或选择在没有用户详细信息服务UserDetailsService 的情况下实现身份验证提供程序AuthenticationProvider ，那么只要满足接口契约，您就可以完全自由地存储数据。
40.2 Persistent Login (Remember-Me) Schema
此表用于存储更安全的持久令牌“remember-me”实现所使用的数据。如果您直接或通过命名空间使用JdbcTokenRepositoryImpl，那么您将需要这个表。请记住调整此模式以匹配您正在使用的数据库方言。
1 create table persistent_logins ( 2 username varchar(64) not null, 3 series varchar(64) primary key, 4 token varchar(64) not null, 5 last_used timestamp not null 6 );
40.3 ACL Schema
Spring安全ACL实现使用了四个表。
acl_sid存储由acl系统识别的安全身份。这些可以是唯一的主体或可以应用于多个主体的权限。
acl_class定义了应用acl的域对象类型。类列存储对象的Java类名。
acl_object_identity存储特定域对象的对象标识定义。
acl_entry存储应用于特定对象标识和安全标识的acl权限。
假设数据库将自动为每个身份生成主键。当在acl_sid或acl_class表中创建新行时，JdbcMutableAclService必须能够检索这些值。有两个属性定义了检索这些值类标识查询sidIdentityQuery和类标识查询classIdentityQuery 所需的SQL。这两者都默认为调用标识call identity()
ACL工件JAR包含用于在HyperSQL (HSQLDB)、PostgreSQL、MySQL/MariaDB、微软SQL服务器和Oracle数据库中创建ACL模式的文件。这些模式也将在下面的章节中演示。
40.3.1 HyperSQL
默认模式与框架内单元测试中使用的嵌入式HSQLDB数据库一起工作。
1 create table acl_sid( 2 id bigint generated by default as identity(start with 100) not null primary key, 3 principal boolean not null, 4 sid varchar_ignorecase(100) not null, 5 constraint unique_uk_1 unique(sid,principal) 6 ); 7 8 create table acl_class( 9 id bigint generated by default as identity(start with 100) not null primary key, 10 class varchar_ignorecase(100) not null, 11 constraint unique_uk_2 unique(class) 12 ); 13 14 create table acl_object_identity( 15 id bigint generated by default as identity(start with 100) not null primary key, 16 object_id_class bigint not null, 17 object_id_identity bigint not null, 18 parent_object bigint, 19 owner_sid bigint, 20 entries_inheriting boolean not null, 21 constraint unique_uk_3 unique(object_id_class,object_id_identity), 22 constraint foreign_fk_1 foreign key(parent_object)references acl_object_identity(id), 23 constraint foreign_fk_2 foreign key(object_id_class)references acl_class(id), 24 constraint foreign_fk_3 foreign key(owner_sid)references acl_sid(id) 25 ); 26 27 create table acl_entry( 28 id bigint generated by default as identity(start with 100) not null primary key, 29 acl_object_identity bigint not null, 30 ace_order int not null, 31 sid bigint not null, 32 mask integer not null, 33 granting boolean not null, 34 audit_success boolean not null, 35 audit_failure boolean not null, 36 constraint unique_uk_4 unique(acl_object_identity,ace_order), 37 constraint foreign_fk_4 foreign key(acl_object_identity) references acl_object_identity(id), 38 constraint foreign_fk_5 foreign key(sid) references acl_sid(id) 39 );
40.3.2 PostgreSQL
1 create table acl_sid( 2 id bigserial not null primary key, 3 principal boolean not null, 4 sid varchar(100) not null, 5 constraint unique_uk_1 unique(sid,principal) 6 ); 7 8 create table acl_class( 9 id bigserial not null primary key, 10 class varchar(100) not null, 11 constraint unique_uk_2 unique(class) 12 ); 13 14 create table acl_object_identity( 15 id bigserial primary key, 16 object_id_class bigint not null, 17 object_id_identity bigint not null, 18 parent_object bigint, 19 owner_sid bigint, 20 entries_inheriting boolean not null, 21 constraint unique_uk_3 unique(object_id_class,object_id_identity), 22 constraint foreign_fk_1 foreign key(parent_object)references acl_object_identity(id), 23 constraint foreign_fk_2 foreign key(object_id_class)references acl_class(id), 24 constraint foreign_fk_3 foreign key(owner_sid)references acl_sid(id) 25 ); 26 27 create table acl_entry( 28 id bigserial primary key, 29 acl_object_identity bigint not null, 30 ace_order int not null, 31 sid bigint not null, 32 mask integer not null, 33 granting boolean not null, 34 audit_success boolean not null, 35 audit_failure boolean not null, 36 constraint unique_uk_4 unique(acl_object_identity,ace_order), 37 constraint foreign_fk_4 foreign key(acl_object_identity) references acl_object_identity(id), 38 constraint foreign_fk_5 foreign key(sid) references acl_sid(id) 39 );
您必须将JdbcMutableAclService的类标识查询和类标识查询属性分别设置为以下值:
select currval(pg_get_serial_sequence('acl_class', 'id')）
select currval(pg_get_serial_sequence('acl_sid', 'id')
40.3.3 MySQL and MariaDB
1 CREATE TABLE acl_sid ( 2 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 3 principal BOOLEAN NOT NULL, 4 sid VARCHAR(100) NOT NULL, 5 UNIQUE KEY unique_acl_sid (sid, principal) 6 ) ENGINE=InnoDB; 7 8 CREATE TABLE acl_class ( 9 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 10 class VARCHAR(100) NOT NULL, 11 UNIQUE KEY uk_acl_class (class) 12 ) ENGINE=InnoDB; 13 14 CREATE TABLE acl_object_identity ( 15 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 16 object_id_class BIGINT UNSIGNED NOT NULL, 17 object_id_identity BIGINT NOT NULL, 18 parent_object BIGINT UNSIGNED, 19 owner_sid BIGINT UNSIGNED, 20 entries_inheriting BOOLEAN NOT NULL, 21 UNIQUE KEY uk_acl_object_identity (object_id_class, object_id_identity), 22 CONSTRAINT fk_acl_object_identity_parent FOREIGN KEY (parent_object) REFERENCES acl_object_identity (id), 23 CONSTRAINT fk_acl_object_identity_class FOREIGN KEY (object_id_class) REFERENCES acl_class (id), 24 CONSTRAINT fk_acl_object_identity_owner FOREIGN KEY (owner_sid) REFERENCES acl_sid (id) 25 ) ENGINE=InnoDB; 26 27 CREATE TABLE acl_entry ( 28 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 29 acl_object_identity BIGINT UNSIGNED NOT NULL, 30 ace_order INTEGER NOT NULL, 31 sid BIGINT UNSIGNED NOT NULL, 32 mask INTEGER UNSIGNED NOT NULL, 33 granting BOOLEAN NOT NULL, 34 audit_success BOOLEAN NOT NULL, 35 audit_failure BOOLEAN NOT NULL, 36 UNIQUE KEY unique_acl_entry (acl_object_identity, ace_order), 37 CONSTRAINT fk_acl_entry_object FOREIGN KEY (acl_object_identity) REFERENCES acl_object_identity (id), 38 CONSTRAINT fk_acl_entry_acl FOREIGN KEY (sid) REFERENCES acl_sid (id) 39 ) ENGINE=InnoDB;
40.3.4 Microsoft SQL Server
1 CREATE TABLE acl_sid ( 2 id BIGINT NOT NULL IDENTITY PRIMARY KEY, 3 principal BIT NOT NULL, 4 sid VARCHAR(100) NOT NULL, 5 CONSTRAINT unique_acl_sid UNIQUE (sid, principal) 6 ); 7 8 CREATE TABLE acl_class ( 9 id BIGINT NOT NULL IDENTITY PRIMARY KEY, 10 class VARCHAR(100) NOT NULL, 11 CONSTRAINT uk_acl_class UNIQUE (class) 12 ); 13 14 CREATE TABLE acl_object_identity ( 15 id BIGINT NOT NULL IDENTITY PRIMARY KEY, 16 object_id_class BIGINT NOT NULL, 17 object_id_identity BIGINT NOT NULL, 18 parent_object BIGINT, 19 owner_sid BIGINT, 20 entries_inheriting BIT NOT NULL, 21 CONSTRAINT uk_acl_object_identity UNIQUE (object_id_class, object_id_identity), 22 CONSTRAINT fk_acl_object_identity_parent FOREIGN KEY (parent_object) REFERENCES acl_object_identity (id), 23 CONSTRAINT fk_acl_object_identity_class FOREIGN KEY (object_id_class) REFERENCES acl_class (id), 24 CONSTRAINT fk_acl_object_identity_owner FOREIGN KEY (owner_sid) REFERENCES acl_sid (id) 25 ); 26 27 CREATE TABLE acl_entry ( 28 id BIGINT NOT NULL IDENTITY PRIMARY KEY, 29 acl_object_identity BIGINT NOT NULL, 30 ace_order INTEGER NOT NULL, 31 sid BIGINT NOT NULL, 32 mask INTEGER NOT NULL, 33 granting BIT NOT NULL, 34 audit_success BIT NOT NULL, 35 audit_failure BIT NOT NULL, 36 CONSTRAINT unique_acl_entry UNIQUE (acl_object_identity, ace_order), 37 CONSTRAINT fk_acl_entry_object FOREIGN KEY (acl_object_identity) REFERENCES acl_object_identity (id), 38 CONSTRAINT fk_acl_entry_acl FOREIGN KEY (sid) REFERENCES acl_sid (id) 39 );
40.3.5 Oracle Database
1 CREATE TABLE acl_sid ( 2 id NUMBER(38) NOT NULL PRIMARY KEY, 3 principal NUMBER(1) NOT NULL CHECK (principal in (0, 1)), 4 sid NVARCHAR2(100) NOT NULL, 5 CONSTRAINT unique_acl_sid UNIQUE (sid, principal) 6 ); 7 CREATE SEQUENCE acl_sid_sequence START WITH 1 INCREMENT BY 1 NOMAXVALUE; 8 CREATE OR REPLACE TRIGGER acl_sid_id_trigger 9 BEFORE INSERT ON acl_sid 10 FOR EACH ROW 11 BEGIN 12 SELECT acl_sid_sequence.nextval INTO :new.id FROM dual; 13 END; 14 15 CREATE TABLE acl_class ( 16 id NUMBER(38) NOT NULL PRIMARY KEY, 17 class NVARCHAR2(100) NOT NULL, 18 CONSTRAINT uk_acl_class UNIQUE (class) 19 ); 20 CREATE SEQUENCE acl_class_sequence START WITH 1 INCREMENT BY 1 NOMAXVALUE; 21 CREATE OR REPLACE TRIGGER acl_class_id_trigger 22 BEFORE INSERT ON acl_class 23 FOR EACH ROW 24 BEGIN 25 SELECT acl_class_sequence.nextval INTO :new.id FROM dual; 26 END; 27 28 CREATE TABLE acl_object_identity ( 29 id NUMBER(38) NOT NULL PRIMARY KEY, 30 object_id_class NUMBER(38) NOT NULL, 31 object_id_identity NUMBER(38) NOT NULL, 32 parent_object NUMBER(38), 33 owner_sid NUMBER(38), 34 entries_inheriting NUMBER(1) NOT NULL CHECK (entries_inheriting in (0, 1)), 35 CONSTRAINT uk_acl_object_identity UNIQUE (object_id_class, object_id_identity), 36 CONSTRAINT fk_acl_object_identity_parent FOREIGN KEY (parent_object) REFERENCES acl_object_identity (id), 37 CONSTRAINT fk_acl_object_identity_class FOREIGN KEY (object_id_class) REFERENCES acl_class (id), 38 CONSTRAINT fk_acl_object_identity_owner FOREIGN KEY (owner_sid) REFERENCES acl_sid (id) 39 ); 40 CREATE SEQUENCE acl_object_identity_sequence START WITH 1 INCREMENT BY 1 NOMAXVALUE; 41 CREATE OR REPLACE TRIGGER acl_object_identity_id_trigger 42 BEFORE INSERT ON acl_object_identity 43 FOR EACH ROW 44 BEGIN 45 SELECT acl_object_identity_sequence.nextval INTO :new.id FROM dual; 46 END; 47 48 CREATE TABLE acl_entry ( 49 id NUMBER(38) NOT NULL PRIMARY KEY, 50 acl_object_identity NUMBER(38) NOT NULL, 51 ace_order INTEGER NOT NULL, 52 sid NUMBER(38) NOT NULL, 53 mask INTEGER NOT NULL, 54 granting NUMBER(1) NOT NULL CHECK (granting in (0, 1)), 55 audit_success NUMBER(1) NOT NULL CHECK (audit_success in (0, 1)), 56 audit_failure NUMBER(1) NOT NULL CHECK (audit_failure in (0, 1)), 57 CONSTRAINT unique_acl_entry UNIQUE (acl_object_identity, ace_order), 58 CONSTRAINT fk_acl_entry_object FOREIGN KEY (acl_object_identity) REFERENCES acl_object_identity (id), 59 CONSTRAINT fk_acl_entry_acl FOREIGN KEY (sid) REFERENCES acl_sid (id) 60 ); 61 CREATE SEQUENCE acl_entry_sequence START WITH 1 INCREMENT BY 1 NOMAXVALUE; 62 CREATE OR REPLACE TRIGGER acl_entry_id_trigger 63 BEFORE INSERT ON acl_entry 64 FOR EACH ROW 65 BEGIN 66 SELECT acl_entry_sequence.nextval INTO :new.id FROM dual; 67 END;
