-- ============================================================================
-- TEST139 - tests grant and revoke privileges
--
-- @@@ START COPYRIGHT @@@
--
-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at
--
--   http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied.  See the License for the
-- specific language governing permissions and limitations
-- under the License.
--
-- @@@ END COPYRIGHT @@@
--
-- This tests the following commands:
--
--   GRANT <privilege> ON <object> TO <grantee-list>
--   REVOKE <privilege> ON <object> FROM <grantee-list>
--
-- Sections:
--   clean_up - removes database setup
--   set_up - prepares for test
--   tests - runs tests
-- ============================================================================

cqd CAT_ENABLE_QUERY_INVALIDATION 'OFF';
obey TEST139(clean_up);
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
log LOG139 clear;
obey TEST139(set_up);
obey TEST139(create_db);
obey TEST139(tests);
-- sh sqlci -i "TEST139(authorized)" -u sql_user4;
log;
obey TEST139(clean_up);
exit;

?section clean_up
-- drop database
drop schema t139sch cascade;
revoke role1 from sq1_user1;
drop role role1;

?section create_db
create shared schema t139sch;
set schema t139sch;
create table teams
  (team_number int not null primary key,
   team_name char(20) not null,
   team_contact varchar(50) not null,
   team_contact_number char (10) not null
   )
  ;

alter table teams add constraint valid_team_no check (team_number > 0);

insert into teams values
   (1, 'White Socks', 'Sam','4082282222'),
   (2, 'Giants', 'Joe', '5102839483'),
   (3, 'Cardinals', 'Stella', '9513849384'),
   (4, 'Indians', 'Matt', '5128383748'),
   (5, 'Tigers', 'Ronit', '6198273827');

create table team_statistics
  (team_number int not null primary key,
   num_players int not null)
;

create table games
   ( home_team_number int not null,
     visitor_team_number int not null,
     game_number int not null primary key,
     game_time timestamp not null,
     game_location varchar(50) not null)
  ;
--create index home_games on games (home_team_number);

alter table games add constraint valid_game_number check (game_number > 0);

create table standings
    (team_number int not null primary key,
     wins int default 0,
     loses int default 0,
     last_updated timestamp default current_timestamp)
;
insert into standings (team_number)
  select team_number from teams;

insert into games values
   (1, 2, 1, timestamp '2009-04-23 19:30:00', 'California'),
   (1, 3, 2, timestamp '2009-04-24 19:30:00', 'California'),
   (1, 4, 3, timestamp '2009-04-25 10:00:00', 'Oklahoma'),
   (2, 3, 4, timestamp '2009-04-25 13:30:00', 'Michigan'),
   (1, 5, 5, timestamp '2009-04-25 15:00:00', 'Oklahoma'),
   (2, 5, 6, timestamp '2009-04-27 17:00:00', 'New York'),
   (3, 4, 7, timestamp '2009-04-28 17:00:00', 'Florida'),
   (4, 2, 8, current_timestamp, 'Missouri');

create view home_teams_games as
  select t.team_number, g.game_number, g.game_time
  from "TEAMS" t,
       "GAMES" g
  where t.team_number = g.home_team_number
  order by 1, game_number, game_time;

create table players
  (player_number int not null,
   player_name varchar (50) not null,
   player_team_number int not null,
   player_phone_number char (10) not null,
   player_details varchar(50),
   primary key (player_number, player_team_number))
  no partition;

insert into players values
   (1, 'Tom', 1, '4083948394', null),
   (2, 'Bob', 1, '4089483948', null),
   (3, 'Toby',1, '4082938493', 'pitcher'),
   (3, 'Toby',2, '4082938493', null),
   (4, 'Julie', 2, '5108394839', 'catcher'),
   (5, 'Joanne', 2, '5103849384', null),
   (6, 'Pete', 2, '5102839483', null),
   (6, 'Pete', 3, '5102839483', 'third base'),
   (7, 'Jared',4, '9518293849', 'short stop'),
   (8, 'Zachary', 4, '9518293840', null),
   (9, 'Lynne', 5, '9518293892', 'pitcher'),
   (10, 'Omar', 5, '5128394893', null);

alter table players add constraint valid_player_number check(player_number > 0);

create view players_on_team as
  select player_name, team_name
  from teams t, players p
  where p.player_team_number = t.team_number
  order by t.team_name;
select * from players_on_team;

create view games_by_player as
  select player_name, game_time
  from teams t, games g, players p
  where p.player_team_number = t.team_number and
        t.team_number = g.home_team_number
  order by player_name, team_number;

alter table players add constraint players_teams
   foreign key (player_team_number) references teams (team_number);

insert into team_statistics
  select team_number, count (player_number)
   from teams t, players p
   where t.team_number = p.player_team_number
   group by team_number;

create volatile table home_games as
  select home_team_number, visitor_team_number, game_number,  game_location
  from games
    where extract(year  from games.game_time) =
          extract(year  from current_timestamp) and
          extract(month from games.game_time) =
          extract(month from current_timestamp);

?section set_up
set schema "_PRIVMGR_MD_";
prepare get_owner_privs from
select distinct
   substring (object_name,1,10) as object_name,
   object_type as type,
   substring(authname(grantee_id),1,10) as grantee,
   privileges_bitmap,
   grantable_bitmap
from object_privileges 
where grantor_id = -2
    and object_uid in 
     (select object_uid
      from "_MD_".objects
      where object_type in ('VI','BT','LB','UR')
        and schema_name in ('_PRIVMGR_MD_', 'T139SCH'))
  order by 1, 2
;

prepare object_privs from
select distinct
  object_type as type,
  substring (grantor_name,1,10) as grantor,
  substring (grantee_name,1,10) as grantee,
  privileges_bitmap as privs,
  grantable_bitmap as wgo
from "_PRIVMGR_MD_".object_privileges
where object_name like ?tblname 
order by 1, 2, 3; 

prepare all_privs from
select distinct
  object_type as type,
  substring (object_name,1,20) as objname,
  substring (grantor_name,1,10) as grantor,
  substring (grantee_name,1,10) as grantee,
  privileges_bitmap as privs,
  grantable_bitmap as wgo
from "_PRIVMGR_MD_".object_privileges
where object_uid in
   (select object_uid from "_MD_".objects
    where schema_name in ('T139SCH'))
ORDER BY 1, 2,3,4;
 
?section tests
-- =================================================================
-- this set of tests run basic grant and revoke tests
-- =================================================================
set schema t139sch;

set param ?tblname '%GAMES';

-- test SELECT, UPDATE, DELETE, INSERT on games
grant select on games to sql_user1;
showddl games;
-- make sure user1 can select but not other operations
sh sqlci -i "TEST139(user1_dml)" -u sql_user1;

grant update on games to sql_user1;
showddl games;
-- make sure user 1 can select and update but not other operations
sh sqlci -i "TEST139(user1_dml)" -u sql_user1;

grant delete, insert, references on games to sql_user1;
showddl games;
-- make sure user1 can do all dml
sh sqlci -i "TEST139(user1_dml)" -u sql_user1;
execute object_privs;
-- this query does not always return the correct rows 
-- execute all_privs;

-- test WITH GRANT OPTION
--grant select, insert, delete on teams to sql_user2 with grant option;
--showddl teams;
-- make sure user2 grant of select, insert, delete to user2 succeeds
-- make sure user2 grant of update, references fails
-- user2 will grant select to user3
--sh sqlci -i "TEST139(user2_dml)" -u sql_user2;
-- make sure user3 can select
-- user3 will grant select to user4
--sh sqlci -i "TEST139(user3_dml)" -u sql_user3;
--grant update on teams to sql_user2 with grant option;
--showddl teams;
-- make sure user2 can grant update privilege
--sh sqlci -i "TEST139(user2_dml)" -u sql_user2;
-- make sure user3 can update
--sh sqlci -i "TEST139(user3_dml)" -u sql_user3;
-- make sure user4 can select, but not other privs
--sh sqlci -i "TEST139(user4_dml)" -u sql_user4;

-- test revoke SELECT, UPDATE, DELETE, INSERT, REFERENCES 

revoke update on games from sql_user1;
showddl games;
revoke all_dml on games from sql_user2;
showddl games;
revoke all_dml on teams from sql_user3;
showddl teams;

-- test for JIRA 2177
create role role1;
grant role role1 to sql_user1;
grant insert(team_contact,team_contact_number), select on teams to role1;
showddl teams;
revoke insert(team_contact,team_contact_number), select on teams from role1;
showddl teams;

-- test for JIRA 2196/2197
grant select,insert ,delete, update ,references(game_time) on games to sql_user1 with grant option;
showddl games;
sh sqlci -i "TEST139(user1_grants)" -u sql_user1;
revoke select,insert ,delete, update ,references(game_time) on games 
   from sql_user1;
revoke role role1 from sql_user1;
drop role role1;

?section user1_dml
set schema t139sch;
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
log LOG139;
select count(*) from games;
update games set game_location = 'Ohio' where game_location = 'New York';
update games set game_location = 'New York' where game_location = 'Ohio'; 
insert into games values (4, 5, 9, current_timestamp, 'Ohio');
select count(*) from games;
delete from games where game_number = 9;
select count(*) from games;
exit;

?section user2_dml
set schema t139sch;
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
log LOG139;
select count(*) from teams;
update teams set team_contact = 'Ophelia', team_contact_number = '2344652934'
  where team_number = 4;
insert into teams values (6, 'Angels','Ophelia','2344652934');
select * from teams;
delete from teams where team_number = 6;
update teams set team_contact = 'Matt', team_contact_number = '5128383748'
  where team_number = 4;
grant select on teams to sql_user3 with grant option;
grant update on teams to sql_user3;
showddl teams;

?section user3_dml
set schema t139sch;
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
log LOG139;
select count(*) from teams;
update teams set team_contact = 'Ophelia', team_contact_number = '2344652934'
  where team_number = 4;
insert into teams values (6, 'Angels','Ophelia','2344652934');
select * from teams;
delete from teams where team_number = 6;
update teams set team_contact = 'Matt', team_contact_number = '5128383748'
  where team_number = 4;
grant select on teams to sql_user4;
grant update on teams to sql_user4;

?section user4_dml
set schema t139sch;
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
log LOG139;
select count(*) from teams;
update teams set team_contact = 'Ophelia', team_contact_number = '2344652934'
  where team_number = 4;
insert into teams values (6, 'Angels','Ophelia','2344652934');
delete from teams where team_number = 6;
grant select on teams to sql_user4;
grant update on teams to sql_user4;

?section user1_grants
set schema t139sch;
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
log LOG139;
grant select(home_team_number,game_time),
      insert(home_team_number),
      update(visitor_team_number,game_time),
      references(game_time) 
on games to role1;
showddl games;
revoke select(home_team_number,game_time),
      insert(home_team_number),
      update(visitor_team_number,game_time),
      references(game_time)
on games from role1;

