spring-security/doc/xdocs/dbinit.txt

--- $Id$

--- Sample Hypersonic SQL compatible schema and data
---
--- All Acegi Security JDBC DAOs can be customised to use a different schema.
--- In addition, the Acegi Security JDBC DAOs do not even need to be used
--- with Acegi Security, and an entirely customised persistence strategy
--- can be employed via standard interfaces (eg in-memory, Hibernate etc).

SET IGNORECASE TRUE;

CREATE TABLE users (
	username VARCHAR(50) NOT NULL PRIMARY KEY,
	password VARCHAR(50) NOT NULL,
	enabled BIT NOT NULL
);

CREATE TABLE authorities (
	username VARCHAR(50) NOT NULL,
	authority VARCHAR(50) NOT NULL
);
CREATE UNIQUE INDEX ix_auth_username ON authorities ( username, authority );

ALTER TABLE authorities ADD CONSTRAINT fk_authorities_users foreign key (username) REFERENCES users(username);

INSERT INTO users VALUES ('marissa', 'koala', true);
INSERT INTO users VALUES ('dianne', 'emu', true);
INSERT INTO users VALUES ('scott', 'wombat', true);
INSERT INTO users VALUES ('peter', 'opal', false);

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');

--- Indexes auto created in HSQLDB for primary keys and unique columns

CREATE TABLE acl_object_identity (
     id BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
     object_identity VARCHAR_IGNORECASE(250) NOT NULL,
     parent_object BIGINT,
     acl_class VARCHAR_IGNORECASE(250) NOT NULL,
     CONSTRAINT unique_object_identity UNIQUE(object_identity),
     FOREIGN KEY (parent_object) REFERENCES acl_object_identity(id)
);

CREATE TABLE acl_permission (
     id BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 0)  NOT NULL PRIMARY KEY,
     acl_object_identity BIGINT NOT NULL,
     recipient VARCHAR_IGNORECASE(100) NOT NULL,
     mask INTEGER NOT NULL,
     CONSTRAINT unique_recipient UNIQUE(acl_object_identity, recipient),
     FOREIGN KEY (acl_object_identity) REFERENCES acl_object_identity(id)
);

--- Mask integer 0  = no permissions
--- Mask integer 1  = administer
--- Mask integer 2  = read
--- Mask integer 6  = read and write permissions
--- Mask integer 14 = read and write and create permissions

---------------------------------------------------------------------
--- *** INHERITED RIGHTS FOR DIFFERENT INSTANCES AND RECIPIENTS *** 
--- INSTANCE  RECIPIENT         PERMISSION(S) (COMMENT #INSTANCE)
---------------------------------------------------------------------
---    1      ROLE_SUPERVISOR   Administer
---    2      ROLE_SUPERVISOR   None (overrides parent #1)
---           marissa           Read
---    3      ROLE_SUPERVISOR   Administer (from parent #1)
---           scott             Read, Write, Create
---    4      ROLE_SUPERVISOR   Administer (from parent #1)
---    5      ROLE_SUPERVISOR   Administer (from parent #3)
---           scott             Read, Write, Create (from parent #3)
---    6      ROLE_SUPERVISOR   Administer (from parent #3)
---           scott             Administer (overrides parent #3)
---------------------------------------------------------------------

INSERT INTO acl_object_identity VALUES (1, 'org.acegisecurity.acl.DomainObject:1', null, 'org.acegisecurity.acl.basic.SimpleAclEntry');
INSERT INTO acl_object_identity VALUES (2, 'org.acegisecurity.acl.DomainObject:2', 1, 'org.acegisecurity.acl.basic.SimpleAclEntry');
INSERT INTO acl_object_identity VALUES (3, 'org.acegisecurity.acl.DomainObject:3', 1, 'org.acegisecurity.acl.basic.SimpleAclEntry');
INSERT INTO acl_object_identity VALUES (4, 'org.acegisecurity.acl.DomainObject:4', 1, 'org.acegisecurity.acl.basic.SimpleAclEntry');
INSERT INTO acl_object_identity VALUES (5, 'org.acegisecurity.acl.DomainObject:5', 3, 'org.acegisecurity.acl.basic.SimpleAclEntry');
INSERT INTO acl_object_identity VALUES (6, 'org.acegisecurity.acl.DomainObject:6', 3, 'org.acegisecurity.acl.basic.SimpleAclEntry');

INSERT INTO acl_permission VALUES (null, 1, 'ROLE_SUPERVISOR', 1);
INSERT INTO acl_permission VALUES (null, 2, 'ROLE_SUPERVISOR', 0);
INSERT INTO acl_permission VALUES (null, 2, 'marissa', 2);
INSERT INTO acl_permission VALUES (null, 3, 'scott', 14);
INSERT INTO acl_permission VALUES (null, 6, 'scott', 1);