Docs4dev
Docs
spring-security / 4.2.10.RELEASE / reference / appendix-schema.html

40. 安全数据库架构

框架使用了各种数据库模式，本附录为它们提供了单个参考点。您只需要提供所需功能范围的表即可。

为 HSQLDB 数据库提供了 DDL 语句。您可以将这些用作定义正在使用的数据库的架构的准则。

40.1 用户架构

UserDetailsService(JdbcDaoImpl)的标准 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);

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

请记住，仅在使用提供的 JDBC UserDetailsService实现时才需要这些表。如果您自己编写或选择不使用UserDetailsService来实现AuthenticationProvider，那么只要满足接口协定，就可以完全自由地存储数据。

40.2 永久登录(记住我)架构

该表用于存储更安全的persistent tokenmeme-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
);

40.3 ACL 架构

Spring Security ACL实现使用了四个表。

  • acl_sid存储 ACL 系统识别的安全身份。这些可以是唯一的委托人或可以应用于多个委托人的权限。

  • acl_class定义适用于 ACL 的域对象类型。 class列存储对象的 Java 类名称。

  • acl_object_identity存储特定 domai 对象的对象标识定义。

  • acl_entry存储适用于特定对象标识和安全标识的 ACL 权限。

假设数据库将为每个身份自动生成主键。当JdbcMutableAclService在acl_sid或acl_class表中创建新行时，它们必须能够检索它们。它具有两个属性，这些属性定义检索这些值classIdentityQuery和sidIdentityQuery所需的 SQL。两者都默认为call identity()

ACL 工件 JAR 包含用于在 HyperSQL(HSQLDB)，PostgreSQL，MySQL/MariaDB，Microsoft SQL Server 和 Oracle 数据库中创建 ACL 模式的文件。在以下各节中还将演示这些架构。

40.3.1 HyperSQL

默认模式与框架中的单元测试中使用的嵌入式 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,
	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)
);

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

您必须将JdbcMutableAclService的classIdentityQuery和sidIdentityQuery属性分别设置为以下值：

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

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

40.3.3 MySQL 和 MariaDB

CREATE TABLE acl_sid (
	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	principal BOOLEAN NOT NULL,
	sid VARCHAR(100) NOT NULL,
	UNIQUE KEY unique_acl_sid (sid, principal)
) ENGINE=InnoDB;

CREATE TABLE acl_class (
	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	class VARCHAR(100) NOT NULL,
	UNIQUE KEY uk_acl_class (class)
) ENGINE=InnoDB;

CREATE TABLE acl_object_identity (
	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	object_id_class BIGINT UNSIGNED NOT NULL,
	object_id_identity BIGINT NOT NULL,
	parent_object BIGINT UNSIGNED,
	owner_sid BIGINT UNSIGNED,
	entries_inheriting BOOLEAN NOT NULL,
	UNIQUE KEY uk_acl_object_identity (object_id_class, object_id_identity),
	CONSTRAINT fk_acl_object_identity_parent FOREIGN KEY (parent_object) REFERENCES acl_object_identity (id),
	CONSTRAINT fk_acl_object_identity_class FOREIGN KEY (object_id_class) REFERENCES acl_class (id),
	CONSTRAINT fk_acl_object_identity_owner FOREIGN KEY (owner_sid) REFERENCES acl_sid (id)
) ENGINE=InnoDB;

CREATE TABLE acl_entry (
	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	acl_object_identity BIGINT UNSIGNED NOT NULL,
	ace_order INTEGER NOT NULL,
	sid BIGINT UNSIGNED NOT NULL,
	mask INTEGER UNSIGNED NOT NULL,
	granting BOOLEAN NOT NULL,
	audit_success BOOLEAN NOT NULL,
	audit_failure BOOLEAN NOT NULL,
	UNIQUE KEY unique_acl_entry (acl_object_identity, ace_order),
	CONSTRAINT fk_acl_entry_object FOREIGN KEY (acl_object_identity) REFERENCES acl_object_identity (id),
	CONSTRAINT fk_acl_entry_acl FOREIGN KEY (sid) REFERENCES acl_sid (id)
) ENGINE=InnoDB;

40.3.4 Microsoft SQL Server

CREATE TABLE acl_sid (
	id BIGINT NOT NULL IDENTITY PRIMARY KEY,
	principal BIT NOT NULL,
	sid VARCHAR(100) NOT NULL,
	CONSTRAINT unique_acl_sid UNIQUE (sid, principal)
);

CREATE TABLE acl_class (
	id BIGINT NOT NULL IDENTITY PRIMARY KEY,
	class VARCHAR(100) NOT NULL,
	CONSTRAINT uk_acl_class UNIQUE (class)
);

CREATE TABLE acl_object_identity (
	id BIGINT NOT NULL IDENTITY PRIMARY KEY,
	object_id_class BIGINT NOT NULL,
	object_id_identity BIGINT NOT NULL,
	parent_object BIGINT,
	owner_sid BIGINT,
	entries_inheriting BIT NOT NULL,
	CONSTRAINT uk_acl_object_identity UNIQUE (object_id_class, object_id_identity),
	CONSTRAINT fk_acl_object_identity_parent FOREIGN KEY (parent_object) REFERENCES acl_object_identity (id),
	CONSTRAINT fk_acl_object_identity_class FOREIGN KEY (object_id_class) REFERENCES acl_class (id),
	CONSTRAINT fk_acl_object_identity_owner FOREIGN KEY (owner_sid) REFERENCES acl_sid (id)
);

CREATE TABLE acl_entry (
	id BIGINT NOT NULL IDENTITY PRIMARY KEY,
	acl_object_identity BIGINT NOT NULL,
	ace_order INTEGER NOT NULL,
	sid BIGINT NOT NULL,
	mask INTEGER NOT NULL,
	granting BIT NOT NULL,
	audit_success BIT NOT NULL,
	audit_failure BIT NOT NULL,
	CONSTRAINT unique_acl_entry UNIQUE (acl_object_identity, ace_order),
	CONSTRAINT fk_acl_entry_object FOREIGN KEY (acl_object_identity) REFERENCES acl_object_identity (id),
	CONSTRAINT fk_acl_entry_acl FOREIGN KEY (sid) REFERENCES acl_sid (id)
);

40.3.5 Oracle 数据库

CREATE TABLE acl_sid (
	id NUMBER(38) NOT NULL PRIMARY KEY,
	principal NUMBER(1) NOT NULL CHECK (principal in (0, 1)),
	sid NVARCHAR2(100) NOT NULL,
	CONSTRAINT unique_acl_sid UNIQUE (sid, principal)
);
CREATE SEQUENCE acl_sid_sequence START WITH 1 INCREMENT BY 1 NOMAXVALUE;
CREATE OR REPLACE TRIGGER acl_sid_id_trigger
	BEFORE INSERT ON acl_sid
	FOR EACH ROW
BEGIN
	SELECT acl_sid_sequence.nextval INTO :new.id FROM dual;
END;

CREATE TABLE acl_class (
	id NUMBER(38) NOT NULL PRIMARY KEY,
	class NVARCHAR2(100) NOT NULL,
	CONSTRAINT uk_acl_class UNIQUE (class)
);
CREATE SEQUENCE acl_class_sequence START WITH 1 INCREMENT BY 1 NOMAXVALUE;
CREATE OR REPLACE TRIGGER acl_class_id_trigger
	BEFORE INSERT ON acl_class
	FOR EACH ROW
BEGIN
	SELECT acl_class_sequence.nextval INTO :new.id FROM dual;
END;

CREATE TABLE acl_object_identity (
	id NUMBER(38) NOT NULL PRIMARY KEY,
	object_id_class NUMBER(38) NOT NULL,
	object_id_identity NUMBER(38) NOT NULL,
	parent_object NUMBER(38),
	owner_sid NUMBER(38),
	entries_inheriting NUMBER(1) NOT NULL CHECK (entries_inheriting in (0, 1)),
	CONSTRAINT uk_acl_object_identity UNIQUE (object_id_class, object_id_identity),
	CONSTRAINT fk_acl_object_identity_parent FOREIGN KEY (parent_object) REFERENCES acl_object_identity (id),
	CONSTRAINT fk_acl_object_identity_class FOREIGN KEY (object_id_class) REFERENCES acl_class (id),
	CONSTRAINT fk_acl_object_identity_owner FOREIGN KEY (owner_sid) REFERENCES acl_sid (id)
);
CREATE SEQUENCE acl_object_identity_sequence START WITH 1 INCREMENT BY 1 NOMAXVALUE;
CREATE OR REPLACE TRIGGER acl_object_identity_id_trigger
	BEFORE INSERT ON acl_object_identity
	FOR EACH ROW
BEGIN
	SELECT acl_object_identity_sequence.nextval INTO :new.id FROM dual;
END;

CREATE TABLE acl_entry (
	id NUMBER(38) NOT NULL PRIMARY KEY,
	acl_object_identity NUMBER(38) NOT NULL,
	ace_order INTEGER NOT NULL,
	sid NUMBER(38) NOT NULL,
	mask INTEGER NOT NULL,
	granting NUMBER(1) NOT NULL CHECK (granting in (0, 1)),
	audit_success NUMBER(1) NOT NULL CHECK (audit_success in (0, 1)),
	audit_failure NUMBER(1) NOT NULL CHECK (audit_failure in (0, 1)),
	CONSTRAINT unique_acl_entry UNIQUE (acl_object_identity, ace_order),
	CONSTRAINT fk_acl_entry_object FOREIGN KEY (acl_object_identity) REFERENCES acl_object_identity (id),
	CONSTRAINT fk_acl_entry_acl FOREIGN KEY (sid) REFERENCES acl_sid (id)
);
CREATE SEQUENCE acl_entry_sequence START WITH 1 INCREMENT BY 1 NOMAXVALUE;
CREATE OR REPLACE TRIGGER acl_entry_id_trigger
	BEFORE INSERT ON acl_entry
	FOR EACH ROW
BEGIN
	SELECT acl_entry_sequence.nextval INTO :new.id FROM dual;
END;