| >> |
| >>-- 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 MXID11000007322212391194409553273000000000206U3334000_56_S1; |
| |
| *** ERROR[8031] Server declined cancel request for query ID MXID11000007322212391194409553273000000000206U3334000_56_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 by DB__HIVEROLE; |
| |
| --- 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 by DB__HIVEROLE; |
| |
| --- 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; |
