HSQLDB example source code file (TestSelfGrantees.txt)

This example HSQLDB source code file (TestSelfGrantees.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, grant, grant, insert, on, password, password, roles, select, to, user, user

The HSQLDB TestSelfGrantees.txt source code

/*e*/CREATE USER public PASSWORD public ADMIN
/*e*/CREATE USER _SYSTEM PASSWORD system ADMIN
/*e*/CREATE USER public PASSWORD public
/*e*/CREATE USER _SYSTEM PASSWORD system
CREATE USER blaine PASSWORD blaine;
CREATE USER blaineadm PASSWORD blaineadm ADMIN;
CREATE USER debbie PASSWORD debbie;
DROP TABLE public.t1 IF EXISTS;
CREATE TABLE public.t1 (i int);
INSERT INTO public.t1 VALUES (1);
CREATE USER us3 PASSWORD us3;

-----------------------------------------------------------------------
--                        RESERVED USERS and ROLES
-- Test public grants
DROP TABLE t2 IF EXISTS;
CREATE TABLE public.t2 (i int);
INSERT INTO public.t2 VALUES (1);
CONNECT USER us3 PASSWORD us3;
/*e*/SELECT * FROM t2;
CONNECT USER sa PASSWORD "";
GRANT SELECT ON t2 TO public;
CONNECT USER us3 PASSWORD us3;
/*c1*/SELECT * FROM t2;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON t2 FROM public;
CONNECT USER us3 PASSWORD us3;
/*e*/SELECT * FROM t2;
CONNECT USER sa PASSWORD "";

-- Prohibit adding/dropping reserved Roles and Users
-- _SYSTEM role?
/*e*/DROP ROLE dba;
-- Spec says can't create a user or role called "public"
/*e*/DROP USER public password x;
/*e*/DROP USER _system password x;
/*e*/DROP USER dba password x;
/*e*/CREATE ROLE public;
/*e*/CREATE ROLE _system;
/*e*/CREATE ROLE dba;
-- We prohibit change the built-in permissions for the DBA group, since
-- the role is not persisted.

-- Prohibit grants/revokes for _SYSTEM, DBA
/*e*/GRANT ALL ON public.t1 TO dba;
/*e*/GRANT ALL ON public.t1 TO _system;
/*e*/REVOKE ALL ON public.t1 TO dba;
/*e*/REVOKE ALL ON public.t1 TO _system;
-----------------------------------------------------------------------

-- Roles and Users share a namespace
CREATE USER conflict1 PASSWORD conflict1;
/*e*/CREATE ROLE conflict1;
/*u0*/CREATE ROLE conflict2;
/*e*/CREATE USER conflict2 PASSWORD conflict2;
-- Can grant a Role but not a user
/*e*/GRANT us3 TO conflict2;

-- Grant and revoke lists for users and roles
GRANT SELECT, UPDATE, DELETE, INSERT ON public.t2 TO conflict1;
GRANT SELECT, UPDATE, DELETE, INSERT ON public.t2 TO conflict2;
REVOKE SELECT, UPDATE, DELETE, INSERT ON public.t2 FROM conflict1;
REVOKE SELECT, UPDATE, DELETE, INSERT ON public.t2 FROM conflict2;

-- Wrong right names
/*e*/GRANT SELECT, TABLE, COLUMN ON public.t2 TO conflict1;
/*e*/REVOKE SELECT, TABLE, COLUMN ON public.t2 FROM conflict1;

-- Roles are not schemas
/*e*/CREATE TABLE dba.x1 (i int);

-- Test global (non-object) permissions of DBA Role
GRANT ALL ON public.t1 TO blaine;
CONNECT USER blaineadm PASSWORD blaineadm;
/*u0*/SET TABLE public.t1 READONLY true;
CONNECT USER blaine PASSWORD blaine;
/*e*/INSERT INTO public.t1 VALUES(1);
/*e*/SET TABLE public.t1 READONLY true;
/*e*/CREATE USER user1 password user1;
/*e*/GRANT dba TO debbie;
CONNECT USER blaineadm PASSWORD blaineadm;
/*u0*/GRANT dba TO blaine;
CONNECT USER blaine PASSWORD blaine;
/*e*/INSERT INTO public.t1 VALUES(1);
/*u0*/SET TABLE public.t1 READONLY false;
/*u1*/INSERT INTO public.t1 VALUES(1);
/*u0*/CREATE USER user1 password user1;
/*u0*/GRANT dba TO debbie;
CONNECT USER sa PASSWORD "";
/*u0*/REVOKE dba FROM blaine;
CONNECT USER blaine PASSWORD blaine;
/*e*/SET TABLE public.t1 READONLY true;
/*e*/CREATE USER user2 password user2;
/*e*/REVOKE dba FROM debbie;

-- Test using non-existent roles and re-creating existing roles.
CONNECT USER sa PASSWORD "";
CREATE USER us1 PASSWORD us1;
CREATE USER us2 PASSWORD us2;
/*e*/DROP ROLE r1;
/*u0*/CREATE ROLE r1;
/*u0*/GRANT r1 TO us1;
/*e*/GRANT r1 TO us1;
/*u0*/REVOKE r1 FROM us1;
/*e*/REVOKE r1 FROM us1;
/*e*/REVOKE r2 FROM us1;
/*u0*/DROP ROLE r1;

-- Basics
CONNECT USER blaineadm PASSWORD blaineadm;
/*e*/GRANT ALL ON t1 TO r1;
/*u0*/CREATE ROLE r1;
/*e*/GRANT r1 TO r1;
-- us1: no privs
CONNECT USER us1 PASSWORD us1;
/*e*/SELECT * FROM public.t1;
/*e*/CREATE TABLE us1t1 (i int);
CONNECT USER sa PASSWORD "";
GRANT r1 TO us1;
-- us1: no privs
CONNECT USER us1 PASSWORD us1;
/*e*/SELECT * FROM public.t1;
/*e*/CREATE TABLE us1t1 (i int);
CONNECT USER sa PASSWORD "";
GRANT SELECT ON t1 TO r1;
-- us1: t1 rights via role r1
CONNECT USER us1 PASSWORD us1;
/*c2*/SELECT * FROM public.t1;
/*e*/CREATE TABLE us1t1 (i int);
CONNECT USER sa PASSWORD "";
GRANT dba TO us1;
-- us1: DBA + t1 rights via role r1
CONNECT USER us1 PASSWORD us1;
/*c2*/SELECT * FROM public.t1;
/*u0*/CREATE TABLE us1t1 (i int);
/*u0*/DROP TABLE us1t1;
CONNECT USER sa PASSWORD "";
/*u0*/REVOKE dba FROM us1;
-- us1: t1 rights via role r1
CONNECT USER us1 PASSWORD us1;
/*c2*/SELECT * FROM public.t1;
/*e*/CREATE TABLE us1t1 (i int);
CONNECT USER sa PASSWORD "";
/*u0*/REVOKE r1 FROM us1;
-- us1: no privs
CONNECT USER us1 PASSWORD us1;
/*e*/SELECT * FROM public.t1;
/*e*/CREATE TABLE us1t1 (i int);
CONNECT USER sa PASSWORD "";
/*u0*/GRANT r1 TO us1;
-- us1: t1 rights via role r1
CONNECT USER us1 PASSWORD us1;
/*c2*/SELECT * FROM public.t1;
/*e*/CREATE TABLE us1t1 (i int);
CONNECT USER sa PASSWORD "";
/*u0*/REVOKE ALL ON t1 FROM r1;
-- us1: no privs
CONNECT USER us1 PASSWORD us1;
/*e*/SELECT * FROM public.t1;
/*e*/CREATE TABLE us1t1 (i int);
CONNECT USER sa PASSWORD "";
/*u0*/GRANT ALL ON t1 TO r1;
-- us1: t1 rights via role r1
CONNECT USER us1 PASSWORD us1;
/*c2*/SELECT * FROM public.t1;
/*e*/CREATE TABLE us1t1 (i int);
CONNECT USER sa PASSWORD "";
/*u0*/DROP ROLE r1;
-- us1: no privs
CONNECT USER us1 PASSWORD us1;
/*e*/SELECT * FROM public.t1;
/*e*/CREATE TABLE us1t1 (i int);
CONNECT USER sa PASSWORD '';
/*u0*/GRANT SELECT ON t1 TO us1;
-- us1: t1 rights via role table right
CONNECT USER us1 PASSWORD us1;
/*c2*/SELECT * FROM public.t1;
/*e*/CREATE TABLE us1t1 (i int);
CONNECT USER sa PASSWORD "";
/*u0*/REVOKE ALL ON t1 FROM us1;

-- Role nesting
CREATE ROLE r1;
CONNECT USER us1 PASSWORD us1;
/*e*/CREATE TABLE us1t1 (i int);
CONNECT USER sa PASSWORD "";
GRANT r1 TO us1;
CONNECT USER us1 PASSWORD us1;
/*e*/CREATE TABLE us1t1 (i int);
CONNECT USER sa PASSWORD "";
GRANT dba TO r1;
CONNECT USER us1 PASSWORD us1;
/*u0*/CREATE TABLE us1t1 (i int);
/*u0*/DROP TABLE us1t1;
CONNECT USER sa PASSWORD "";
REVOKE dba FROM r1;