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