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>