HSQLDB example source code file (TestSelfMultiGrants.txt)

This example HSQLDB source code file (TestSelfMultiGrants.txt) is included in the DevDaily.com "Java Source Code Warehouse" project. The intent of this project is to help you "Learn Java by Example" ^TM.

Java - HSQLDB tags/keywords

connect, connect, create, drop, if, insert, into, password, password, table, table, user, user, values

The HSQLDB TestSelfMultiGrants.txt source code

/*s*/DROP USER peon1;
/*s*/DROP USER peon2;
/*s*/DROP USER peon3;
/*s*/DROP ROLE r1;
/*s*/DROP ROLE r2;
/*s*/DROP ROLE r3;
/*s*/DROP ROLE r12;
DROP TABLE t1 IF exists;
DROP TABLE t2 IF exists;
DROP TABLE t3 IF exists;
DROP TABLE t4 IF exists;
CREATE TABLE t1(i int);
CREATE TABLE t2(i int);
CREATE TABLE t3(i int);
CREATE TABLE t4(i int);
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(2);
INSERT INTO t3 VALUES(3);
INSERT INTO t4 VALUES(4);
COMMIT;
CREATE USER peon1 PASSWORD password;
CREATE USER peon2 PASSWORD password;
CREATE USER peon3 PASSWORD password;

/*u0*/CREATE ROLE r1;
/*u0*/CREATE ROLE r2;
/*u0*/CREATE ROLE r3;
/*u0*/CREATE ROLE r12;
/*u0*/GRANT ALL ON t1 TO r1;
/*u0*/GRANT ALL ON t1 TO r12;
/*u0*/GRANT ALL ON t2 TO r2;
/*u0*/GRANT ALL ON t2 TO r12;
/*u0*/GRANT ALL ON t3 TO r3;

-- Can't mix right-grants and role-grants
/*e*/GRANT r1, SELECT ON t1 TO peon1;
/*e*/GRANT SELECT ON t1, r1 TO peon1;

/*u0*/GRANT SELECT, INSERT ON t1 TO peon1;
/*u0*/GRANT r2, r3 TO peon2;
/*u0*/GRANT r12 TO peon3;

CONNECT USER peon1 PASSWORD password;
/*c1*/SELECT * FROM t1;
/*e*/SELECT * FROM t2;
/*e*/SELECT * FROM t3;
/*e*/SELECT * FROM t4;
CONNECT USER peon2 PASSWORD password;
/*e*/SELECT * FROM t1;
/*c1*/SELECT * FROM t2;
/*c1*/SELECT * FROM t3;
/*e*/SELECT * FROM t4;
CONNECT USER peon3 PASSWORD password;
/*c1*/SELECT * FROM t1;
/*c1*/SELECT * FROM t2;
/*e*/SELECT * FROM t3;
/*e*/SELECT * FROM t4;
CONNECT USER sa PASSWORD "";
/*u0*/GRANT r2 TO peon1;
/*u0*/GRANT r3 TO r12;  -- r12 held by peon3.  Nest r3 into it too.
/*u0*/GRANT SELECT ON t1 TO peon2;
CONNECT USER peon1 PASSWORD password;
/*c1*/SELECT * FROM t1;
/*c1*/SELECT * FROM t2;
/*e*/SELECT * FROM t3;
/*e*/SELECT * FROM t4;
CONNECT USER peon2 PASSWORD password;
/*c1*/SELECT * FROM t1;
/*c1*/SELECT * FROM t2;
/*c1*/SELECT * FROM t3;
/*e*/SELECT * FROM t4;
CONNECT USER peon3 PASSWORD password;
/*c1*/SELECT * FROM t1;
/*c1*/SELECT * FROM t2;
/*c1*/SELECT * FROM t3;
/*e*/SELECT * FROM t4;

-- Access to t3 has been granted only through r3, either directly or
-- indirectly (the latter through nesting r3 in another role).
CONNECT USER sa PASSWORD "";
DROP ROLE r3;
CONNECT USER peon1 PASSWORD password;
/*c1*/SELECT * FROM t1;
/*c1*/SELECT * FROM t2;
/*e*/SELECT * FROM t3;
/*e*/SELECT * FROM t4;
CONNECT USER peon2 PASSWORD password;
/*c1*/SELECT * FROM t1;
/*c1*/SELECT * FROM t2;
/*e*/SELECT * FROM t3;
/*e*/SELECT * FROM t4;
CONNECT USER peon3 PASSWORD password;
/*c1*/SELECT * FROM t1;
/*c1*/SELECT * FROM t2;
/*e*/SELECT * FROM t3;
/*e*/SELECT * FROM t4;