Spring Security ACL使用Oracle數(shù)據(jù)庫(kù)的配置與數(shù)據(jù)庫(kù)腳本

在Spring Security的官方文檔上面只給出了關(guān)于security ACL的hsql腳本，但是在使用Oracle數(shù)據(jù)庫(kù)時(shí)spring沒有明確給出數(shù)據(jù)庫(kù)創(chuàng)建腳本和配置說(shuō)明，以下是自己在使用Oracle數(shù)據(jù)庫(kù)時(shí)使用到的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> 

服務(wù)器租用/服務(wù)器托管中國(guó)五強(qiáng)！虛擬主機(jī)域名注冊(cè)頂級(jí)提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]