在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>
