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:05 UTC
[05/20] incubator-trafodion git commit: TRAFODION-1087 &
TRAFODION-1671 fixes
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/e3d65c15/core/sql/regress/privs2/EXPECTED129
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs2/EXPECTED129 b/core/sql/regress/privs2/EXPECTED129
new file mode 100644
index 0000000..6ce955a
--- /dev/null
+++ b/core/sql/regress/privs2/EXPECTED129
@@ -0,0 +1,1237 @@
+>>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 every column ;
+
+--- 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 ;