You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by rm...@apache.org on 2016/01/12 23:47:14 UTC
[14/20] incubator-trafodion git commit: TRAFODION-1087 &
TRAFODION-1671 fixes
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/e3d65c15/core/sql/regress/catman1/EXPECTED138
----------------------------------------------------------------------
diff --git a/core/sql/regress/catman1/EXPECTED138 b/core/sql/regress/catman1/EXPECTED138
deleted file mode 100644
index 4134ad2..0000000
--- a/core/sql/regress/catman1/EXPECTED138
+++ /dev/null
@@ -1,1692 +0,0 @@
->>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;
-
---- 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;
-
---- 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.
-
---- SQL operation failed with errors.
->>
->>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.
-
---- SQL operation failed with errors.
->>
->>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;
-
---- 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)
- )
-;
-
--- 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
- )
-;
-
--- 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
-==================================
-
-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)
- )
-;
-
--- 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
- )
-;
-
--- 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)
- )
-;
-
--- 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
- )
-;
-
--- 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[1389] Object TRAFODION.T138SCH.USER1_T4 does not exist in Trafodion.
-
---- 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
-==================================
-
-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
-==================================
-
-USER1_T1
-USER1_T2
-
---- SQL operation complete.
->>
->>exit;
-
-End of MXCI Session
-
->>
->>get tables;
-
-Tables in Schema TRAFODION.T138SCH
-==================================
-
-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
-==================================
-
-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;
-
---- SQL operation complete.
->>
->>
->>get tables;
-
---- 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;
-
---- 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;
-
---- 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)
- )
-;
-
--- 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
- )
-;
-
--- 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
-==================================
-
-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)
- )
-;
-
--- 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
- )
-;
-
--- 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)
- )
-;
-
--- 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
- )
-;
-
--- 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[1389] Object TRAFODION.T138SCH.USER1_T4 does not exist in Trafodion.
-
---- 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
-==================================
-
-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)
- )
-;
-
--- 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
- )
-;
-
-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)
- )
-;
-
--- 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
- )
-;
-
--- 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
-==================================
-
-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)
- )
-;
-
--- 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
- )
-;
-
--- 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)
- )
-;
-
--- 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
- )
-;
-
--- 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[1389] Object TRAFODION.T138SCH.USER1_T4 does not exist in Trafodion.
-
---- 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
-==================================
-
-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;
-
---- 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;
-
---- 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)
- )
-;
-
--- 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
- )
-;
-
--- 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
-==================================
-
-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;
-
---- 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)
---------------------
-
- 7
-
---- 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)
---------------------
-
- 7
-
---- 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;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/e3d65c15/core/sql/regress/catman1/EXPECTED139
----------------------------------------------------------------------
diff --git a/core/sql/regress/catman1/EXPECTED139 b/core/sql/regress/catman1/EXPECTED139
deleted file mode 100644
index 934a2dd..0000000
--- a/core/sql/regress/catman1/EXPECTED139
+++ /dev/null
@@ -1,585 +0,0 @@
->>obey TEST139(set_up);
->>set schema "_PRIVMGR_MD_";
-
---- SQL operation complete.
->>prepare get_owner_privs from
-+>select distinct
-+> substring (object_name,1,10) as object_name,
-+> object_type as type,
-+> substring(authname(grantee_id),1,10) as grantee,
-+> privileges_bitmap,
-+> grantable_bitmap
-+>from object_privileges
-+>where grantor_id = -2
-+> and object_uid in
-+> (select object_uid
-+> from "_MD_".objects
-+> where object_type in ('VI','BT','LB','UR')
-+> and schema_name in ('_PRIVMGR_MD_', 'T139SCH'))
-+> order by 1, 2
-+>;
-
---- SQL command prepared.
->>
->>prepare object_privs from
-+>select distinct
-+> object_type as type,
-+> substring (grantor_name,1,10) as grantor,
-+> substring (grantee_name,1,10) as grantee,
-+> privileges_bitmap as privs,
-+> grantable_bitmap as wgo
-+>from "_PRIVMGR_MD_".object_privileges
-+>where object_name like ?tblname
-+>order by 1, 2, 3;
-
---- SQL command prepared.
->>
->>prepare all_privs from
-+>select distinct
-+> object_type as type,
-+> substring (object_name,1,20) as objname,
-+> substring (grantor_name,1,10) as grantor,
-+> substring (grantee_name,1,10) as grantee,
-+> privileges_bitmap as privs,
-+> grantable_bitmap as wgo
-+>from "_PRIVMGR_MD_".object_privileges
-+>where object_uid in
-+> (select object_uid from "_MD_".objects
-+> where schema_name in ('T139SCH'))
-+>ORDER BY 1, 2,3,4;
-
---- SQL command prepared.
->>
->>obey TEST139(create_db);
->>create shared schema t139sch;
-
---- SQL operation complete.
->>set schema t139sch;
-
---- SQL operation complete.
->>create table teams
-+> (team_number int not null primary key,
-+> team_name char(20) not null,
-+> team_contact varchar(50) not null,
-+> team_contact_number char (10) not null
-+> )
-+> ;
-
---- SQL operation complete.
->>
->>alter table teams add constraint valid_team_no check (team_number > 0);
-
---- SQL operation complete.
->>
->>insert into teams values
-+> (1, 'White Socks', 'Sam','4082282222'),
-+> (2, 'Giants', 'Joe', '5102839483'),
-+> (3, 'Cardinals', 'Stella', '9513849384'),
-+> (4, 'Indians', 'Matt', '5128383748'),
-+> (5, 'Tigers', 'Ronit', '6198273827');
-
---- 5 row(s) inserted.
->>
->>create table team_statistics
-+> (team_number int not null primary key,
-+> num_players int not null)
-+>;
-
---- SQL operation complete.
->>
->>create table games
-+> ( home_team_number int not null,
-+> visitor_team_number int not null,
-+> game_number int not null primary key,
-+> game_time timestamp not null,
-+> game_location varchar(50) not null)
-+> ;
-
---- SQL operation complete.
->>--create index home_games on games (home_team_number);
->>
->>alter table games add constraint valid_game_number check (game_number > 0);
-
---- SQL operation complete.
->>
->>create table standings
-+> (team_number int not null primary key,
-+> wins int default 0,
-+> loses int default 0,
-+> last_updated timestamp default current_timestamp)
-+>;
-
---- SQL operation complete.
->>insert into standings (team_number)
-+> select team_number from teams;
-
---- 5 row(s) inserted.
->>
->>insert into games values
-+> (1, 2, 1, timestamp '2009-04-23 19:30:00', 'California'),
-+> (1, 3, 2, timestamp '2009-04-24 19:30:00', 'California'),
-+> (1, 4, 3, timestamp '2009-04-25 10:00:00', 'Oklahoma'),
-+> (2, 3, 4, timestamp '2009-04-25 13:30:00', 'Michigan'),
-+> (1, 5, 5, timestamp '2009-04-25 15:00:00', 'Oklahoma'),
-+> (2, 5, 6, timestamp '2009-04-27 17:00:00', 'New York'),
-+> (3, 4, 7, timestamp '2009-04-28 17:00:00', 'Florida'),
-+> (4, 2, 8, current_timestamp, 'Missouri');
-
---- 8 row(s) inserted.
->>
->>create view home_teams_games as
-+> select t.team_number, g.game_number, g.game_time
-+> from "TEAMS" t,
-+> "GAMES" g
-+> where t.team_number = g.home_team_number
-+> order by 1, game_number, game_time;
-
---- SQL operation complete.
->>
->>create table players
-+> (player_number int not null,
-+> player_name varchar (50) not null,
-+> player_team_number int not null,
-+> player_phone_number char (10) not null,
-+> player_details varchar(50),
-+> primary key (player_number, player_team_number))
-+> no partition;
-
---- SQL operation complete.
->>
->>insert into players values
-+> (1, 'Tom', 1, '4083948394', null),
-+> (2, 'Bob', 1, '4089483948', null),
-+> (3, 'Toby',1, '4082938493', 'pitcher'),
-+> (3, 'Toby',2, '4082938493', null),
-+> (4, 'Julie', 2, '5108394839', 'catcher'),
-+> (5, 'Joanne', 2, '5103849384', null),
-+> (6, 'Pete', 2, '5102839483', null),
-+> (6, 'Pete', 3, '5102839483', 'third base'),
-+> (7, 'Jared',4, '9518293849', 'short stop'),
-+> (8, 'Zachary', 4, '9518293840', null),
-+> (9, 'Lynne', 5, '9518293892', 'pitcher'),
-+> (10, 'Omar', 5, '5128394893', null);
-
---- 12 row(s) inserted.
->>
->>alter table players add constraint valid_player_number check(player_number > 0);
-
---- SQL operation complete.
->>
->>create view players_on_team as
-+> select player_name, team_name
-+> from teams t, players p
-+> where p.player_team_number = t.team_number
-+> order by t.team_name;
-
---- SQL operation complete.
->>select * from players_on_team;
-
-PLAYER_NAME TEAM_NAME
--------------------------------------------------- --------------------
-
-Pete Cardinals
-Julie Giants
-Joanne Giants
-Toby Giants
-Pete Giants
-Jared Indians
-Zachary Indians
-Omar Tigers
-Lynne Tigers
-Toby White Socks
-Bob White Socks
-Tom White Socks
-
---- 12 row(s) selected.
->>
->>create view games_by_player as
-+> select player_name, game_time
-+> from teams t, games g, players p
-+> where p.player_team_number = t.team_number and
-+> t.team_number = g.home_team_number
-+> order by player_name, team_number;
-
---- SQL operation complete.
->>
->>alter table players add constraint players_teams
-+> foreign key (player_team_number) references teams (team_number);
-
---- SQL operation complete.
->>
->>insert into team_statistics
-+> select team_number, count (player_number)
-+> from teams t, players p
-+> where t.team_number = p.player_team_number
-+> group by team_number;
-
---- 5 row(s) inserted.
->>
->>create volatile table home_games as
-+> select home_team_number, visitor_team_number, game_number, game_location
-+> from games
-+> where extract(year from games.game_time) =
-+> extract(year from current_timestamp) and
-+> extract(month from games.game_time) =
-+> extract(month from current_timestamp);
-
---- 1 row(s) inserted.
->>
->>obey TEST139(tests);
->>-- =================================================================
->>-- this set of tests run basic grant and revoke tests
->>-- =================================================================
->>set schema t139sch;
-
---- SQL operation complete.
->>
->>set param ?tblname '%GAMES';
->>
->>-- test SELECT, UPDATE, DELETE, INSERT on games
->>grant select on games to sql_user1;
-
---- SQL operation complete.
->>showddl games;
-
-CREATE TABLE TRAFODION.T139SCH.GAMES
- (
- HOME_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
- , VISITOR_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
- , GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
- , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT
- DROPPABLE
- , GAME_LOCATION VARCHAR(50) CHARACTER SET ISO88591 COLLATE
- DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
- , PRIMARY KEY (GAME_NUMBER ASC)
- )
-;
-
-ALTER TABLE TRAFODION.T139SCH.GAMES ADD CONSTRAINT
- TRAFODION.T139SCH.VALID_GAME_NUMBER CHECK
- (TRAFODION.T139SCH.GAMES.GAME_NUMBER > 0)
-
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T139SCH.GAMES TO DB__ROOT WITH GRANT OPTION;
- GRANT SELECT ON TRAFODION.T139SCH.GAMES TO SQL_USER1;
-
---- SQL operation complete.
->>-- make sure user1 can select but not other operations
->>sh sqlci -i "TEST139(user1_dml)" -u sql_user1;
->>select count(*) from games;
-
-(EXPR)
---------------------
-
- 8
-
---- 1 row(s) selected.
->>update games set game_location = 'Ohio' where game_location = 'New York';
-
-*** ERROR[4481] The user does not have UPDATE privilege on table or view TRAFODION.T139SCH.GAMES.
-
-*** ERROR[8822] The statement was not prepared.
-
->>update games set game_location = 'New York' where game_location = 'Ohio';
-
-*** ERROR[4481] The user does not have UPDATE privilege on table or view TRAFODION.T139SCH.GAMES.
-
-*** ERROR[8822] The statement was not prepared.
-
->>insert into games values (4, 5, 9, current_timestamp, 'Ohio');
-
-*** ERROR[4481] The user does not have INSERT privilege on table or view TRAFODION.T139SCH.GAMES.
-
-*** ERROR[8822] The statement was not prepared.
-
->>select count(*) from games;
-
-(EXPR)
---------------------
-
- 8
-
---- 1 row(s) selected.
->>delete from games where game_number = 9;
-
-*** ERROR[4481] The user does not have DELETE privilege on table or view TRAFODION.T139SCH.GAMES.
-
-*** ERROR[8822] The statement was not prepared.
-
->>select count(*) from games;
-
-(EXPR)
---------------------
-
- 8
-
---- 1 row(s) selected.
->>exit;
-
-End of MXCI Session
-
->>
->>grant update on games to sql_user1;
-
---- SQL operation complete.
->>showddl games;
-
-CREATE TABLE TRAFODION.T139SCH.GAMES
- (
- HOME_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
- , VISITOR_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
- , GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
- , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT
- DROPPABLE
- , GAME_LOCATION VARCHAR(50) CHARACTER SET ISO88591 COLLATE
- DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
- , PRIMARY KEY (GAME_NUMBER ASC)
- )
-;
-
-ALTER TABLE TRAFODION.T139SCH.GAMES ADD CONSTRAINT
- TRAFODION.T139SCH.VALID_GAME_NUMBER CHECK
- (TRAFODION.T139SCH.GAMES.GAME_NUMBER > 0)
-
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T139SCH.GAMES TO DB__ROOT WITH GRANT OPTION;
- GRANT SELECT, UPDATE ON TRAFODION.T139SCH.GAMES TO SQL_USER1;
-
---- SQL operation complete.
->>-- make sure user 1 can select and update but not other operations
->>sh sqlci -i "TEST139(user1_dml)" -u sql_user1;
->>select count(*) from games;
-
-(EXPR)
---------------------
-
- 8
-
---- 1 row(s) selected.
->>update games set game_location = 'Ohio' where game_location = 'New York';
-
---- 1 row(s) updated.
->>update games set game_location = 'New York' where game_location = 'Ohio';
-
---- 1 row(s) updated.
->>insert into games values (4, 5, 9, current_timestamp, 'Ohio');
-
-*** ERROR[4481] The user does not have INSERT privilege on table or view TRAFODION.T139SCH.GAMES.
-
-*** ERROR[8822] The statement was not prepared.
-
->>select count(*) from games;
-
-(EXPR)
---------------------
-
- 8
-
---- 1 row(s) selected.
->>delete from games where game_number = 9;
-
-*** ERROR[4481] The user does not have DELETE privilege on table or view TRAFODION.T139SCH.GAMES.
-
-*** ERROR[8822] The statement was not prepared.
-
->>select count(*) from games;
-
-(EXPR)
---------------------
-
- 8
-
---- 1 row(s) selected.
->>exit;
-
-End of MXCI Session
-
->>
->>grant delete, insert, references on games to sql_user1;
-
---- SQL operation complete.
->>showddl games;
-
-CREATE TABLE TRAFODION.T139SCH.GAMES
- (
- HOME_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
- , VISITOR_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
- , GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
- , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT
- DROPPABLE
- , GAME_LOCATION VARCHAR(50) CHARACTER SET ISO88591 COLLATE
- DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
- , PRIMARY KEY (GAME_NUMBER ASC)
- )
-;
-
-ALTER TABLE TRAFODION.T139SCH.GAMES ADD CONSTRAINT
- TRAFODION.T139SCH.VALID_GAME_NUMBER CHECK
- (TRAFODION.T139SCH.GAMES.GAME_NUMBER > 0)
-
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T139SCH.GAMES TO DB__ROOT WITH GRANT OPTION;
- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T139SCH.GAMES
- TO SQL_USER1;
-
---- SQL operation complete.
->>-- make sure user1 can do all dml
->>sh sqlci -i "TEST139(user1_dml)" -u sql_user1;
->>select count(*) from games;
-
-(EXPR)
---------------------
-
- 8
-
---- 1 row(s) selected.
->>update games set game_location = 'Ohio' where game_location = 'New York';
-
---- 1 row(s) updated.
->>update games set game_location = 'New York' where game_location = 'Ohio';
-
---- 1 row(s) updated.
->>insert into games values (4, 5, 9, current_timestamp, 'Ohio');
-
---- 1 row(s) inserted.
->>select count(*) from games;
-
-(EXPR)
---------------------
-
- 9
-
---- 1 row(s) selected.
->>delete from games where game_number = 9;
-
---- 1 row(s) deleted.
->>select count(*) from games;
-
-(EXPR)
---------------------
-
- 8
-
---- 1 row(s) selected.
->>exit;
-
-End of MXCI Session
-
->>execute object_privs;
-
-TYPE GRANTOR GRANTEE PRIVS WGO
----- ---------------------------------------- ---------------------------------------- -------------------- --------------------
-
-BT DB__ROOT SQL_USER1 47 0
-BT _SYSTEM DB__ROOT 47 47
-VI _SYSTEM DB__ROOT 33 33
-
---- 3 row(s) selected.
->>-- this query does not always return the correct rows
->>-- execute all_privs;
->>
->>-- test WITH GRANT OPTION
->>--grant select, insert, delete on teams to sql_user2 with grant option;
->>--showddl teams;
->>-- make sure user2 grant of select, insert, delete to user2 succeeds
->>-- make sure user2 grant of update, references fails
->>-- user2 will grant select to user3
->>--sh sqlci -i "TEST139(user2_dml)" -u sql_user2;
->>-- make sure user3 can select
->>-- user3 will grant select to user4
->>--sh sqlci -i "TEST139(user3_dml)" -u sql_user3;
->>--grant update on teams to sql_user2 with grant option;
->>--showddl teams;
->>-- make sure user2 can grant update privilege
->>--sh sqlci -i "TEST139(user2_dml)" -u sql_user2;
->>-- make sure user3 can update
->>--sh sqlci -i "TEST139(user3_dml)" -u sql_user3;
->>-- make sure user4 can select, but not other privs
->>--sh sqlci -i "TEST139(user4_dml)" -u sql_user4;
->>
->>-- test revoke SELECT, UPDATE, DELETE, INSERT, REFERENCES
->>
->>revoke update on games from sql_user1;
-
---- SQL operation complete.
->>showddl games;
-
-CREATE TABLE TRAFODION.T139SCH.GAMES
- (
- HOME_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
- , VISITOR_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
- , GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
- , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT
- DROPPABLE
- , GAME_LOCATION VARCHAR(50) CHARACTER SET ISO88591 COLLATE
- DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
- , PRIMARY KEY (GAME_NUMBER ASC)
- )
-;
-
-ALTER TABLE TRAFODION.T139SCH.GAMES ADD CONSTRAINT
- TRAFODION.T139SCH.VALID_GAME_NUMBER CHECK
- (TRAFODION.T139SCH.GAMES.GAME_NUMBER > 0)
-
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T139SCH.GAMES TO DB__ROOT WITH GRANT OPTION;
- GRANT SELECT, INSERT, DELETE, REFERENCES ON TRAFODION.T139SCH.GAMES TO
- SQL_USER1;
-
---- SQL operation complete.
->>revoke all_dml on games from sql_user2;
-
-*** WARNING[1018] Grant of role or privilege ALL_DML from DB__ROOT to SQL_USER2 not found, revoke request ignored.
-
---- SQL operation complete.
->>showddl games;
-
-CREATE TABLE TRAFODION.T139SCH.GAMES
- (
- HOME_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
- , VISITOR_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
- , GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
- , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT
- DROPPABLE
- , GAME_LOCATION VARCHAR(50) CHARACTER SET ISO88591 COLLATE
- DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
- , PRIMARY KEY (GAME_NUMBER ASC)
- )
-;
-
-ALTER TABLE TRAFODION.T139SCH.GAMES ADD CONSTRAINT
- TRAFODION.T139SCH.VALID_GAME_NUMBER CHECK
- (TRAFODION.T139SCH.GAMES.GAME_NUMBER > 0)
-
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T139SCH.GAMES TO DB__ROOT WITH GRANT OPTION;
- GRANT SELECT, INSERT, DELETE, REFERENCES ON TRAFODION.T139SCH.GAMES TO
- SQL_USER1;
-
---- SQL operation complete.
->>revoke all_dml on teams from sql_user3;
-
-*** WARNING[1018] Grant of role or privilege ALL_DML from DB__ROOT to SQL_USER3 not found, revoke request ignored.
-
---- SQL operation complete.
->>showddl teams;
-
-CREATE TABLE TRAFODION.T139SCH.TEAMS
- (
- TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
- , TEAM_NAME CHAR(20) CHARACTER SET ISO88591 COLLATE
- DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
- , TEAM_CONTACT VARCHAR(50) CHARACTER SET ISO88591 COLLATE
- DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
- , TEAM_CONTACT_NUMBER CHAR(10) CHARACTER SET ISO88591 COLLATE
- DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
- , PRIMARY KEY (TEAM_NUMBER ASC)
- )
-;
-
-ALTER TABLE TRAFODION.T139SCH.TEAMS ADD CONSTRAINT
- TRAFODION.T139SCH.VALID_TEAM_NO CHECK (TRAFODION.T139SCH.TEAMS.TEAM_NUMBER >
- 0)
-
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T139SCH.TEAMS TO DB__ROOT WITH GRANT OPTION;
-
---- SQL operation complete.
->>
->>
->>-- sh sqlci -i "TEST139(authorized)" -u sql_user4;
->>log;