-- ============================================================================
-- TEST140 - tests initializing, dropping and upgrading privilege metadata
--
-- @@@ 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 using the WGO command
--   REVOKE using the WGO command
--
-- Sections:
--   clean_up - removes database setup
--   set_up - prepares for test
--   tests - runs tests
-- ============================================================================

obey TEST140(clean_up);
log LOG140 clear;
env;
obey TEST140(create_db);
obey TEST140(set_up);
obey TEST140(tests);
obey TEST140(revoke_tests);
log;
obey TEST140(clean_up);
exit;

?section clean_up
revoke select on "_MD_".objects from sql_user1;
revoke select on "_PRIVMGR_MD_".object_privileges from sql_user1;

set schema t140_shared_views;
drop function t140_translatePrivsBitmap;
drop library t140_l1;

drop schema t140_user1_private cascade;
drop schema t140_user1_shared cascade;
drop schema t140_priv1_private cascade;
drop schema t140_priv2_shared cascade;
drop schema t140_shared_views cascade;

revoke role priv1, priv2, priv3, priv4 from sql_user5;
revoke role metadata_access from sql_user1, sql_user2, sql_user3,
   sql_user4, sql_user5, sql_user6, sql_user7, sql_user8, sql_user9;
revoke select on "_MD_".objects from metadata_access;
revoke select on "_PRIVMGR_MD_".object_privileges from metadata_access;
revoke select on "_MD_".auths from metadata_access;

drop role priv1;
drop role priv2;
drop role priv3;
drop role priv4;
drop role metadata_access;

?section create_db
-- create roles
create role priv1;
create role priv2;
create role priv3;
create role priv4;
create role metadata_access;
grant role priv1, priv2, priv3, priv4 to sql_user5;
grant role metadata_access to sql_user1, sql_user2, sql_user3,
   sql_user4, sql_user5, sql_user6, sql_user7, sql_user8, sql_user9;

grant select on "_MD_".objects to metadata_access;
grant select on "_PRIVMGR_MD_".object_privileges to metadata_access;
grant select on "_MD_".auths to metadata_access;

create shared schema t140_shared_views;
set schema t140_shared_views;

-- compile cpp program for function
sh rm -f ./etest140.dll;
sh sh $$scriptsdir$$/tools/dll-compile.ksh etest140.cpp
  2>&1 | tee LOG140-SECONDARY;
set pattern $$DLL$$ etest140.dll;
set pattern $$QUOTE$$ '''';

-- create the library and udf
create library t140_l1 file $$QUOTE$$ $$REGRRUNDIR$$/$$DLL$$ $$QUOTE$$ ;
create function t140_translatePrivsBitmap(bitmap largeint) returns (bitmap_string char (20))
language c parameter style sql external name 'translateBitmap'
library t140_l1
deterministic no sql final call allow any parallelism state area size 1024 ;
grant execute on function t140_translatePrivsBitmap to "PUBLIC";
 
-- private schema owned by sql_user1
create schema t140_user1_private authorization sql_user1;
set schema t140_user1_private;
obey TEST140(create_tables);
get tables in schema t140_user1_private;


-- shared schema owned by sql_user5
--create shared schema t140_user1_shared authorization sql_user5;
--set schema t140_user5_shared;
--obey TEST140(create_tables);
--get tables in schema t140_user5_shared;

-- private schema owned by role priv1
--create private schema t140_priv1_private authorization priv1;
--set schema t140_priv1_private;
--obey TEST140(create_tables);
--get tables in schema t140_priv1_private;

-- shared schema owner by role priv2
--create shared schema t140_priv2_shared authorization priv2;
--set schema t140_priv2_shared;
--obey TEST140(create_tables);
--get tables in schema t140_priv2_shared;

?section create_tables

-- Creates and loads three tables:  teams, games, players
-- All tables have a check constraint (other than NOT NULL)
-- Table games has an index
-- Table players has a RI contraint referencing teams
-- A view exists between games & teams

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 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);

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 t140_shared_views.home_team_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;
grant select on t140_shared_views.home_team_games to sql_user1 with grant option;

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;

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

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

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);

?section set_up
prepare get_privs from
select distinct
   substring (object_name,1,50) as object_name,
   object_type as type,
   substring(authname(grantor_id),1,10) as grantor,
   substring(authname(grantee_id),1,10) as grantee,
   t140_shared_views.t140_translatePrivsBitmap(privileges_bitmap) as granted_privs,
   t140_shared_views.t140_translatePrivsBitmap(grantable_bitmap) as grantable_privs
from "_PRIVMGR_MD_".object_privileges 
where object_uid in 
     (select object_uid
      from "_MD_".objects
      where schema_name like 'T140_%')
  order by 1, 2, 3, 4, 5
;

?section tests
values (user);

-- user1 owns everything, start of by doing initial grants
sh sqlci -i "TEST140(user1_cmds)" -u sql_user1;

-- Go see what user2, user3, user4 and user5 can do
sh sqlci -i "TEST140(user2_cmds)" -u sql_user2;
sh sqlci -i "TEST140(user3_cmds)" -u sql_user3;
sh sqlci -i "TEST140(user4_cmds)" -u sql_user4;
sh sqlci -i "TEST140(user5_cmds)" -u sql_user5;

?section user1_cmds
obey TEST140(set_up);
log LOG140;
values (user);
set schema t140_user1_private;

grant select on games to sql_user2;

-- grants to handle table requests
grant all on teams to sql_user2 with grant option;
grant select, insert on players to sql_user2 with grant option;

-- grants to handle view requests
grant select on t140_shared_views.home_team_games to sql_user2;

obey TEST140(set_up);
execute get_privs;

?section user2_cmds
log LOG140;
values (user);
set schema t140_user1_private;

-- user2 has insert privilege on teams and players
insert into teams values (6, 'Mets', 'Harry', '8007218888');
insert into players values (11, 'Barry', 3, '2342342345', 'left field');

-- user2 does not have insert privilege on games
insert into games values (5,6,9, current_timestamp, 'Michigan');

-- user2 can select from all tables
select count(*) from teams;
select count(*) from games;
select count(*) from players;

-- user2 can select from home_team_games
select team_number, game_number from t140_shared_views.home_team_games;

-- user2 can create a view that spans all tables
create view t140_shared_views.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;
select distinct player_name from t140_shared_views.games_by_player order by 1;

-- user2 cannot propagate select privilege on games
grant select on games to sql_user3;

-- user2 can propagate all privileges on teams
grant select, delete on teams to sql_user3 with grant option;

-- user2 can propagate select and insert privilege on players
grant select on players to sql_user3;
grant insert on players to sql_user3 with grant option;

obey TEST140(set_up);
execute get_privs;

?section user3_cmds
obey TEST140(set_up);
log LOG140;
values (user);
set schema t140_user1_private;

-- user3 cannot select from games
select count(*) from games;

-- user3 can select from teams and players
select count(*) from teams;
select count(*) from players;

-- user3 can create a view between teams and players
create view t140_shared_views.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 t140_shared_views.players_on_team;

-- user3 cannot select from view games_by_player or home_team_games;
select * from t140_shared_views.games_by_player;
select team_number, game_number from t140_shared_views.home_team_games;

-- user3 can delete from teams;
delete from teams where team_number = 6;

-- user3 cannot insert into teams
insert into teams values (6, 'Mets', 'Harry', '8007218888');

-- user3 cannot propagate select on table games
grant select on games to sql_user4;

-- user3 can propagate select but no insert on table teams
grant select, insert on teams to sql_user4;

-- user3 can propagate insert on table players
grant insert on players to sql_user4;

execute get_privs;

?section user4_cmds
obey TEST140(set_up);
log LOG140;
values (user);
set schema t140_user1_private;

-- user4 cannot select from games or players
select count(*) from games;
select count(*) from players;

-- user4 can create a view on teams;
create view t140_shared_views.team_names as
select team_name from teams;

-- user4 cannot select from other views
select * from t140_shared_views.players_on_team;

-- user4 can select from teams;
select count(*) from teams;

-- user3 has insert privilege into players but not references for the 
-- associated RI constraint
insert into players values (12, 'Aaron', 4, '3453453456', 'right field');

--user4 cannot grant any privileges
grant select on teams to sql_user5;
grant insert on players to sql_user5;

execute get_privs;

?section user5_cmds
log LOG140;
values (user);
obey TEST140(set_up);
set schema t140_user1_private;

-- user 5 has no privs
select count(*) from teams;

?section revoke_tests
set schema t140_user1_private;

-- set up grant tree
--  user1 grants select, insert WGO to user2
--     user2 grants select, insert WGO to user3 
--        user3 grants select to user4 and user5 
--     user2 grants select WGO to user4
--        user4 grants select WGO to user5
--           user5 grants to user6
--     user2 grants insert to user4 
--  user1 grants select, insert to user4

-- owner (user1) grants
grant select, insert on games to sql_user2 with grant option;
grant select, insert on games to sql_user4;

-- user2 grants
grant select, insert on games to sql_user3 with grant option by sql_user2;
grant select on games to sql_user4 with grant option by sql_user2;
grant insert on games to sql_user4 by sql_user2;

-- user3 grants
grant select on games to sql_user4 granted by sql_user3;
grant select on games to sql_user5 with grant option granted by sql_user3;

-- user4 grants
grant select on games to sql_user5 with grant option granted by sql_user4;

-- user5 grante
grant select on games to sql_user6 by sql_user5;
execute get_privs;

-- user6 tries to grant select to user7
grant select on games to sql_user7 by sql_user6;

-- user5 tries to grant to user2 (circular)
grant select on games to sql_user2 by sql_user5;

-- user2 tries to remove WGO from user3, 
-- fails because of user3->user4 and user3->user5 grants
revoke grant option for select on games from sql_user3 by sql_user2;

-- remove user3->user5 grant
revoke select on games from sql_user4 by sql_user3;
revoke grant option for select on games from sql_user3 by sql_user2;
revoke select on games from sql_user5 by sql_user3;
revoke grant option for select on games from sql_user3 by sql_user2;

execute get_privs;

