>>
>>-- run the test in sqlci sessions which start after authorization
>>-- is enabled.
>>log;
>>
>>obey TEST131(setup);
>>
>>create table T131a
+>  (uniq int not null,
+>   c10K int ,
+>   c1K   int,
+>   c100  int,
+>   c10   int,
+>   c1    int,
+>   primary key (uniq)
+>  )  no partition;

--- SQL operation complete.
>>
>>create table T131b
+>  (uniq int not null,
+>   c10K int ,
+>   c1K   int,
+>   c100  int,
+>   c10   int,
+>   c1    int,
+>   primary key (uniq)
+>  )  no partition;

--- SQL operation complete.
>>
>>create table T131c
+>  (uniq int not null,
+>   c10K int ,
+>   c1K   int,
+>   c100  int,
+>   c10   int,
+>   c1    int,
+>   primary key (uniq)
+>  )  no partition;

--- SQL operation complete.
>>
>>grant all on t131a to SQL_USER7;

--- SQL operation complete.
>>grant all on t131b to SQL_USER7;

--- SQL operation complete.
>>grant all on t131c to SQL_USER7;

--- SQL operation complete.
>>
>>-- setup for test_native
>>grant role DB__HIVEROLE to SQL_USER4;

--- SQL operation complete.
>>showddl role DB__HIVEROLE;

CREATE ROLE "DB__HIVEROLE";

--- SQL operation complete.
>>create external table item for hive.hive.item;

--- SQL operation complete.
>>
>>log;
>>
>>cqd AUTO_QUERY_RETRY 'OFF';

--- SQL operation complete.
>>
>>cqd CAT_ENABLE_QUERY_INVALIDATION 'ON';

--- SQL operation complete.
>>
>>prepare sel_abc from select * from t131a, t131b, t131c;

--- SQL command prepared.
>>
>>log;
Query_Invalidation_Keys explain output
  Query_Invalidation_Keys  {
>>
>>-- run a session to revoke privs for this user.
>>
>>log;
>>
>>revoke all on t131c from SQL_USER7;

--- SQL operation complete.
>>
>>-- Test that identical revokes do not make new RMS siks.
>>grant all on t131a to SQL_USER3;

--- SQL operation complete.
>>grant all on t131b to SQL_USER3;

--- SQL operation complete.
>>grant all on T131c to SQL_USER3;

--- SQL operation complete.
>>
>>revoke all on t131a from SQL_USER3;

--- SQL operation complete.
>>revoke all on t131b from SQL_USER3;

--- SQL operation complete.
>>revoke all on t131c from SQL_USER3;

--- SQL operation complete.
>>
>>log;
>>sh grep "Query Invalidation Keys *[1-9]" STATS131 >> STATS131_1;
>>
>>grant all on t131a to SQL_USER3;

--- SQL operation complete.
>>grant all on t131b to SQL_USER3;

--- SQL operation complete.
>>grant all on T131c to SQL_USER3;

--- SQL operation complete.
>>
>>revoke all on t131a from SQL_USER3;

--- SQL operation complete.
>>revoke all on t131b from SQL_USER3;

--- SQL operation complete.
>>revoke all on t131c from SQL_USER3;

--- SQL operation complete.
>>
>>log;
>>
>>execute sel_abc;

*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.SCH.T131C.

*** ERROR[8822] The statement was not prepared.

--- 0 row(s) selected.
>>
>>-- test the GET STATISTICS reporting of No. Query Invalidation Keys
>>log;
No. Query Invalidation Keys 
No. Query Invalidation Keys 
No. Query Invalidation Keys 
numQueryInvKeys: 
numQueryInvKeys: 
>>
>>exit;

End of MXCI Session

>>grant all on t131a to SQL_USER7;

--- SQL operation complete.
>>insert into t131a values(1, 1, 1, 1, 1, 1);

--- 1 row(s) inserted.
>>log;
>>
>>cqd AUTO_QUERY_RETRY_WARNINGS 'ON';

--- SQL operation complete.
>>
>>
>>set envvar sqlci_cursor '1';

--- SQL operation complete.
>>declare c3 cursor for
+>select * from t131a for update of c10k;

--- SQL operation complete.
>>prepare s3 from update t131a set c10k = c10k+22 where current of c3;

--- SQL command prepared.
>>
>>-- run a session to revoke UPDATE for this user, then grant it again.
>>
>>log;
>>
>>revoke UPDATE on t131a from SQL_USER7;

--- SQL operation complete.
>>
>>grant UPDATE on t131a to SQL_USER7;

--- SQL operation complete.
>>
>>
>>exit;

End of MXCI Session

>>
>>begin work;

--- SQL operation complete.
>>open c3;

--- SQL operation complete.
>>fetch c3;

UNIQ         C10K         C1K          C100         C10          C1
-----------  -----------  -----------  -----------  -----------  -----------

          1            1            1            1            1            1

--- 1 row(s) selected.
>>execute s3;

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.

*** WARNING[8734] Statement must be recompiled to allow privileges to be re-evaluated.

--- 1 row(s) updated.
>>commit;

--- SQL operation complete.
>>select * from t131a;

UNIQ         C10K         C1K          C100         C10          C1
-----------  -----------  -----------  -----------  -----------  -----------

          1           23            1            1            1            1

--- 1 row(s) selected.
>>
>>-- run a session to revoke UPDATE for this user.
>>
>>declare c4 cursor for
+>select * from t131a for update of c10k;

--- SQL operation complete.
>>prepare s4 from update t131a set c10k = c10k+22 where current of c4;

--- SQL command prepared.
>>
>>log;
>>
>>revoke UPDATE on t131a from SQL_USER7;

--- SQL operation complete.
>>
>>exit;

End of MXCI Session

>>
>>begin work;

--- SQL operation complete.
>>open c4;

--- SQL operation complete.
>>fetch c4;

UNIQ         C10K         C1K          C100         C10          C1
-----------  -----------  -----------  -----------  -----------  -----------

          1           23            1            1            1            1

--- 1 row(s) selected.
>>execute s4;

*** ERROR[4481] The user does not have UPDATE privilege on table or view TRAFODION.SCH.T131A.

*** ERROR[8822] The statement was not prepared.

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.

*** WARNING[8734] Statement must be recompiled to allow privileges to be re-evaluated.

--- 0 row(s) updated.
>>commit;

--- SQL operation complete.
>>select * from t131a;

UNIQ         C10K         C1K          C100         C10          C1
-----------  -----------  -----------  -----------  -----------  -----------

          1           23            1            1            1            1

--- 1 row(s) selected.
>>
>>exit;

End of MXCI Session

>>grant all on t131a to SQL_USER7;

--- SQL operation complete.
>>
>>sh sqlci -i"TEST131(priv_cancel)" -u"SQL_User7"  ;
>>
>>-- cancel my own query is allowed with no grant
>>prepare s1 from
+>values(user());

--- SQL command prepared.
>>
>>execute s1;

(EXPR)
---------------------------------------------------------------------------------------------------------------------------------

SQL_USER7                                                                                                                        

--- 1 row(s) selected.
>>
>>log;
>>
>>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;
>>control query cancel qid MXID11000007219212324915937583329000000000206U3335600_52_S1;

*** ERROR[8031] Server declined cancel request for query ID MXID11000007219212324915937583329000000000206U3335600_52_S1. The query is not in OPEN or FETCH or EXECUTE state.

--- SQL operation failed with errors.
>>
>>-- expect error 8029
>>control query cancel qid
+>MXID11000023943212197828612249700000000000206U6553500_20_S1;

*** ERROR[8029] Server declined cancel request. The user is not authorized to make this request.

*** ERROR[8822] The statement was not prepared.

>>
>>sh sqlci -i"TEST131(grant_cancel)";
>>
>>grant component privilege QUERY_CANCEL on sql_operations to SQL_USER7;

--- SQL operation complete.
>>
>>exit;

End of MXCI Session

>>
>>cqd AUTO_QUERY_RETRY_WARNINGS 'ON';

--- SQL operation complete.
>>
>>-- expect error 8026 and no AQR warning
>>control query cancel qid
+>MXID11000023943212197828612249700000000000206U6553500_20_S1;

*** ERROR[8026] Server declined cancel request. The query ID  of the targeted query was not found.

--- SQL operation failed with errors.
>>
>>prepare s1 from
+>control query cancel qid
+>MXID11000023943212197828612249700000000000206U6553500_20_S1;

--- SQL command prepared.
>>
>>-- expect error 8026 and no AQR warning
>>execute s1;

*** ERROR[8026] Server declined cancel request. The query ID  of the targeted query was not found.

--- SQL operation failed with errors.
>>
>>sh sleep 2;
>>-- expect error 8026 and AQR warning
>>execute s1;

*** ERROR[8026] Server declined cancel request. The query ID  of the targeted query was not found.

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.

*** WARNING[8734] Statement must be recompiled to allow privileges to be re-evaluated.

--- SQL operation failed with errors.
>>
>>sh sqlci -i"TEST131(revoke_cancel)";
>>
>>revoke component privilege QUERY_CANCEL on sql_operations from SQL_USER7;

--- SQL operation complete.
>>
>>exit;

End of MXCI Session

>>
>>-- expect error 8029
>>control query cancel qid
+>MXID11000023943212197828612249700000000000206U6553500_20_S1;

*** ERROR[8029] Server declined cancel request. The user is not authorized to make this request.

*** ERROR[8822] The statement was not prepared.

>>
>>sh sqlci -i"TEST131(grant_cancel_role)";
>>create role role131;

--- SQL operation complete.
>>grant component privilege QUERY_CANCEL on SQL_OPERATIONS to role131;

--- SQL operation complete.
>>grant role role131 to sql_user7;

--- SQL operation complete.
>>
>>exit;

End of MXCI Session

>>
>>-- expect error 8026
>>control query cancel qid
+>MXID11000023943212197828612249700000000000206U6553500_20_S1;

*** ERROR[8026] Server declined cancel request. The query ID  of the targeted query was not found.

--- SQL operation failed with errors.
>>
>>sh sqlci -i"TEST131(revoke_cancel_role)";
>>revoke role role131  from sql_user7;

--- SQL operation complete.
>>revoke component privilege QUERY_CANCEL on SQL_OPERATIONS from role131;

--- SQL operation complete.
>>drop role role131;

--- SQL operation complete.
>>
>>exit;

End of MXCI Session

>>
>>-- expect error 8029
>>control query cancel qid
+>MXID11000023943212197828612249700000000000206U6553500_20_S1;

*** ERROR[8029] Server declined cancel request. The user is not authorized to make this request.

*** ERROR[8822] The statement was not prepared.

>>
>>
>>exit;

End of MXCI Session

>>
>>exit;

End of MXCI Session

>>sh sqlci -i"TEST131(test_native)" -u sql_user3;
>>values(user);

(EXPR)
---------------------------------------------------------------------------------------------------------------------------------

SQL_USER3                                                                                                                        

--- 1 row(s) selected.
>>
>>-- user3 has no privs
>>select count(*) from hive.hive.item;

*** ERROR[4481] The user does not have SELECT privilege on table or view HIVE.HIVE.ITEM.

*** ERROR[8822] The statement was not prepared.

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

--- SQL operation complete.
>>
>>exit;

End of MXCI Session

>>select count(*) from hive.hive.item;

(EXPR)              
--------------------

               18000

--- 1 row(s) selected.
>>
>>-- 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"  ;
>>revoke select on hive.hive.item from sql_user3;

--- SQL operation complete.
>>
>>exit;

End of MXCI Session

>>select count(*) from hive.hive.item;

*** ERROR[4481] The user does not have SELECT privilege on table or view HIVE.HIVE.ITEM.

*** ERROR[8822] The statement was not prepared.

>>
>>exit;

End of MXCI Session

>>
>>obey TEST131(clnup);
>>
>>set schema $$TEST_SCHEMA$$;

--- SQL operation complete.
>>
>>drop table if exists T131c;

--- SQL operation complete.
>>drop table if exists T131b;

--- SQL operation complete.
>>drop table if exists T131a;

--- SQL operation complete.
>>revoke component privilege QUERY_CANCEL on sql_operations from SQL_USER7;

*** ERROR[1018] Grant of role or privilege QUERY_CANCEL on component SQL_OPERATIONS from DB__ROOT to SQL_USER7 not found, revoke request ignored.

--- SQL operation failed with errors.
>>revoke role role131  from sql_user7;

*** ERROR[1338] Role ROLE131 is not defined in the database.

--- SQL operation failed with errors.
>>revoke component privilege QUERY_CANCEL on SQL_OPERATIONS from role131;

*** ERROR[1008] Authorization identifier ROLE131 does not exist.

--- SQL operation failed with errors.
>>drop role role131;

*** ERROR[1338] Role ROLE131 is not defined in the database.

--- SQL operation failed with errors.
>>
>>-- cleanup for test_native
>>drop external table item for hive.hive.item;

--- SQL operation complete.
>>revoke role DB__HIVEROLE from SQL_USER4;

--- SQL operation complete.
>>showddl role DB__HIVEROLE;

CREATE ROLE "DB__HIVEROLE";
  -- GRANT ROLE "DB__HIVEROLE" TO "DB__ROOT" WITH ADMIN OPTION;

--- SQL operation complete.
>>
>>
>>log;
