>>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)
  )
 ATTRIBUTES ALIGNED FORMAT
;

-- 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)
  )
 ATTRIBUTES ALIGNED FORMAT
;

-- 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)
  )
 ATTRIBUTES ALIGNED FORMAT
;

-- 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)
  )
 ATTRIBUTES ALIGNED FORMAT
;

-- 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[3242] This statement is not supported. Reason: DDL views can only be created or dropped in trafodion schema.

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

>>select * from v3bd;

*** ERROR[1002] Catalog CAT does not exist.

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

>>create view v3b as select b from t3;

*** ERROR[3242] This statement is not supported. Reason: DDL views can only be created or dropped in trafodion schema.

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

>>select * from v3b;

*** ERROR[1002] Catalog CAT does not exist.

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

>>create view v3d as select d from t3;

*** ERROR[3242] This statement is not supported. Reason: DDL views can only be created or dropped in trafodion schema.

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

>>select * from v3d;

*** ERROR[1002] Catalog CAT does not exist.

*** 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[3242] This statement is not supported. Reason: DDL views can only be created or dropped in trafodion schema.

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

>>select * from v3bbbbbb;

*** ERROR[1002] Catalog CAT does not exist.

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

>>
>>-- View on single table (negative):
>>
>>create view v3ac as select a,c from t3;

*** ERROR[3242] This statement is not supported. Reason: DDL views can only be created or dropped in trafodion schema.

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

>>create view v3a as select a from t3;

*** ERROR[3242] This statement is not supported. Reason: DDL views can only be created or dropped in trafodion schema.

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

>>create view v3c as select c from t3;

*** ERROR[3242] This statement is not supported. Reason: DDL views can only be created or dropped in trafodion schema.

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

>>
>>create view v3 as select * from t3;

*** ERROR[3242] This statement is not supported. Reason: DDL views can only be created or dropped in trafodion schema.

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

>>
>>create view v3ab as select a,b from t3;

*** ERROR[3242] This statement is not supported. Reason: DDL views can only be created or dropped in trafodion schema.

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

>>create view v3abcd as select a,b,c,d from t3;

*** ERROR[3242] This statement is not supported. Reason: DDL views can only be created or dropped in trafodion schema.

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

>>create view v3bc as select b,c from t3;

*** ERROR[3242] This statement is not supported. Reason: DDL views can only be created or dropped in trafodion schema.

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

>>
>>-- View on two tables (positive):
>>
>>create view v34bf as select b,f from t3, t4;

*** ERROR[3242] This statement is not supported. Reason: DDL views can only be created or dropped in trafodion schema.

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

>>select * from v34bf;

*** ERROR[1002] Catalog CAT does not exist.

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

>>create view v34bdfg as select b,d,f,g from t3, t4;

*** ERROR[3242] This statement is not supported. Reason: DDL views can only be created or dropped in trafodion schema.

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

>>select * from v34bdfg;

*** ERROR[1002] Catalog CAT does not exist.

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

>>create view v34bdfg2 (c1,c2,c3,c4) as select b,d,f,g from t3, t4;

*** ERROR[3242] This statement is not supported. Reason: DDL views can only be created or dropped in trafodion schema.

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

>>select * from v34bdfg2;

*** ERROR[1002] Catalog CAT does not exist.

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

>>create view v34gb as select g,b from t3, t4;

*** ERROR[3242] This statement is not supported. Reason: DDL views can only be created or dropped in trafodion schema.

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

>>select * from v34gb;

*** ERROR[1002] Catalog CAT does not exist.

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

>>
>>-- View on two tables (negative):
>>
>>create view v34 as select * from t3,t4;

*** ERROR[3242] This statement is not supported. Reason: DDL views can only be created or dropped in trafodion schema.

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

>>create view v34af as select a,f from t3, t4;

*** ERROR[3242] This statement is not supported. Reason: DDL views can only be created or dropped in trafodion schema.

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

>>create view v34bh as select b,h from t3,t4;

*** ERROR[3242] This statement is not supported. Reason: DDL views can only be created or dropped in trafodion schema.

*** 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)
  )
 ATTRIBUTES ALIGNED FORMAT
;

--- 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)
  )
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>
>>-- Negative tests: revoking from table
>>revoke select (c) on t3 from sql_user3;

*** WARNING[1018] Grant of role or privilege SELECT WITH GRANT OPTION (columm number 2) on TRAFODION.US4.T3 from SQL_USER4 to SQL_USER3 not found, revoke request ignored.

--- SQL operation complete.
>>revoke select (b) on t3 from sql_user3;

--- SQL operation complete.
>>
>>revoke grant option for select (c) on t3 from sql_user3;

*** WARNING[1018] Grant of role or privilege SELECT WITH GRANT OPTION (columm number 2) on TRAFODION.US4.T3 from SQL_USER4 to SQL_USER3 not found, revoke request ignored.

--- SQL operation complete.
>>
>>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;

*** WARNING[1018] Grant of role or privilege SELECT WITH GRANT OPTION (columm number 1) on TRAFODION.US4.T3 from SQL_USER4 to SQL_USER3 not found, revoke request ignored.

--- SQL operation complete.
>>revoke grant option for select (b) on t3 from sql_user3 cascade;

*** WARNING[1018] Grant of role or privilege SELECT WITH GRANT OPTION (columm number 1) on TRAFODION.US4.T3 from SQL_USER4 to SQL_USER3 not found, revoke request ignored.

--- 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)
  )
 ATTRIBUTES ALIGNED FORMAT
;

--- 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 ;
