-- ============================================================================
-- TEST138 - tests DDL privilege checking
--
-- @@@ START COPYRIGHT @@@
--
-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at
--
--   http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied.  See the License for the
-- specific language governing permissions and limitations
-- under the License.
--
-- @@@ END COPYRIGHT @@@
--
-- This tests the following commands:
--
--   CREATE, DROP, ALTER table
--   CREATE, DISABLE/ENABLE, DROP index
--   CREATE, DROP view
--   CREATE, DROP sequence
--
-- Sections:
--   clean_up - removes database setup
--   set_up - prepares for test
--   test_<type> - runs tests for different types (tables, views, etc)
--   test_<type>_<user> - runs tests as a particular user
-- ============================================================================

obey TEST138(clean_up);
obey TEST138(set_up);
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'on';
log LOG138 clear;
obey TEST138(test_create);
obey TEST138(test_alter);
obey TEST138(test_view);
obey TEST138(test_sequence);
obey TEST138(test_drop);
log;
obey TEST138(clean_up);
exit;

?section clean_up
set schema t138sch;
drop sequence user1_seq1;
drop sequence user1_seq2;
-- drop database
drop schema t138sch cascade;
drop schema sch138c cascade;

?section set_up
-- turn off CREATE_TABLE privilege, so user1 can no longer create objs
revoke component privilege "CREATE" on SQL_OPERATIONS from sql_user1;
revoke component privilege CREATE_SEQUENCE on SQL_OPERATIONS from sql_user1;
revoke component privilege "CREATE_TABLE" on SQL_OPERATIONS from sql_user1;
revoke component privilege "CREATE_VIEW" on SQL_OPERATIONS from sql_user1;
revoke component privilege "ALTER" on SQL_OPERATIONS from sql_user2;
revoke component privilege "ALTER_TABLE" on SQL_OPERATIONS from sql_user2;
revoke component privilege "DROP" on SQL_OPERATIONS from sql_user2;
revoke component privilege DROP_SEQUENCE on SQL_OPERATIONS from sql_user2;
revoke component privilege "DROP_VIEW" on SQL_OPERATIONS from sql_user2;

?section 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;
set schema t138sch;
get tables;

-- Verify sql_user1 does not have CREATE or CREATE_TABLE privilege
get privileges on component sql_operations for sql_user1;
sh sqlci -i "TEST138(create_tbl)" -u sql_user1;

-- turn on CREATE_TABLE privilege
get privileges on component sql_operations for sql_user1;
grant component privilege CREATE_TABLE on SQL_OPERATIONS to sql_user1;
get privileges on component sql_operations for sql_user1;
sh sqlci -i "TEST138(create_tbl)" -u sql_user1;
-- sql_user1 cannot alter or drop
sh sqlci -i "TEST138(alter_tbl)" -u sql_user1;
sh sqlci -i "TEST138(drop_tbl)" -u sql_user1;

get tables;

obey TEST138(drop_tbl);

get tables;

?section 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;
get tables;

-- Verify sql_user2 does not have ALTER or ALTER_TABLE privilege
get privileges on component sql_operations for sql_user2;

-- create some tables
grant component privilege CREATE_TABLE on sql_operations to sql_user1;
sh sqlci -i "TEST138(create_tbl)" -u sql_user1;
revoke component privilege CREATE_TABLE on sql_operations from sql_user1;

-- user2 cannot alter them
sh sqlci -i "TEST138(alter_tbl)" -u sql_user2;

-- grant the ALTER privilege to user2
grant component privilege alter_table on sql_operations to sql_user2;
sh sqlci -i "TEST138(alter_tbl)" -u sql_user2;

-- 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;
grant component privilege alter_library, alter_routine, alter_sequence, alter_view 
on sql_operations to sql_user2;
get component privileges on sql_operations for sql_user2;
sh sqlci -i "TEST138(alter_tbl)" -u sql_user2;
revoke component privilege alter_library, alter_routine, alter_sequence, alter_view 
on sql_operations from sql_user2;
get component privileges on sql_operations for sql_user2;
obey TEST138(drop_tbl);

-- reset
revoke component privilege CREATE_TABLE on SQL_OPERATIONS from sql_user1;
get privileges on component sql_operations for "PUBLIC";
get privileges on component sql_operations for sql_user1;

?section test_view
-- =================================================================
-- run tests to make sure view related operations are
-- authorized correctly. 
-- =================================================================

set schema t138sch;
get views;
get privileges on component sql_operations for sql_user1;
get privileges on component sql_operations for sql_user2;

-- create some tables
grant component privilege CREATE_TABLE on sql_operations to sql_user1;
sh sqlci -i "TEST138(create_tbl)" -u sql_user1;
revoke component privilege CREATE_TABLE on sql_operations from sql_user1;

-- user1 should not be able to create any views
sh sqlci -i "TEST138(create_view)" -u sql_user1;

-- grant CREATE_VIEW privilege and try again
grant component privilege "CREATE_VIEW" on SQL_OPERATIONS to sql_user1;
get privileges on component sql_operations for sql_user1;
sh sqlci -i "TEST138(create_view)" -u sql_user1;

-- see if user2 can drop user1's views
sh sqlci -i "TEST138(drop_view)" -u sql_user2;

-- give user2 drop privilege and then drop
grant component privilege drop_view on sql_operations to sql_user2;
get privileges on component sql_operations for sql_user2;
sh sqlci -i "TEST138(drop_view)" -u sql_user2;

-- restore
revoke component privilege DROP_VIEW on SQL_OPERATIONS from sql_user2;
revoke component privilege CREATE_VIEW on SQL_OPERATIONS from sql_user1;
get privileges on component sql_operations for sql_user1;
get privileges on component sql_operations for sql_user2;
obey TEST138(drop_tbl);

?section test_sequence
-- =================================================================
-- run tests to make sure sequence related operations are
-- authorized correctly.
-- =================================================================

set schema t138sch;
get sequences in schema t138sch;
get privileges on component sql_operations for sql_user1;
get privileges on component sql_operations for sql_user2;

-- user1 should not be able to create any sequences
sh sqlci -i "TEST138(create_sequence)" -u sql_user1;

-- grant CREATE_SEQUENCE privilege and try again
grant component privilege "CREATE_SEQUENCE" on SQL_OPERATIONS to sql_user1;
get privileges on component sql_operations for sql_user1;
sh sqlci -i "TEST138(create_sequence)" -u sql_user1;

-- see if user2 can drop user1's sequences
sh sqlci -i "TEST138(drop_sequence)" -u sql_user2;

-- give user2 drop privilege and then drop
grant component privilege drop_sequence on sql_operations to sql_user2;
get privileges on component sql_operations for sql_user2;
sh sqlci -i "TEST138(drop_sequence)" -u sql_user2;

-- restore
revoke component privilege DROP_SEQUENCE on SQL_OPERATIONS from sql_user2;
revoke component privilege CREATE_SEQUENCE on SQL_OPERATIONS from sql_user1;
get privileges on component sql_operations for sql_user1;
get privileges on component sql_operations for sql_user2;

?section test_drop
create schema sch138c;
set schema sch138c;
-- Setup libraries, procedures and functions
create library t138_l1 file 'udrtest135.dll';

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 ;
-- 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);
create view v138 as select * from T138;
create sequence sq138;

select count (*) from TRAFODION."_MD_".OBJECTS WHERE schema_name = 'SCH138C';

drop schema SCH138C;
select count (*) from TRAFODION."_MD_".OBJECTS WHERE schema_name = 'SCH138C';

drop schema SCH138C cascade;
select count (*) from TRAFODION."_MD_".OBJECTS WHERE schema_name = 'SCH138C';

?section create_tbl
set schema t138sch;
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'on';
log LOG138;
get tables;
create table user1_t1 (c1 int not null primary key, c2 int);
insert into user1_t1 values (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7);
select count(*) from user1_t1;
-- add grant to allow user2 to create FK constraints
grant references on user1_t1 to sql_user2;
showddl user1_t1;

create table user1_t2 (c1 int, c2 int);
insert into user1_t2 values (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7);
select count(*) from user1_t2;
showddl user1_t2;

?section alter_tbl
set schema t138sch;
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'on';
log LOG138;
get tables;

-- add columns, constraints, and indexes
alter table user1_t1 add column c3 int default 0;
alter table user1_t1 add constraint user1_ck check (c2 > 0);
alter table user1_t1 add constraint user1_uq unique (c2);
alter table user1_t2 add constraint user1_pk primary key (c1);
alter table user1_t2 add constraint user1_fk foreign key (c2) references user1_t1;
create index user1_t1_ndx on user1_t1(c2);
alter table user1_t1 disable index user1_t1_ndx;
create index user1_t2_ndx  on user1_t2(c2) no populate;
alter table user1_t2 disable all indexes;

showddl user1_t1;
showddl user1_t2;

-- remove indexes, constraints, and columns
alter table user1_t1 enable index user1_t1_ndx;
alter table user1_t2 enable all indexes;
drop index user1_t1_ndx;
drop index user1_t2_ndx;
alter table user1_t1 drop constraint user1_ck;
alter table user1_t1 drop constraint user1_uq;
alter table user1_t2 drop constraint user1_pk;
alter table user1_t2 drop constraint user1_fk;
alter table user1_t1 drop column c3;

showddl user1_t1;
showddl user1_t2;

alter table user1_t1 rename to user1_t4;
select count(*) from user1_t4;
alter table user1_t4 rename to user1_t1;

?section drop_tbl
set schema t138sch;
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'on';
log LOG138;
get tables;
drop table user1_t1 cascade;
drop table user1_t2 cascade;
get tables;

?section create_view
set schema t138sch;
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'on';
log LOG138;
get views;
create view user1_v1 as select * from user1_t1;
create view user1_v2 (c1, c2) as 
  select t1.c1, t2.c2 from user1_t1 t1, user1_t2 t2;
get views;

?section drop_view
set schema t138sch;
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'on';
log LOG138;
get views;
drop view user1_v1;
drop view user1_v2;
get views;

?section create_sequence
set schema t138sch;
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'on';
log LOG138;
get sequences in schema t138sch;
create sequence user1_seq1;
create sequence user1_seq2;
get sequences in schema t138sch;

?section drop_sequence
set schema t138sch;
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'on';
log LOG138;
get sequences in schema t138sch;
drop sequence user1_seq1;
drop sequence user1_seq2;
get sequences in schema t138sch;

