Spring Security ACL使用Oracle資料庫的配置與資料庫指令碼

 

在Spring Security的官方文檔上面只給出了關於security ACL的hsql指令碼，但是在使用Oracle資料庫時spring沒有明確給出資料庫建立指令碼和配置說明，以下是自己在使用Oracle資料庫時使用到的sql指令碼和配置

sql指令碼如下

 

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

 

spring配置片段如下

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