SET AUTOCOMMIT TRUE
/*s*/DROP USER blaine;
/*s*/DROP USER debbie;
/*s*/DROP SCHEMA bschema cascade;
/*s*/DROP SCHEMA dschema cascade;

-- Test persistence to .log file of personal schemas.
/*u0*/CREATE TABLE schema_a.t1(i int);
/*u0*/DROP SCHEMA schema_a CASCADE;
/*u0*/DROP SCHEMA schema_b;
/*e*/DROP SCHEMA schema_z;
/*u0*/DROP ROLE r1;
/*u0*/DROP USER owner1;

-- Schema authorization of individual User
-- Test that non-DBA schema owners can CREATE/SELECT/INSERT objects in their
-- own schema but not in somebody else's schema (assuming explicit object
-- rights have not been granted, of course).
/*u0*/create table public.ptable(i int);
/*u1*/insert into public.ptable values(9);
/*u0*/CREATE INDEX public.pindex ON public.ptable(i);
/*u0*/CREATE SEQUENCE public.psequence;
/*u0*/create user blaine password "blaine";
/*u0*/GRANT CHANGE_AUTHORIZATION TO blaine;
/*u0*/create schema bschema authorization blaine;
/*u0*/create table bschema.b1(i int);
/*c0*/select * from bschema.b1;
/*u0*/connect user blaine password "blaine";
/*c0*/select * from bschema.b1;
/*u1*/insert into bschema.b1 values(3);
/*c1*/select * from bschema.b1;
/*u0*/connect user sa password "";
/*u0*/create user debbie password debbie;
/*u0*/GRANT CHANGE_AUTHORIZATION TO debbie;
/*u0*/create schema dschema authorization debbie;
/*u0*/create table dschema.d1(i int);
/*u1*/insert into dschema.d1 values(5);
/*u0*/commit;
/*u0*/connect user debbie password debbie;
/*e*/select * from bschema.b1;
/*e*/create table bschema.b3(i int);
/*u0*/create table dschema.d2 (i int);
/*u0*/set schema dschema;
/*c1*/select * from d1;
/*c0*/select * from d2;
/*u0*/connect user blaine password "blaine";
/*u0*/set schema bschema;
/*e*/create table dschema.d4(i int);
/*u0*/create table b2(i int);
/*c1*/select * from b1;
/*u1*/insert into b1 values(5);
/*u1*/insert into b2 values (55);
/*c2*/select * from b1;
/*c1*/select * from b2;

-- Test of same-named-objects in different schemas.  As an Admin.
/*u0*/connect user sa password "";
/*u0*/CREATE TABLE bschema.sametname(i int);
/*u0*/CREATE TABLE dschema.sametname(i int);
/*u0*/CREATE VIEW bschema.samevname AS SELECT * FROM bschema.sametname;
/*u0*/CREATE VIEW dschema.samevname AS SELECT * FROM dschema.sametname;
/*u0*/CREATE SEQUENCE bschema.samesname START WITH 10;
/*u0*/CREATE SEQUENCE dschema.samesname START WITH 20;
/*u1*/INSERT INTO bschema.sametname(i) VALUES(1);
/*u1*/INSERT INTO dschema.sametname(i) VALUES(2);
/*r1,10*/SELECT i, NEXT VALUE FOR bschema.samesname FROM bschema.sametname;
/*r2,20*/SELECT i, NEXT VALUE FOR dschema.samesname FROM dschema.sametname;
/*r1*/SELECT i FROM bschema.samevname;
/*r2*/SELECT i FROM dschema.samevname;
-- Verify that same-named-objects does not make system tables choke
/*u0*/SET SCHEMA information_schema;
SELECT * FROM authorizations;
SELECT * FROM constraint_column_usage;
SELECT * FROM system_columns;
SELECT * FROM system_schemas;
SELECT * FROM schemata;
SELECT * FROM sequences;
SELECT * FROM system_sessioninfo;
SELECT * FROM system_sessions;
SELECT * FROM table_privileges;
SELECT * FROM system_tables;
SELECT * FROM usage_privileges;
SELECT * FROM system_users;

-- Basic user schema permission capability has been tested well above.
-- Now LIGHTER TESTING FOR REMAINING COMMANDS AND SCHEMA OBJECT TYPES:
-- (purpose to see if the non-Admin, non-right-granted user may do these
--  things on his objects, but not to other objects).
--     Commands to test:  ALTER, DELETE, DROP, GRANT, REVOKE, UPDATE
--     Object types to test:  INDEX, SEQUENCE, VIEW
/*u0*/connect user blaine password "blaine";
/*u0*/CREATE TABLE bschema.extra(i1 int);
/*u0*/CREATE TABLE bschema.todrop(i1 int);
/*u1*/insert into bschema.extra values(8);
-- First Commands on TABLEs
/*e*/ALTER TABLE public.ptable ADD COLUMN i2 int;
/*u0*/ALTER TABLE bschema.extra ADD COLUMN i2 int;
/*c1*/SELECT * FROM bschema.extra;
/*e*/UPDATE public.ptable SET i = 11;
/*u1*/UPDATE bschema.extra SET i1 = 44;
/*e*/DELETE FROM public.ptable;
/*u1*/DELETE FROM bschema.extra;
/*e*/GRANT SELECT ON public.ptable TO debbie;
/*u0*/GRANT SELECT ON bschema.extra TO debbie;
/*e*/REVOKE SELECT ON public.ptable FROM debbie RESTRICT;
/*u0*/REVOKE SELECT ON bschema.extra FROM debbie RESTRICT;
/*e*/DROP TABLE public.ptable;
/*u0*/DROP TABLE bschema.todrop;
-- Commands on INDEXes
/*e*/CREATE INDEX public.i1 ON public.ptable(i);
/*u0*/CREATE UNIQUE INDEX bschema.ui1 ON bschema.extra(i1);
/*c1*/SELECT count(*) FROM bschema.extra;
/*e*/ALTER INDEX public.pindex RENAME TO othername;
/*u0*/ALTER INDEX bschema.ui1 RENAME TO othername;
/*u1*/insert into bschema.extra(i1, i2) values(8,1);
-- Following violates the UNIQUE index
/*e*/insert into bschema.extra(i1, i2) values(8,2);
/*e*/DROP INDEX public.pindex;
/*u0*/DROP index bschema.othername;
-- Commands on SEQUENCEs
/*e*/CREATE SEQUENCE public.pseq;
/*u0*/CREATE SEQUENCE bschema.bseq;
/*e*/SELECT next value for public.psequence FROM bschema.extra;
/*c1*/SELECT next value for bschema.bseq FROM bschema.extra;
/*e*/ALTER SEQUENCE public.psequence RESTART WITH 10;
/*u0*/ALTER SEQUENCE bschema.bseq RESTART WITH 10;
/*e*/GRANT USAGE ON SEQUENCE public.psequence TO debbie;
/*u0*/GRANT USAGE ON SEQUENCE bschema.bseq TO debbie;
/*e*/REVOKE USAGE ON SEQUENCE public.psequence FROM debbie RESTRICT;
/*u0*/REVOKE USAGE ON SEQUENCE bschema.bseq FROM debbie RESTRICT;
/*e*/DROP SEQUENCE public.psequence;
/*u0*/DROP SEQUENCE bschema.bseq;
-- Adding views now.  Didn't want them to impact operations on underlying
-- tables in tests above.
/*u0*/connect user sa password "";
/*u0*/create VIEW public.pview AS SELECT * FROM public.ptable;
/*u0*/connect user blaine password "blaine";
/*e*/create VIEW public.oview AS SELECT * FROM public.ptable;
/*u0*/create VIEW bschema.bview AS SELECT * FROM bschema.extra;
/*e*/SELECT * FROM public.pview;
/*c1*/SELECT * FROM bschema.bview;
/*e*/GRANT SELECT ON public.pview TO debbie;
/*u0*/GRANT SELECT ON bschema.bview TO debbie;
/*e*/REVOKE SELECT ON public.pview FROM debbie RESTRICT;
/*u0*/REVOKE SELECT ON bschema.bview FROM debbie RESTRICT;
/*e*/DROP VIEW public.pview;
/*u0*/DROP VIEW bschema.bview;
/*u0*/DROP TABLE bschema.extra;

/*u0*/connect user sa password "";
/*u0*/DROP VIEW public.pview;
/*u0*/DROP INDEX public.pindex;
/*u0*/DROP TABLE public.ptable;
/*u0*/DROP SEQUENCE public.psequence;
/*u0*/SHUTDOWN;
