-- ============================================================================
-- TEST137 - tests:
--   REGISTER COMPONENT
--   UNREGISTER COMPONENT
--   CREATE COMPONENT PRIVILEGE
--   DROP COMPONENT PRIVILEGE
--   GRANT COMPONENT PRIVILEGE
--   REVOKE COMPONENT PRIVILEGE
--
-- @@@ 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 in valid only for Linux
-- This test cannot run in parallel with other tests, it turns off the
-- public privilege for create catalog and create schema.  This would cause
-- concurrent tests to fail.
--
-- Sections:
--   clean_up - removes database setup
--   set_up - prepares for test
--   create_objects - creates objects needed by the test
--   tests - runs tests
-- ============================================================================

control query default SKIP_METADATA_VIEWS 'ON';

obey TEST137(clean_up);
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
log LOG137 clear;
obey TEST137(set_up);
obey TEST137(create_db);
obey TEST137(register_components);
obey TEST137(create_privs);
obey TEST137(grant_revoke_priv_desc);
obey TEST137(drop_privs);

log;
-- run tests as different users
sh sqlci -i "TEST137(user1_tests)" -u sql_user1;
sh sqlci -i "TEST137(user4_tests)" -u sql_user4;;

log LOG137;
-- test getting component privileges through roles
obey TEST137(role_tests);

-- test the SQL_OPERATIONS privilege
obey TEST137(operator_privs);

obey TEST137(clean_up);

-- run tests for revoke.
obey TEST137(revoke_comp_privs_setup);
obey TEST137(select_for_lv);
sh sh runmxci.ksh -i "TEST137(revoke_comp_privs_setup_user1)" -u sql_user1;
obey TEST137(select_for_lv);
sh sh runmxci.ksh -i "TEST137(revoke_comp_privs_setup_user2)" -u sql_user2;
obey TEST137(select_for_lv);
sh sh runmxci.ksh -i "TEST137(revoke_comp_privs_setup_user3)" -u sql_user3;
obey TEST137(select_for_lv);
sh sh runmxci.ksh -i "TEST137(revoke_comp_privs_setup_user4)" -u sql_user4;
obey TEST137(select_for_lv);
sh sh runmxci.ksh -i "TEST137(revoke_comp_privs_test_user2)" -u sql_user2;
obey TEST137(select_for_lv);
obey TEST137(revoke_comp_privs_cleanup);
log;
exit;

?section clean_up
-- remove component information
unregister component library_books cascade;
unregister component library_accounts cascade;
unregister component library_users cascade;
unregister component library_other cascade;
execute get_component_operations;
execute get_components;
-- drop database
drop schema t137 cascade;

drop component privilege lib_view_benefactors cascade;
unregister component library_fund_raisers cascade;

-- remove roles
revoke role library_admin from sql_user1;
revoke role library_admin from sql_user2;
drop role library_admin;

revoke role library_ckout_clerks from sql_user1;
revoke role library_ckout_clerks from sql_user2;
revoke role library_ckout_clerks from sql_user3, sql_user4, sql_user5;
drop role library_ckout_clerks;

revoke component privilege lib_view_checkouts on library_books from lib_role_test;
revoke role lib_role_test from sql_user5;
revoke role db__rootrole from sql_user5;
drop role lib_role_test;
     
revoke role md_access from sql_user1, sql_user4, sql_user5;
revoke select on "_MD_".auths from md_access;
revoke select on "_PRIVMGR_MD_".components from md_access;
revoke select on "_PRIVMGR_MD_".component_operations from md_access;
revoke select on "_PRIVMGR_MD_".component_privileges from md_access;
drop role md_access;

?section set_up
prepare get_roles from
select substring (auth_db_name,1,20) as role_name
from "_MD_".AUTHS
where auth_db_name like 'LIBRARY%'
order by 1
for read uncommitted access;

prepare get_components from
select substring (component_name,1,20) as component,
       substring (component_description,1,30) as description
from trafodion."_PRIVMGR_MD_".components
where component_name like 'LIBRARY%'
order by 1
for read uncommitted access;

prepare get_component_operations from
select substring (c.component_name,1,20) as component,
       substring (o.operation_name,1,20) as operation_name,
       substring (o.operation_code,1,2) as operation_code
from trafodion."_PRIVMGR_MD_".component_operations o,
     trafodion."_PRIVMGR_MD_".components c 
where (component_name like 'LIBRARY%' and 
       c.component_uid = o.component_uid)
order by 1,2, 3
for read uncommitted access;

prepare get_priv_desc from
select substring (component_name,1,20) as component,
       substring (operation_name,1,20) as operation_name,
       substring (grantee_name,1,20) as grantee_name,
       grant_depth as grant_depth
from trafodion."_PRIVMGR_MD_".components c,
     trafodion."_PRIVMGR_MD_".component_operations p,
     trafodion."_PRIVMGR_MD_".component_privileges d
where c.component_uid = p.component_uid
  and p.operation_code = d.operation_code
  and p.component_uid = d.component_uid
  and c.component_name like 'LIBRARY%'
order by 1, 2, d.grantor_ID, d.grantee_name, grant_depth
for read uncommitted access;

?section create_db
create role md_access;
grant select on "_MD_".auths to md_access;
grant select on "_PRIVMGR_MD_".components to md_access;
grant select on "_PRIVMGR_MD_".component_operations to md_access;
grant select on "_PRIVMGR_MD_".component_privileges to md_access;
grant role md_access to sql_user1, sql_user4, sql_user5;

create shared schema t137;
set schema t137;

create role library_admin;
grant role library_admin to sql_user1;
grant role library_admin to sql_user2;
create role library_ckout_clerks;
grant role library_ckout_clerks to sql_user1;
grant role library_ckout_clerks to sql_user2;
grant role library_ckout_clerks to sql_user3, sql_user4, sql_user5;

create table books
  (book_name  varchar (50) not null,
   book_id    largeint primary key not null,
   book_author varchar (50) not null,
   book_type  int not null);

--grant all on books to library_admin;
--grant select, update on bools to library_ckout_clerks;
grant select on books to public;
grant all on books to sql_user1; 
grant select on books to sql_user1;
grant update on books to sql_user1;
grant select on books to sql_user2;
grant update on books to sql_user2;
grant select on books to sql_user6;
grant select on books to sql_user7;
grant select on books to sql_user8;

create table library_users
   (user_name    varchar (50) not null,
    user_id      int primary key not null,
    user_details varchar (50) not null);

--grant all on library_users to library_admin;
grant all on library_users to sql_user1;
grant select on library_users to sql_user6;
grant select on library_users to sql_user7;
grant select on library_users to sql_user8;
--grant select on library_users to library_ckout_clerk;

showddl role library_admin;
showddl role library_ckout_clerks;

?section register_components
register component Library_books;
register component library_accounts detail 'Test component 2';
register component library_users detail 'Test component 3';
-- should be 3 rows
execute get_components;

-- fails with a syntax error
register component user;
register component "delimited not supported";
register component abi**def;

-- fails with component already registered
register component library_books;

-- unregister a component that has not been defined
unregister component library_clerks;

-- a successful unregister
register component library_clerks;
-- should be 4 rows
execute get_components;
unregister component library_clerks;
-- should return 3 rows
execute get_components;

?section create_privs
-- create component privileges for library_books:
create component privilege lib_manage_checkouts as 'MC' on library_books;
create component privilege lib_view_checkouts as 'VC' on library_books
   detail 'Can see checkout information';
create component privilege lib_view_repository as 'VR' on library_books;

-- create component privileges for library_accounts:
create component privilege lib_manage_overdue as 'MO' on library_accounts
  detail 'Can handle tasks to manage overdue books';
create component privilege lib_view_checkouts as 'VC' on library_accounts;

-- create component privileges for library_users:
create component privilege lib_manage_users as 'MU' on library_users;
create component privilege lib_view_users as 'VU' on library_users;

-- should return 7 rows
execute get_component_operations; 

-- error: component does not exist
create component privilege lib_misc as 'MI' on library_xxxx;

-- error: privilege already defined
create component privilege lib_manage_users as 'MA' on library_users;

-- error: privilege type already defined
create component privilege lib_misc as 'MU' on library_users;

?section grant_revoke_priv_desc
grant component privilege lib_manage_checkouts, 
                          lib_view_checkouts, 
                          lib_view_repository 
   on library_books to library_admin;

grant component privilege lib_manage_checkouts, 
                          lib_view_checkouts, 
                          lib_view_repository 
   on library_books to sql_user1 with grant option;

grant component privilege lib_view_checkouts,
                          lib_view_repository
  on library_books to library_ckout_clerks;

grant component privilege lib_view_repository 
   on library_books to public;
grant component privilege lib_view_repository 
   on library_books to sql_user5; 
grant component privilege lib_view_repository 
   on library_books to sql_user6; 
grant component privilege lib_view_repository 
   on library_books to sql_user7; 
grant component privilege lib_view_repository 
   on library_books to sql_user8; 

grant component privilege lib_manage_overdue, lib_view_checkouts
   on library_accounts to library_admin;
grant component privilege lib_manage_overdue, lib_view_checkouts
   on library_accounts to sql_user2 with grant option;
grant component privilege lib_view_checkouts 
   on library_accounts to library_ckout_clerks;

grant component privilege lib_manage_users, lib_view_users 
   on library_users to library_admin;
grant component privilege lib_manage_users, lib_view_users
   on library_users to sql_user3 with grant option;

-- should return 7 rows
execute get_component_operations;

-- should return 28 rows
execute get_priv_desc;

-- grant privileges already granted
grant component privilege lib_view_repository
   on library_books to sql_user6;

-- should return 7 rows
execute get_component_operations;

-- add WGO
-- should return 28 rows, sql_user6 should be WGO
grant component privilege lib_view_repository
   on library_books to sql_user6 with grant option; 
execute get_priv_desc;

-- make sure it is not taken away
grant component privilege lib_view_repository
   on library_books to sql_user6; 
-- returns 28 rows, sql_user6 still has WGO as Y
execute get_priv_desc;

-- error unknown component
grant component privilege lib_view_repository on library_xxxx to sql_user9;

-- error: unknown privilege
grant component privilege lib_view_xxx on library_books to sql_user9;
grant component privilege lib_view_checkouts,
                          lib_view_repository,
                          lib_view_xxx
  on library_books to sql_user9;
 
-- error: grant to unknown user or role
grant component privilege lib_view_repository on library_books to authid_xxxx;

-- success: grant to a role WGO
grant component privilege lib_view_repository
  on library_books to DB__ROOTROLE with grant option;
revoke grant option for component privilege lib_view_repository
  on library_books from DB__ROOTROLE;
revoke component privilege lib_view_repository
  on library_books from DB__ROOTROLE;

-- error: specify the same privilege twice
grant component privilege lib_view_repository,
                          lib_view_repository
  on library_books to DB__ROOTROLE;

?section drop_privs
drop component privilege lib_view_repository on library_books cascade;
-- returns 6 rows
execute get_component_operations;

-- unregister component gets rid of priivleges
unregister component library_users cascade;
-- returns 4 rows
execute get_component_operations;
-- returns 2 rows
execute get_components;

-- error: unknown component
drop component privilege lib_view_repository on library_xxxx;

-- error unknown privilege
drop component privilege lib_view_xxxx on library_books;

?section role_tests
-- tests grants and revoke on roles owned by user work correctly
create role lib_role_test;

-- user5_tests fail - user5 has no priv
grant component privilege lib_view_checkouts on library_books to lib_role_test with grant option;
sh sqlci -i "TEST137(user5_tests)" -u sql_user5;

-- user5_tests for priv lib_view_checkouts succeeds, have privs through role
grant role lib_role_test to sql_user5;
sh sqlci -i "TEST137(user5_tests)" -u sql_user5;
revoke component privilege lib_view_checkouts on library_books from lib_role_test;
revoke role lib_role_test from sql_user5;
drop role lib_role_test;

-- user5_tests for priv lib_view_checkouts succeeds, have privs through DB__ROOTROLE
grant role db__rootrole to sql_user5;
sh sqlci -i "TEST137(user5_tests)" -u sql_user5;
revoke role db__rootrole from sql_user5;

?section operator_privs
-- This section tests operation privileges:
-- Test environment
--   sql_user2 - has been granted library_admin role
--   sql_user3 - has been granted library_ckout_clerks role
--   sql_user6 - has been granted db__useradmin role 
--   sql_user7 - will be granted/revoked SQL privileges
--   sql_user8 - no SQL privileges or roles with SQL privileges
--   db__root
--   db__useradmin - has implicit SQL privileges
--   library_admin - will be granted and revoked SQL privileges
--   library_ckout_clerks - no SQL privileges

-- set up privileges
showddl role library_admin;
showddl role library_ckout_clerks;
showddl component sql_operations;

-- user2
-- Create operations fail
sh sh runmxci.ksh -i "TEST137(user2_cc)" -u sql_user2;
-- create catalog works
grant component privilege create_catalog on sql_operations to library_admin;
sh sh runmxci.ksh -i "TEST137(user2_cc)" -u sql_user2;
-- create catalog and create schema works
grant component privilege create_schema on sql_operations to library_admin;
sh sh runmxci.ksh -i "TEST137(user2_cc)" -u sql_user2;
-- create catalog fails
revoke component privilege create_catalog 
   on sql_operations from library_admin;
sh sh runmxci.ksh -i "TEST137(user2_cc)" -u sql_user2;
revoke component privilege create_schema
   on sql_operations from library_admin;

-- user3 (operation fail)
sh sh runmxci.ksh -i "TEST137(user3_cc)" -u sql_user3;

-- user6 
-- operation fails
sh sh runmxci.ksh -i "TEST137(user6_cc)" -u sql_user6;
-- operation works
grant role db__useradmin to sql_user6;
sh sh runmxci.ksh -i "TEST137(user6_cc)" -u sql_user6;
-- operation fails
revoke role db__useradmin from sql_user6;
sh sh runmxci.ksh -i "TEST137(user6_cc)" -u sql_user6;

-- user7
-- fails
sh sh runmxci.ksh -i "TEST137(user7_cc)" -u sql_user7;
-- works
grant component privilege create_catalog, create_schema 
   on sql_operations to sql_user7;
sh sh runmxci.ksh -i "TEST137(user7_cc)" -u sql_user7;
-- fails
revoke component privilege create_schema, create_catalog 
   on sql_operations from sql_user7;
sh sh runmxci.ksh -i "TEST137(user7_cc)" -u sql_user7;

-- user8 (fails)
sh sh runmxci.ksh -i "TEST137(user8_cc)" -u sql_user8;

showddl component sql_operations;

?section user1_tests
obey TEST137 (set_up);
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
-- user1_tests
log LOG137;
values (user);
-- These tests should succeed
-- returns 2 rows
showddl role db__rootrole;
execute get_component_operations;
grant component privilege lib_view_checkouts on library_books to sql_user4 
   with grant option;
grant component privilege lib_manage_checkouts on library_books to sql_user5;
-- returns 16 rows
execute get_priv_desc;

-- These tests should fail
register component library_other;
create component privilege lib_other as 'LO' on library_books;
--revoke component privilege lib_manage_checkouts
--   on library_books from library_admin;
--revoke component privilege lib_view_repository
--   on library_books from sql_user6;
log;

?section user4_tests
obey TEST137 (set_up);
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
log LOG137;
values(user);
-- these tests should succeed
grant component privilege lib_view_checkouts on library_books to sql_user6;
execute get_priv_desc;
revoke component privilege lib_view_checkouts on library_books from sql_user6;

-- these tests should fail
grant component privilege lib_manage_checkouts on library_books to sql_user6;
log;

?section user5_tests
obey TEST137 (set_up);
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
log LOG137;
values(user);
grant component privilege lib_view_repository on library_books to sql_user7;
grant component privilege lib_view_checkouts on library_books to sql_user7;
log;

-- below is the setup and testing for various scenarios for revoke component privileges
?section revoke_comp_privs_setup
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
log LOG137;
create role library_admin;
grant role library_admin to sql_user1, sql_user2;
create role library_ckout_clerks;
grant role library_ckout_clerks to sql_user1, sql_user2, sql_user3, sql_user4, sql_user5;


register component Library_books;
create component privilege lib_manage_checkouts as 'MC' on library_books;
create component privilege lib_view_checkouts as 'VC' on library_books
   detail 'Can see checkout information';
create component privilege lib_view_repository as 'VR' on library_books;

grant component privilege lib_manage_checkouts,
                          lib_view_checkouts,
                          lib_view_repository
   on library_books to library_admin;

grant component privilege lib_manage_checkouts,
                          lib_view_checkouts,
                          lib_view_repository
   on library_books to sql_user1 with grant option;

grant component privilege lib_view_checkouts,
                          lib_view_repository
  on library_books to library_ckout_clerks;

grant component privilege lib_view_repository
   on library_books to sql_user5;
grant component privilege lib_view_repository
   on library_books to sql_user6;
grant component privilege lib_view_repository
   on library_books to sql_user7;
grant component privilege lib_view_repository
   on library_books to sql_user8;

log;

?section revoke_comp_privs_setup_user1
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
log LOG137;
grant component privilege lib_view_repository
on library_books to sql_user2 with grant option;
log;

?section revoke_comp_privs_setup_user2
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
log LOG137;
grant component privilege lib_view_repository
on library_books to sql_user3 with grant option;

grant component privilege lib_view_repository
on library_books to sql_user1;

grant component privilege lib_view_repository
on library_books to sql_user5;
log;

?section revoke_comp_privs_setup_user3
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
log LOG137;
grant component privilege lib_view_repository
on library_books to DB__ROOT with grant option;

grant component privilege lib_view_repository
on library_books to sql_user5;

grant component privilege lib_view_repository
on library_books to sql_user4 with grant option;
log;

?section revoke_comp_privs_setup_user4
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
log LOG137;
grant component privilege lib_view_repository
on library_books to sql_user5;
log;

?section revoke_comp_privs_test_user2
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
log LOG137;
revoke component privilege lib_view_repository
on library_books from sql_user3;
log;

?section revoke_comp_privs_cleanup
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
log LOG137 ;
unregister component Library_books cascade;
revoke role library_admin from sql_user1, sql_user2;
revoke role library_ckout_clerks from sql_user1, sql_user2, sql_user3, sql_user4, sql_user5;
drop role library_admin;
drop role library_ckout_clerks;
log;

?section user2_cc
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
log LOG137;
-- user2 requests
--create catalog t137cat1;
--create schema t137cat1.t137sch;
--drop schema t137cat1.t137sch;
--drop catalog t137cat1;
--create schema t137sch;
--drop schema t137sch cascade;
log;

?section user3_cc
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
log LOG137;
-- user3 requests
--create catalog t137cat1;
--create schema t137sch;
log;

?section user6_cc
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
log LOG137;
-- user6 requests
--create schema t137sch;
--drop schema t137sch cascade;
log;

?section user7_cc
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
log LOG137;
-- user7 requests
--create catalog t137cat1;
--create schema t137cat1.t137sch;
--drop schema t137cat1.t137sch;
--drop catalog t137cat1;
log;

?section user8_cc
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
log LOG137;
-- user8 requests
--create catalog t137cat1;
--create schema t137sch;
log;


