spring-security/hsqldb/acegisecurity.script

CREATE TABLE USERS(USERNAME VARCHAR_IGNORECASE(50) NOT NULL PRIMARY KEY,PASSWORD VARCHAR_IGNORECASE(50) NOT NULL,ENABLED BOOLEAN NOT NULL)
CREATE TABLE AUTHORITIES(USERNAME VARCHAR_IGNORECASE(50) NOT NULL,AUTHORITY VARCHAR_IGNORECASE(50) NOT NULL,CONSTRAINT FK_AUTHORITIES_USERS FOREIGN KEY(USERNAME) REFERENCES USERS(USERNAME))
CREATE UNIQUE INDEX IX_AUTH_USERNAME ON AUTHORITIES(USERNAME,AUTHORITY)
CREATE TABLE ACL_OBJECT_IDENTITY(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0)  NOT NULL PRIMARY KEY,OBJECT_IDENTITY VARCHAR_IGNORECASE(250) NOT NULL,PARENT_OBJECT INTEGER,ACL_CLASS VARCHAR_IGNORECASE(250) NOT NULL,CONSTRAINT UNIQUE_OBJECT_IDENTITY UNIQUE(OBJECT_IDENTITY),CONSTRAINT SYS_FK_3 FOREIGN KEY(PARENT_OBJECT) REFERENCES ACL_OBJECT_IDENTITY(ID))
CREATE TABLE ACL_PERMISSION(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0)  NOT NULL PRIMARY KEY,ACL_OBJECT_IDENTITY INTEGER NOT NULL,RECIPIENT VARCHAR_IGNORECASE(100) NOT NULL,MASK INTEGER NOT NULL,CONSTRAINT UNIQUE_RECIPIENT UNIQUE(ACL_OBJECT_IDENTITY,RECIPIENT),CONSTRAINT SYS_FK_7 FOREIGN KEY(ACL_OBJECT_IDENTITY) REFERENCES ACL_OBJECT_IDENTITY(ID))
SET IGNORECASE TRUE
CREATE USER SA PASSWORD "" ADMIN
INSERT INTO USERS VALUES('dianne','emu',TRUE)
INSERT INTO USERS VALUES('marissa','koala',TRUE)
INSERT INTO USERS VALUES('peter','opal',FALSE)
INSERT INTO USERS VALUES('scott','wombat',TRUE)
INSERT INTO AUTHORITIES VALUES('marissa','ROLE_TELLER')
INSERT INTO AUTHORITIES VALUES('marissa','ROLE_SUPERVISOR')
INSERT INTO AUTHORITIES VALUES('dianne','ROLE_TELLER')
INSERT INTO AUTHORITIES VALUES('scott','ROLE_TELLER')
INSERT INTO AUTHORITIES VALUES('peter','ROLE_TELLER')
INSERT INTO ACL_OBJECT_IDENTITY VALUES(1,'net.sf.acegisecurity.acl.DomainObject:1',NULL,'net.sf.acegisecurity.acl.basic.SimpleAclEntry')
INSERT INTO ACL_OBJECT_IDENTITY VALUES(2,'net.sf.acegisecurity.acl.DomainObject:2',1,'net.sf.acegisecurity.acl.basic.SimpleAclEntry')
INSERT INTO ACL_OBJECT_IDENTITY VALUES(3,'net.sf.acegisecurity.acl.DomainObject:3',1,'net.sf.acegisecurity.acl.basic.SimpleAclEntry')
INSERT INTO ACL_OBJECT_IDENTITY VALUES(4,'net.sf.acegisecurity.acl.DomainObject:4',1,'net.sf.acegisecurity.acl.basic.SimpleAclEntry')
INSERT INTO ACL_OBJECT_IDENTITY VALUES(5,'net.sf.acegisecurity.acl.DomainObject:5',3,'net.sf.acegisecurity.acl.basic.SimpleAclEntry')
INSERT INTO ACL_OBJECT_IDENTITY VALUES(6,'net.sf.acegisecurity.acl.DomainObject:6',3,'net.sf.acegisecurity.acl.basic.SimpleAclEntry')
INSERT INTO ACL_PERMISSION VALUES(0,1,'ROLE_SUPERVISOR',1)
INSERT INTO ACL_PERMISSION VALUES(1,2,'ROLE_SUPERVISOR',0)
INSERT INTO ACL_PERMISSION VALUES(2,2,'marissa',2)
INSERT INTO ACL_PERMISSION VALUES(3,3,'scott',14)
INSERT INTO ACL_PERMISSION VALUES(4,6,'scott',1)