|
HSQLDB example source code file (TestSelfGrantees.txt)
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; Other HSQLDB examples (source code examples)Here is a short list of links related to this HSQLDB TestSelfGrantees.txt source code file: |
... this post is sponsored by my books ... | |
#1 New Release! |
FP Best Seller |
Copyright 1998-2021 Alvin Alexander, alvinalexander.com
All Rights Reserved.
A percentage of advertising revenue from
pages under the /java/jwarehouse
URI on this website is
paid back to open source projects.