core/sql/regress/core/TEST131

-- Test: TEST131 (Executor)
-- @@@ 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 @@@
--
-- Functionality: Executor support for REVOKE (query invalidation).
-- Expected files: EXPECTED131
-- Table created: T131a, T131b, T131c
--      
-- 

obey TEST131(clnup);

log LOG131 ;

-- run the test in sqlci sessions which start after authorization
-- is enabled.
log;
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
sh sqlci -i"TEST131(test_session1)";
log LOG131 ;
sh sqlci -i"TEST131(test_native)" -u sql_user3;

obey TEST131(clnup);

log;
exit;

?section test_session1

log LOG131 ;

obey TEST131(setup);
log;

sh sqlci -i"TEST131(dml)" -u"SQL_User7"  ;

log LOG131;
grant all on t131a to SQL_USER7;
insert into t131a values(1, 1, 1, 1, 1, 1);
log;

sh sqlci -i"TEST131(update_where_current_of)" -u"SQL_User7"  ;


log LOG131;
grant all on t131a to SQL_USER7;

sh sqlci -i"TEST131(priv_cancel)" -u"SQL_User7"  ;

?section clnup

set schema $$TEST_SCHEMA$$;

drop table if exists T131c;
drop table if exists T131b;
drop table if exists T131a;
revoke component privilege QUERY_CANCEL on sql_operations from SQL_USER7;
revoke role role131  from sql_user7;
revoke component privilege QUERY_CANCEL on SQL_OPERATIONS from role131;
drop role role131;
 
-- cleanup for test_native
drop external table item for hive.hive.item;
revoke role DB__HIVEROLE from SQL_USER4;
showddl role DB__HIVEROLE;

?section setup

create table T131a
  (uniq int not null,
   c10K int ,
   c1K   int,
   c100  int,
   c10   int,
   c1    int,
   primary key (uniq)
  )  no partition;

create table T131b
  (uniq int not null,
   c10K int ,
   c1K   int,
   c100  int,
   c10   int,
   c1    int,
   primary key (uniq)
  )  no partition;

create table T131c
  (uniq int not null,
   c10K int ,
   c1K   int,
   c100  int,
   c10   int,
   c1    int,
   primary key (uniq)
  )  no partition;

grant all on t131a to SQL_USER7;
grant all on t131b to SQL_USER7;
grant all on t131c to SQL_USER7;

-- setup for test_native
grant role DB__HIVEROLE to SQL_USER4;
showddl role DB__HIVEROLE;
create external table item for hive.hive.item;

?section dml

log LOG131;

cqd AUTO_QUERY_RETRY 'OFF';

cqd CAT_ENABLE_QUERY_INVALIDATION 'ON';

prepare sel_abc from select * from t131a, t131b, t131c;

log; log EXPLAIN131 clear;
explain sel_abc;
log;

sh echo "Query_Invalidation_Keys explain output"   >> LOG131; 
sh grep "Query_Invalidation_Keys *{[-0-9]" EXPLAIN131 | cut -c 1-28 >> LOG131; 

log LOG131;

-- run a session to revoke privs for this user.

log;

sh sqlci -i"TEST131(revoke1)";

log LOG131;

execute sel_abc;

-- test the GET STATISTICS reporting of No. Query Invalidation Keys
log; log STATS131 clear;
get statistics for rms all;
get statistics for rms 0;

select
 substr(variable_info,
 position('numQueryInvKeys' in variable_info),
 position('numQueryInvKeys' in variable_info) +
 17 + (position(' ' in
 substr(variable_info,
 17 + position('numQueryInvKeys:' in variable_info))) -
 position('numQueryInvKeys:' in variable_info)))
from table(statistics(NULL, 'RMS_INFO=-1'));

log;

sh grep "Query Invalidation Keys *[1-9]" STATS131 | cut -c 1-28 >> LOG131;
sh grep "^numQueryInvKeys: *[1-9]" STATS131 | cut -c 1-17>> LOG131;


log LOG131;

?section update_where_current_of
log LOG131;

cqd AUTO_QUERY_RETRY_WARNINGS 'ON';


set envvar sqlci_cursor '1';
declare c3 cursor for
select * from t131a for update of c10k;
prepare s3 from update t131a set c10k = c10k+22 where current of c3;

-- run a session to revoke UPDATE for this user, then grant it again.

log;
sh sqlci -i"TEST131(revoke_and_regrant_upd)";
log LOG131;

begin work;
open c3;
fetch c3;
execute s3;
commit;
select * from t131a;

-- run a session to revoke UPDATE for this user.

declare c4 cursor for
select * from t131a for update of c10k;
prepare s4 from update t131a set c10k = c10k+22 where current of c4;

log;
sh sqlci -i"TEST131(revoke_update)";
log LOG131;

begin work;
open c4;
fetch c4;
execute s4;
commit;
select * from t131a;

?section priv_cancel
log LOG131;

-- cancel my own query is allowed with no grant
prepare s1 from
values(user());

execute s1;

log;
log QIDLOG clear;
display qid for s1;

log;
log LOG131;

sh grep "^QID is " QIDLOG | cut -c 8- | sed "s/^/control query cancel qid /g" | sed "s/$/;/g"  > cancel_cmd;

-- expect error 8031 since the query is not running.
obey cancel_cmd;

-- expect error 8029
control query cancel qid
MXID11000023943212197828612249700000000000206U6553500_20_S1;

sh sqlci -i"TEST131(grant_cancel)";

cqd AUTO_QUERY_RETRY_WARNINGS 'ON';

-- expect error 8026 and no AQR warning
control query cancel qid
MXID11000023943212197828612249700000000000206U6553500_20_S1;

prepare s1 from
control query cancel qid
MXID11000023943212197828612249700000000000206U6553500_20_S1;

-- expect error 8026 and no AQR warning
execute s1;

sh sleep 2;
-- expect error 8026 and AQR warning
execute s1;

sh sqlci -i"TEST131(revoke_cancel)";

-- expect error 8029
control query cancel qid
MXID11000023943212197828612249700000000000206U6553500_20_S1;

sh sqlci -i"TEST131(grant_cancel_role)";

-- expect error 8026
control query cancel qid
MXID11000023943212197828612249700000000000206U6553500_20_S1;

sh sqlci -i"TEST131(revoke_cancel_role)";

-- expect error 8029
control query cancel qid
MXID11000023943212197828612249700000000000206U6553500_20_S1;


?section revoke1

log LOG131;

revoke all on t131c from SQL_USER7;

-- Test that identical revokes do not make new RMS siks.
grant all on t131a to SQL_USER3;
grant all on t131b to SQL_USER3;
grant all on T131c to SQL_USER3;

revoke all on t131a from SQL_USER3;
revoke all on t131b from SQL_USER3;
revoke all on t131c from SQL_USER3;

log; log STATS131 clear;
get statistics for RMS 0;
log LOG131;
sh grep "Query Invalidation Keys *[1-9]" STATS131 >> STATS131_1;

grant all on t131a to SQL_USER3;
grant all on t131b to SQL_USER3;
grant all on T131c to SQL_USER3;

revoke all on t131a from SQL_USER3;
revoke all on t131b from SQL_USER3;
revoke all on t131c from SQL_USER3;

log; log STATS131 clear;
get statistics for RMS 0;
sh grep "Query Invalidation Keys *[1-9]" STATS131 >> STATS131_2;

sh diff --brief STATS131_1 STATS131_2 >> LOG131;

log LOG131;

log;

?section revoke_and_regrant_upd

log LOG131;

revoke UPDATE on t131a from SQL_USER7;

grant UPDATE on t131a to SQL_USER7;


?section revoke_update

log LOG131;

revoke UPDATE on t131a from SQL_USER7;

?section revoke_and_regrant_sel

log LOG131;

revoke SELECT on t131a from SQL_USER7;

grant SELECT on t131a to SQL_USER7;


?section revoke_select

log LOG131;

revoke SELECT on t131a from SQL_USER7;

?section grant_cancel

log LOG131;

grant component privilege QUERY_CANCEL on sql_operations to SQL_USER7;

?section revoke_cancel

log LOG131;

revoke component privilege QUERY_CANCEL on sql_operations from SQL_USER7;

?section grant_cancel_role

log LOG131;
create role role131;
grant component privilege QUERY_CANCEL on SQL_OPERATIONS to role131;
grant role role131 to sql_user7;

?section revoke_cancel_role
log LOG131;
revoke role role131  from sql_user7;
revoke component privilege QUERY_CANCEL on SQL_OPERATIONS from role131;
drop role role131;

?section test_native
-- user3
log LOG131;
values(user);
 
-- user3 has no privs
select count(*) from hive.hive.item;

-- grant privs to user3, now user3 can execute dml
sh sqlci -i"TEST131(grant_hive_privs)" -u"SQL_User4"  ;
select count(*) from hive.hive.item;

-- revoke privs from user3, user3 can no longer perform dml
-- verify that query invalidate works as designed
sh sqlci -i"TEST131(revoke_hive_privs)" -u"SQL_User4"  ;
select count(*) from hive.hive.item;

?section grant_hive_privs
-- user4 who has been granted DB__HIVEROLE
values (user);
log LOG131;
grant select on hive.hive.item to sql_user3 by DB__HIVEROLE;

?section revoke_hive_privs
-- user4, has been granted DB__HIVEROLE
values (user);
log LOG131;
revoke select on hive.hive.item from sql_user3 by DB__HIVEROLE;

?section end_of_test
-- end of test.