| >>obey TEST138(test_create); |
| >>-- ================================================================= |
| >>-- run tests to make sure table related operations are |
| >>-- authorized correctly. If a user does not have create privileges |
| >>-- they cannot create object. If a user has CREATE_TABLE privilege |
| >>-- they can create and manage their objects |
| >>-- ================================================================= |
| >> |
| >>create private schema t138sch; |
| |
| --- SQL operation complete. |
| >>set schema t138sch; |
| |
| --- SQL operation complete. |
| >>get tables; |
| |
| Tables in Schema TRAFODION.T138SCH |
| ================================== |
| |
| SB_HISTOGRAMS |
| SB_HISTOGRAM_INTERVALS |
| SB_PERSISTENT_SAMPLES |
| |
| --- SQL operation complete. |
| >> |
| >>-- Verify sql_user1 does not have CREATE or CREATE_TABLE privilege |
| >>get privileges on component sql_operations for sql_user1; |
| |
| --- SQL operation complete. |
| >>sh sqlci -i "TEST138(create_tbl)" -u sql_user1; |
| >>get tables; |
| |
| Tables in Schema TRAFODION.T138SCH |
| ================================== |
| |
| SB_HISTOGRAMS |
| SB_HISTOGRAM_INTERVALS |
| SB_PERSISTENT_SAMPLES |
| |
| --- SQL operation complete. |
| >>create table user1_t1 (c1 int not null primary key, c2 int); |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>insert into user1_t1 values (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7); |
| |
| *** ERROR[4082] Object TRAFODION.T138SCH.USER1_T1 does not exist or is inaccessible. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>select count(*) from user1_t1; |
| |
| *** ERROR[4082] Object TRAFODION.T138SCH.USER1_T1 does not exist or is inaccessible. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>-- add grant to allow user2 to create FK constraints |
| >>grant references on user1_t1 to sql_user2; |
| |
| *** ERROR[4082] Object TRAFODION.T138SCH.USER1_T1 does not exist or is inaccessible. |
| |
| *** ERROR[4082] Object TRAFODION.T138SCH.USER1_T1 does not exist or is inaccessible. |
| |
| --- SQL operation failed with errors. |
| >>showddl user1_t1; |
| |
| *** ERROR[4082] Object TRAFODION.T138SCH.USER1_T1 does not exist or is inaccessible. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>create table user1_t2 (c1 int, c2 int); |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>insert into user1_t2 values (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7); |
| |
| *** ERROR[4082] Object TRAFODION.T138SCH.USER1_T2 does not exist or is inaccessible. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>select count(*) from user1_t2; |
| |
| *** ERROR[4082] Object TRAFODION.T138SCH.USER1_T2 does not exist or is inaccessible. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>showddl user1_t2; |
| |
| *** ERROR[4082] Object TRAFODION.T138SCH.USER1_T2 does not exist or is inaccessible. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- turn on CREATE_TABLE privilege |
| >>get privileges on component sql_operations for sql_user1; |
| |
| --- SQL operation complete. |
| >>grant component privilege CREATE_TABLE on SQL_OPERATIONS to sql_user1; |
| |
| --- SQL operation complete. |
| >>get privileges on component sql_operations for sql_user1; |
| |
| Privilege information on Component SQL_OPERATIONS for SQL_USER1 |
| =============================================================== |
| |
| CREATE_TABLE |
| |
| --- SQL operation complete. |
| >>sh sqlci -i "TEST138(create_tbl)" -u sql_user1; |
| >>get tables; |
| |
| Tables in Schema TRAFODION.T138SCH |
| ================================== |
| |
| SB_HISTOGRAMS |
| SB_HISTOGRAM_INTERVALS |
| SB_PERSISTENT_SAMPLES |
| |
| --- SQL operation complete. |
| >>create table user1_t1 (c1 int not null primary key, c2 int); |
| |
| --- SQL operation complete. |
| >>insert into user1_t1 values (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7); |
| |
| --- 7 row(s) inserted. |
| >>select count(*) from user1_t1; |
| |
| (EXPR) |
| -------------------- |
| |
| 7 |
| |
| --- 1 row(s) selected. |
| >>-- add grant to allow user2 to create FK constraints |
| >>grant references on user1_t1 to sql_user2; |
| |
| --- SQL operation complete. |
| >>showddl user1_t1; |
| |
| CREATE TABLE TRAFODION.T138SCH.USER1_T1 |
| ( |
| C1 INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , C2 INT DEFAULT NULL |
| , PRIMARY KEY (C1 ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T138SCH.USER1_T1 TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON |
| TRAFODION.T138SCH.USER1_T1 TO SQL_USER1 WITH GRANT OPTION; |
| GRANT REFERENCES |
| ON TRAFODION.T138SCH.USER1_T1 TO SQL_USER2 GRANTED BY SQL_USER1; |
| |
| --- SQL operation complete. |
| >> |
| >>create table user1_t2 (c1 int, c2 int); |
| |
| --- SQL operation complete. |
| >>insert into user1_t2 values (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7); |
| |
| --- 7 row(s) inserted. |
| >>select count(*) from user1_t2; |
| |
| (EXPR) |
| -------------------- |
| |
| 7 |
| |
| --- 1 row(s) selected. |
| >>showddl user1_t2; |
| |
| CREATE TABLE TRAFODION.T138SCH.USER1_T2 |
| ( |
| C1 INT DEFAULT NULL |
| , C2 INT DEFAULT NULL |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T138SCH.USER1_T2 TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON |
| TRAFODION.T138SCH.USER1_T2 TO SQL_USER1 WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >>-- sql_user1 cannot alter or drop |
| >>sh sqlci -i "TEST138(alter_tbl)" -u sql_user1; |
| >>get tables; |
| |
| Tables in Schema TRAFODION.T138SCH |
| ================================== |
| |
| SB_HISTOGRAMS |
| SB_HISTOGRAM_INTERVALS |
| SB_PERSISTENT_SAMPLES |
| USER1_T1 |
| USER1_T2 |
| |
| --- SQL operation complete. |
| >> |
| >>-- add columns, constraints, and indexes |
| >>alter table user1_t1 add column c3 int default 0; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t1 add constraint user1_ck check (c2 > 0); |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t1 add constraint user1_uq unique (c2); |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t2 add constraint user1_pk primary key (c1); |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t2 add constraint user1_fk foreign key (c2) references user1_t1; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>create index user1_t1_ndx on user1_t1(c2); |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t1 disable index user1_t1_ndx; |
| |
| *** ERROR[1389] Object TRAFODION.T138SCH.USER1_T1_NDX does not exist in Trafodion. |
| |
| --- SQL operation failed with errors. |
| >>create index user1_t2_ndx on user1_t2(c2) no populate; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t2 disable all indexes; |
| |
| --- SQL operation complete. |
| >> |
| >>showddl user1_t1; |
| |
| CREATE TABLE TRAFODION.T138SCH.USER1_T1 |
| ( |
| C1 INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , C2 INT DEFAULT NULL |
| , PRIMARY KEY (C1 ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T138SCH.USER1_T1 TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON |
| TRAFODION.T138SCH.USER1_T1 TO SQL_USER1 WITH GRANT OPTION; |
| GRANT REFERENCES |
| ON TRAFODION.T138SCH.USER1_T1 TO SQL_USER2 GRANTED BY SQL_USER1; |
| |
| --- SQL operation complete. |
| >>showddl user1_t2; |
| |
| CREATE TABLE TRAFODION.T138SCH.USER1_T2 |
| ( |
| C1 INT DEFAULT NULL |
| , C2 INT DEFAULT NULL |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T138SCH.USER1_T2 TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON |
| TRAFODION.T138SCH.USER1_T2 TO SQL_USER1 WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >> |
| >>-- remove indexes, constraints, and columns |
| >>alter table user1_t1 enable index user1_t1_ndx; |
| |
| *** ERROR[1389] Object TRAFODION.T138SCH.USER1_T1_NDX does not exist in Trafodion. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t2 enable all indexes; |
| |
| --- SQL operation complete. |
| >>drop index user1_t1_ndx; |
| |
| *** ERROR[1389] Object TRAFODION.T138SCH.USER1_T1_NDX does not exist in Trafodion. |
| |
| --- SQL operation failed with errors. |
| >>drop index user1_t2_ndx; |
| |
| *** ERROR[1389] Object TRAFODION.T138SCH.USER1_T2_NDX does not exist in Trafodion. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t1 drop constraint user1_ck; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t1 drop constraint user1_uq; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t2 drop constraint user1_pk; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t2 drop constraint user1_fk; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t1 drop column c3; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>showddl user1_t1; |
| |
| CREATE TABLE TRAFODION.T138SCH.USER1_T1 |
| ( |
| C1 INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , C2 INT DEFAULT NULL |
| , PRIMARY KEY (C1 ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T138SCH.USER1_T1 TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON |
| TRAFODION.T138SCH.USER1_T1 TO SQL_USER1 WITH GRANT OPTION; |
| GRANT REFERENCES |
| ON TRAFODION.T138SCH.USER1_T1 TO SQL_USER2 GRANTED BY SQL_USER1; |
| |
| --- SQL operation complete. |
| >>showddl user1_t2; |
| |
| CREATE TABLE TRAFODION.T138SCH.USER1_T2 |
| ( |
| C1 INT DEFAULT NULL |
| , C2 INT DEFAULT NULL |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T138SCH.USER1_T2 TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON |
| TRAFODION.T138SCH.USER1_T2 TO SQL_USER1 WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >> |
| >>alter table user1_t1 rename to user1_t4; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>select count(*) from user1_t4; |
| |
| *** ERROR[4082] Object TRAFODION.T138SCH.USER1_T4 does not exist or is inaccessible. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>alter table user1_t4 rename to user1_t1; |
| |
| *** ERROR[1127] The specified table TRAFODION.T138SCH.USER1_T4 does not exist, is inaccessible or is not a base table. Please verify that the correct table was specified. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >>sh sqlci -i "TEST138(drop_tbl)" -u sql_user1; |
| >>get tables; |
| |
| Tables in Schema TRAFODION.T138SCH |
| ================================== |
| |
| SB_HISTOGRAMS |
| SB_HISTOGRAM_INTERVALS |
| SB_PERSISTENT_SAMPLES |
| USER1_T1 |
| USER1_T2 |
| |
| --- SQL operation complete. |
| >>drop table user1_t1 cascade; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>drop table user1_t2 cascade; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>get tables; |
| |
| Tables in Schema TRAFODION.T138SCH |
| ================================== |
| |
| SB_HISTOGRAMS |
| SB_HISTOGRAM_INTERVALS |
| SB_PERSISTENT_SAMPLES |
| USER1_T1 |
| USER1_T2 |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>get tables; |
| |
| Tables in Schema TRAFODION.T138SCH |
| ================================== |
| |
| SB_HISTOGRAMS |
| SB_HISTOGRAM_INTERVALS |
| SB_PERSISTENT_SAMPLES |
| USER1_T1 |
| USER1_T2 |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST138(drop_tbl); |
| >>set schema t138sch; |
| |
| --- SQL operation complete. |
| >>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'on'; |
| |
| --- SQL operation complete. |
| >>log LOG138; |
| >>get tables; |
| |
| Tables in Schema TRAFODION.T138SCH |
| ================================== |
| |
| SB_HISTOGRAMS |
| SB_HISTOGRAM_INTERVALS |
| SB_PERSISTENT_SAMPLES |
| USER1_T1 |
| USER1_T2 |
| |
| --- SQL operation complete. |
| >>drop table user1_t1 cascade; |
| |
| --- SQL operation complete. |
| >>drop table user1_t2 cascade; |
| |
| --- SQL operation complete. |
| >>get tables; |
| |
| Tables in Schema TRAFODION.T138SCH |
| ================================== |
| |
| SB_HISTOGRAMS |
| SB_HISTOGRAM_INTERVALS |
| SB_PERSISTENT_SAMPLES |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>get tables; |
| |
| Tables in Schema TRAFODION.T138SCH |
| ================================== |
| |
| SB_HISTOGRAMS |
| SB_HISTOGRAM_INTERVALS |
| SB_PERSISTENT_SAMPLES |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST138(test_alter); |
| >>-- ================================================================= |
| >>-- run tests to make sure table related operations are |
| >>-- authorized correctly. If a user does not have an alter privilege |
| >>-- they cannot change objects. If a user has the appropriate alter |
| >>-- privilege they can perform the operation |
| >>-- ================================================================= |
| >> |
| >>set schema t138sch; |
| |
| --- SQL operation complete. |
| >>get tables; |
| |
| Tables in Schema TRAFODION.T138SCH |
| ================================== |
| |
| SB_HISTOGRAMS |
| SB_HISTOGRAM_INTERVALS |
| SB_PERSISTENT_SAMPLES |
| |
| --- SQL operation complete. |
| >> |
| >>-- Verify sql_user2 does not have ALTER or ALTER_TABLE privilege |
| >>get privileges on component sql_operations for sql_user2; |
| |
| --- SQL operation complete. |
| >> |
| >>-- create some tables |
| >>grant component privilege CREATE_TABLE on sql_operations to sql_user1; |
| |
| --- SQL operation complete. |
| >>sh sqlci -i "TEST138(create_tbl)" -u sql_user1; |
| >>get tables; |
| |
| Tables in Schema TRAFODION.T138SCH |
| ================================== |
| |
| SB_HISTOGRAMS |
| SB_HISTOGRAM_INTERVALS |
| SB_PERSISTENT_SAMPLES |
| |
| --- SQL operation complete. |
| >>create table user1_t1 (c1 int not null primary key, c2 int); |
| |
| --- SQL operation complete. |
| >>insert into user1_t1 values (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7); |
| |
| --- 7 row(s) inserted. |
| >>select count(*) from user1_t1; |
| |
| (EXPR) |
| -------------------- |
| |
| 7 |
| |
| --- 1 row(s) selected. |
| >>-- add grant to allow user2 to create FK constraints |
| >>grant references on user1_t1 to sql_user2; |
| |
| --- SQL operation complete. |
| >>showddl user1_t1; |
| |
| CREATE TABLE TRAFODION.T138SCH.USER1_T1 |
| ( |
| C1 INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , C2 INT DEFAULT NULL |
| , PRIMARY KEY (C1 ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T138SCH.USER1_T1 TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON |
| TRAFODION.T138SCH.USER1_T1 TO SQL_USER1 WITH GRANT OPTION; |
| GRANT REFERENCES |
| ON TRAFODION.T138SCH.USER1_T1 TO SQL_USER2 GRANTED BY SQL_USER1; |
| |
| --- SQL operation complete. |
| >> |
| >>create table user1_t2 (c1 int, c2 int); |
| |
| --- SQL operation complete. |
| >>insert into user1_t2 values (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7); |
| |
| --- 7 row(s) inserted. |
| >>select count(*) from user1_t2; |
| |
| (EXPR) |
| -------------------- |
| |
| 7 |
| |
| --- 1 row(s) selected. |
| >>showddl user1_t2; |
| |
| CREATE TABLE TRAFODION.T138SCH.USER1_T2 |
| ( |
| C1 INT DEFAULT NULL |
| , C2 INT DEFAULT NULL |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T138SCH.USER1_T2 TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON |
| TRAFODION.T138SCH.USER1_T2 TO SQL_USER1 WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >>revoke component privilege CREATE_TABLE on sql_operations from sql_user1; |
| |
| --- SQL operation complete. |
| >> |
| >>-- user2 cannot alter them |
| >>sh sqlci -i "TEST138(alter_tbl)" -u sql_user2; |
| >>get tables; |
| |
| Tables in Schema TRAFODION.T138SCH |
| ================================== |
| |
| SB_HISTOGRAMS |
| SB_HISTOGRAM_INTERVALS |
| SB_PERSISTENT_SAMPLES |
| USER1_T1 |
| USER1_T2 |
| |
| --- SQL operation complete. |
| >> |
| >>-- add columns, constraints, and indexes |
| >>alter table user1_t1 add column c3 int default 0; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t1 add constraint user1_ck check (c2 > 0); |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t1 add constraint user1_uq unique (c2); |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t2 add constraint user1_pk primary key (c1); |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t2 add constraint user1_fk foreign key (c2) references user1_t1; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>create index user1_t1_ndx on user1_t1(c2); |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t1 disable index user1_t1_ndx; |
| |
| *** ERROR[1389] Object TRAFODION.T138SCH.USER1_T1_NDX does not exist in Trafodion. |
| |
| --- SQL operation failed with errors. |
| >>create index user1_t2_ndx on user1_t2(c2) no populate; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t2 disable all indexes; |
| |
| --- SQL operation complete. |
| >> |
| >>showddl user1_t1; |
| |
| CREATE TABLE TRAFODION.T138SCH.USER1_T1 |
| ( |
| C1 INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , C2 INT DEFAULT NULL |
| , PRIMARY KEY (C1 ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T138SCH.USER1_T1 TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON |
| TRAFODION.T138SCH.USER1_T1 TO SQL_USER1 WITH GRANT OPTION; |
| GRANT REFERENCES |
| ON TRAFODION.T138SCH.USER1_T1 TO SQL_USER2 GRANTED BY SQL_USER1; |
| |
| --- SQL operation complete. |
| >>showddl user1_t2; |
| |
| CREATE TABLE TRAFODION.T138SCH.USER1_T2 |
| ( |
| C1 INT DEFAULT NULL |
| , C2 INT DEFAULT NULL |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T138SCH.USER1_T2 TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON |
| TRAFODION.T138SCH.USER1_T2 TO SQL_USER1 WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >> |
| >>-- remove indexes, constraints, and columns |
| >>alter table user1_t1 enable index user1_t1_ndx; |
| |
| *** ERROR[1389] Object TRAFODION.T138SCH.USER1_T1_NDX does not exist in Trafodion. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t2 enable all indexes; |
| |
| --- SQL operation complete. |
| >>drop index user1_t1_ndx; |
| |
| *** ERROR[1389] Object TRAFODION.T138SCH.USER1_T1_NDX does not exist in Trafodion. |
| |
| --- SQL operation failed with errors. |
| >>drop index user1_t2_ndx; |
| |
| *** ERROR[1389] Object TRAFODION.T138SCH.USER1_T2_NDX does not exist in Trafodion. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t1 drop constraint user1_ck; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t1 drop constraint user1_uq; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t2 drop constraint user1_pk; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t2 drop constraint user1_fk; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t1 drop column c3; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>showddl user1_t1; |
| |
| CREATE TABLE TRAFODION.T138SCH.USER1_T1 |
| ( |
| C1 INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , C2 INT DEFAULT NULL |
| , PRIMARY KEY (C1 ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T138SCH.USER1_T1 TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON |
| TRAFODION.T138SCH.USER1_T1 TO SQL_USER1 WITH GRANT OPTION; |
| GRANT REFERENCES |
| ON TRAFODION.T138SCH.USER1_T1 TO SQL_USER2 GRANTED BY SQL_USER1; |
| |
| --- SQL operation complete. |
| >>showddl user1_t2; |
| |
| CREATE TABLE TRAFODION.T138SCH.USER1_T2 |
| ( |
| C1 INT DEFAULT NULL |
| , C2 INT DEFAULT NULL |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T138SCH.USER1_T2 TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON |
| TRAFODION.T138SCH.USER1_T2 TO SQL_USER1 WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >> |
| >>alter table user1_t1 rename to user1_t4; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>select count(*) from user1_t4; |
| |
| *** ERROR[4082] Object TRAFODION.T138SCH.USER1_T4 does not exist or is inaccessible. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>alter table user1_t4 rename to user1_t1; |
| |
| *** ERROR[1127] The specified table TRAFODION.T138SCH.USER1_T4 does not exist, is inaccessible or is not a base table. Please verify that the correct table was specified. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- grant the ALTER privilege to user2 |
| >>grant component privilege alter_table on sql_operations to sql_user2; |
| |
| --- SQL operation complete. |
| >>sh sqlci -i "TEST138(alter_tbl)" -u sql_user2; |
| >>get tables; |
| |
| Tables in Schema TRAFODION.T138SCH |
| ================================== |
| |
| SB_HISTOGRAMS |
| SB_HISTOGRAM_INTERVALS |
| SB_PERSISTENT_SAMPLES |
| USER1_T1 |
| USER1_T2 |
| |
| --- SQL operation complete. |
| >> |
| >>-- add columns, constraints, and indexes |
| >>alter table user1_t1 add column c3 int default 0; |
| |
| --- SQL operation complete. |
| >>alter table user1_t1 add constraint user1_ck check (c2 > 0); |
| |
| --- SQL operation complete. |
| >>alter table user1_t1 add constraint user1_uq unique (c2); |
| |
| --- SQL operation complete. |
| >>alter table user1_t2 add constraint user1_pk primary key (c1); |
| |
| --- SQL operation complete. |
| >>alter table user1_t2 add constraint user1_fk foreign key (c2) references user1_t1; |
| |
| --- SQL operation complete. |
| >>create index user1_t1_ndx on user1_t1(c2); |
| |
| --- SQL operation complete. |
| >>alter table user1_t1 disable index user1_t1_ndx; |
| |
| --- SQL operation complete. |
| >>create index user1_t2_ndx on user1_t2(c2) no populate; |
| |
| --- SQL operation complete. |
| >>alter table user1_t2 disable all indexes; |
| |
| --- SQL operation complete. |
| >> |
| >>showddl user1_t1; |
| |
| CREATE TABLE TRAFODION.T138SCH.USER1_T1 |
| ( |
| C1 INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , C2 INT DEFAULT NULL |
| , C3 INT DEFAULT 0 /*added_col*/ |
| , PRIMARY KEY (C1 ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- The following index is a system created index -- |
| CREATE UNIQUE INDEX USER1_UQ ON TRAFODION.T138SCH.USER1_T1 |
| ( |
| C2 ASC |
| ) |
| ; |
| |
| ALTER TABLE TRAFODION.T138SCH.USER1_T1 ADD CONSTRAINT |
| TRAFODION.T138SCH.USER1_UQ UNIQUE |
| ( |
| C2 |
| ) |
| ; |
| |
| ALTER TABLE TRAFODION.T138SCH.USER1_T1 ADD CONSTRAINT |
| TRAFODION.T138SCH.USER1_CK CHECK (TRAFODION.T138SCH.USER1_T1.C2 > 0) |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T138SCH.USER1_T1 TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON |
| TRAFODION.T138SCH.USER1_T1 TO SQL_USER1 WITH GRANT OPTION; |
| GRANT REFERENCES |
| ON TRAFODION.T138SCH.USER1_T1 TO SQL_USER2 GRANTED BY SQL_USER1; |
| |
| --- SQL operation complete. |
| >>showddl user1_t2; |
| |
| CREATE TABLE TRAFODION.T138SCH.USER1_T2 |
| ( |
| C1 INT DEFAULT NULL |
| , C2 INT DEFAULT NULL |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| ALTER TABLE TRAFODION.T138SCH.USER1_T2 ADD CONSTRAINT |
| TRAFODION.T138SCH.USER1_PK UNIQUE |
| ( |
| C1 |
| ) |
| ; |
| |
| ALTER TABLE TRAFODION.T138SCH.USER1_T2 ADD CONSTRAINT |
| TRAFODION.T138SCH.USER1_FK FOREIGN KEY |
| ( |
| C2 |
| ) |
| REFERENCES TRAFODION.T138SCH.USER1_T1 |
| ( |
| C1 |
| ) |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T138SCH.USER1_T2 TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON |
| TRAFODION.T138SCH.USER1_T2 TO SQL_USER1 WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >> |
| >>-- remove indexes, constraints, and columns |
| >>alter table user1_t1 enable index user1_t1_ndx; |
| |
| --- SQL operation complete. |
| >>alter table user1_t2 enable all indexes; |
| |
| --- SQL operation complete. |
| >>drop index user1_t1_ndx; |
| |
| --- SQL operation complete. |
| >>drop index user1_t2_ndx; |
| |
| --- SQL operation complete. |
| >>alter table user1_t1 drop constraint user1_ck; |
| |
| --- SQL operation complete. |
| >>alter table user1_t1 drop constraint user1_uq; |
| |
| --- SQL operation complete. |
| >>alter table user1_t2 drop constraint user1_pk; |
| |
| --- SQL operation complete. |
| >>alter table user1_t2 drop constraint user1_fk; |
| |
| --- SQL operation complete. |
| >>alter table user1_t1 drop column c3; |
| |
| --- SQL operation complete. |
| >> |
| >>showddl user1_t1; |
| |
| CREATE TABLE TRAFODION.T138SCH.USER1_T1 |
| ( |
| C1 INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , C2 INT DEFAULT NULL |
| , PRIMARY KEY (C1 ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T138SCH.USER1_T1 TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON |
| TRAFODION.T138SCH.USER1_T1 TO SQL_USER1 WITH GRANT OPTION; |
| GRANT REFERENCES |
| ON TRAFODION.T138SCH.USER1_T1 TO SQL_USER2 GRANTED BY SQL_USER1; |
| |
| --- SQL operation complete. |
| >>showddl user1_t2; |
| |
| CREATE TABLE TRAFODION.T138SCH.USER1_T2 |
| ( |
| C1 INT DEFAULT NULL |
| , C2 INT DEFAULT NULL |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T138SCH.USER1_T2 TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON |
| TRAFODION.T138SCH.USER1_T2 TO SQL_USER1 WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >> |
| >>alter table user1_t1 rename to user1_t4; |
| |
| --- SQL operation complete. |
| >>select count(*) from user1_t4; |
| |
| *** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T138SCH.USER1_T4. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>alter table user1_t4 rename to user1_t1; |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- turn off the alter_table privilege but grant other alters |
| >>-- all alter's should fail |
| >>revoke component privilege alter_table on sql_operations from sql_user2; |
| |
| --- SQL operation complete. |
| >>grant component privilege alter_library, alter_routine, alter_sequence, alter_view |
| +>on sql_operations to sql_user2; |
| |
| --- SQL operation complete. |
| >>get component privileges on sql_operations for sql_user2; |
| |
| Privilege information on Component SQL_OPERATIONS for SQL_USER2 |
| =============================================================== |
| |
| ALTER_LIBRARY |
| ALTER_ROUTINE |
| ALTER_SEQUENCE |
| ALTER_VIEW |
| |
| --- SQL operation complete. |
| >>sh sqlci -i "TEST138(alter_tbl)" -u sql_user2; |
| >>get tables; |
| |
| Tables in Schema TRAFODION.T138SCH |
| ================================== |
| |
| SB_HISTOGRAMS |
| SB_HISTOGRAM_INTERVALS |
| SB_PERSISTENT_SAMPLES |
| USER1_T1 |
| USER1_T2 |
| |
| --- SQL operation complete. |
| >> |
| >>-- add columns, constraints, and indexes |
| >>alter table user1_t1 add column c3 int default 0; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t1 add constraint user1_ck check (c2 > 0); |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t1 add constraint user1_uq unique (c2); |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t2 add constraint user1_pk primary key (c1); |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t2 add constraint user1_fk foreign key (c2) references user1_t1; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>create index user1_t1_ndx on user1_t1(c2); |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t1 disable index user1_t1_ndx; |
| |
| *** ERROR[1389] Object TRAFODION.T138SCH.USER1_T1_NDX does not exist in Trafodion. |
| |
| --- SQL operation failed with errors. |
| >>create index user1_t2_ndx on user1_t2(c2) no populate; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t2 disable all indexes; |
| |
| --- SQL operation complete. |
| >> |
| >>showddl user1_t1; |
| |
| CREATE TABLE TRAFODION.T138SCH.USER1_T1 |
| ( |
| C1 INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , C2 INT DEFAULT NULL |
| , PRIMARY KEY (C1 ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T138SCH.USER1_T1 TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON |
| TRAFODION.T138SCH.USER1_T1 TO SQL_USER1 WITH GRANT OPTION; |
| GRANT REFERENCES |
| ON TRAFODION.T138SCH.USER1_T1 TO SQL_USER2 GRANTED BY SQL_USER1; |
| |
| --- SQL operation complete. |
| >>showddl user1_t2; |
| |
| CREATE TABLE TRAFODION.T138SCH.USER1_T2 |
| ( |
| C1 INT DEFAULT NULL |
| , C2 INT DEFAULT NULL |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T138SCH.USER1_T2 TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON |
| TRAFODION.T138SCH.USER1_T2 TO SQL_USER1 WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >> |
| >>-- remove indexes, constraints, and columns |
| >>alter table user1_t1 enable index user1_t1_ndx; |
| |
| *** ERROR[1389] Object TRAFODION.T138SCH.USER1_T1_NDX does not exist in Trafodion. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t2 enable all indexes; |
| |
| --- SQL operation complete. |
| >>drop index user1_t1_ndx; |
| |
| *** ERROR[1389] Object TRAFODION.T138SCH.USER1_T1_NDX does not exist in Trafodion. |
| |
| --- SQL operation failed with errors. |
| >>drop index user1_t2_ndx; |
| |
| *** ERROR[1389] Object TRAFODION.T138SCH.USER1_T2_NDX does not exist in Trafodion. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t1 drop constraint user1_ck; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t1 drop constraint user1_uq; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t2 drop constraint user1_pk; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t2 drop constraint user1_fk; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1_t1 drop column c3; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>showddl user1_t1; |
| |
| CREATE TABLE TRAFODION.T138SCH.USER1_T1 |
| ( |
| C1 INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , C2 INT DEFAULT NULL |
| , PRIMARY KEY (C1 ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T138SCH.USER1_T1 TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON |
| TRAFODION.T138SCH.USER1_T1 TO SQL_USER1 WITH GRANT OPTION; |
| GRANT REFERENCES |
| ON TRAFODION.T138SCH.USER1_T1 TO SQL_USER2 GRANTED BY SQL_USER1; |
| |
| --- SQL operation complete. |
| >>showddl user1_t2; |
| |
| CREATE TABLE TRAFODION.T138SCH.USER1_T2 |
| ( |
| C1 INT DEFAULT NULL |
| , C2 INT DEFAULT NULL |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T138SCH.USER1_T2 TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON |
| TRAFODION.T138SCH.USER1_T2 TO SQL_USER1 WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >> |
| >>alter table user1_t1 rename to user1_t4; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>select count(*) from user1_t4; |
| |
| *** ERROR[4082] Object TRAFODION.T138SCH.USER1_T4 does not exist or is inaccessible. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>alter table user1_t4 rename to user1_t1; |
| |
| *** ERROR[1127] The specified table TRAFODION.T138SCH.USER1_T4 does not exist, is inaccessible or is not a base table. Please verify that the correct table was specified. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >>revoke component privilege alter_library, alter_routine, alter_sequence, alter_view |
| +>on sql_operations from sql_user2; |
| |
| --- SQL operation complete. |
| >>get component privileges on sql_operations for sql_user2; |
| |
| --- SQL operation complete. |
| >>obey TEST138(drop_tbl); |
| >>set schema t138sch; |
| |
| --- SQL operation complete. |
| >>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'on'; |
| |
| --- SQL operation complete. |
| >>log LOG138; |
| >>get tables; |
| |
| Tables in Schema TRAFODION.T138SCH |
| ================================== |
| |
| SB_HISTOGRAMS |
| SB_HISTOGRAM_INTERVALS |
| SB_PERSISTENT_SAMPLES |
| USER1_T1 |
| USER1_T2 |
| |
| --- SQL operation complete. |
| >>drop table user1_t1 cascade; |
| |
| --- SQL operation complete. |
| >>drop table user1_t2 cascade; |
| |
| --- SQL operation complete. |
| >>get tables; |
| |
| Tables in Schema TRAFODION.T138SCH |
| ================================== |
| |
| SB_HISTOGRAMS |
| SB_HISTOGRAM_INTERVALS |
| SB_PERSISTENT_SAMPLES |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>-- reset |
| >>revoke component privilege CREATE_TABLE on SQL_OPERATIONS from sql_user1; |
| |
| *** ERROR[1018] Grant of role or privilege CREATE_TABLE on component SQL_OPERATIONS from DB__ROOT to SQL_USER1 not found, revoke request ignored. |
| |
| --- SQL operation failed with errors. |
| >>get privileges on component sql_operations for "PUBLIC"; |
| |
| Privilege information on Component SQL_OPERATIONS for PUBLIC |
| ============================================================ |
| |
| CREATE_SCHEMA |
| SHOW |
| |
| --- SQL operation complete. |
| >>get privileges on component sql_operations for sql_user1; |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST138(test_view); |
| >>-- ================================================================= |
| >>-- run tests to make sure view related operations are |
| >>-- authorized correctly. |
| >>-- ================================================================= |
| >> |
| >>set schema t138sch; |
| |
| --- SQL operation complete. |
| >>get views; |
| |
| --- SQL operation complete. |
| >>get privileges on component sql_operations for sql_user1; |
| |
| --- SQL operation complete. |
| >>get privileges on component sql_operations for sql_user2; |
| |
| --- SQL operation complete. |
| >> |
| >>-- create some tables |
| >>grant component privilege CREATE_TABLE on sql_operations to sql_user1; |
| |
| --- SQL operation complete. |
| >>sh sqlci -i "TEST138(create_tbl)" -u sql_user1; |
| >>get tables; |
| |
| Tables in Schema TRAFODION.T138SCH |
| ================================== |
| |
| SB_HISTOGRAMS |
| SB_HISTOGRAM_INTERVALS |
| SB_PERSISTENT_SAMPLES |
| |
| --- SQL operation complete. |
| >>create table user1_t1 (c1 int not null primary key, c2 int); |
| |
| --- SQL operation complete. |
| >>insert into user1_t1 values (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7); |
| |
| --- 7 row(s) inserted. |
| >>select count(*) from user1_t1; |
| |
| (EXPR) |
| -------------------- |
| |
| 7 |
| |
| --- 1 row(s) selected. |
| >>-- add grant to allow user2 to create FK constraints |
| >>grant references on user1_t1 to sql_user2; |
| |
| --- SQL operation complete. |
| >>showddl user1_t1; |
| |
| CREATE TABLE TRAFODION.T138SCH.USER1_T1 |
| ( |
| C1 INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , C2 INT DEFAULT NULL |
| , PRIMARY KEY (C1 ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T138SCH.USER1_T1 TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON |
| TRAFODION.T138SCH.USER1_T1 TO SQL_USER1 WITH GRANT OPTION; |
| GRANT REFERENCES |
| ON TRAFODION.T138SCH.USER1_T1 TO SQL_USER2 GRANTED BY SQL_USER1; |
| |
| --- SQL operation complete. |
| >> |
| >>create table user1_t2 (c1 int, c2 int); |
| |
| --- SQL operation complete. |
| >>insert into user1_t2 values (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7); |
| |
| --- 7 row(s) inserted. |
| >>select count(*) from user1_t2; |
| |
| (EXPR) |
| -------------------- |
| |
| 7 |
| |
| --- 1 row(s) selected. |
| >>showddl user1_t2; |
| |
| CREATE TABLE TRAFODION.T138SCH.USER1_T2 |
| ( |
| C1 INT DEFAULT NULL |
| , C2 INT DEFAULT NULL |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T138SCH.USER1_T2 TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON |
| TRAFODION.T138SCH.USER1_T2 TO SQL_USER1 WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >>revoke component privilege CREATE_TABLE on sql_operations from sql_user1; |
| |
| --- SQL operation complete. |
| >> |
| >>-- user1 should not be able to create any views |
| >>sh sqlci -i "TEST138(create_view)" -u sql_user1; |
| >>get views; |
| |
| --- SQL operation complete. |
| >>create view user1_v1 as select * from user1_t1; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>create view user1_v2 (c1, c2) as |
| +> select t1.c1, t2.c2 from user1_t1 t1, user1_t2 t2; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>get views; |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- grant CREATE_VIEW privilege and try again |
| >>grant component privilege "CREATE_VIEW" on SQL_OPERATIONS to sql_user1; |
| |
| --- SQL operation complete. |
| >>get privileges on component sql_operations for sql_user1; |
| |
| Privilege information on Component SQL_OPERATIONS for SQL_USER1 |
| =============================================================== |
| |
| CREATE_VIEW |
| |
| --- SQL operation complete. |
| >>sh sqlci -i "TEST138(create_view)" -u sql_user1; |
| >>get views; |
| |
| --- SQL operation complete. |
| >>create view user1_v1 as select * from user1_t1; |
| |
| --- SQL operation complete. |
| >>create view user1_v2 (c1, c2) as |
| +> select t1.c1, t2.c2 from user1_t1 t1, user1_t2 t2; |
| |
| --- SQL operation complete. |
| >>get views; |
| |
| Views in Schema TRAFODION.T138SCH |
| ================================= |
| |
| USER1_V1 |
| USER1_V2 |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- see if user2 can drop user1's views |
| >>sh sqlci -i "TEST138(drop_view)" -u sql_user2; |
| >>get views; |
| |
| Views in Schema TRAFODION.T138SCH |
| ================================= |
| |
| USER1_V1 |
| USER1_V2 |
| |
| --- SQL operation complete. |
| >>drop view user1_v1; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>drop view user1_v2; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>get views; |
| |
| Views in Schema TRAFODION.T138SCH |
| ================================= |
| |
| USER1_V1 |
| USER1_V2 |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- give user2 drop privilege and then drop |
| >>grant component privilege drop_view on sql_operations to sql_user2; |
| |
| --- SQL operation complete. |
| >>get privileges on component sql_operations for sql_user2; |
| |
| Privilege information on Component SQL_OPERATIONS for SQL_USER2 |
| =============================================================== |
| |
| DROP_VIEW |
| |
| --- SQL operation complete. |
| >>sh sqlci -i "TEST138(drop_view)" -u sql_user2; |
| >>get views; |
| |
| Views in Schema TRAFODION.T138SCH |
| ================================= |
| |
| USER1_V1 |
| USER1_V2 |
| |
| --- SQL operation complete. |
| >>drop view user1_v1; |
| |
| --- SQL operation complete. |
| >>drop view user1_v2; |
| |
| --- SQL operation complete. |
| >>get views; |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- restore |
| >>revoke component privilege DROP_VIEW on SQL_OPERATIONS from sql_user2; |
| |
| --- SQL operation complete. |
| >>revoke component privilege CREATE_VIEW on SQL_OPERATIONS from sql_user1; |
| |
| --- SQL operation complete. |
| >>get privileges on component sql_operations for sql_user1; |
| |
| --- SQL operation complete. |
| >>get privileges on component sql_operations for sql_user2; |
| |
| --- SQL operation complete. |
| >>obey TEST138(drop_tbl); |
| >>set schema t138sch; |
| |
| --- SQL operation complete. |
| >>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'on'; |
| |
| --- SQL operation complete. |
| >>log LOG138; |
| >>get tables; |
| |
| Tables in Schema TRAFODION.T138SCH |
| ================================== |
| |
| SB_HISTOGRAMS |
| SB_HISTOGRAM_INTERVALS |
| SB_PERSISTENT_SAMPLES |
| USER1_T1 |
| USER1_T2 |
| |
| --- SQL operation complete. |
| >>drop table user1_t1 cascade; |
| |
| --- SQL operation complete. |
| >>drop table user1_t2 cascade; |
| |
| --- SQL operation complete. |
| >>get tables; |
| |
| Tables in Schema TRAFODION.T138SCH |
| ================================== |
| |
| SB_HISTOGRAMS |
| SB_HISTOGRAM_INTERVALS |
| SB_PERSISTENT_SAMPLES |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>obey TEST138(test_sequence); |
| >>-- ================================================================= |
| >>-- run tests to make sure sequence related operations are |
| >>-- authorized correctly. |
| >>-- ================================================================= |
| >> |
| >>set schema t138sch; |
| |
| --- SQL operation complete. |
| >>get sequences in schema t138sch; |
| |
| --- SQL operation complete. |
| >>get privileges on component sql_operations for sql_user1; |
| |
| --- SQL operation complete. |
| >>get privileges on component sql_operations for sql_user2; |
| |
| --- SQL operation complete. |
| >> |
| >>-- user1 should not be able to create any sequences |
| >>sh sqlci -i "TEST138(create_sequence)" -u sql_user1; |
| >>get sequences in schema t138sch; |
| |
| --- SQL operation complete. |
| >>create sequence user1_seq1; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>create sequence user1_seq2; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>get sequences in schema t138sch; |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- grant CREATE_SEQUENCE privilege and try again |
| >>grant component privilege "CREATE_SEQUENCE" on SQL_OPERATIONS to sql_user1; |
| |
| --- SQL operation complete. |
| >>get privileges on component sql_operations for sql_user1; |
| |
| Privilege information on Component SQL_OPERATIONS for SQL_USER1 |
| =============================================================== |
| |
| CREATE_SEQUENCE |
| |
| --- SQL operation complete. |
| >>sh sqlci -i "TEST138(create_sequence)" -u sql_user1; |
| >>get sequences in schema t138sch; |
| |
| --- SQL operation complete. |
| >>create sequence user1_seq1; |
| |
| --- SQL operation complete. |
| >>create sequence user1_seq2; |
| |
| --- SQL operation complete. |
| >>get sequences in schema t138sch; |
| |
| Sequences in schema TRAFODION.T138SCH |
| ===================================== |
| |
| USER1_SEQ1 |
| USER1_SEQ2 |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- see if user2 can drop user1's sequences |
| >>sh sqlci -i "TEST138(drop_sequence)" -u sql_user2; |
| >>get sequences in schema t138sch; |
| |
| Sequences in schema TRAFODION.T138SCH |
| ===================================== |
| |
| USER1_SEQ1 |
| USER1_SEQ2 |
| |
| --- SQL operation complete. |
| >>drop sequence user1_seq1; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>drop sequence user1_seq2; |
| |
| *** ERROR[1017] You are not authorized to perform this operation. |
| |
| --- SQL operation failed with errors. |
| >>get sequences in schema t138sch; |
| |
| Sequences in schema TRAFODION.T138SCH |
| ===================================== |
| |
| USER1_SEQ1 |
| USER1_SEQ2 |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- give user2 drop privilege and then drop |
| >>grant component privilege drop_sequence on sql_operations to sql_user2; |
| |
| --- SQL operation complete. |
| >>get privileges on component sql_operations for sql_user2; |
| |
| Privilege information on Component SQL_OPERATIONS for SQL_USER2 |
| =============================================================== |
| |
| DROP_SEQUENCE |
| |
| --- SQL operation complete. |
| >>sh sqlci -i "TEST138(drop_sequence)" -u sql_user2; |
| >>get sequences in schema t138sch; |
| |
| Sequences in schema TRAFODION.T138SCH |
| ===================================== |
| |
| USER1_SEQ1 |
| USER1_SEQ2 |
| |
| --- SQL operation complete. |
| >>drop sequence user1_seq1; |
| |
| --- SQL operation complete. |
| >>drop sequence user1_seq2; |
| |
| --- SQL operation complete. |
| >>get sequences in schema t138sch; |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- restore |
| >>revoke component privilege DROP_SEQUENCE on SQL_OPERATIONS from sql_user2; |
| |
| --- SQL operation complete. |
| >>revoke component privilege CREATE_SEQUENCE on SQL_OPERATIONS from sql_user1; |
| |
| --- SQL operation complete. |
| >>get privileges on component sql_operations for sql_user1; |
| |
| --- SQL operation complete. |
| >>get privileges on component sql_operations for sql_user2; |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST138(test_drop); |
| >>create schema sch138c; |
| |
| --- SQL operation complete. |
| >>set schema sch138c; |
| |
| --- SQL operation complete. |
| >>-- Setup libraries, procedures and functions |
| >>create library t138_l1 file 'udrtest135.dll'; |
| |
| --- SQL operation complete. |
| >> |
| >>create function t138_ADD2(int,int) returns (ADD2 int) |
| +>language c parameter style sql external name 'add2' |
| +>library t138_l1 |
| +>deterministic no sql final call allow any parallelism state area size 1024 ; |
| |
| --- SQL operation complete. |
| >>-- Procedure creation not working |
| >>--sh sh $$scriptsdir$$/tools/java-compile.ksh TEST138.java 2> LOG138-SECONDARY | tee -a LOG138; |
| >>--sh sh $$scriptsdir$$/tools/java-archive.ksh TEST138.jar TEST138.class 2>> LOG138-SECONDARY | tee -a LOG138; |
| >>--create procedure p138(in cmd char(1000),out status char(60)) |
| >>--language java parameter style java modifies sql data |
| >>--external name 'TEST101.Xact' library t138_l1; |
| >> |
| >>create table t138 (a int not null primary key); |
| |
| --- SQL operation complete. |
| >>create view v138 as select * from T138; |
| |
| --- SQL operation complete. |
| >>create sequence sq138; |
| |
| --- SQL operation complete. |
| >> |
| >>select count (*) from TRAFODION."_MD_".OBJECTS WHERE schema_name = 'SCH138C'; |
| |
| (EXPR) |
| -------------------- |
| |
| 13 |
| |
| --- 1 row(s) selected. |
| >> |
| >>drop schema SCH138C; |
| |
| *** ERROR[1028] The schema must be empty. It contains at least one object SQ138. |
| |
| --- SQL operation failed with errors. |
| >>select count (*) from TRAFODION."_MD_".OBJECTS WHERE schema_name = 'SCH138C'; |
| |
| (EXPR) |
| -------------------- |
| |
| 13 |
| |
| --- 1 row(s) selected. |
| >> |
| >>drop schema SCH138C cascade; |
| |
| --- SQL operation complete. |
| >>select count (*) from TRAFODION."_MD_".OBJECTS WHERE schema_name = 'SCH138C'; |
| |
| (EXPR) |
| -------------------- |
| |
| 0 |
| |
| --- 1 row(s) selected. |
| >> |
| >>log; |
