HSQLDB example source code file (TestSelfRoleNesting.txt)

This example HSQLDB source code file (TestSelfRoleNesting.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, create, drop, if, into, password, password, sa, table, user, user, values

The HSQLDB TestSelfRoleNesting.txt source code

-- First with a table grant

/*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*/GRANT r3 TO r2;
/*u0*/GRANT r2 TO r1;
-- Same role may be nested under the same user/role multiple times.
/*u0*/GRANT r3 TO r1;
/*u0*/REVOKE r3 FROM r1;
/*u0*/GRANT r1 TO peon1;
-- Circular nesting not allowed.
/*e*/GRANT r1 TO r3;

CONNECT USER peon1 PASSWORD password;
/*e*/SELECT * FROM t1;
CONNECT USER SA PASSWORD "";
/*u0*/GRANT SELECT on t1 TO r3;
CONNECT USER peon1 PASSWORD password;
/*c1*/SELECT * FROM t1;
CONNECT USER SA PASSWORD "";
/*u0*/REVOKE ALL ON t1 FROM r3;
CONNECT USER peon1 PASSWORD password;
/*e*/SELECT * FROM t1;
CONNECT USER SA PASSWORD "";
/*u0*/GRANT SELECT ON t1 TO r3;
CONNECT USER peon1 PASSWORD password;
/*c1*/SELECT * FROM t1;
CONNECT USER SA PASSWORD "";
/*u0*/DROP ROLE r3;
CONNECT USER peon1 PASSWORD password;
/*e*/SELECT * FROM t1;

-- Now with Admin priv
CONNECT USER SA PASSWORD "";
/*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;
CREATE TABLE t1(i int);
INSERT INTO t1 VALUES(1);
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*/GRANT r3 TO r2;
/*u0*/GRANT r2 TO r1;
/*u0*/GRANT r1 TO peon1;

CONNECT USER peon1 PASSWORD password;
/*e*/SET TABLE t1 READONLY false;
CONNECT USER SA PASSWORD "";
/*u0*/GRANT dba TO r3;
CONNECT USER peon1 PASSWORD password;
/*u0*/SET TABLE t1 READONLY false;
CONNECT USER SA PASSWORD "";
/*e*/REVOKE dba FROM r1;
/*e*/REVOKE dba FROM r2;
/*u0*/REVOKE dba FROM r3;
CONNECT USER peon1 PASSWORD password;
/*e*/SET TABLE t1 READONLY false;
CONNECT USER SA PASSWORD "";
/*u0*/GRANT dba TO r3;
CONNECT USER peon1 PASSWORD password;
/*u0*/SET TABLE t1 READONLY false;
CONNECT USER SA PASSWORD "";
/*e*/DROP ROLE dba;


-- Massively redundant privileges
CONNECT USER SA PASSWORD "";
DROP TABLE t1 IF exists;
CREATE TABLE t1(i int);
/*s*/DROP USER peon1;
/*s*/DROP ROLE r1;
/*s*/DROP ROLE r2;
/*s*/DROP ROLE r3;
/*s*/DROP ROLE r31;
/*s*/DROP ROLE r311;
/*u0*/CREATE ROLE r1;
/*u0*/CREATE ROLE r2;
/*u0*/CREATE ROLE r3;
/*u0*/CREATE ROLE r31;
/*u0*/CREATE ROLE r311;
/*u0*/GRANT r31 to r3;
/*u0*/GRANT dba TO r1;
/*u0*/GRANT dba TO r2;
/*u0*/GRANT dba TO r3;
/*u0*/GRANT dba TO r31;
/*u0*/GRANT dba TO r311;
/*u0*/GRANT r311 TO r31;
CREATE USER peon1 PASSWORD password ADMIN;
CONNECT USER sa PASSWORD "";
/*u0*/GRANT r1 TO peon1;
/*u0*/GRANT r2 TO peon1;
/*u0*/GRANT r3 TO peon1;
/*u0*/GRANT r31 TO peon1;
/*u0*/GRANT r311 TO peon1;
CONNECT USER peon1 PASSWORD password;
/*u0*/SET TABLE t1 READONLY false;
-- Start revoking, piecemeal
CONNECT USER sa PASSWORD "";
/*u0*/REVOKE dba FROM peon1;
CONNECT USER peon1 PASSWORD password;
/*u0*/SET TABLE t1 READONLY false;
CONNECT USER sa PASSWORD "";
/*u0*/REVOKE r1 FROM peon1;
CONNECT USER peon1 PASSWORD password;
/*u0*/SET TABLE t1 READONLY false;
CONNECT USER sa PASSWORD "";
/*u0*/DROP ROLE r2;
CONNECT USER peon1 PASSWORD password;
/*u0*/SET TABLE t1 READONLY false;
CONNECT USER sa PASSWORD "";
/*u0*/REVOKE r311 FROM peon1;
CONNECT USER peon1 PASSWORD password;
/*u0*/SET TABLE t1 READONLY false;
CONNECT USER sa PASSWORD "";
/*u0*/REVOKE r3 FROM peon1;
CONNECT USER peon1 PASSWORD password;
/*u0*/SET TABLE t1 READONLY false;
CONNECT USER sa PASSWORD "";
/*u0*/REVOKE r31 FROM peon1;
CONNECT USER peon1 PASSWORD password;
/*e*/SET TABLE t1 READONLY false;

-- Ditto for table privilege
CONNECT USER SA PASSWORD "";
DROP TABLE t1 IF exists;
CREATE TABLE t1(i int);
/*u1*/INSERT INTO t1 VALUES(1);
COMMIT;
/*s*/DROP USER peon1;
/*s*/DROP ROLE r1;
/*s*/DROP ROLE r2;
/*s*/DROP ROLE r3;
/*s*/DROP ROLE r31;
/*s*/DROP ROLE r311;
/*u0*/CREATE ROLE r1;
/*u0*/CREATE ROLE r2;
/*u0*/CREATE ROLE r3;
/*u0*/CREATE ROLE r31;
/*u0*/CREATE ROLE r311;
/*u0*/GRANT r31 to r3;
/*u0*/GRANT SELECT ON t1 TO r1;
/*u0*/GRANT SELECT ON t1 TO r2;
/*u0*/GRANT SELECT ON t1 TO r3;
/*u0*/GRANT SELECT ON t1 TO r31;
/*u0*/GRANT SELECT ON t1 TO r311;
/*u0*/GRANT r311 TO r31;
CREATE USER peon1 PASSWORD password;
CONNECT USER peon1 PASSWORD password;
/*e*/SELECT * FROM t1;
CONNECT USER sa PASSWORD "";
/*u0*/GRANT SELECT ON t1 TO peon1;
/*u0*/GRANT r1 TO peon1;
/*u0*/GRANT r2 TO peon1;
/*u0*/GRANT r3 TO peon1;
/*u0*/GRANT r31 TO peon1;
/*u0*/GRANT r311 TO peon1;
CONNECT USER peon1 PASSWORD password;
/*c1*/SELECT * FROM t1;
-- Start revoking, piecemeal
CONNECT USER sa PASSWORD "";
/*u0*/REVOKE SELECT ON t1 FROM peon1;
CONNECT USER peon1 PASSWORD password;
/*c1*/SELECT * FROM t1;
CONNECT USER sa PASSWORD "";
/*u0*/REVOKE r1 FROM peon1;
CONNECT USER peon1 PASSWORD password;
/*c1*/SELECT * FROM t1;
CONNECT USER sa PASSWORD "";
/*u0*/DROP ROLE r2;
CONNECT USER peon1 PASSWORD password;
/*c1*/SELECT * FROM t1;
CONNECT USER sa PASSWORD "";
/*u0*/REVOKE r311 FROM peon1;
CONNECT USER peon1 PASSWORD password;
/*c1*/SELECT * FROM t1;
CONNECT USER sa PASSWORD "";
/*u0*/REVOKE r3 FROM peon1;
CONNECT USER peon1 PASSWORD password;
/*c1*/SELECT * FROM t1;
CONNECT USER sa PASSWORD "";
/*u0*/REVOKE r31 FROM peon1;
CONNECT USER peon1 PASSWORD password;
/*e*/SELECT * FROM t1;

CONNECT USER sa PASSWORD "";
/*s*/DROP ROLE r1 CASCADE;
/*s*/DROP ROLE r2 CASCADE;
/*s*/DROP ROLE r3 CASCADE;
/*U0*/ Workaround for bug where DDL will not Commit
/*s*/CREATE TABLE bug_workaround (i int);
/*s*/INSERT INTO bug_workaround VALUES(1);
/*u0*/SHUTDOWN;