You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by rm...@apache.org on 2016/01/12 23:47:19 UTC
[19/20] incubator-trafodion git commit: TRAFODION-1087 &
TRAFODION-1671 fixes
TRAFODION-1087 & TRAFODION-1671 fixes
TRAFODION-1087: Security Regressions take too long
Removed catman1 test suite and replaced with privs1 & privs2.
Moved catman1/TEST131 to core, TEST131 tests query invalidation.
Added back skipped privilege tests because catman1 took to long to run.
Also, cleaned up runregr scripts a bit - still need some more work.
TRAFODION-1671: Hive regression TEST009 fails
Changed the cleanup procedure to remove damaged files
Added an order by to select statement to make it more robust
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/e3d65c15
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/e3d65c15
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/e3d65c15
Branch: refs/heads/master
Commit: e3d65c1519b559a13b09b2b7766c7bb48a2882c7
Parents: 9f00dd5
Author: Roberta Marton <ro...@apache.org>
Authored: Mon Jan 11 21:44:18 2016 +0000
Committer: Roberta Marton <ro...@apache.org>
Committed: Mon Jan 11 21:44:18 2016 +0000
----------------------------------------------------------------------
core/sql/regress/catman1/DIFF133.KNOWN | 25 -
core/sql/regress/catman1/DIFF136.KNOWN | 11 -
core/sql/regress/catman1/DIFF139.KNOWN | 6 -
core/sql/regress/catman1/EXPECTED129 | 1237 -------------
core/sql/regress/catman1/EXPECTED131 | 491 -----
core/sql/regress/catman1/EXPECTED132 | 2214 -----------------------
core/sql/regress/catman1/EXPECTED133 | 453 -----
core/sql/regress/catman1/EXPECTED135 | 1354 --------------
core/sql/regress/catman1/EXPECTED136 | 870 ---------
core/sql/regress/catman1/EXPECTED137 | 2501 --------------------------
core/sql/regress/catman1/EXPECTED138 | 1692 -----------------
core/sql/regress/catman1/EXPECTED139 | 585 ------
core/sql/regress/catman1/EXPECTED140 | 926 ----------
core/sql/regress/catman1/EXPECTED141 | Bin 78469 -> 0 bytes
core/sql/regress/catman1/FILTER132 | 40 -
core/sql/regress/catman1/TEST129 | 442 -----
core/sql/regress/catman1/TEST131 | 365 ----
core/sql/regress/catman1/TEST132 | 447 -----
core/sql/regress/catman1/TEST133 | 236 ---
core/sql/regress/catman1/TEST135 | 465 -----
core/sql/regress/catman1/TEST136 | 400 ----
core/sql/regress/catman1/TEST137 | 608 -------
core/sql/regress/catman1/TEST138 | 347 ----
core/sql/regress/catman1/TEST139 | 325 ----
core/sql/regress/catman1/TEST140 | 433 -----
core/sql/regress/catman1/TEST141 | 394 ----
core/sql/regress/catman1/cidefs | 116 --
core/sql/regress/catman1/etest140.cpp | 98 -
core/sql/regress/catman1/runregr | 31 -
core/sql/regress/catman1/udrtest135.cpp | 55 -
core/sql/regress/core/EXPECTED131 | 491 +++++
core/sql/regress/core/TEST000 | 14 +-
core/sql/regress/core/TEST131 | 365 ++++
core/sql/regress/hive/EXPECTED009 | 24 +-
core/sql/regress/hive/TEST009 | 14 +-
core/sql/regress/privs1/DIFF133.KNOWN | 25 +
core/sql/regress/privs1/DIFF136.KNOWN | 11 +
core/sql/regress/privs1/EXPECTED132 | 2214 +++++++++++++++++++++++
core/sql/regress/privs1/EXPECTED133 | 453 +++++
core/sql/regress/privs1/EXPECTED136 | 870 +++++++++
core/sql/regress/privs1/EXPECTED137 | 2501 ++++++++++++++++++++++++++
core/sql/regress/privs1/EXPECTED141 | Bin 0 -> 78469 bytes
core/sql/regress/privs1/FILTER132 | 40 +
core/sql/regress/privs1/TEST132 | 447 +++++
core/sql/regress/privs1/TEST133 | 236 +++
core/sql/regress/privs1/TEST136 | 400 ++++
core/sql/regress/privs1/TEST137 | 608 +++++++
core/sql/regress/privs1/TEST141 | 394 ++++
core/sql/regress/privs1/etest132.cpp | 55 +
core/sql/regress/privs1/etest141.cpp | 98 +
core/sql/regress/privs2/DIFF139.KNOWN | 6 +
core/sql/regress/privs2/EXPECTED129 | 1237 +++++++++++++
core/sql/regress/privs2/EXPECTED135 | 1354 ++++++++++++++
core/sql/regress/privs2/EXPECTED138 | 1692 +++++++++++++++++
core/sql/regress/privs2/EXPECTED139 | 585 ++++++
core/sql/regress/privs2/EXPECTED140 | 926 ++++++++++
core/sql/regress/privs2/FILTER140 | 33 +
core/sql/regress/privs2/TEST129 | 442 +++++
core/sql/regress/privs2/TEST135 | 465 +++++
core/sql/regress/privs2/TEST138 | 347 ++++
core/sql/regress/privs2/TEST139 | 325 ++++
core/sql/regress/privs2/TEST140 | 433 +++++
core/sql/regress/privs2/etest140.cpp | 98 +
core/sql/regress/privs2/udrtest135.cpp | 55 +
core/sql/regress/tools/runallsb | 12 +-
core/sql/regress/tools/runregr | 227 +--
core/sql/regress/tools/runregr_core.ksh | 2 +-
core/sql/regress/tools/setupenv | 52 +-
core/sql/regress/tools/setuplnxenv | 25 +-
69 files changed, 17331 insertions(+), 17412 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/e3d65c15/core/sql/regress/catman1/DIFF133.KNOWN
----------------------------------------------------------------------
diff --git a/core/sql/regress/catman1/DIFF133.KNOWN b/core/sql/regress/catman1/DIFF133.KNOWN
deleted file mode 100644
index 3ad9979..0000000
--- a/core/sql/regress/catman1/DIFF133.KNOWN
+++ /dev/null
@@ -1,25 +0,0 @@
-288,290d287
-<
-<
-<
-326,343c323
-< OBJECT_NAME TYPE GRANTEE PRIVILEGES_BITMAP GRANTABLE_BITMAP
-< ----------
-<
-< TRAFODION."PRIVMGR_MD"."COMPONENTS" BT DB__ROOT 47 47
-< TRAFODION."PRIVMGR_MD"."COMPONENT_OPERAT BT DB__ROOT 47 47
-< TRAFODION."PRIVMGR_MD"."COMPONENT_PRIVIL BT DB__ROOT 47 47
-< TRAFODION."PRIVMGR_MD"."OBJECT_PRIVILEGE BT DB__ROOT 47 47
-< TRAFODION."PRIVMGR_MD"."ROLE_USAGE" BT DB__ROOT 47 47
-< TRAFODION."T133SCH"."GAMES" BT DB__ROOT 47 47
-< TRAFODION."T133SCH"."GAMES_BY_PLAYER" VI DB__ROOT 1 0
-< TRAFODION."T133SCH"."HOME_TEAMS_GAMES" VI DB__ROOT 1 0
-< TRAFODION."T133SCH"."PLAYERS" BT DB__ROOT 47 47
-< TRAFODION."T133SCH"."PLAYERS_ON_TEAM" VI DB__ROOT 1 0
-< TRAFODION."T133SCH"."STANDINGS" BT DB__ROOT 47 47
-< TRAFODION."T133SCH"."TEAMS" BT DB__ROOT 47 47
-< TRAFODION."T133SCH"."TEAM_STATISTICS" BT DB__ROOT 47 47
-<
-< --- 13 row(s) selected.
----
-> --- 0 row(s) selected.
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/e3d65c15/core/sql/regress/catman1/DIFF136.KNOWN
----------------------------------------------------------------------
diff --git a/core/sql/regress/catman1/DIFF136.KNOWN b/core/sql/regress/catman1/DIFF136.KNOWN
deleted file mode 100644
index 51f6dac..0000000
--- a/core/sql/regress/catman1/DIFF136.KNOWN
+++ /dev/null
@@ -1,11 +0,0 @@
-260,269d259
-< SQL_USER1
-< SQL_USER10
-< SQL_USER2
-< SQL_USER3
-< SQL_USER4
-< SQL_USER5
-< SQL_USER6
-< SQL_USER7
-< SQL_USER8
-< SQL_USER9
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/e3d65c15/core/sql/regress/catman1/DIFF139.KNOWN
----------------------------------------------------------------------
diff --git a/core/sql/regress/catman1/DIFF139.KNOWN b/core/sql/regress/catman1/DIFF139.KNOWN
deleted file mode 100644
index 3b4dff1..0000000
--- a/core/sql/regress/catman1/DIFF139.KNOWN
+++ /dev/null
@@ -1,6 +0,0 @@
-209c209,211
-< --- SQL operation complete.
----
-> *** ERROR[1034] Unable to obtain privileges
->
-> --- SQL operation failed with errors.
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/e3d65c15/core/sql/regress/catman1/EXPECTED129
----------------------------------------------------------------------
diff --git a/core/sql/regress/catman1/EXPECTED129 b/core/sql/regress/catman1/EXPECTED129
deleted file mode 100644
index 36f0835..0000000
--- a/core/sql/regress/catman1/EXPECTED129
+++ /dev/null
@@ -1,1237 +0,0 @@
->>log LOG129;
->>create schema us4;
-
---- SQL operation complete.
->>set schema us4;
-
---- SQL operation complete.
->>
->>create table t1 (col1 int not null primary key, col2 int, col3 int) no partition;
-
---- SQL operation complete.
->>insert into t1 values (1,1,1), (2,2,2), (5,5,5), (7,7,7);
-
---- 4 row(s) inserted.
->>create view v1(vc1,vc2,vc3) as select * from t1 ;
-
---- SQL operation complete.
->>
->>grant update on t1 to sql_user1 ;
-
---- SQL operation complete.
->>grant select(col3) on t1 to sql_user1;
-
---- SQL operation complete.
->>grant select on v1 to sql_user1;
-
---- SQL operation complete.
->>grant update(vc1) on v1 to sql_user1 ;
-
---- SQL operation complete.
->>grant insert(vc1) on v1 to sql_user1 ;
-
---- SQL operation complete.
->>
->>grant select on t1 to sql_user2;
-
---- SQL operation complete.
->>grant update (col3) on t1 to sql_user2 ;
-
---- SQL operation complete.
->>grant update(col2) on t1 to sql_user2;
-
---- SQL operation complete.
->>
->>grant update(col1) on t1 to sql_user3;
-
---- SQL operation complete.
->>grant select(col1) on t1 to sql_user3;
-
---- SQL operation complete.
->>showddl t1;
-
-CREATE TABLE TRAFODION.US4.T1
- (
- COL1 INT NO DEFAULT NOT NULL NOT DROPPABLE
- , COL2 INT DEFAULT NULL
- , COL3 INT DEFAULT NULL
- , PRIMARY KEY (COL1 ASC)
- )
-;
-
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.US4.T1 TO SQL_USER4 WITH GRANT OPTION;
- GRANT UPDATE ON TRAFODION.US4.T1 TO SQL_USER1;
-GRANT SELECT ON
- TRAFODION.US4.T1 TO SQL_USER2;
-GRANT SELECT(COL3) ON TRAFODION.US4.T1 TO
- SQL_USER1;
-GRANT UPDATE(COL2, COL3) ON TRAFODION.US4.T1 TO SQL_USER2;
-GRANT
- SELECT(COL1), UPDATE(COL1) ON TRAFODION.US4.T1 TO SQL_USER3;
-
---- SQL operation complete.
->>
->>create table t2( a int not null primary key, b int default null, c int default null) ;
-
---- SQL operation complete.
->>grant insert on t2 to sql_user1;
-
---- SQL operation complete.
->>grant select on t2 to sql_user1;
-
---- SQL operation complete.
->>grant insert(a,b) on t2 to sql_user2 ;
-
---- SQL operation complete.
->>grant select(a,b) on t2 to sql_user2 ;
-
---- SQL operation complete.
->>grant insert(a) on t2 to sql_user3;
-
---- SQL operation complete.
->>grant select(a) on t2 to sql_user3;
-
---- SQL operation complete.
->>showddl t2;
-
-CREATE TABLE TRAFODION.US4.T2
- (
- A INT NO DEFAULT NOT NULL NOT DROPPABLE
- , B INT DEFAULT NULL
- , C INT DEFAULT NULL
- , PRIMARY KEY (A ASC)
- )
-;
-
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.US4.T2 TO SQL_USER4 WITH GRANT OPTION;
- GRANT SELECT, INSERT ON TRAFODION.US4.T2 TO SQL_USER1;
-GRANT SELECT(A, B),
- INSERT(A, B) ON TRAFODION.US4.T2 TO SQL_USER2;
-GRANT SELECT(A), INSERT(A) ON
- TRAFODION.US4.T2 TO SQL_USER3;
-
---- SQL operation complete.
->>
->>create table t129_starter (a int not null, primary key(a) NOT DROPPABLE )no partition;
-
---- SQL operation complete.
->>insert into t129_starter values (1);
-
---- 1 row(s) inserted.
->>
->>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 ;
-
---- SQL operation complete.
->>
->>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
-+> ;
-
---- 1000 row(s) inserted.
->>
->>update statistics for table t129_a on uniq ;
-
---- SQL operation complete.
->>
->>grant select on t129_a to sql_user1 ;
-
---- SQL operation complete.
->>grant select on t129_starter to sql_user1 ;
-
---- SQL operation complete.
->>
->>-- 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));
-
---- SQL operation complete.
->>create table t4 (e int not null not droppable, f int, g int, h int, primary key (e));
-
---- SQL operation complete.
->>
->>grant select (b,d) on t3 to sql_user3 with grant option;
-
---- SQL operation complete.
->>grant select (f,g) on t4 to sql_user3;
-
---- SQL operation complete.
->>
->>showddl t3;
-
-CREATE TABLE TRAFODION.US4.T3
- (
- A INT NO DEFAULT NOT NULL NOT DROPPABLE
- , B INT DEFAULT NULL
- , C INT DEFAULT NULL
- , D INT DEFAULT NULL
- , PRIMARY KEY (A ASC)
- )
-;
-
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.US4.T3 TO SQL_USER4 WITH GRANT OPTION;
- GRANT SELECT(B, D) ON TRAFODION.US4.T3 TO SQL_USER3 WITH GRANT OPTION;
-
---- SQL operation complete.
->>showddl t4;
-
-CREATE TABLE TRAFODION.US4.T4
- (
- E INT NO DEFAULT NOT NULL NOT DROPPABLE
- , F INT DEFAULT NULL
- , G INT DEFAULT NULL
- , H INT DEFAULT NULL
- , PRIMARY KEY (E ASC)
- )
-;
-
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.US4.T4 TO SQL_USER4 WITH GRANT OPTION;
- GRANT SELECT(F, G) ON TRAFODION.US4.T4 TO SQL_USER3;
-
---- SQL operation complete.
->>
->>insert into t3 values (1,2,3,4), (5,6,7,8);
-
---- 2 row(s) inserted.
->>insert into t4 values (11,22,33,44), (55,66,77,88);
-
---- 2 row(s) inserted.
->>
->>exit;
-
-End of MXCI Session
-
->>-- As user1, should fail
->>select * from us4.t1 ;
-
-*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.US4.T1(columns: COL1, COL2).
-
-*** ERROR[8822] The statement was not prepared.
-
->>delete from us4.t1;
-
-*** ERROR[4481] The user does not have DELETE privilege on table or view TRAFODION.US4.T1.
-
-*** ERROR[8822] The statement was not prepared.
-
->>insert into us4.t1 values (1,1,1);
-
-*** ERROR[4481] The user does not have INSERT privilege on table or view TRAFODION.US4.T1.
-
-*** ERROR[8822] The statement was not prepared.
-
->>update us4.t2 set b = c ;
-
-*** ERROR[4481] The user does not have UPDATE privilege on table or view TRAFODION.US4.T2.
-
-*** ERROR[8822] The statement was not prepared.
-
->>update us4.v1 set vc2 = vc1 ;
-
-*** ERROR[4481] The user does not have UPDATE privilege on table or view TRAFODION.US4.V1(columns: VC2).
-
-*** ERROR[8822] The statement was not prepared.
-
->>insert into us4.v1(vc1,vc2) values (10,10) ;
-
-*** ERROR[4481] The user does not have INSERT privilege on table or view TRAFODION.US4.V1(columns: VC2).
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>-- As user1, should succeed
->>update us4.t1 set col3 = 2 ;
-
---- 4 row(s) updated.
->>select * from us4.v1 order by vc1;
-
-VC1 VC2 VC3
------------ ----------- -----------
-
- 1 1 2
- 2 2 2
- 5 5 2
- 7 7 2
-
---- 4 row(s) selected.
->>insert into us4.t2 values (5,5,5);
-
---- 1 row(s) inserted.
->>update us4.v1 set vc1 = vc1 ;
-
---- 4 row(s) updated.
->>insert into us4.v1(vc1) values (10) ;
-
---- 1 row(s) inserted.
->>
->>delete all from table(querycache()) ;
-
-*** ERROR[15001] A syntax error occurred at or before:
-delete all from table(querycache()) ;
- ^ (34 characters from start of SQL statement)
-
-*** ERROR[8822] The statement was not prepared.
-
->>delete all from table(natablecache());
-
---- 0 row(s) deleted.
->>select * from table(querycacheentries());
-
-*** ERROR[15001] A syntax error occurred at or before:
-select * from table(querycacheentries());
- ^ (39 characters from start of SQL statement)
-
-*** ERROR[8822] The statement was not prepared.
-
->>select * from table(natablecache()) ;
-
-*** ERROR[15001] A syntax error occurred at or before:
-select * from table(natablecache()) ;
- ^ (34 characters from start of SQL statement)
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>log;
->>-- As user1, should fail
->>select * from us4.t1 ;
-
-*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.US4.T1(columns: COL1, COL2).
-
-*** ERROR[8822] The statement was not prepared.
-
->>delete from us4.t1;
-
-*** ERROR[4481] The user does not have DELETE privilege on table or view TRAFODION.US4.T1.
-
-*** ERROR[8822] The statement was not prepared.
-
->>insert into us4.t1 values (1,1,1);
-
-*** ERROR[4481] The user does not have INSERT privilege on table or view TRAFODION.US4.T1.
-
-*** ERROR[8822] The statement was not prepared.
-
->>update us4.t2 set b = c ;
-
-*** ERROR[4481] The user does not have UPDATE privilege on table or view TRAFODION.US4.T2.
-
-*** ERROR[8822] The statement was not prepared.
-
->>update us4.v1 set vc2 = vc1 ;
-
-*** ERROR[4481] The user does not have UPDATE privilege on table or view TRAFODION.US4.V1(columns: VC2).
-
-*** ERROR[8822] The statement was not prepared.
-
->>insert into us4.v1(vc1,vc2) values (10,10) ;
-
-*** ERROR[4481] The user does not have INSERT privilege on table or view TRAFODION.US4.V1(columns: VC2).
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>-- As user1, should succeed
->>update us4.t1 set col3 = 2 ;
-
---- 5 row(s) updated.
->>select * from us4.v1 order by vc1;
-
-VC1 VC2 VC3
------------ ----------- -----------
-
- 1 1 2
- 2 2 2
- 5 5 2
- 7 7 2
- 10 ? 2
-
---- 5 row(s) selected.
->>insert into us4.t2 values (5,5,5);
-
-*** ERROR[8102] The operation is prevented by a unique constraint.
-
---- 0 row(s) inserted.
->>update us4.v1 set vc1 = vc1 ;
-
---- 5 row(s) updated.
->>insert into us4.v1(vc1) values (10) ;
-
-*** ERROR[8102] The operation is prevented by a unique constraint.
-
---- 0 row(s) inserted.
->>
->>delete all from table(querycache()) ;
-
-*** ERROR[15001] A syntax error occurred at or before:
-delete all from table(querycache()) ;
- ^ (34 characters from start of SQL statement)
-
-*** ERROR[8822] The statement was not prepared.
-
->>delete all from table(natablecache());
-
---- 0 row(s) deleted.
->>select * from table(querycacheentries());
-
-*** ERROR[15001] A syntax error occurred at or before:
-select * from table(querycacheentries());
- ^ (39 characters from start of SQL statement)
-
-*** ERROR[8822] The statement was not prepared.
-
->>select * from table(natablecache()) ;
-
-*** ERROR[15001] A syntax error occurred at or before:
-select * from table(natablecache()) ;
- ^ (34 characters from start of SQL statement)
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>log;
->>-- As user2, should fail
->>delete from us4.t1 ;
-
-*** ERROR[4481] The user does not have DELETE privilege on table or view TRAFODION.US4.T1.
-
-*** ERROR[8822] The statement was not prepared.
-
->>insert into us4.t1 values (1,1,1);
-
-*** ERROR[4481] The user does not have INSERT privilege on table or view TRAFODION.US4.T1.
-
-*** ERROR[8822] The statement was not prepared.
-
->>select * from us4.v1 ;
-
-*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.US4.V1.
-
-*** ERROR[8822] The statement was not prepared.
-
->>insert into us4.t2 values (1,1,1);
-
-*** ERROR[4481] The user does not have INSERT privilege on table or view TRAFODION.US4.T2(columns: C).
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>-- As user2, should succeed
->>update us4.t1 set col3 = 4 ;
-
---- 5 row(s) updated.
->>update us4.t1 set col2 = 3 ;
-
---- 5 row(s) updated.
->>select * from us4.t1 order by col1;
-
-COL1 COL2 COL3
------------ ----------- -----------
-
- 1 3 4
- 2 3 4
- 5 3 4
- 7 3 4
- 10 3 4
-
---- 5 row(s) selected.
->>insert into us4.t2(a,b) values (1,1);
-
---- 1 row(s) inserted.
->>log;
->>-- as user3
->>insert into us4.t2(a,b) values (2,2) ;
-
-*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.US4.T2(columns: B).
-
-*** ERROR[4481] The user does not have INSERT privilege on table or view TRAFODION.US4.T2(columns: B).
-
-*** ERROR[8822] The statement was not prepared.
-
->>update us4.t1 set col2 = col1;
-
-*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.US4.T1(columns: COL2).
-
-*** ERROR[4481] The user does not have UPDATE privilege on table or view TRAFODION.US4.T1(columns: COL2).
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>
->>insert into us4.t2(a) values (3) ;
-
---- 1 row(s) inserted.
->>update us4.t1 set col1 = col1 + 1;
-
---- 5 row(s) updated.
->>
->>select col1 from us4.t1 order by 1;
-
-COL1
------------
-
- 2
- 3
- 6
- 8
- 11
-
---- 5 row(s) selected.
->>select col2 from us4.t1 ;
-
-*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.US4.T1(columns: COL2).
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>select col1 from us4.t1 where col2 > 100;
-
-*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.US4.T1(columns: COL2).
-
-*** ERROR[8822] The statement was not prepared.
-
->>select col1 from us4.t1 where col1 > 100 order by 1;
-
---- 0 row(s) selected.
->>
->>select count(*), min(col1) from us4.t1 group by col2;
-
-*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.US4.T1(columns: COL2).
-
-*** ERROR[8822] The statement was not prepared.
-
->>select count(*), min(col1) from us4.t1 group by col1;
-
-(EXPR) (EXPR)
--------------------- -----------
-
- 1 2
- 1 3
- 1 6
- 1 8
- 1 11
-
---- 5 row(s) selected.
->>
->>select count(*) from us4.t1 group by col1 having min(col2) > 10;
-
-*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.US4.T1(columns: COL2).
-
-*** ERROR[8822] The statement was not prepared.
-
->>select count(*) from us4.t1 group by col1 having min(col1) > 10;
-
-(EXPR)
---------------------
-
- 1
-
---- 1 row(s) selected.
->>
->>select x from us4.t1 transpose col2 as x;
-
-*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.US4.T1(columns: COL2).
-
-*** ERROR[8822] The statement was not prepared.
-
->>select x from us4.t1 transpose col1 as x order by 1;
-
-X
------------
-
- 2
- 3
- 6
- 8
- 11
-
---- 5 row(s) selected.
->>
->>select col1 from us4.t1 sample random balance
-+>when col2 = 1 then 100 percent else 0 percent end;
-
-*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.US4.T1(columns: COL2).
-
-*** ERROR[8822] The statement was not prepared.
-
->>select col1 from us4.t1 sample random balance
-+>when col1 = 1 then 100 percent else 0 percent end order by 1;
-
---- 0 row(s) selected.
->>
->>select col1 from us4.t1 where exists (select 1 from (values (us4.t1.col2)) t2(x) where t2.x = 100);
-
-*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.US4.T1(columns: COL2).
-
-*** ERROR[8822] The statement was not prepared.
-
->>select col1 from us4.t1 where exists (select 1 from (values (us4.t1.col1)) t2(x) where t2.x = 100) order by 1;
-
---- 0 row(s) selected.
->>
->>select * from us4.t1 ;
-
-*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.US4.T1(columns: COL2, COL3).
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>log;
->>-- Testing create view based on column-level SELECT
->>-- as user3
->>
->>set schema cat.us4;
-
---- SQL operation complete.
->>
->>-- View on single table (positive):
->>
->>create view v3bd as select b,d from t3;
-
-*** ERROR[4222] The DDL feature is not supported in this software version.
-
-*** ERROR[8822] The statement was not prepared.
-
->>select * from v3bd;
-
-*** ERROR[1002] Catalog CAT does not exist or has not been registered on node .
-
-*** ERROR[8822] The statement was not prepared.
-
->>create view v3b as select b from t3;
-
-*** ERROR[4222] The DDL feature is not supported in this software version.
-
-*** ERROR[8822] The statement was not prepared.
-
->>select * from v3b;
-
-*** ERROR[1002] Catalog CAT does not exist or has not been registered on node .
-
-*** ERROR[8822] The statement was not prepared.
-
->>create view v3d as select d from t3;
-
-*** ERROR[4222] The DDL feature is not supported in this software version.
-
-*** ERROR[8822] The statement was not prepared.
-
->>select * from v3d;
-
-*** ERROR[1002] Catalog CAT does not exist or has not been registered on node .
-
-*** ERROR[8822] The statement was not prepared.
-
->>create view v3bbbbbb (c1,c2,c3,c4,c5,c6) as select b,b,b,b,b,b from t3;
-
-*** ERROR[4222] The DDL feature is not supported in this software version.
-
-*** ERROR[8822] The statement was not prepared.
-
->>select * from v3bbbbbb;
-
-*** ERROR[1002] Catalog CAT does not exist or has not been registered on node .
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>-- View on single table (negative):
->>
->>create view v3ac as select a,c from t3;
-
-*** ERROR[4222] The DDL feature is not supported in this software version.
-
-*** ERROR[8822] The statement was not prepared.
-
->>create view v3a as select a from t3;
-
-*** ERROR[4222] The DDL feature is not supported in this software version.
-
-*** ERROR[8822] The statement was not prepared.
-
->>create view v3c as select c from t3;
-
-*** ERROR[4222] The DDL feature is not supported in this software version.
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>create view v3 as select * from t3;
-
-*** ERROR[4222] The DDL feature is not supported in this software version.
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>create view v3ab as select a,b from t3;
-
-*** ERROR[4222] The DDL feature is not supported in this software version.
-
-*** ERROR[8822] The statement was not prepared.
-
->>create view v3abcd as select a,b,c,d from t3;
-
-*** ERROR[4222] The DDL feature is not supported in this software version.
-
-*** ERROR[8822] The statement was not prepared.
-
->>create view v3bc as select b,c from t3;
-
-*** ERROR[4222] The DDL feature is not supported in this software version.
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>-- View on two tables (positive):
->>
->>create view v34bf as select b,f from t3, t4;
-
-*** ERROR[4222] The DDL feature is not supported in this software version.
-
-*** ERROR[8822] The statement was not prepared.
-
->>select * from v34bf;
-
-*** ERROR[1002] Catalog CAT does not exist or has not been registered on node .
-
-*** ERROR[8822] The statement was not prepared.
-
->>create view v34bdfg as select b,d,f,g from t3, t4;
-
-*** ERROR[4222] The DDL feature is not supported in this software version.
-
-*** ERROR[8822] The statement was not prepared.
-
->>select * from v34bdfg;
-
-*** ERROR[1002] Catalog CAT does not exist or has not been registered on node .
-
-*** ERROR[8822] The statement was not prepared.
-
->>create view v34bdfg2 (c1,c2,c3,c4) as select b,d,f,g from t3, t4;
-
-*** ERROR[4222] The DDL feature is not supported in this software version.
-
-*** ERROR[8822] The statement was not prepared.
-
->>select * from v34bdfg2;
-
-*** ERROR[1002] Catalog CAT does not exist or has not been registered on node .
-
-*** ERROR[8822] The statement was not prepared.
-
->>create view v34gb as select g,b from t3, t4;
-
-*** ERROR[4222] The DDL feature is not supported in this software version.
-
-*** ERROR[8822] The statement was not prepared.
-
->>select * from v34gb;
-
-*** ERROR[1002] Catalog CAT does not exist or has not been registered on node .
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>-- View on two tables (negative):
->>
->>create view v34 as select * from t3,t4;
-
-*** ERROR[4222] The DDL feature is not supported in this software version.
-
-*** ERROR[8822] The statement was not prepared.
-
->>create view v34af as select a,f from t3, t4;
-
-*** ERROR[4222] The DDL feature is not supported in this software version.
-
-*** ERROR[8822] The statement was not prepared.
-
->>create view v34bh as select b,h from t3,t4;
-
-*** ERROR[4222] The DDL feature is not supported in this software version.
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>
->>log;
->>-- Testing revoke for column-level SELECT
->>-- as user4
->>
->>set schema us4;
-
---- SQL operation complete.
->>
->>grant select (b,d) on t3 to sql_user3 with grant option;
-
---- SQL operation complete.
->>grant select (f,g) on t4 to sql_user3;
-
---- SQL operation complete.
->>showddl t3;
-
-CREATE TABLE TRAFODION.US4.T3
- (
- A INT NO DEFAULT NOT NULL NOT DROPPABLE
- , B INT DEFAULT NULL
- , C INT DEFAULT NULL
- , D INT DEFAULT NULL
- , PRIMARY KEY (A ASC)
- )
-;
-
---- SQL operation complete.
->>showddl t4;
-
-CREATE TABLE TRAFODION.US4.T4
- (
- E INT NO DEFAULT NOT NULL NOT DROPPABLE
- , F INT DEFAULT NULL
- , G INT DEFAULT NULL
- , H INT DEFAULT NULL
- , PRIMARY KEY (E ASC)
- )
-;
-
---- SQL operation complete.
->>
->>-- Negative tests: revoking from table
->>revoke select (c) on t3 from sql_user3;
-
-*** ERROR[1018] Grant of role or privilege SELECT on TRAFODION.US4.T3 from SQL_USER4 to SQL_USER3 not found, revoke request ignored.
-
---- SQL operation failed with errors.
->>revoke select (b) on t3 from sql_user3;
-
---- SQL operation complete.
->>
->>revoke grant option for select (c) on t3 from sql_user3;
-
-*** ERROR[1018] Grant of role or privilege SELECT on TRAFODION.US4.T3 from SQL_USER4 to SQL_USER3 not found, revoke request ignored.
-
---- SQL operation failed with errors.
->>
->>revoke grant option for select (d) on t3 from sql_user3;
-
---- SQL operation complete.
->>
->>revoke grant option for select (b) on t3 from sql_user3;
-
-*** ERROR[1018] Grant of role or privilege SELECT on TRAFODION.US4.T3 from SQL_USER4 to SQL_USER3 not found, revoke request ignored.
-
---- SQL operation failed with errors.
->>revoke grant option for select (b) on t3 from sql_user3 cascade;
-
-*** ERROR[1018] Grant of role or privilege SELECT on TRAFODION.US4.T3 from SQL_USER4 to SQL_USER3 not found, revoke request ignored.
-
---- SQL operation failed with errors.
->>showddl t3;
-
-CREATE TABLE TRAFODION.US4.T3
- (
- A INT NO DEFAULT NOT NULL NOT DROPPABLE
- , B INT DEFAULT NULL
- , C INT DEFAULT NULL
- , D INT DEFAULT NULL
- , PRIMARY KEY (A ASC)
- )
-;
-
---- SQL operation complete.
->>
->>exit;
-
-End of MXCI Session
-
->>-- as user1
->>
->>cqd query_cache '0' ;
-
---- SQL operation complete.
->>cqd metadata_cache_size '0' ;
-
---- SQL operation complete.
->>
->>select count(*) from us4.t129_a ;
-
-(EXPR)
---------------------
-
- 1000
-
---- 1 row(s) selected.
->>
->>sh sh runmxci.ksh -i "TEST129(revoke1)" -u sql_user4;
->>revoke select on us4.t129_a from sql_user1 ;
-
---- SQL operation complete.
->>
->>exit;
-
-End of MXCI Session
-
->>-- 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 ;
-
-*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.US4.T129_A.
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>cqd metadata_cache_size reset ;
-
---- SQL operation complete.
->>
->>select case when current_cache_size > 0 then 1 else 0 end from table(natablecache());
-
-*** ERROR[15001] A syntax error occurred at or before:
-select case when current_cache_size > 0 then 1 else 0 end from table(natablecac
-he());
- ^ (83 characters from start of SQL statement)
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>--should error but place t129_a in natable cache
->>prepare s1 from select * from us4.t129_a where c1 > 10;
-
-*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.US4.T129_A.
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>select case when current_cache_size > 0 then 1 else 0 end from table(natablecache());
-
-*** ERROR[15001] A syntax error occurred at or before:
-select case when current_cache_size > 0 then 1 else 0 end from table(natablecac
-he());
- ^ (83 characters from start of SQL statement)
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>sh sh runmxci.ksh -i "TEST129(grant1)" -u sql_user4;
->>grant select on us4.t129_a to sql_user1 ;
-
---- SQL operation complete.
->>
->>exit;
-
-End of MXCI Session
-
->>
->>
->>-- should succeed
->>prepare s1 from select * from us4.t129_a as t1, us4.t129_a as t2;
-
---- SQL command prepared.
->>
->>cqd query_cache reset ;
-
---- SQL operation complete.
->>cqd query_text_cache 'off' ;
-
---- SQL operation complete.
->>
->>select count(*) from us4.t129_a where c1 = 10 ;
-
-(EXPR)
---------------------
-
- 0
-
---- 1 row(s) selected.
->>
->>select num_entries from table(querycache());
-
-*** ERROR[15001] A syntax error occurred at or before:
-select num_entries from table(querycache());
- ^ (42 characters from start of SQL statement)
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>sh sh runmxci.ksh -i "TEST129(revoke1)" -u sql_user4;
->>revoke select on us4.t129_a from sql_user1 ;
-
---- SQL operation complete.
->>
->>exit;
-
-End of MXCI Session
-
->>-- 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 ;
-
-*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.US4.T129_A.
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>-- cache has 1 entry
->>select num_entries from table(querycache());
-
-*** ERROR[15001] A syntax error occurred at or before:
-select num_entries from table(querycache());
- ^ (42 characters from start of SQL statement)
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>
->>cqd query_text_cache reset ;
-
---- SQL operation complete.
->>cqd auto_query_retry 'off' ;
-
---- SQL operation complete.
->>
->>sh sh runmxci.ksh -i "TEST129(grant1)" -u sql_user4;
->>grant select on us4.t129_a to sql_user1 ;
-
---- SQL operation complete.
->>
->>exit;
-
-End of MXCI Session
-
->>
->>select count(*) from us4.t129_a where c1 = 100 ;
-
-(EXPR)
---------------------
-
- 0
-
---- 1 row(s) selected.
->>
->>sh sh runmxci.ksh -i "TEST129(revoke1)" -u sql_user4;
->>revoke select on us4.t129_a from sql_user1 ;
-
---- SQL operation complete.
->>
->>exit;
-
-End of MXCI Session
-
->>-- 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 ;
-
-*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.US4.T129_A.
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>sh sh runmxci.ksh -i "TEST129(grant1)" -u sql_user4;
->>grant select on us4.t129_a to sql_user1 ;
-
---- SQL operation complete.
->>
->>exit;
-
-End of MXCI Session
-
->>
->>cqd auto_query_retry reset ;
-
---- SQL operation complete.
->>cqd auto_query_retry_warnings 'on' ;
-
---- SQL operation complete.
->>
->>
->>select count(*) from us4.t129_a where c10 = 100 ;
-
-(EXPR)
---------------------
-
- 0
-
---- 1 row(s) selected.
->>
->>sh sh runmxci.ksh -i "TEST129(grant2)" -u sql_user4;
->>grant select on us4.t129_a to sql_user2 ;
-
---- SQL operation complete.
->>
->>exit;
-
-End of MXCI Session
-
->>
->>
->>select count(*) from us4.t129_a where c10 = 100 ;
-
-(EXPR)
---------------------
-
- 0
-
---- 1 row(s) selected.
->>
->>cqd auto_query_retry_warnings reset ;
-
---- SQL operation complete.
->>prepare s1 from select count(*) from us4.t129_a where c10 < 100 ;
-
---- SQL command prepared.
->>
->>sh sh runmxci.ksh -i "TEST129(revoke1)" -u sql_user4;
->>revoke select on us4.t129_a from sql_user1 ;
-
---- SQL operation complete.
->>
->>exit;
-
-End of MXCI Session
-
->>-- 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 ;
-
-*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.US4.T129_A.
-
-*** ERROR[8822] The statement was not prepared.
-
---- 0 row(s) selected.
->>
->>sh sh runmxci.ksh -i "TEST129(grant1)" -u sql_user4;
->>grant select on us4.t129_a to sql_user1 ;
-
---- SQL operation complete.
->>
->>exit;
-
-End of MXCI Session
-
->>
->>set envvar sqlci_cursor '1';
-
---- SQL operation complete.
->>cqd attempt_esp_parallelism 'off' ;
-
---- SQL operation complete.
->>cqd nested_joins 'off' ;
-
---- SQL operation complete.
->>cqd merge_joins 'off' ;
-
---- SQL operation complete.
->>
->>declare c1 cursor for
-+>select uniq from us4.t129_a, us4.t129_starter
-+>where filler = 'a' or a = 1 for read uncommitted access ;
-
---- SQL operation complete.
->>
->>open c1 ;
-
---- SQL operation complete.
->>
->>sh sh runmxci.ksh -i "TEST129(grant3)" -u sql_user4;
->>grant select on us4.t129_a to sql_user3 ;
-
---- SQL operation complete.
->>
->>
->>
->>exit;
-
-End of MXCI Session
-
->>
->>fetch c1;
-
-UNIQ
------------
-
- 0
-
---- 1 row(s) selected.
->>fetch c1;
-
-UNIQ
------------
-
- 1
-
---- 1 row(s) selected.
->>fetch c1;
-
-UNIQ
------------
-
- 2
-
---- 1 row(s) selected.
->>fetch c1;
-
-UNIQ
------------
-
- 3
-
---- 1 row(s) selected.
->>fetch c1;
-
-UNIQ
------------
-
- 4
-
---- 1 row(s) selected.
->>fetch c1;
-
-UNIQ
------------
-
- 5
-
---- 1 row(s) selected.
->>fetch c1;
-
-UNIQ
------------
-
- 6
-
---- 1 row(s) selected.
->>fetch c1;
-
-UNIQ
------------
-
- 7
-
---- 1 row(s) selected.
->>
->>log ;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/e3d65c15/core/sql/regress/catman1/EXPECTED131
----------------------------------------------------------------------
diff --git a/core/sql/regress/catman1/EXPECTED131 b/core/sql/regress/catman1/EXPECTED131
deleted file mode 100644
index 70705e4..0000000
--- a/core/sql/regress/catman1/EXPECTED131
+++ /dev/null
@@ -1,491 +0,0 @@
->>
->>-- 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.
->>
->>
->>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 MXID11000010365212299077168091428000000000206U3334000_38_S1;
-
-*** ERROR[8031] Server declined cancel request for query ID MXID11000010365212299077168091428000000000206U3334000_38_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
-
->>
->>obey TEST131(clnup);
->>
->>set schema $$TEST_SCHEMA$$;
-
---- SQL operation complete.
->>
->>drop table T131c;
-
---- SQL operation complete.
->>drop table T131b;
-
---- SQL operation complete.
->>drop table 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.
->>
->>
->>log;