-- @@@ 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 @@@
--
-- Grant column-level insert and select privilges
-- Check privilege for DML statements in compiler

obey TEST129(clean_up);

sh sqlci -i "TEST129(setup)" -u sql_user4;
sh sqlci -i "TEST129(step1)" -u sql_user1;
sh sqlci -i "TEST129(step1)" -u sql_user1;
sh sqlci -i "TEST129(step2)" -u sql_user2;
sh sqlci -i "TEST129(step3)" -u sql_user3;
sh sqlci -i "TEST129(step4)" -u sql_user3;
sh sqlci -i "TEST129(step5)" -u sql_user4;
sh sqlci -i "TEST129(step6)" -u sql_user4;
sh sqlci -i "TEST129(step7)" -u sql_user4;
sh sqlci -i "TEST129(invalidate)" -u sql_user1;

obey TEST129(clean_up);

exit;

?section clean_up
drop schema us4 cascade;

?section setup
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
cqd CAT_TEST_BOOL 'ON';
log LOG129 clear;
log LOG129;
create schema us4;
set schema us4;

create table t1 (col1 int not null primary key, col2 int, col3 int) no partition;
insert into t1 values (1,1,1), (2,2,2), (5,5,5), (7,7,7);
create view v1(vc1,vc2,vc3) as select * from t1 ;

grant update on t1 to sql_user1 ;
grant select(col3) on t1 to sql_user1;
grant select on v1 to sql_user1;
grant update(vc1) on v1 to sql_user1 ;
grant insert(vc1) on v1 to sql_user1 ;

grant select on t1 to sql_user2;
grant update (col3) on t1 to sql_user2 ;
grant update(col2) on t1 to sql_user2;

grant update(col1) on t1 to sql_user3;
grant select(col1) on t1 to sql_user3;
showddl t1;

create table t2( a int not null primary key, b int default null, c int default null) ;
grant insert on t2 to sql_user1;
grant select on t2 to sql_user1;
grant insert(a,b) on t2 to sql_user2 ;
grant select(a,b) on t2 to sql_user2 ;
grant insert(a) on t2 to sql_user3;
grant select(a) on t2 to sql_user3;
showddl t2;

create table t129_starter (a int not null, primary key(a) NOT DROPPABLE )no partition;
insert into  t129_starter values (1);

create table t129_a
  (uniq int not null,
   c100  int not null,
   c10   int not null,
   c1    int not null,
   filler char(4000) default 'a',
   primary key (uniq)
   NOT DROPPABLE
  ) no partition ;

insert into t129_a (uniq,c100,c10,c1)
  select
   0 + (100 * x100) + (10 * x10) + (1 * x1),
   0 + (10 * x10) + (1 * x1),
   0 + (1 * x1),
   0
  from t129_starter
    transpose 0,1,2,3,4,5,6,7,8,9 as x100
    transpose 0,1,2,3,4,5,6,7,8,9 as x10
    transpose 0,1,2,3,4,5,6,7,8,9 as x1
  ;

update statistics for table t129_a on every column ;

grant select on t129_a to sql_user1 ;
grant select on t129_starter to sql_user1 ;

-- Testing creating views based on Column-level select privilege
--grant create_view on schema cat.us4 to sql_user3;

create table t3 (a int not null not droppable, b int, c int, d int, primary key (a));
create table t4 (e int not null not droppable, f int, g int, h int, primary key (e));

grant select (b,d) on t3 to sql_user3 with grant option;
grant select (f,g) on t4 to sql_user3;

showddl t3;
showddl t4;

insert into t3 values (1,2,3,4), (5,6,7,8);
insert into t4 values (11,22,33,44), (55,66,77,88);

?section step1
cqd CAT_TEST_BOOL 'ON';
log LOG129;
-- As user1, should fail
select * from us4.t1 ;
delete from us4.t1;
insert into us4.t1 values (1,1,1);
update us4.t2 set b = c ;
update us4.v1 set vc2 = vc1 ;
insert into us4.v1(vc1,vc2) values (10,10) ;

-- As user1, should succeed
update us4.t1 set col3 = 2 ;
select * from us4.v1 order by vc1;
insert into us4.t2 values (5,5,5);
update us4.v1 set vc1 = vc1 ;
insert into us4.v1(vc1) values (10) ;

delete all from table(querycache()) ;
delete all from table(natablecache());
select * from table(querycacheentries());
select * from table(natablecache()) ;

log;

?section step2
cqd CAT_TEST_BOOL 'ON';
log LOG129;
-- As user2, should fail
delete from us4.t1 ;
insert into us4.t1 values (1,1,1);
select * from us4.v1 ;
insert into us4.t2 values (1,1,1);

-- As user2, should succeed
update us4.t1 set col3 = 4 ;
update us4.t1 set col2 = 3 ;
select * from us4.t1 order by col1;
insert into us4.t2(a,b) values (1,1);
log;

?section step3
cqd CAT_TEST_BOOL 'ON';
log LOG129;
-- as user3
insert into us4.t2(a,b) values (2,2) ;
update us4.t1 set col2 = col1;


insert into us4.t2(a) values (3) ;
update us4.t1 set col1 = col1 + 1;

select col1 from us4.t1 order by 1;
select col2 from us4.t1 ;

select col1 from us4.t1 where col2 > 100;
select col1 from us4.t1 where col1 > 100 order by 1;

select count(*), min(col1) from us4.t1 group by col2;
select count(*), min(col1) from us4.t1 group by col1;

select count(*) from us4.t1 group by col1 having min(col2) > 10;
select count(*) from us4.t1 group by col1 having min(col1) > 10;

select x from us4.t1 transpose col2 as x;
select x from us4.t1 transpose col1 as x order by 1;

select col1 from us4.t1 sample random balance 
when col2 = 1 then 100 percent else 0 percent end;
select col1 from us4.t1 sample random balance 
when col1 = 1 then 100 percent else 0 percent end order by 1;

select col1 from us4.t1 where exists (select 1 from (values (us4.t1.col2)) t2(x) where t2.x = 100);
select col1 from us4.t1 where exists (select 1 from (values (us4.t1.col1)) t2(x) where t2.x = 100) order by 1;

select * from us4.t1 ;

log;

?section step4
cqd CAT_TEST_BOOL 'ON';
log LOG129;
-- Testing create view based on column-level SELECT
-- as user3

set schema cat.us4;

-- View on single table (positive):

create view v3bd as select b,d from t3;
select * from v3bd;
create view v3b as select b from t3;
select * from v3b;
create view v3d as select d from t3;
select * from v3d;
create view v3bbbbbb (c1,c2,c3,c4,c5,c6) as select b,b,b,b,b,b from t3;
select * from v3bbbbbb;

-- View on single table (negative):

create view v3ac as select a,c from t3;
create view v3a as select a from t3;
create view v3c as select c from t3;

create view v3 as select * from t3;

create view v3ab as select a,b from t3;
create view v3abcd as select a,b,c,d from t3;
create view v3bc as select b,c from t3;

-- View on two tables (positive):

create view v34bf as select b,f from t3, t4;
select * from v34bf;
create view v34bdfg as select b,d,f,g from t3, t4;
select * from v34bdfg;
create view v34bdfg2 (c1,c2,c3,c4) as select b,d,f,g from t3, t4;
select * from v34bdfg2;
create view v34gb as select g,b from t3, t4;
select * from v34gb;

-- View on two tables (negative):

create view v34 as select * from t3,t4;
create view v34af as select a,f from t3, t4;
create view v34bh as select b,h from t3,t4;


log;

?section step5
cqd CAT_TEST_BOOL 'ON';
log LOG129;
-- Testing revoke for column-level SELECT
-- as user4

set schema us4;

grant select (b,d) on t3 to sql_user3 with grant option;
grant select (f,g) on t4 to sql_user3;
showddl t3;
showddl t4;

-- Negative tests: revoking from table
revoke select (c) on t3 from sql_user3;
revoke select (b) on t3 from sql_user3;

revoke grant option for select (c) on t3 from sql_user3;

revoke grant option for select (d) on t3 from sql_user3;

revoke grant option for select (b) on t3 from sql_user3;
revoke grant option for select (b) on t3 from sql_user3 cascade;
showddl t3;

?section step6
cqd CAT_TEST_BOOL 'ON';
set schema us4;

-- Testing other variations of column level

-- Negative tests
grant select (colx) on t1 to sql_user5;
grant select (col1, colx, col2) on t1 to sql_user5;
grant insert (colx) on t1 to sql_user5 with grant option;
revoke select (colx) on t1 from sql_user5;
revoke insert (col1, colx) on t1 from sql_user5 cascade;

-- Negative tests: duplicate columns
grant select on t1 (col1, col1) to sql_user5;
revoke insert on t1 (col1, col1) from sql_user5;
revoke grant option for select (col1, col1) on t1 from sql_user5;

-- Positive tests
create table t1 (a int not null not droppable, b int);
showddl t1;
grant select (a) on t1 to sql_user5 with grant option;
showddl t1;
revoke grant option for select (a) on t1 from sql_user5;
showddl t1;
revoke select (a) on t1 from sql_user5 cascade;
showddl t1;

?section invalidate
cqd CAT_TEST_BOOL 'ON';
log LOG129;
-- as user1

cqd query_cache '0' ;
cqd metadata_cache_size '0' ;

select count(*) from us4.t129_a ;

sh  sh runmxci.ksh -i "TEST129(revoke1)" -u sql_user4; 
-- MUST DELAY so that QVP process has time to send Security Key invalidations to all SQL Compiler caches
sh sleep 10;

-- checking that cache gets refreshed.
-- should get an error
prepare s1 from select * from us4.t129_a ;

cqd metadata_cache_size reset ;

select case when current_cache_size > 0 then 1 else 0 end from table(natablecache());

--should error but place t129_a in natable cache
prepare s1 from select * from us4.t129_a where c1 > 10;

select case when current_cache_size > 0 then 1 else 0 end from table(natablecache());

sh  sh runmxci.ksh -i "TEST129(grant1)" -u sql_user4; 


-- should succeed
prepare s1 from select * from us4.t129_a as t1, us4.t129_a as t2;

cqd query_cache reset ;
cqd query_text_cache 'off' ;

select count(*) from us4.t129_a where c1 = 10 ;

select num_entries from table(querycache());

sh  sh runmxci.ksh -i "TEST129(revoke1)" -u sql_user4; 
-- MUST DELAY so that QVP process has time to send Security Key invalidations to all SQL Compiler caches
sh sleep 10;

-- should fail
select count(*) from us4.t129_a where c1 = 10 ;

-- cache has 1 entry
select num_entries from table(querycache());


cqd query_text_cache reset ;
cqd auto_query_retry 'off' ;

sh  sh runmxci.ksh -i "TEST129(grant1)" -u sql_user4; 

select count(*) from us4.t129_a where c1 = 100 ;

sh  sh runmxci.ksh -i "TEST129(revoke1)" -u sql_user4; 
-- MUST DELAY so that QVP process has time to send Security Key invalidations to all SQL Compiler caches
sh sleep 10;

-- fails
select count(*) from us4.t129_a where c1 = 100 ;

sh  sh runmxci.ksh -i "TEST129(grant1)" -u sql_user4; 

cqd auto_query_retry reset ;
cqd auto_query_retry_warnings 'on' ;


select count(*) from us4.t129_a where c10 = 100 ;

sh  sh runmxci.ksh -i "TEST129(grant2)" -u sql_user4; 


select count(*) from us4.t129_a where c10 = 100 ;

cqd auto_query_retry_warnings reset ;
prepare s1 from select count(*) from us4.t129_a where c10 < 100 ;

sh  sh runmxci.ksh -i "TEST129(revoke1)" -u sql_user4; 
-- MUST DELAY so that QVP process has time to send Security Key invalidations to all SQL Compiler caches
sh sleep 10;

-- should error
execute s1 ;

sh  sh runmxci.ksh -i "TEST129(grant1)" -u sql_user4; 

set envvar sqlci_cursor '1';
cqd attempt_esp_parallelism 'off' ;
cqd nested_joins 'off' ;
cqd merge_joins 'off' ;

declare c1 cursor for 
select uniq from us4.t129_a, us4.t129_starter 
where filler = 'a' or a = 1 for read uncommitted access ;

open c1 ;

sh  sh runmxci.ksh -i "TEST129(grant3)" -u sql_user4; 

fetch c1;
fetch c1;
fetch c1;
fetch c1;
fetch c1;
fetch c1;
fetch c1;
fetch c1;

log ;
exit ;

?section revoke1
cqd CAT_TEST_BOOL 'ON';
log LOG129;
revoke select on us4.t129_a from sql_user1 ;

?section grant1
cqd CAT_TEST_BOOL 'ON';
log LOG129;
grant select on us4.t129_a to sql_user1 ;

?section grant2
cqd CAT_TEST_BOOL 'ON';
log LOG129;
grant select on us4.t129_a to sql_user2 ;

?section grant3
cqd CAT_TEST_BOOL 'ON';
log LOG129;
grant select on us4.t129_a to sql_user3 ;



