| >>obey TEST144(create_db); |
| >>create schema t144user1 authorization sql_user1; |
| |
| --- SQL operation complete. |
| >>set schema t144user1; |
| |
| --- SQL operation complete. |
| >> |
| >>-- 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$$ ; |
| |
| --- SQL operation complete. |
| >>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 ; |
| |
| --- SQL operation complete. |
| >>grant execute on function t144_translatePrivsBitmap to "PUBLIC"; |
| |
| --- SQL operation complete. |
| >> |
| >>-- create library and functions for the test |
| >>create library t144_l2 file $$QUOTE$$ $$REGRRUNDIR$$/$$DLL_UDF$$ $$QUOTE$$ ; |
| |
| --- SQL operation complete. |
| >> |
| >>drop function if exists gen_phone; |
| |
| --- SQL operation complete. |
| >>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 ; |
| |
| --- SQL operation complete. |
| >> |
| >>drop function if exists gen_random; |
| |
| --- SQL operation complete. |
| >>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 ; |
| |
| --- SQL operation complete. |
| >> |
| >>drop function if exists gen_time; |
| |
| --- SQL operation complete. |
| >>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 ; |
| |
| --- SQL operation complete. |
| >> |
| >>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) |
| +>); |
| |
| --- SQL operation complete. |
| >> |
| >>insert into customers (customer_name, customer_areacode) values |
| +> ('Tommy', '510'), ('Sammy', '408'), ('Billy', '610'), ('Joey', '619'); |
| |
| --- 4 row(s) inserted. |
| >> |
| >>select customer_id, customer_areacode from customers; |
| |
| CUSTOMER_ID CUSTOMER_AREACODE |
| -------------------- ----------------- |
| |
| 1 510 |
| 2 408 |
| 3 610 |
| 4 619 |
| |
| --- 4 row(s) selected. |
| >>grant select, insert on customers to "PUBLIC"; |
| |
| --- SQL operation complete. |
| >>showddl customers; |
| |
| CREATE TABLE TRAFODION.T144USER1.CUSTOMERS |
| ( |
| TENANT_ID LARGEINT DEFAULT 1 |
| , CUSTOMER_ID LARGEINT GENERATED BY DEFAULT AS IDENTITY |
| ( START WITH 1 INCREMENT BY 1 MAXVALUE 9223372036854775806 MINVALUE 1 |
| CACHE 25 NO CYCLE LARGEINT ) NOT NULL NOT DROPPABLE |
| , CUSTOMER_NAME VARCHAR(100) CHARACTER SET ISO88591 |
| COLLATE DEFAULT DEFAULT NULL |
| , CUSTOMER_AREACODE CHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT DEFAULT NULL |
| , CUSTOMER_PHONE_NUMBER CHAR(10) CHARACTER SET ISO88591 COLLATE |
| DEFAULT DEFAULT NULL |
| , CUSTOMER_TIME_CREATED TIMESTAMP(6) DEFAULT CURRENT |
| , CUSTOMER_TIME_UPDATED TIMESTAMP(6) DEFAULT CURRENT |
| , PRIMARY KEY (CUSTOMER_ID ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T144USER1.CUSTOMERS TO SQL_USER1 WITH GRANT OPTION; |
| GRANT SELECT, INSERT ON TRAFODION.T144USER1.CUSTOMERS TO PUBLIC; |
| |
| --- SQL operation complete. |
| >>create role t144role1; |
| |
| --- SQL operation complete. |
| >> |
| >>revoke component privilege "SHOW" on sql_operations from "PUBLIC"; |
| |
| --- SQL operation complete. |
| >>get privileges on component sql_operations for "PUBLIC"; |
| |
| Privilege information on Component SQL_OPERATIONS for PUBLIC |
| ============================================================ |
| |
| CREATE_SCHEMA |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST144(set_up); |
| >>set schema "_PRIVMGR_MD_"; |
| |
| --- SQL operation complete. |
| >>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 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 |
| +>; |
| |
| --- SQL command prepared. |
| >> |
| >>obey TEST144(test_grants); |
| >>-- ================================================================= |
| >>-- this set of tests run basic grant tests for udrs |
| >>-- ================================================================= |
| >>set schema t144user1; |
| |
| --- SQL operation complete. |
| >>execute get_privs; |
| |
| OBJECT_NAME GRANTOR_ID GRANTEE_ID GRANTED_PRIVS GRANTABLE_PRIVS |
| ------------------------------------------------------------ -------------------- -------------------- -------------------- -------------------- |
| |
| CUSTOMERS -2 33334 SIDU-R- SIDU-R- |
| CUSTOMERS 33334 -1 SI----- NONE |
| GEN_PHONE -2 33334 ------E ------E |
| GEN_RANDOM -2 33334 ------E ------E |
| GEN_TIME -2 33334 ------E ------E |
| T144_L1 -2 33334 ---UG-- ---UG-- |
| T144_L2 -2 33334 ---UG-- ---UG-- |
| T144_TRANSLATEP -2 33334 ------E ------E |
| T144_TRANSLATEP 33334 -1 ------E NONE |
| _TRAFODION_T144 -2 33334 ----G-- ----G-- |
| |
| --- 10 row(s) selected. |
| >>get privileges on function gen_phone; |
| |
| Privileges on Routine T144USER1.GEN_PHONE |
| ========================================= |
| |
| ------E SQL_USER1 |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >>get privileges on function gen_random; |
| |
| Privileges on Routine T144USER1.GEN_RANDOM |
| ========================================== |
| |
| ------E SQL_USER1 |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >>get privileges on function gen_time; |
| |
| Privileges on Routine T144USER1.GEN_TIME |
| ======================================== |
| |
| ------E SQL_USER1 |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >>get privileges on procedure "_LIBMGR_".help; |
| |
| Privileges on Routine _LIBMGR_.HELP |
| =================================== |
| |
| ------E DB__LIBMGRROLE |
| ------E DB__ROOT |
| |
| ======================= |
| 2 row(s) returned |
| |
| --- SQL operation complete. |
| >>get privileges on table_mapping function "_LIBMGR_".event_log_reader; |
| |
| Privileges on Routine _LIBMGR_.EVENT_LOG_READER |
| =============================================== |
| |
| ------E DB__ROOT |
| ------E PUBLIC |
| |
| ======================= |
| 2 row(s) returned |
| |
| --- SQL operation complete. |
| >> |
| >>-- 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; |
| |
| Privileges on Routine T144USER1.GEN_PHONE |
| ========================================= |
| |
| ------E SQL_USER1 |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >>get privileges on procedure "_LIBMGR_".help for sql_user1; |
| |
| --- SQL operation complete. |
| >>get privileges on table_mapping function "_LIBMGR_".event_log_reader for sql_user1; |
| |
| Privileges on Routine _LIBMGR_.EVENT_LOG_READER |
| =============================================== |
| |
| ------E PUBLIC |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >>sh sqlci -i "TEST144(cmds)" -u sql_user1; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER1 |
| |
| --- 1 row(s) selected. |
| >>set schema t144user1; |
| |
| --- SQL operation complete. |
| >>-- should return privileges only for users that have execute privilege |
| >>get privileges on function gen_time; |
| |
| Privileges on Routine T144USER1.GEN_TIME |
| ======================================== |
| |
| ------E SQL_USER1 |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >>-- should return no rows for users other than sql_user1 |
| >>get privileges on function gen_random for sql_user1; |
| |
| Privileges on Routine T144USER1.GEN_RANDOM |
| ========================================== |
| |
| ------E SQL_USER1 |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >>select customer_id, |
| +> 'PHONE: ' || gen_phone(customer_id, customer_areacode) as phone, |
| +> customer_areacode |
| +>from customers; |
| |
| CUSTOMER_ID PHONE CUSTOMER_AREACODE |
| -------------------- --------------------- ----------------- |
| |
| 1 PHONE: 510 3675356� 510 |
| 2 PHONE: 408 1985184� 408 |
| 3 PHONE: 610 6580502� 610 |
| 4 PHONE: 619 1346375� 619 |
| |
| --- 4 row(s) selected. |
| >>select customer_id, |
| +> 'NUMBER: ' || gen_random(customer_id, 10) as tenant_id |
| +>from customers; |
| |
| CUSTOMER_ID TENANT_ID |
| -------------------- ---------------------- |
| |
| 1 NUMBER: 3854444342� |
| 2 NUMBER: 1197489382� |
| 3 NUMBER: 2000346994� |
| 4 NUMBER: 4321965412� |
| |
| --- 4 row(s) selected. |
| >>select customer_name, |
| +> 'TIME: ' || cast (gen_time(customer_id, 5, 212342970132970472) as char(30)) as customer_time_updated |
| +>from customers; |
| |
| CUSTOMER_NAME CUSTOMER_TIME_UPDATED |
| ---------------------------------------------------------------------------------------------------- ------------------------------------ |
| |
| Tommy TIME: 212348150390970472 |
| Sammy TIME: 212345415070970472 |
| Billy TIME: 212344531815970472 |
| Joey TIME: 212344826188970472 |
| |
| --- 4 row(s) selected. |
| >>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; |
| |
| CUSTOMER_ID TENANT_ID PHONE CUSTOMER_AREACODE |
| -------------------- ---------------------- --------------------- ----------------- |
| |
| 1 NUMBER: 3854444342� PHONE: 510 3675356� 510 |
| 2 NUMBER: 1197489382� PHONE: 408 1985184� 408 |
| 3 NUMBER: 2000346994� PHONE: 610 6580502� 610 |
| 4 NUMBER: 4321965412� PHONE: 619 1346375� 619 |
| |
| --- 4 row(s) selected. |
| >> |
| >>set param ?proc 'rm'; |
| >>call "_LIBMGR_".help (?proc); |
| |
| *** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION."_LIBMGR_".HELP. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- no other user or role has privileges |
| >>sh sqlci -i "TEST144(cmds)" -u sql_user2; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER2 |
| |
| --- 1 row(s) selected. |
| >>set schema t144user1; |
| |
| --- SQL operation complete. |
| >>-- should return privileges only for users that have execute privilege |
| >>get privileges on function gen_time; |
| |
| --- SQL operation complete. |
| >>-- should return no rows for users other than sql_user1 |
| >>get privileges on function gen_random for sql_user1; |
| |
| --- SQL operation complete. |
| >>select customer_id, |
| +> 'PHONE: ' || gen_phone(customer_id, customer_areacode) as phone, |
| +> customer_areacode |
| +>from customers; |
| |
| *** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION.T144USER1.GEN_PHONE. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>select customer_id, |
| +> 'NUMBER: ' || gen_random(customer_id, 10) as tenant_id |
| +>from customers; |
| |
| *** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION.T144USER1.GEN_RANDOM. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>select customer_name, |
| +> 'TIME: ' || cast (gen_time(customer_id, 5, 212342970132970472) as char(30)) as customer_time_updated |
| +>from customers; |
| |
| *** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION.T144USER1.GEN_TIME. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>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; |
| |
| *** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION.T144USER1.GEN_RANDOM. |
| |
| *** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION.T144USER1.GEN_PHONE. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>set param ?proc 'rm'; |
| >>call "_LIBMGR_".help (?proc); |
| |
| *** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION."_LIBMGR_".HELP. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>exit; |
| |
| End of MXCI Session |
| |
| >>sh sqlci -i "TEST144(cmds)" -u sql_user3; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER3 |
| |
| --- 1 row(s) selected. |
| >>set schema t144user1; |
| |
| --- SQL operation complete. |
| >>-- should return privileges only for users that have execute privilege |
| >>get privileges on function gen_time; |
| |
| --- SQL operation complete. |
| >>-- should return no rows for users other than sql_user1 |
| >>get privileges on function gen_random for sql_user1; |
| |
| --- SQL operation complete. |
| >>select customer_id, |
| +> 'PHONE: ' || gen_phone(customer_id, customer_areacode) as phone, |
| +> customer_areacode |
| +>from customers; |
| |
| *** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION.T144USER1.GEN_PHONE. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>select customer_id, |
| +> 'NUMBER: ' || gen_random(customer_id, 10) as tenant_id |
| +>from customers; |
| |
| *** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION.T144USER1.GEN_RANDOM. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>select customer_name, |
| +> 'TIME: ' || cast (gen_time(customer_id, 5, 212342970132970472) as char(30)) as customer_time_updated |
| +>from customers; |
| |
| *** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION.T144USER1.GEN_TIME. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>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; |
| |
| *** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION.T144USER1.GEN_RANDOM. |
| |
| *** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION.T144USER1.GEN_PHONE. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>set param ?proc 'rm'; |
| >>call "_LIBMGR_".help (?proc); |
| |
| *** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION."_LIBMGR_".HELP. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>exit; |
| |
| End of MXCI Session |
| |
| >>sh sqlci -i "TEST144(cmds)" -u sql_user4; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER4 |
| |
| --- 1 row(s) selected. |
| >>set schema t144user1; |
| |
| --- SQL operation complete. |
| >>-- should return privileges only for users that have execute privilege |
| >>get privileges on function gen_time; |
| |
| --- SQL operation complete. |
| >>-- should return no rows for users other than sql_user1 |
| >>get privileges on function gen_random for sql_user1; |
| |
| --- SQL operation complete. |
| >>select customer_id, |
| +> 'PHONE: ' || gen_phone(customer_id, customer_areacode) as phone, |
| +> customer_areacode |
| +>from customers; |
| |
| *** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION.T144USER1.GEN_PHONE. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>select customer_id, |
| +> 'NUMBER: ' || gen_random(customer_id, 10) as tenant_id |
| +>from customers; |
| |
| *** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION.T144USER1.GEN_RANDOM. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>select customer_name, |
| +> 'TIME: ' || cast (gen_time(customer_id, 5, 212342970132970472) as char(30)) as customer_time_updated |
| +>from customers; |
| |
| *** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION.T144USER1.GEN_TIME. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>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; |
| |
| *** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION.T144USER1.GEN_RANDOM. |
| |
| *** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION.T144USER1.GEN_PHONE. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>set param ?proc 'rm'; |
| >>call "_LIBMGR_".help (?proc); |
| |
| *** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION."_LIBMGR_".HELP. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- grant user2 execute |
| >>grant execute on function gen_phone to sql_user2 with grant option; |
| |
| --- SQL operation complete. |
| >>grant execute on function gen_random to sql_user2 with grant option; |
| |
| --- SQL operation complete. |
| >>grant execute on function gen_time to sql_user2; |
| |
| --- SQL operation complete. |
| >>grant execute on procedure "_LIBMGR_".help to sql_user2 with grant option; |
| |
| --- SQL operation complete. |
| >>get privileges on function gen_phone for sql_user2; |
| |
| Privileges on Routine T144USER1.GEN_PHONE |
| ========================================= |
| |
| ------E SQL_USER2 |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >>get privileges on function gen_random for sql_user2; |
| |
| Privileges on Routine T144USER1.GEN_RANDOM |
| ========================================== |
| |
| ------E SQL_USER2 |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >>get privileges on function gen_time for user sql_user2; |
| |
| Privileges on Routine T144USER1.GEN_TIME |
| ======================================== |
| |
| ------E SQL_USER2 |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >>get privileges on procedure "_LIBMGR_".help for user sql_user2; |
| |
| Privileges on Routine _LIBMGR_.HELP |
| =================================== |
| |
| ------E SQL_USER2 |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >>execute get_privs; |
| |
| OBJECT_NAME GRANTOR_ID GRANTEE_ID GRANTED_PRIVS GRANTABLE_PRIVS |
| ------------------------------------------------------------ -------------------- -------------------- -------------------- -------------------- |
| |
| CUSTOMERS -2 33334 SIDU-R- SIDU-R- |
| CUSTOMERS 33334 -1 SI----- NONE |
| GEN_PHONE -2 33334 ------E ------E |
| GEN_PHONE 33334 33335 ------E ------E |
| GEN_RANDOM -2 33334 ------E ------E |
| GEN_RANDOM 33334 33335 ------E ------E |
| GEN_TIME -2 33334 ------E ------E |
| GEN_TIME 33334 33335 ------E NONE |
| T144_L1 -2 33334 ---UG-- ---UG-- |
| T144_L2 -2 33334 ---UG-- ---UG-- |
| T144_TRANSLATEP -2 33334 ------E ------E |
| T144_TRANSLATEP 33334 -1 ------E NONE |
| _TRAFODION_T144 -2 33334 ----G-- ----G-- |
| |
| --- 13 row(s) selected. |
| >> |
| >> |
| >>-- user2 can execute |
| >>sh sqlci -i "TEST144(cmds)" -u sql_user2; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER2 |
| |
| --- 1 row(s) selected. |
| >>set schema t144user1; |
| |
| --- SQL operation complete. |
| >>-- should return privileges only for users that have execute privilege |
| >>get privileges on function gen_time; |
| |
| Privileges on Routine T144USER1.GEN_TIME |
| ======================================== |
| |
| ------E SQL_USER2 |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >>-- should return no rows for users other than sql_user1 |
| >>get privileges on function gen_random for sql_user1; |
| |
| --- SQL operation complete. |
| >>select customer_id, |
| +> 'PHONE: ' || gen_phone(customer_id, customer_areacode) as phone, |
| +> customer_areacode |
| +>from customers; |
| |
| CUSTOMER_ID PHONE CUSTOMER_AREACODE |
| -------------------- --------------------- ----------------- |
| |
| 1 PHONE: 510 3675356� 510 |
| 2 PHONE: 408 1985184� 408 |
| 3 PHONE: 610 6580502� 610 |
| 4 PHONE: 619 1346375� 619 |
| |
| --- 4 row(s) selected. |
| >>select customer_id, |
| +> 'NUMBER: ' || gen_random(customer_id, 10) as tenant_id |
| +>from customers; |
| |
| CUSTOMER_ID TENANT_ID |
| -------------------- ---------------------- |
| |
| 1 NUMBER: 3854444342� |
| 2 NUMBER: 1197489382� |
| 3 NUMBER: 2000346994� |
| 4 NUMBER: 4321965412� |
| |
| --- 4 row(s) selected. |
| >>select customer_name, |
| +> 'TIME: ' || cast (gen_time(customer_id, 5, 212342970132970472) as char(30)) as customer_time_updated |
| +>from customers; |
| |
| CUSTOMER_NAME CUSTOMER_TIME_UPDATED |
| ---------------------------------------------------------------------------------------------------- ------------------------------------ |
| |
| Tommy TIME: 212348150390970472 |
| Sammy TIME: 212345415070970472 |
| Billy TIME: 212344531815970472 |
| Joey TIME: 212344826188970472 |
| |
| --- 4 row(s) selected. |
| >>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; |
| |
| CUSTOMER_ID TENANT_ID PHONE CUSTOMER_AREACODE |
| -------------------- ---------------------- --------------------- ----------------- |
| |
| 1 NUMBER: 3854444342� PHONE: 510 3675356� 510 |
| 2 NUMBER: 1197489382� PHONE: 408 1985184� 408 |
| 3 NUMBER: 2000346994� PHONE: 610 6580502� 610 |
| 4 NUMBER: 4321965412� PHONE: 619 1346375� 619 |
| |
| --- 4 row(s) selected. |
| >> |
| >>set param ?proc 'rm'; |
| >>call "_LIBMGR_".help (?proc); |
| |
| COMMANDNAME |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| |
| RM - Remove a library file. SHOWDDL PROCEDURE [SCHEMA NAME.]RM for more info. |
| |
| --- SQL operation complete. |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- user3 still cannot execute |
| >>sh sqlci -i "TEST144(cmds)" -u sql_user3; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER3 |
| |
| --- 1 row(s) selected. |
| >>set schema t144user1; |
| |
| --- SQL operation complete. |
| >>-- should return privileges only for users that have execute privilege |
| >>get privileges on function gen_time; |
| |
| --- SQL operation complete. |
| >>-- should return no rows for users other than sql_user1 |
| >>get privileges on function gen_random for sql_user1; |
| |
| --- SQL operation complete. |
| >>select customer_id, |
| +> 'PHONE: ' || gen_phone(customer_id, customer_areacode) as phone, |
| +> customer_areacode |
| +>from customers; |
| |
| *** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION.T144USER1.GEN_PHONE. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>select customer_id, |
| +> 'NUMBER: ' || gen_random(customer_id, 10) as tenant_id |
| +>from customers; |
| |
| *** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION.T144USER1.GEN_RANDOM. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>select customer_name, |
| +> 'TIME: ' || cast (gen_time(customer_id, 5, 212342970132970472) as char(30)) as customer_time_updated |
| +>from customers; |
| |
| *** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION.T144USER1.GEN_TIME. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>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; |
| |
| *** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION.T144USER1.GEN_RANDOM. |
| |
| *** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION.T144USER1.GEN_PHONE. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>set param ?proc 'rm'; |
| >>call "_LIBMGR_".help (?proc); |
| |
| *** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION."_LIBMGR_".HELP. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- grant user3 by user2 |
| >>grant execute on function gen_phone to sql_user3 with grant option by sql_user2; |
| |
| --- SQL operation complete. |
| >>grant execute on function gen_random to sql_user3 by sql_user2; |
| |
| --- SQL operation complete. |
| >>grant execute on procedure "_LIBMGR_".help to sql_user3 by sql_user2; |
| |
| --- SQL operation complete. |
| >> |
| >>-- user2 does not have WGO on gen_time |
| >>grant execute on function gen_time to sql_user3 by sql_user2; |
| |
| *** ERROR[1012] No privileges were granted. SQL_USER2 lacks grant option on the specified privileges. |
| |
| --- SQL operation failed with errors. |
| >>get privileges on function gen_phone for user sql_user3; |
| |
| Privileges on Routine T144USER1.GEN_PHONE |
| ========================================= |
| |
| ------E SQL_USER3 |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >>get privileges on function gen_random for user sql_user3; |
| |
| Privileges on Routine T144USER1.GEN_RANDOM |
| ========================================== |
| |
| ------E SQL_USER3 |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >>get privileges on function gen_time for user sql_user3; |
| |
| --- SQL operation complete. |
| >>get privileges on procedure "_LIBMGR_".help for user sql_user3; |
| |
| Privileges on Routine _LIBMGR_.HELP |
| =================================== |
| |
| ------E SQL_USER3 |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >>execute get_privs; |
| |
| OBJECT_NAME GRANTOR_ID GRANTEE_ID GRANTED_PRIVS GRANTABLE_PRIVS |
| ------------------------------------------------------------ -------------------- -------------------- -------------------- -------------------- |
| |
| CUSTOMERS -2 33334 SIDU-R- SIDU-R- |
| CUSTOMERS 33334 -1 SI----- NONE |
| GEN_PHONE -2 33334 ------E ------E |
| GEN_PHONE 33334 33335 ------E ------E |
| GEN_PHONE 33335 33336 ------E ------E |
| GEN_RANDOM -2 33334 ------E ------E |
| GEN_RANDOM 33334 33335 ------E ------E |
| GEN_RANDOM 33335 33336 ------E NONE |
| GEN_TIME -2 33334 ------E ------E |
| GEN_TIME 33334 33335 ------E NONE |
| T144_L1 -2 33334 ---UG-- ---UG-- |
| T144_L2 -2 33334 ---UG-- ---UG-- |
| T144_TRANSLATEP -2 33334 ------E ------E |
| T144_TRANSLATEP 33334 -1 ------E NONE |
| _TRAFODION_T144 -2 33334 ----G-- ----G-- |
| |
| --- 15 row(s) selected. |
| >> |
| >>-- user 3 can execute gen_phone, gen_random, and help but not gen_time |
| >>sh sqlci -i "TEST144(cmds)" -u sql_user3; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER3 |
| |
| --- 1 row(s) selected. |
| >>set schema t144user1; |
| |
| --- SQL operation complete. |
| >>-- should return privileges only for users that have execute privilege |
| >>get privileges on function gen_time; |
| |
| --- SQL operation complete. |
| >>-- should return no rows for users other than sql_user1 |
| >>get privileges on function gen_random for sql_user1; |
| |
| --- SQL operation complete. |
| >>select customer_id, |
| +> 'PHONE: ' || gen_phone(customer_id, customer_areacode) as phone, |
| +> customer_areacode |
| +>from customers; |
| |
| CUSTOMER_ID PHONE CUSTOMER_AREACODE |
| -------------------- --------------------- ----------------- |
| |
| 1 PHONE: 510 3675356� 510 |
| 2 PHONE: 408 1985184� 408 |
| 3 PHONE: 610 6580502� 610 |
| 4 PHONE: 619 1346375� 619 |
| |
| --- 4 row(s) selected. |
| >>select customer_id, |
| +> 'NUMBER: ' || gen_random(customer_id, 10) as tenant_id |
| +>from customers; |
| |
| CUSTOMER_ID TENANT_ID |
| -------------------- ---------------------- |
| |
| 1 NUMBER: 3854444342� |
| 2 NUMBER: 1197489382� |
| 3 NUMBER: 2000346994� |
| 4 NUMBER: 4321965412� |
| |
| --- 4 row(s) selected. |
| >>select customer_name, |
| +> 'TIME: ' || cast (gen_time(customer_id, 5, 212342970132970472) as char(30)) as customer_time_updated |
| +>from customers; |
| |
| *** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION.T144USER1.GEN_TIME. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>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; |
| |
| CUSTOMER_ID TENANT_ID PHONE CUSTOMER_AREACODE |
| -------------------- ---------------------- --------------------- ----------------- |
| |
| 1 NUMBER: 3854444342� PHONE: 510 3675356� 510 |
| 2 NUMBER: 1197489382� PHONE: 408 1985184� 408 |
| 3 NUMBER: 2000346994� PHONE: 610 6580502� 610 |
| 4 NUMBER: 4321965412� PHONE: 619 1346375� 619 |
| |
| --- 4 row(s) selected. |
| >> |
| >>set param ?proc 'rm'; |
| >>call "_LIBMGR_".help (?proc); |
| |
| COMMANDNAME |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| |
| RM - Remove a library file. SHOWDDL PROCEDURE [SCHEMA NAME.]RM for more info. |
| |
| --- SQL operation complete. |
| >>exit; |
| |
| End of MXCI Session |
| |
| >>grant execute on function gen_phone to sql_user5 by sql_user3; |
| |
| --- SQL operation complete. |
| >>grant execute on procedure "_LIBMGR_".help to sql_user5 by sql_user3; |
| |
| *** ERROR[1012] No privileges were granted. SQL_USER3 lacks grant option on the specified privileges. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>-- test execute privilege with roles |
| >>grant execute on function gen_random to t144role1; |
| |
| --- SQL operation complete. |
| >>grant execute on function gen_time to t144role1; |
| |
| --- SQL operation complete. |
| >>grant execute on procedure "_LIBMGR_".help to t144role1; |
| |
| --- SQL operation complete. |
| >>grant role t144role1 to sql_user4; |
| |
| --- SQL operation complete. |
| >>get privileges on function gen_phone for user sql_user4; |
| |
| --- SQL operation complete. |
| >>get privileges on function gen_random for user sql_user4; |
| |
| Privileges on Routine T144USER1.GEN_RANDOM |
| ========================================== |
| |
| ------E T144ROLE1 |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >>get privileges on function gen_time for user sql_user4; |
| |
| Privileges on Routine T144USER1.GEN_TIME |
| ======================================== |
| |
| ------E T144ROLE1 |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >>get privileges on procedure "_LIBMGR_".help for user sql_user4; |
| |
| Privileges on Routine _LIBMGR_.HELP |
| =================================== |
| |
| ------E T144ROLE1 |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >>get privileges on function gen_random for t144role1; |
| |
| Privileges on Routine T144USER1.GEN_RANDOM |
| ========================================== |
| |
| ------E T144ROLE1 |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >>execute get_privs; |
| |
| OBJECT_NAME GRANTOR_ID GRANTEE_ID GRANTED_PRIVS GRANTABLE_PRIVS |
| ------------------------------------------------------------ -------------------- -------------------- -------------------- -------------------- |
| |
| CUSTOMERS -2 33334 SIDU-R- SIDU-R- |
| CUSTOMERS 33334 -1 SI----- NONE |
| GEN_PHONE -2 33334 ------E ------E |
| GEN_PHONE 33334 33335 ------E ------E |
| GEN_PHONE 33335 33336 ------E ------E |
| GEN_PHONE 33336 33338 ------E NONE |
| GEN_RANDOM -2 33334 ------E ------E |
| GEN_RANDOM 33334 33335 ------E ------E |
| GEN_RANDOM 33334 1000002 ------E NONE |
| GEN_RANDOM 33335 33336 ------E NONE |
| GEN_TIME -2 33334 ------E ------E |
| GEN_TIME 33334 33335 ------E NONE |
| GEN_TIME 33334 1000002 ------E NONE |
| T144_L1 -2 33334 ---UG-- ---UG-- |
| T144_L2 -2 33334 ---UG-- ---UG-- |
| T144_TRANSLATEP -2 33334 ------E ------E |
| T144_TRANSLATEP 33334 -1 ------E NONE |
| _TRAFODION_T144 -2 33334 ----G-- ----G-- |
| |
| --- 18 row(s) selected. |
| >> |
| >>-- user4 can execute through role t144role1 |
| >>sh sqlci -i "TEST144(cmds)" -u sql_user4; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER4 |
| |
| --- 1 row(s) selected. |
| >>set schema t144user1; |
| |
| --- SQL operation complete. |
| >>-- should return privileges only for users that have execute privilege |
| >>get privileges on function gen_time; |
| |
| Privileges on Routine T144USER1.GEN_TIME |
| ======================================== |
| |
| ------E T144ROLE1 |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >>-- should return no rows for users other than sql_user1 |
| >>get privileges on function gen_random for sql_user1; |
| |
| --- SQL operation complete. |
| >>select customer_id, |
| +> 'PHONE: ' || gen_phone(customer_id, customer_areacode) as phone, |
| +> customer_areacode |
| +>from customers; |
| |
| *** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION.T144USER1.GEN_PHONE. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>select customer_id, |
| +> 'NUMBER: ' || gen_random(customer_id, 10) as tenant_id |
| +>from customers; |
| |
| CUSTOMER_ID TENANT_ID |
| -------------------- ---------------------- |
| |
| 1 NUMBER: 3675356291� |
| 2 NUMBER: 2709360626� |
| 3 NUMBER: 1879202375� |
| 4 NUMBER: 9228973612� |
| |
| --- 4 row(s) selected. |
| >>select customer_name, |
| +> 'TIME: ' || cast (gen_time(customer_id, 5, 212342970132970472) as char(30)) as customer_time_updated |
| +>from customers; |
| |
| CUSTOMER_NAME CUSTOMER_TIME_UPDATED |
| ---------------------------------------------------------------------------------------------------- ------------------------------------ |
| |
| Tommy TIME: 212348150390970472 |
| Sammy TIME: 212345415070970472 |
| Billy TIME: 212344531815970472 |
| Joey TIME: 212344826188970472 |
| |
| --- 4 row(s) selected. |
| >>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; |
| |
| *** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION.T144USER1.GEN_PHONE. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>set param ?proc 'rm'; |
| >>call "_LIBMGR_".help (?proc); |
| |
| COMMANDNAME |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| |
| RM - Remove a library file. SHOWDDL PROCEDURE [SCHEMA NAME.]RM for more info. |
| |
| --- SQL operation complete. |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>get privileges on function gen_phone; |
| |
| Privileges on Routine T144USER1.GEN_PHONE |
| ========================================= |
| |
| ------E SQL_USER1 |
| ------E SQL_USER2 |
| ------E SQL_USER3 |
| ------E SQL_USER5 |
| |
| ======================= |
| 4 row(s) returned |
| |
| --- SQL operation complete. |
| >>get privileges on function gen_random; |
| |
| Privileges on Routine T144USER1.GEN_RANDOM |
| ========================================== |
| |
| ------E SQL_USER1 |
| ------E SQL_USER2 |
| ------E SQL_USER3 |
| ------E T144ROLE1 |
| |
| ======================= |
| 4 row(s) returned |
| |
| --- SQL operation complete. |
| >>get privileges on function gen_time; |
| |
| Privileges on Routine T144USER1.GEN_TIME |
| ======================================== |
| |
| ------E SQL_USER1 |
| ------E SQL_USER2 |
| ------E T144ROLE1 |
| |
| ======================= |
| 3 row(s) returned |
| |
| --- SQL operation complete. |
| >>get privileges on procedure "_LIBMGR_".help; |
| |
| Privileges on Routine _LIBMGR_.HELP |
| =================================== |
| |
| ------E DB__LIBMGRROLE |
| ------E DB__ROOT |
| ------E SQL_USER2 |
| ------E SQL_USER3 |
| ------E T144ROLE1 |
| |
| ======================= |
| 5 row(s) returned |
| |
| --- SQL operation complete. |
| >>obey TEST144(test_revokes); |
| >>-- ============================================================================ |
| >>-- verify that revoking privileges handle EXECUTE privilege correctly |
| >>-- ============================================================================ |
| >>set schema t144user1; |
| |
| --- SQL operation complete. |
| >>execute get_privs; |
| |
| OBJECT_NAME GRANTOR_ID GRANTEE_ID GRANTED_PRIVS GRANTABLE_PRIVS |
| ------------------------------------------------------------ -------------------- -------------------- -------------------- -------------------- |
| |
| CUSTOMERS -2 33334 SIDU-R- SIDU-R- |
| CUSTOMERS 33334 -1 SI----- NONE |
| GEN_PHONE -2 33334 ------E ------E |
| GEN_PHONE 33334 33335 ------E ------E |
| GEN_PHONE 33335 33336 ------E ------E |
| GEN_PHONE 33336 33338 ------E NONE |
| GEN_RANDOM -2 33334 ------E ------E |
| GEN_RANDOM 33334 33335 ------E ------E |
| GEN_RANDOM 33334 1000002 ------E NONE |
| GEN_RANDOM 33335 33336 ------E NONE |
| GEN_TIME -2 33334 ------E ------E |
| GEN_TIME 33334 33335 ------E NONE |
| GEN_TIME 33334 1000002 ------E NONE |
| T144_L1 -2 33334 ---UG-- ---UG-- |
| T144_L2 -2 33334 ---UG-- ---UG-- |
| T144_TRANSLATEP -2 33334 ------E ------E |
| T144_TRANSLATEP 33334 -1 ------E NONE |
| _TRAFODION_T144 -2 33334 ----G-- ----G-- |
| |
| --- 18 row(s) selected. |
| >> |
| >>revoke grant option for execute on function gen_phone from sql_user3 by sql_user2; |
| |
| *** ERROR[1037] Revoke failed because of a dependent grant between authorization ID SQL_USER3 and authorization ID SQL_USER5. |
| |
| --- SQL operation failed with errors. |
| >>revoke execute on function gen_phone from sql_user5 by sql_user3; |
| |
| --- SQL operation complete. |
| >>revoke grant option for execute on function gen_phone from sql_user3 by sql_user2; |
| |
| --- SQL operation complete. |
| >>revoke grant option for execute on procedure "_LIBMGR_".help from sql_user3 by sql_user2; |
| |
| --- SQL operation complete. |
| >> |
| >>drop role t144role1; |
| |
| *** ERROR[1348] Cannot drop role. Role is granted to one or more users. |
| |
| --- SQL operation failed with errors. |
| >>revoke role t144role1 from sql_user4; |
| |
| --- SQL operation complete. |
| >>drop role t144role1; |
| |
| *** ERROR[1228] Cannot drop role. Role T144ROLE1 has been granted privileges on TRAFODION._LIBMGR_.HELP. |
| |
| --- SQL operation failed with errors. |
| >>revoke execute on function gen_random from t144role1; |
| |
| --- SQL operation complete. |
| >>revoke execute on function gen_time from t144role1; |
| |
| --- SQL operation complete. |
| >>revoke execute on procedure "_LIBMGR_".help from t144role1; |
| |
| --- SQL operation complete. |
| >>drop role t144role1; |
| |
| --- SQL operation complete. |
| >> |
| >>revoke execute on function gen_phone from sql_user3 by sql_user2; |
| |
| --- SQL operation complete. |
| >>revoke execute on function gen_random from sql_user3 by sql_user2; |
| |
| --- SQL operation complete. |
| >>revoke execute on procedure "_LIBMGR_".help from sql_user3 by sql_user2; |
| |
| --- SQL operation complete. |
| >> |
| >>revoke execute on function gen_phone from sql_user2; |
| |
| --- SQL operation complete. |
| >>revoke execute on function gen_random from sql_user2; |
| |
| --- SQL operation complete. |
| >>revoke execute on function gen_time from sql_user2; |
| |
| --- SQL operation complete. |
| >>revoke execute on procedure "_LIBMGR_".help from sql_user2; |
| |
| --- SQL operation complete. |
| >>obey TEST144(set_up); |
| >>set schema "_PRIVMGR_MD_"; |
| |
| --- SQL operation complete. |
| >>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 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 |
| +>; |
| |
| --- SQL command prepared. |
| >> |
| >>execute get_privs; |
| |
| OBJECT_NAME GRANTOR_ID GRANTEE_ID GRANTED_PRIVS GRANTABLE_PRIVS |
| ------------------------------------------------------------ -------------------- -------------------- -------------------- -------------------- |
| |
| CUSTOMERS -2 33334 SIDU-R- SIDU-R- |
| CUSTOMERS 33334 -1 SI----- NONE |
| GEN_PHONE -2 33334 ------E ------E |
| GEN_RANDOM -2 33334 ------E ------E |
| GEN_TIME -2 33334 ------E ------E |
| T144_L1 -2 33334 ---UG-- ---UG-- |
| T144_L2 -2 33334 ---UG-- ---UG-- |
| T144_TRANSLATEP -2 33334 ------E ------E |
| T144_TRANSLATEP 33334 -1 ------E NONE |
| _TRAFODION_T144 -2 33334 ----G-- ----G-- |
| |
| --- 10 row(s) selected. |
| >> |
| >> |
| >>log; |
