Spring Security ACL使用Oracle数据库的配置与数据库脚本

在Spring Security的官方文档上面只给出了关于security ACL的hsql脚本，但是在使用Oracle数据库时spring没有明确给出数据库创建脚本和配置说明，以下是自己在使用Oracle数据库时使用到的sql脚本和配置sql脚本如下

--------------------------------------------------------
-- Create sequences
--------------------------------------------------------
CREATE SEQUENCE "ACL_CLASS_SEQ"
INCREMENT BY 1
MAXVALUE 9999999999999999999999999999
START WITH 1
CACHE 20
NOORDER
NOCYCLE;
CREATE SEQUENCE "ACL_ENTRY_SEQ"
INCREMENT BY 1
MAXVALUE 9999999999999999999999999999
START WITH 1
CACHE 20
NOORDER
NOCYCLE;
CREATE SEQUENCE "ACL_OBJECT_IDENTITY_SEQ"
INCREMENT BY 1
MAXVALUE 9999999999999999999999999999
START WITH 1
CACHE 20
NOORDER
NOCYCLE;
CREATE SEQUENCE "ACL_SID_SEQ"
INCREMENT BY 1
MAXVALUE 9999999999999999999999999999
START WITH 1
CACHE 20
NOORDER
NOCYCLE;
--------------------------------------------------------
-- ACL_CLASS Table
--------------------------------------------------------
CREATE TABLE "ACL_CLASS" （
"ID" NUMBER（19,0） NOT NULL,
"CLASS" VARCHAR2（100） NOT NULL,
PRIMARY KEY （"ID"）,
CONSTRAINT "ACL_CLASS_CLASS_UQ" UNIQUE （"CLASS"）
）TABLESPACE &TSDATA;
--------------------------------------------------------
-- ACL_ENTRY Table
--------------------------------------------------------
CREATE TABLE "ACL_ENTRY" （
"ID" NUMBER（19,0） NOT NULL,
"ACL_OBJECT_IDENTITY" NUMBER（19,0） NOT NULL,
"ACE_ORDER" NUMBER（19,0） NOT NULL,
"SID" NUMBER（19,0） NOT NULL,
"MASK" NUMBER（19,0） NOT NULL,
"GRANTING" NUMBER（1,0） NOT NULL,
"AUDIT_SUCCESS" NUMBER（1,0） NOT NULL,
"AUDIT_FAILURE" NUMBER（1,0） NOT NULL,
PRIMARY KEY （"ID"）,
CONSTRAINT "ACL_ENTRY_IDENT_ORDER_UQ" UNIQUE （"ACL_OBJECT_IDENTITY", "ACE_ORDER"）
）TABLESPACE &TSDATA;
ALTER TABLE "ACL_ENTRY" ADD CONSTRAINT "ACL_ENTRY_GRANTING_CK"
CHECK （"GRANTING" in （1,0））;
ALTER TABLE "ACL_ENTRY" ADD CONSTRAINT "ACL_ENTRY_AUDIT_SUCCESS_CK"
CHECK （"AUDIT_SUCCESS" in （1,0））;
ALTER TABLE "ACL_ENTRY" ADD CONSTRAINT "ACL_ENTRY_AUDIT_FAILURE_CK"
CHECK （"AUDIT_FAILURE" in （1,0））;
--------------------------------------------------------
-- ACL_OBJECT_IDENTITY Table
--------------------------------------------------------
CREATE TABLE "ACL_OBJECT_IDENTITY" （
"ID" NUMBER（19,0） NOT NULL,
"OBJECT_ID_CLASS" NUMBER（19,0） NOT NULL,
"OBJECT_ID_IDENTITY" NUMBER（19,0） NOT NULL,
"PARENT_OBJECT" NUMBER（19,0）,
"OWNER_SID" NUMBER（19,0） NOT NULL,
"ENTRIES_INHERITING" NUMBER（1,0） NOT NULL,
PRIMARY KEY （"ID"）,
CONSTRAINT "ACL_OBJ_ID_CLASS_IDENT_UQ" UNIQUE （"OBJECT_ID_CLASS", "OBJECT_ID_IDENTITY"）
）TABLESPACE &TSDATA;
ALTER TABLE "ACL_OBJECT_IDENTITY" ADD CONSTRAINT "ACL_OBJ_ID_ENTRIES_CK"
CHECK （"ENTRIES_INHERITING" in （1,0））;
--------------------------------------------------------
-- ACL_SID Table
--------------------------------------------------------
CREATE TABLE "ACL_SID" （
"ID" NUMBER（19,0） NOT NULL,
"PRINCIPAL" NUMBER（1,0） NOT NULL,
"SID" VARCHAR2（100） NOT NULL,
PRIMARY KEY （"ID"）,
CONSTRAINT "ACL_SID_PRINCIPAL_SID_UQ" UNIQUE （"SID", "PRINCIPAL"）
）TABLESPACE &TSDATA;
ALTER TABLE "ACL_SID" ADD CONSTRAINT "ACL_SID_PRINCIPAL_CK"
CHECK （"PRINCIPAL" in （1,0））;
--------------------------------------------------------
-- Relationships
--------------------------------------------------------
ALTER TABLE "ACL_ENTRY" ADD CONSTRAINT "FK_ACL_ENTRY_ACL_OBJECT_ID"
FOREIGN KEY （"ACL_OBJECT_IDENTITY"）
REFERENCES "ACL_OBJECT_IDENTITY" （"ID"）;
ALTER TABLE "ACL_ENTRY" ADD CONSTRAINT "FK_ACL_ENTRY_SID"
FOREIGN KEY （"SID"）
REFERENCES "ACL_SID" （"ID"）;
ALTER TABLE "ACL_OBJECT_IDENTITY" ADD CONSTRAINT "FK_ACL_OBJ_ID_CLASS"
FOREIGN KEY （"OBJECT_ID_CLASS"）
REFERENCES "ACL_CLASS" （"ID"）;
ALTER TABLE "ACL_OBJECT_IDENTITY" ADD CONSTRAINT "FK_ACL_OBJ_ID_PARENT"
FOREIGN KEY （"PARENT_OBJECT"）
REFERENCES "ACL_OBJECT_IDENTITY" （"ID"）;
ALTER TABLE "ACL_OBJECT_IDENTITY" ADD CONSTRAINT "FK_ACL_OBJ_ID_SID"
FOREIGN KEY （"OWNER_SID"）
REFERENCES "ACL_SID" （"ID"）;
--------------------------------------------------------
-- Triggers
--------------------------------------------------------
CREATE OR REPLACE TRIGGER "ACL_CLASS_ID"
BEFORE INSERT ON ACL_CLASS
FOR EACH ROW
BEGIN
SELECT ACL_CLASS_SEQ.NEXTVAL INTO :new.id FROM dual;
END;
/
CREATE OR REPLACE TRIGGER "ACL_ENTRY_ID"
BEFORE INSERT ON ACL_ENTRY
FOR EACH ROW
BEGIN
SELECT ACL_ENTRY_SEQ.NEXTVAL INTO :new.id FROM dual;
END;
/
CREATE OR REPLACE TRIGGER "ACL_OBJECT_IDENTITY_ID"
BEFORE INSERT ON ACL_OBJECT_IDENTITY
FOR EACH ROW
BEGIN
SELECT ACL_OBJECT_IDENTITY_SEQ.NEXTVAL INTO :new.id FROM dual;
END;
/
CREATE OR REPLACE TRIGGER "ACL_SID_ID"
BEFORE INSERT ON ACL_SID
FOR EACH ROW
BEGIN
SELECT ACL_SID_SEQ.NEXTVAL INTO :new.id FROM dual;
END;
/

spring配置片段如下

<beans:bean id="aclService" class="org.springframework.security.acls.jdbc.JdbcMutableAclService">
<beans:constructor-arg ref="dataSource" />
<beans:constructor-arg ref="lookupStrategy" />
<beans:constructor-arg ref="aclCache" />
<beans:property name="classIdentityQuery" value="select ACL_CLASS_SEQ.currval from dual"/>
<beans:property name="sidIdentityQuery" value="select ACL_SID_SEQ.currval from dual"/>
</beans:bean>

Spring Security ACL使用MySQL配置与数据库脚本Oracle 10G RAC数据库日志报错LMS 0: 8069 GCS shadows traversed, 4001 replayed相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）