Spring Security ACL uses Oracle Database Configuration and database scripts

 

In the official spring security documents, only the security ACL hsql script is provided. However, spring does not explicitly provide the database creation script and configuration instructions when using Oracle databases, the following are the SQL scripts and configurations used when you use the Oracle database:

The SQL script is as follows:

 

Sequence <br/> -- create sequences <br/> sequence <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> comment <br/> -- acl_class table <br/> comment <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> comment <br/> -- acl_entry table <br/> comment <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> condition <br/> -- acl_object_identity table <br/> condition <br/> Create Table "acl_object_identity" (<br/> "ID" number () not null, <br/> "object_id_class" number (19,0) not null, <br/> "object_id_identity" number (19,0) not null, <br/> "parent_object" number (), <br/> "owner_sid" number () not null, <br/> "entries_inheriting" number () 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> comment <br/> -- acl_sid table <br/> comment <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> constraint <br/> -- relationships <br/> constraint </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> condition <br/> -- triggers <br/> condition <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: 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: 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: 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: new. ID from dual; <br/> end; <br/>/<br/>

 

The spring configuration snippets are as follows:

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