core/sql/regress/privs2/TEST144

-- ============================================================================
-- TEST144 - tests grant and revoke privileges for UDRs
--
-- @@@ 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 @@@
--
--  Tests grant and revoke for functions
--  Tests get commands:
--    get [libraries, functions, table_mapping_functions, procedures] for user
--    get [libraries, functions, table_mapping_functions, procedures] for role
-- ============================================================================

cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
obey TEST144(clean_up);
log LOG144 clear;
obey TEST144(create_db);
obey TEST144(set_up);
obey TEST144(test_grants);
obey TEST144(test_revokes);
log;
obey TEST144(clean_up);
exit;

?section clean_up
-- drop database
drop schema if exists t144user1 cascade;

revoke update, usage on library t144_l1 from sql_user2;
revoke usage on library t144_l2 from t144role1;
revoke execute on procedure "_LIBMGR_".help from t144role1;
revoke role t144role1 from sql_user4;
drop role t144role1;

revoke execute on procedure "_LIBMGR_".help from sql_user5 by sql_user3;
revoke execute on procedure "_LIBMGR_".help from sql_user3 by sql_user2;
revoke execute on procedure "_LIBMGR_".help from sql_user2;

grant component privilege "SHOW" on sql_operations to "PUBLIC";

?section create_db
create schema t144user1 authorization sql_user1;
set schema t144user1;

-- compile cpp programs
set pattern $$QUOTE$$ '''';

sh rm -f ./etest140.dll;
sh sh $$scriptsdir$$/tools/dll-compile.ksh etest140.cpp
  2>&1 | tee LOG144_MD_OUTPUT;
set pattern $$DLL_MD$$ etest140.dll;

sh rm -f ./udfs.dll;
sh sh $$scriptsdir$$/tools/dll-compile.ksh udfs.cpp
  2>&1 | tee LOG144_UDF_OUTPUT;
set pattern $$DLL_UDF$$ udfs.dll;

-- create the library for metadata udf
create library t144_l1 file $$QUOTE$$ $$REGRRUNDIR$$/$$DLL_MD$$ $$QUOTE$$ ;
create function t144_translatePrivsBitmap(bitmap largeint) returns (bitmap_string char (20))
language c parameter style sql external name 'translateBitmap'
library t144_l1
deterministic no sql final call allow any parallelism state area size 1024 ;
grant execute on function t144_translatePrivsBitmap to "PUBLIC";

-- create library and functions for the test
create library t144_l2 file $$QUOTE$$ $$REGRRUNDIR$$/$$DLL_UDF$$ $$QUOTE$$ ;

drop function if exists gen_phone;
create function gen_phone(seedValue int, areaCode char (4)) returns (results char (14))
language c parameter style sql external name 'genPhoneNumber'
library t144_l2
deterministic no sql final call allow any parallelism state area size 1024 ;

drop function if exists gen_random;
create function gen_random(seedValue int, numberDigits int) returns (results char (14))
language c parameter style sql external name 'genRandomNumber'
library t144_l2
deterministic no sql final call allow any parallelism state area size 1024 ;

drop function if exists gen_time;
create function gen_time(seedValue largeint, numberDays int, startTime largeint) 
returns (timestamp_value largeint)
language c parameter style sql external name 'genTimestamp'
library t144_l2
deterministic no sql final call allow any parallelism state area size 1024 ;

create table if not exists customers
   (tenant_id largeint default 1,
    customer_id largeint generated by default as identity,
    customer_name varchar(100) default null,
    customer_areacode char(4) default null,
    customer_phone_number char(10) default null,
    customer_time_created timestamp default current_timestamp,
    customer_time_updated timestamp default current_timestamp,
    primary key (customer_id)
);

insert into customers (customer_name, customer_areacode) values 
   ('Tommy', '510'), ('Sammy', '408'), ('Billy', '610'), ('Joey', '619');

select customer_id, customer_areacode from customers;
grant select, insert on customers to "PUBLIC";
showddl customers;
create role t144role1;

grant update, usage on library t144_l1 to sql_user2;
grant usage on library t144_l2 to t144role1;

revoke component privilege "SHOW" on sql_operations from "PUBLIC";
get privileges on component sql_operations for "PUBLIC";

?section set_up
prepare get_privs from
select distinct
   trim(substring (o.object_name,1,15)) as object_name,
   grantor_id, grantee_id,
   t144user1.t144_translatePrivsBitmap(privileges_bitmap) as granted_privs,
   t144user1.t144_translatePrivsBitmap(grantable_bitmap) as grantable_privs
from "_PRIVMGR_MD_".object_privileges p, "_MD_".objects o 
where p.object_uid in 
  (select object_uid
   from "_MD_".objects
     where schema_name like 'T144USER%'
       and object_name not like 'SB_%')
  and p.object_uid = o.object_uid
order by 1, 2, 3, 4
;

?section test_grants
-- =================================================================
-- this set of tests run basic grant tests for udrs
-- =================================================================
set schema t144user1;
execute get_privs;
get privileges on function gen_phone;
get privileges on function gen_random;
get privileges on function gen_time;
get privileges on procedure "_LIBMGR_".help;
get privileges on table_mapping function "_LIBMGR_".event_log_reader;

-- user1 can execute udfs (as owner), but cannot execute procedure help,
-- can execute table_mapping function event_log_reader through public grant
get privileges on function gen_phone for sql_user1;
get privileges on procedure "_LIBMGR_".help for sql_user1;
get privileges on table_mapping function "_LIBMGR_".event_log_reader for sql_user1;

get functions for user sql_user1;
get table_mapping functions for user sql_user1;
get procedures for user sql_user1;

get libraries for user sql_user2;
get libraries for user sql_user3;
get libraries for user sql_user4;
get libraries for role t144role1;

sh sqlci -i "TEST144(cmds_user1)" -u sql_user1;

-- no other user or role has privileges
sh sqlci -i "TEST144(cmds_user2)" -u sql_user2;
sh sqlci -i "TEST144(cmds_user3)" -u sql_user3;
sh sqlci -i "TEST144(cmds_user4)" -u sql_user4;

-- grant user2 execute
grant execute on function gen_phone to sql_user2 with grant option;
grant execute on function gen_random to sql_user2 with grant option;
grant execute on function gen_time to sql_user2;
grant execute on procedure "_LIBMGR_".help to sql_user2 with grant option;
get privileges on function gen_phone for sql_user2;
get privileges on function gen_random for sql_user2;
get privileges on function gen_time for user sql_user2;
get privileges on procedure "_LIBMGR_".help for user sql_user2;
get functions for user sql_user2;
get table_mapping functions for user sql_user2;
get procedures for user sql_user2;
execute get_privs;

-- user2 can execute
sh sqlci -i "TEST144(cmds_user2)" -u sql_user2;

-- user3 still cannot execute
sh sqlci -i "TEST144(cmds_user3)" -u sql_user3;

-- grant user3 by user2
grant execute on function gen_phone to sql_user3 with grant option by sql_user2;
grant execute on function gen_random to sql_user3 by sql_user2;
grant execute on procedure "_LIBMGR_".help to sql_user3 by sql_user2;

-- user2 does not have WGO on gen_time
grant execute on function gen_time to sql_user3 by sql_user2;
get privileges on function gen_phone for user sql_user3;
get privileges on function gen_random for user sql_user3;
get privileges on function gen_time for user sql_user3;
get privileges on procedure "_LIBMGR_".help for user sql_user3;
get functions for user sql_user3;
get table_mapping functions for user sql_user3;
get procedures for user sql_user3;
execute get_privs;

-- user 3 can execute gen_phone, gen_random, and help but not gen_time
sh sqlci -i "TEST144(cmds_user3)" -u sql_user3;
grant execute on function gen_phone to sql_user5 by sql_user3;
grant execute on procedure "_LIBMGR_".help to sql_user5 by sql_user3;
 
-- test execute privilege with roles
grant execute on function gen_random to t144role1;
grant execute on function gen_time to t144role1;
grant execute on procedure "_LIBMGR_".help to t144role1;
get functions for role t144role1;
get table_mapping functions for role t144role1;
get procedures for role t144role1;
grant role t144role1 to sql_user4;

get privileges on function gen_phone for user sql_user4;
get privileges on function gen_random for user sql_user4;
get privileges on function gen_time for user sql_user4;
get privileges on procedure "_LIBMGR_".help for user sql_user4;
get privileges on function gen_random for t144role1;
get functions for user sql_user4;
get table_mapping functions for user sql_user4;
get procedures for user sql_user4;
get libraries for user sql_user4;
execute get_privs;

-- user4 can execute through role t144role1
sh sqlci -i "TEST144(cmds_user4)" -u sql_user4;

get privileges on function gen_phone;
get privileges on function gen_random;
get privileges on function gen_time;
get privileges on procedure "_LIBMGR_".help;
?section test_revokes
-- ============================================================================
-- verify that revoking privileges handle EXECUTE privilege correctly
-- ============================================================================
set schema t144user1;
execute get_privs;

revoke usage on library t144_l2 from t144role1;

revoke grant option for execute on function gen_phone from sql_user3 by sql_user2;
revoke execute on function gen_phone from sql_user5 by sql_user3;
revoke grant option for execute on function gen_phone from sql_user3 by sql_user2;
revoke grant option for execute on procedure "_LIBMGR_".help from sql_user3 by sql_user2;

drop role t144role1;
revoke role t144role1 from sql_user4;
drop role t144role1;
revoke execute on function gen_random from t144role1;
revoke execute on function gen_time from t144role1;
revoke execute on procedure "_LIBMGR_".help from t144role1;
drop role t144role1;

revoke execute on function gen_phone from sql_user3 by sql_user2;
revoke execute on function gen_random from sql_user3 by sql_user2;
revoke execute on procedure "_LIBMGR_".help from sql_user3 by sql_user2;

revoke execute on function gen_phone from sql_user2;
revoke execute on function gen_random from sql_user2;
revoke execute on function gen_time from sql_user2;
revoke execute on procedure "_LIBMGR_".help from sql_user2;
obey TEST144(set_up);
execute get_privs;

revoke execute on function t144_translatePrivsBitmap from "PUBLIC";
execute get_privs;
sh sqlci -i "TEST144(cmds_user2)" -u sql_user2;

?section cmds_user1
-- ============================================================================
-- verify user1 privs
-- ============================================================================
log LOG144;
values (user);
set schema t144user1;
get functions for user sql_user1;
get table_mapping functions for user sql_user1;
get procedures for user sql_user1;
get functions for user sql_user2;
obey TEST144(cmds);

?section cmds_user2
-- ============================================================================
-- verify user1 privs
-- ============================================================================
log LOG144;
values (user);
set schema t144user1;
get functions for user sql_user2;
get table_mapping functions for user sql_user2;
get procedures for user sql_user2;
get libraries for user sql_user2;

-- no privs
get table_mapping functions for user sql_user1;
get libraries for user sql_user3;
get libraries for role t144role1;

obey TEST144(cmds);

?section cmds_user3
-- ============================================================================
-- verify user1 privs
-- ============================================================================
log LOG144;
values (user);
set schema t144user1;
get functions for user sql_user3;
get table_mapping functions for user sql_user3;
get procedures for user sql_user3;
get procedures for user sql_user1;
obey TEST144(cmds);

?section cmds_user4
-- ============================================================================
-- verify user1 privs
-- ============================================================================
log LOG144;
values (user);
set schema t144user1;
get functions for user sql_user4;
get table_mapping functions for user sql_user4;
get procedures for user sql_user4;
get libraries for user sql_user4;
get libraries for role t144role1;

obey TEST144(cmds);

?section cmds
-- ============================================================================
-- execute functions
-- ============================================================================
-- should return privileges only for users that have execute privilege
get privileges on function gen_time;
-- should return no rows for users other than sql_user1
get privileges on function gen_random for sql_user1;
select customer_id, 
       'PHONE: ' || gen_phone(customer_id, customer_areacode) as phone,
       customer_areacode 
from customers;
select customer_id, 
       'NUMBER: ' || gen_random(customer_id, 10) as tenant_id
from customers;
select customer_name, 
       'TIME: ' || cast (gen_time(customer_id, 5, 212342970132970472) as char(30)) as customer_time_updated
from customers;
select customer_id,
       'NUMBER: ' || gen_random(customer_id, 10) as tenant_id,
       'PHONE: ' || gen_phone(customer_id, customer_areacode) as phone,
       customer_areacode 
from customers;

set param ?proc 'rm';
call "_LIBMGR_".help (?proc);