-- 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;
/*u0*/GRANT CHANGE_AUTHORIZATION TO 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 RESTRICT;
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 RESTRICT;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM cani RESTRICT;
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 RESTRICT;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM cani RESTRICT;
CONNECT USER cani PASSWORD cani;
/*e*/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 RESTRICT;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM cani RESTRICT;
CONNECT USER cani PASSWORD cani;
/*e*/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 RESTRICT;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM PUBLIC RESTRICT;
CONNECT USER cani PASSWORD cani;
/*e*/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 RESTRICT;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM PUBLIC RESTRICT;
CONNECT USER cani PASSWORD cani;
/*e*/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 RESTRICT;
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 RESTRICT;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM cani RESTRICT;
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 RESTRICT;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM cani RESTRICT;
CONNECT USER cani PASSWORD cani;
/*e*/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 RESTRICT;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM cani RESTRICT;
CONNECT USER cani PASSWORD cani;
/*e*/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 RESTRICT;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM sesamerole RESTRICT;
CONNECT USER cani PASSWORD cani;
/*e*/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 RESTRICT;
CONNECT USER cani PASSWORD cani;
/*c1*/SELECT * FROM m;
CONNECT USER sa PASSWORD "";
REVOKE SELECT ON m FROM sesamerole RESTRICT;
CONNECT USER cani PASSWORD cani;
/*e*/SELECT * FROM m;
