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

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

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;
