-- ============================================================================
-- TEST135 - tests privilege grants and revokes associated with object owners
--
-- @@@ 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 test uses users sql_user1, sql_user2, sql_user3, and sql_user4
--
--   Makes sure privileges are granted correctly for new objects
--   Makes sure privileges are revoked correctly for dropped objects
--
-- Sections:
--   clean_up - removes database setup
--   set_up - prepares for test
--   tests - runs tests
-- ============================================================================

obey TEST135(clean_up);
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
log LOG135 clear;
sh rm -f LOG135-SECONDARY;
obey TEST135(set_up);
obey TEST135(tbl_tests);
obey TEST135(view_tests);
obey TEST135(udr_tests);
obey TEST135(negative_tests);
log;
obey TEST135(clean_up);
exit;

?section clean_up
-- ============================================================================
set schema t135sch; 
-- drop mapping functions
drop table_mapping function sessionize;
-- drop database
drop schema t135sch cascade;
drop schema t135sch_user3 cascade;
drop schema t135sch_user4 cascade;
drop schema t135sch_user5 cascade;

drop role t135_role1;
drop role t135_role2;

?section set_up
-- ============================================================================

-- create schema
create shared schema t135sch;

-- Prepare library file
sh rm -f ./udrtest135.dll;
sh sh $$scriptsdir$$/tools/dll-compile.ksh udrtest135.cpp
  2>&1 | tee LOG135-SECONDARY;
set pattern $$DLL$$ udrtest135.dll;

get tables in schema "_PRIVMGR_MD_";

-- Prepare metadata queries
prepare check_privs from 
select object_name, grantee_name, grantor_name 
from "_PRIVMGR_MD_".object_privileges
where 
  object_name in ('TRAFODION.T135SCH.T135_T1', 'TRAFODION.T135SCH.T135_T2', 'TRAFODION.T135SCH.T135_V1', 'TRAFODION.T135SCH.T135_V2', 'TRAFODION.T135SCH.T135_L1', 'TRAFODION.T135SCH.T135_L2', 'TRAFODION.T135SCH.T135_SESSIONIZE', 'TRAFODION.T135SCH.T135_ADD2')
order by 1,3,2 for read uncommitted access;

?section tbl_tests
-- ============================================================================
set schema t135sch;
 
-- Verify that a create table adds privilege manager metadata
create table t135_t1 (c1 int not null primary key, c2 int);
-- returns 1 row
execute check_privs;

-- Verify that a drop table removes privilege manager metadata
drop table t135_t1;
-- returns 0 rows
execute check_privs;

-- Verify metadata for tables and indexes
create table t135_t1 (c1 int not null primary key, c2 int);
create index ndx1 on t135_t1(c2);
-- returns 1 row
execute check_privs;

drop table t135_t1;
-- returns 0 rows
execute check_privs;

-- Verify metadata for tables, indexes, and views
create table t135_t1 (c1 int not null primary key, c2 int);
create index ndx1 on t135_t1(c2);
create view t135_v1 as select * from t135_t1;
create view t135_v2 as select * from t135_t1;
-- returns 3 rows
execute check_privs;

-- fails
drop table t135_t1;
-- returns 3 rows
execute check_privs;

drop table t135_t1 cascade;
-- returns 0 rows
execute check_privs; 

-- verify views referencing multiple tables and create table like
create table t135_t1 (c1 int not null primary key, c2 int);
create table t135_t2 like t135_t1;
create view t135_v1 as select t135_t1.c1, t135_t2.c2 from t135_t1, t135_t2
   where t135_t1.c1 = t135_t2.c1;
-- return 3 rows
execute check_privs;

drop view t135_v1;
-- return 2 rows
execute check_privs;
drop table t135_t1;
-- return 1 rows
execute check_privs;
drop table t135_t2;
-- return 0 rows
execute check_privs;

?section view_tests
-- ============================================================================
set schema t135sch;
create table t135_t1 (c1 int not null primary key, c2 int);
create table t135_t2 (c1 int not null primary key, c2 int);
create table t135_t3 (a int, b int);
create sequence t135seq;

-- create a view referencing a single table where view creator has all privs
create view t135_v1_t1 as select * from t135_t1;
-- view should be granted all DML privileges
showddl t135_v1_t1;
drop view t135_v1_t1;

-- create a non updatable, non insertable view
create view t135_v2_t1 
as select t135_t1.c1, t135_t2.c2 from t135_t1, t135_t2;
-- view should be granted only SELECT and REFERENCES privileges
showddl t135_v2_t1;
drop view t135_v2_t1;

-- verify that users granted select privilege can create views
-- user cannot create view
sh sqlci -i "TEST135(user1_views)" -u sql_user1;

-- user can create view but only have select priv
grant select on t135_t1 to sql_user1;
sh sqlci -i "TEST135(user1_views)" -u sql_user1;

-- user can create view and have all_dml privs
grant all_dml on t135_t1 to sql_user1;
sh sqlci -i "TEST135(user1_views)" -u sql_user1;

-- make sure creator privileges are set when multiple referenced
-- objects are involved.
grant all_dml on t135_t1 to sql_user2;
-- should fail user2 does not have select privilege on t135_t2
sh sqlci -i "TEST135(user2_views)" -u sql_user2;

grant select on t135_t2 to sql_user2;
-- user can create view and have select priv
sh sqlci -i "TEST135(user2_views)" -u sql_user2;
 
grant all_DML on t135_t2 to sql_user2;
showddl t135_t1;
showddl t135_t2;
-- user can create view but just have select and references, it is
-- a not updatable and insertable view
sh sqlci -i "TEST135(user2_views)" -u sql_user2;

-- test creating a view from several views and tables
create view t135_v1_t1 as select c1 from t135_t1;
create view t135_v2_t1 as select c2 from t135_t1;
create view t135_v1_t2 as select * from t135_t2;

-- have user3 create some objects
sh sqlci -i "TEST135(user3_objects)" -u sql_user3;

-- user3 create some views
-- fails because user3 has no privs
sh sqlci -i "TEST135(user3_views)" -u sql_user3;
grant select on t135_v1_t1 to sql_user3;
grant select on t135_t1 to sql_user3;
-- operations should succeed
sh sqlci -i "TEST135(user3_views)" -u sql_user3;

-- Since user3 now has a view, try to revoke privileges
-- fails because of user3's views
revoke select on t135_v1_t1 from sql_user3;
revoke all on t135_t1 from sql_user3;

sh sqlci -i "TEST135(user3_drops)" -u sql_user3;

-- create some roles
create role t135_role1;
create role t135_role2;
grant role t135_role1, t135_role2 to sql_user4;
grant select on t135_t1 to t135_role1;
grant select on t135_v1_t1 to t135_role2; 

-- have sql_user4 create a view based on role privs
create schema if not exists t135sch_user4 authorization sql_user4;
sh sqlci -i "TEST135(user4_views)" -u sql_user4;

-- should not be able to revoke user4 from role
revoke role t135_role1 from sql_user4;
revoke role t135_role2 from sql_user4;

-- after droppping the view, revokes succeed
drop view t135sch_user4.t135_v1_user4;
revoke role t135_role1 from sql_user4;
revoke role t135_role2 from sql_user4;

-- test views that reference sequence generators
GRANT COMPONENT privilege create_view on sql_operations to sql_user5;
insert into t135_t3 values (1,1);
select * from t135_t3;
grant select on t135_t3 to sql_user5;
showddl t135_t3;
create schema if not exists t135sch_user5 authorization sql_user5;

-- unable to create view, lacking USAGE privilege
sh sqlci -i "TEST135(user5_views)" -u sql_user5;

-- grant privilege and try again
grant usage on sequence t135seq to sql_user5;
showddl sequence t135seq;
sh sqlci -i "TEST135(user5_views)" -u sql_user5;

-- try revoking privilege
-- fails because of sql_user5's view
revoke usage on sequence t135seq from sql_user5;

-- drop views and try again
drop view t135sch_user5.t135_v1_user5;
revoke usage on sequence t135seq from sql_user5;
drop view t135sch.t135_v1_user5;
revoke usage on sequence t135seq from sql_user5;
showddl sequence t135seq;

-- redo but this time grant SELECT, should fail
grant select on sequence t135seq to sql_user5;
showddl sequence t135seq;
REVOKE COMPONENT privilege create_view on sql_operations from sql_user5;

drop table t135_t1 cascade;
drop table t135_t2 cascade;
drop role t135_role1;
drop role t135_role2;

?section udr_tests
-- ============================================================================
-- To create a function/table_mapping function, or SPJ, you need
-- to:
--   be DB__ROOT
--   be library owner
--   have the CREATE_ROUTINE component privilege
--   have USAGE privilege on the library
set schema t135sch_udrs;

create role t135_role1;
get roles;

grant component privilege MANAGE_LIBRARY on sql_operations to t135_role1;
grant component privilege CREATE_ROUTINE on sql_operations to "PUBLIC";
get privileges on component sql_operations for t135_role1;
get privileges on component sql_operations for "PUBLIC";


-- create library and udrs as sql_user1
-- sql_user1 needs MANAGE_LIBRARY privilege to create libraries
-- fails - unsufficient privs
sh sqlci -i "TEST135(create_library)" -u sql_user1;

-- now it succeeds
grant role t135_role1 to sql_user1;
sh sqlci -i "TEST135(create_library)" -u sql_user1;
sh sqlci -i "TEST135(create_drop_udrs)" -u sql_user1;

-- verify that DB__ROOT can create/drop udrs
-- library l1 is owned by sql_user1
obey TEST135(create_drop_udrs);

-- verify that user with USAGE privilege on library can create/drop udrs
-- first show lack of USAGE privilege
sh sqlci -i "TEST135(create_udrs)" -u sql_user2;

-- grant and verify user granted USAGE privilege can create udrs
sh sqlci -i "TEST135(create_user3_udrs)" -u sql_user3;

-- try to revoke the USAGE privilege from sql_user3
sh sqlci -i "TEST135(revoke_usage)" -u sql_user1;

-- now it should work
drop function TRAFODION."T135SCH_USER3"."T135_ADD2";
drop table_mapping function t135sch_user3.t135_sessionize;
sh sqlci -i "TEST135(revoke_usage)" -u sql_user1;

-- reset
obey TEST135(drop_library);
revoke component privilege "MANAGE_LIBRARY" on sql_operations from t135_role1;
revoke component privilege CREATE_ROUTINE on sql_operations from "PUBLIC";
get privileges on component sql_operations for t135_role1;
get privileges on component sql_operations for "PUBLIC";

revoke role t135_role1 from sql_user1;
drop role t135_role1;
get roles;

drop schema t135sch_user3 cascade;

?section create_library
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
set schema t135sch_udrs;
log LOG135;
values (user);

create private schema t135sch_udrs;
set schema t135sch_udrs;
create library t135_l1 file 'udrtest135.dll';
grant USAGE on library t135_l1 to sql_user3;

?section drop_library
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
set schema t135sch_udrs;
log LOG135;
values (user);

drop library t135_l1;
drop schema t135sch_udrs;

?section create_drop_udrs
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
set schema t135sch_udrs;
log LOG135;
values (user);

obey TEST135(create_udrs);
obey TEST135(drop_udrs);

?section create_udrs
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
set schema t135sch_udrs;
log LOG135;
values (user);

create table_mapping function t135_sessionize(colname char(10), timeintval int)
returns (userid char(32), ts largeint, session_id largeint)
external name 'SESSIONIZE'
library t135_l1;

create function t135_ADD2(int,int) returns (ADD2 int)
language c parameter style sql external name 'add2'
library t135_l1
deterministic no sql final call allow any parallelism state area size 1024 ;

?section create_user3_udrs
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
create private schema t135sch_user3;
set schema t135sch_user3;
log LOG135;
values (user);

create table_mapping function t135_sessionize(colname char(10), timeintval int)
returns (userid char(32), ts largeint, session_id largeint)
external name 'SESSIONIZE'
library t135sch_udrs.t135_l1;

create function t135_ADD2(int,int) returns (ADD2 int)
language c parameter style sql external name 'add2'
library t135sch_udrs.t135_l1
deterministic no sql final call allow any parallelism state area size 1024 ;

?section revoke_usage
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
set schema t135sch_udrs;
log LOG135;
values (user);

revoke usage on library t135_l1 from sql_user3;

?section drop_udrs
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
set schema t135sch_udrs;
log LOG135;
values (user);

drop function t135_add2;
drop table_mapping function t135_sessionize;

?section negative_tests
-- ============================================================================
set schema t135sch;

?section user1_views
-- ============================================================================
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
set schema t135sch;
log LOG135;
create view user1_v1 as select * from t135_t1;
showddl user1_v1;
drop view user1_v1;

?section user2_views
-- ============================================================================
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
set schema t135sch;
log LOG135;
create view user2_v1 as
select t135_t1.c1, t135_t2.c2 from t135_t1, t135_t2;
showddl user2_v1;
drop view user2_v1;

?section user3_objects
-- ============================================================================
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
create private schema t135sch_user3;
set schema t135sch_user3;
log LOG135;
create table t135_t3 (c1 int not null primary key, c2 int);
create table t135_t4 (c1 int not null, c2 largeint not null primary key, c3 int);
showddl t135_t3;
showddl t135_t4;

?section user3_views
-- ============================================================================
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
set schema t135sch_user3;
log LOG135;
create view t135_v1_user3 as 
  select t135sch.t135_t1.c2, t135sch.t135_v1_t1.c1, t135_t4.c3
  from t135sch.t135_t1, t135sch.t135_v1_t1, t135_t4;
showddl t135_v1_user3;

?section user3_drops
-- ============================================================================
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
set schema t135sch_user3;
log LOG135;
drop table t135_t3 cascade;
drop table t135_t4 cascade;

?section user3_constraint
-- ============================================================================
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
set schema t135sch_user3;
log LOG135;
alter table t135_t3 add constraint t1_t3 foreign key (c1) references t135sch.t135_t1;
showddl t135_t3;

?section user4_views
-- ============================================================================
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
set schema t135sch_user4;
log LOG135;
create view t135_v1_user4 as
  select t135sch.t135_t1.c2, t135sch.t135_v1_t1.c1
  from t135sch.t135_t1, t135sch.t135_v1_t1;
showddl t135_v1_user4;

?section user5_views
-- ============================================================================
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
set schema t135sch_user5;
log LOG135;
create view t135_v1_user5 as select seqnum(t135sch.t135seq) as a from t135sch.t135_t3;
showddl t135_v1_user5;
select * from t135_v1_user5;
set schema t135sch;
create view t135_v1_user5 as select seqnum(t135seq) as a from t135_t3;
showddl t135_v1_user5;
select * from t135_v1_user5;

