HSQLDB example source code file (TestSelfRightCaching.txt)

This example HSQLDB source code file (TestSelfRightCaching.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, from, grant, on, on, password, password, public, revoke, select, to, user, user

The HSQLDB TestSelfRightCaching.txt source code

-- Rights Map and Adminstrative Right Caching tests

DROP TABLE m IF EXISTS;
CREATE TABLE m(i int);
INSERT INTO m VALUES(11);
CREATE USER cani PASSWORD cani;
CONNECT USER cani PASSWORD cani;
/*e*/SELECT * FROM m;

-- Sanity check of individual grants for user and for public
CONNECT USER sa PASSWORD "";
GRANT SELECT ON m TO PUBLIC;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM PUBLIC;
CONNECT USER cani PASSWORD cani;
/*e*/SELECT * FROM m;

-- Dual grants, but with SELECTs in between (which will rebuild the caches)
CONNECT USER sa PASSWORD "";
GRANT SELECT ON m TO cani;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
GRANT SELECT ON m TO PUBLIC;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM PUBLIC;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM cani;
CONNECT USER cani PASSWORD cani;
/*e*/SELECT * FROM m;

-- Dual grants.  No cache build until both GRANTS added.
CONNECT USER sa PASSWORD "";
GRANT SELECT ON m TO PUBLIC;
GRANT SELECT ON m TO cani;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM PUBLIC;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM cani;
CONNECT USER cani PASSWORD cani;
/*e1*/SELECT * FROM m;

-- Ditto, in different sequence.
CONNECT USER sa PASSWORD "";
GRANT SELECT ON m TO cani;
GRANT SELECT ON m TO PUBLIC;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM PUBLIC;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM cani;
CONNECT USER cani PASSWORD cani;
/*e1*/SELECT * FROM m;

-- ... different sequence
CONNECT USER sa PASSWORD "";
GRANT SELECT ON m TO PUBLIC;
GRANT SELECT ON m TO cani;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM cani;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM PUBLIC;
CONNECT USER cani PASSWORD cani;
/*e1*/SELECT * FROM m;

-- Ditto, in different sequence.
CONNECT USER sa PASSWORD "";
GRANT SELECT ON m TO cani;
GRANT SELECT ON m TO PUBLIC;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM cani;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM PUBLIC;
CONNECT USER cani PASSWORD cani;
/*e1*/SELECT * FROM m;


-- REPEAT SAME WITH A NAMED ROLE INSTEAD OF PUBLIC
CONNECT USER sa PASSWORD "";
CREATE ROLE sesamerole;
GRANT sesamerole TO cani;

-- Sanity check of individual grants for user and for sesamerole
CONNECT USER sa PASSWORD "";
GRANT SELECT ON m TO sesamerole;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM sesamerole;
CONNECT USER cani PASSWORD cani;
/*e*/SELECT * FROM m;

-- Dual grants, but with SELECTs in between (which will rebuild the caches)
CONNECT USER sa PASSWORD "";
GRANT SELECT ON m TO cani;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
GRANT SELECT ON m TO sesamerole;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM sesamerole;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM cani;
CONNECT USER cani PASSWORD cani;
/*e*/SELECT * FROM m;

-- Dual grants.  No cache build until both GRANTS added.
CONNECT USER sa PASSWORD "";
GRANT SELECT ON m TO sesamerole;
GRANT SELECT ON m TO cani;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM sesamerole;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM cani;
CONNECT USER cani PASSWORD cani;
/*e1*/SELECT * FROM m;

-- Ditto, in different sequence.
CONNECT USER sa PASSWORD "";
GRANT SELECT ON m TO cani;
GRANT SELECT ON m TO sesamerole;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM sesamerole;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM cani;
CONNECT USER cani PASSWORD cani;
/*e1*/SELECT * FROM m;

-- ... different sequence
CONNECT USER sa PASSWORD "";
GRANT SELECT ON m TO sesamerole;
GRANT SELECT ON m TO cani;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM cani;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM sesamerole;
CONNECT USER cani PASSWORD cani;
/*e1*/SELECT * FROM m;

-- Ditto, in different sequence.
CONNECT USER sa PASSWORD "";
GRANT SELECT ON m TO cani;
GRANT SELECT ON m TO sesamerole;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM cani;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM sesamerole;
CONNECT USER cani PASSWORD cani;
/*e1*/SELECT * FROM m;