Repository: trafodion Updated Branches: refs/heads/master fb269de14 -> 1fd065151
http://git-wip-us.apache.org/repos/asf/trafodion/blob/afff9935/core/sql/regress/privs2/EXPECTED144 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs2/EXPECTED144 b/core/sql/regress/privs2/EXPECTED144 index f3127e0..2c225b4 100644 --- a/core/sql/regress/privs2/EXPECTED144 +++ b/core/sql/regress/privs2/EXPECTED144 @@ -130,6 +130,13 @@ CREATE TABLE TRAFODION.T144USER1.CUSTOMERS --- SQL operation complete. >> +>>grant update, usage on library t144_l1 to sql_user2; + +--- SQL operation complete. +>>grant usage on library t144_l2 to t144role1; + +--- SQL operation complete. +>> >>revoke component privilege "SHOW" on sql_operations from "PUBLIC"; --- SQL operation complete. @@ -146,16 +153,13 @@ CREATE_SCHEMA --- 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 ++>from "_PRIVMGR_MD_".object_privileges p, "_MD_".objects o +>where p.object_uid in +> (select object_uid +> from "_MD_".objects @@ -185,12 +189,14 @@ GEN_PHONE GEN_RANDOM -2 33334 ------E ------E GEN_TIME -2 33334 ------E ------E T144_L1 -2 33334 ---UG-- ---UG-- +T144_L1 33334 33335 ---UG-- NONE T144_L2 -2 33334 ---UG-- ---UG-- +T144_L2 33334 1000002 ----G-- NONE T144_TRANSLATEP -2 33334 ------E ------E T144_TRANSLATEP 33334 -1 ------E NONE _TRAFODION_T144 -2 33334 ----G-- ----G-- ---- 10 row(s) selected. +--- 12 row(s) selected. >>get privileges on function gen_phone; Privileges on Routine T144USER1.GEN_PHONE @@ -276,7 +282,67 @@ Privileges on Routine _LIBMGR_.EVENT_LOG_READER 1 row(s) returned --- SQL operation complete. ->>sh sqlci -i "TEST144(cmds)" -u sql_user1; +>> +>>get functions for user sql_user1; + +Functions for User SQL_USER1 +============================ + +TRAFODION."T144USER1".GEN_PHONE +TRAFODION."T144USER1".GEN_RANDOM +TRAFODION."T144USER1".GEN_TIME +TRAFODION."T144USER1".T144_TRANSLATEPRIVSBITMAP + +======================= + 4 row(s) returned + +--- SQL operation complete. +>>get table_mapping functions for user sql_user1; + +Table mapping functions for User SQL_USER1 +========================================== + +TRAFODION."_LIBMGR_".EVENT_LOG_READER +TRAFODION."_LIBMGR_".JDBC + +======================= + 2 row(s) returned + +--- SQL operation complete. +>>get procedures for user sql_user1; + +--- SQL operation complete. +>> +>>get libraries for user sql_user2; + +Libraries for User SQL_USER2 +============================ + +TRAFODION."T144USER1".T144_L1 + +======================= + 1 row(s) returned + +--- SQL operation complete. +>>get libraries for user sql_user3; + +--- SQL operation complete. +>>get libraries for user sql_user4; + +--- SQL operation complete. +>>get libraries for role t144role1; + +Libraries for Role T144ROLE1 +============================ + +TRAFODION."T144USER1".T144_L2 + +======================= + 1 row(s) returned + +--- SQL operation complete. +>> +>>sh sqlci -i "TEST144(cmds_user1)" -u sql_user1; >>values (user); (EXPR) @@ -288,6 +354,44 @@ SQL_USER1 >>set schema t144user1; --- SQL operation complete. +>>get functions for user sql_user1; + +Functions for User SQL_USER1 +============================ + +TRAFODION."T144USER1".GEN_PHONE +TRAFODION."T144USER1".GEN_RANDOM +TRAFODION."T144USER1".GEN_TIME +TRAFODION."T144USER1".T144_TRANSLATEPRIVSBITMAP + +======================= + 4 row(s) returned + +--- SQL operation complete. +>>get table_mapping functions for user sql_user1; + +Table mapping functions for User SQL_USER1 +========================================== + +TRAFODION."_LIBMGR_".EVENT_LOG_READER +TRAFODION."_LIBMGR_".JDBC + +======================= + 2 row(s) returned + +--- SQL operation complete. +>>get procedures for user sql_user1; + +--- SQL operation complete. +>>get functions for user sql_user2; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>obey TEST144(cmds); +>>-- ============================================================================ +>>-- execute functions +>>-- ============================================================================ >>-- should return privileges only for users that have execute privilege >>get privileges on function gen_time; @@ -375,13 +479,15 @@ CUSTOMER_ID TENANT_ID PHONE CUSTOMER_AR *** 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; +>>sh sqlci -i "TEST144(cmds_user2)" -u sql_user2; >>values (user); (EXPR) @@ -393,6 +499,65 @@ SQL_USER2 >>set schema t144user1; --- SQL operation complete. +>>get functions for user sql_user2; + +Functions for User SQL_USER2 +============================ + +TRAFODION."T144USER1".T144_TRANSLATEPRIVSBITMAP + +======================= + 1 row(s) returned + +--- SQL operation complete. +>>get table_mapping functions for user sql_user2; + +Table mapping functions for User SQL_USER2 +========================================== + +TRAFODION."_LIBMGR_".EVENT_LOG_READER +TRAFODION."_LIBMGR_".JDBC + +======================= + 2 row(s) returned + +--- SQL operation complete. +>>get procedures for user sql_user2; + +--- SQL operation complete. +>>get libraries for user sql_user2; + +Libraries for User SQL_USER2 +============================ + +TRAFODION."T144USER1".T144_L1 + +======================= + 1 row(s) returned + +--- SQL operation complete. +>> +>>-- no privs +>>get table_mapping functions for user sql_user1; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get libraries for user sql_user3; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get libraries for role t144role1; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>> +>>obey TEST144(cmds); +>>-- ============================================================================ +>>-- execute functions +>>-- ============================================================================ >>-- should return privileges only for users that have execute privilege >>get privileges on function gen_time; @@ -400,7 +565,9 @@ SQL_USER2 >>-- should return no rows for users other than sql_user1 >>get privileges on function gen_random for sql_user1; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>select customer_id, +> 'PHONE: ' || gen_phone(customer_id, customer_areacode) as phone, +> customer_areacode @@ -446,11 +613,13 @@ SQL_USER2 *** ERROR[8822] The statement was not prepared. +>> +>> >>exit; End of MXCI Session ->>sh sqlci -i "TEST144(cmds)" -u sql_user3; +>>sh sqlci -i "TEST144(cmds_user3)" -u sql_user3; >>values (user); (EXPR) @@ -462,6 +631,41 @@ SQL_USER3 >>set schema t144user1; --- SQL operation complete. +>>get functions for user sql_user3; + +Functions for User SQL_USER3 +============================ + +TRAFODION."T144USER1".T144_TRANSLATEPRIVSBITMAP + +======================= + 1 row(s) returned + +--- SQL operation complete. +>>get table_mapping functions for user sql_user3; + +Table mapping functions for User SQL_USER3 +========================================== + +TRAFODION."_LIBMGR_".EVENT_LOG_READER +TRAFODION."_LIBMGR_".JDBC + +======================= + 2 row(s) returned + +--- SQL operation complete. +>>get procedures for user sql_user3; + +--- SQL operation complete. +>>get procedures for user sql_user1; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>obey TEST144(cmds); +>>-- ============================================================================ +>>-- execute functions +>>-- ============================================================================ >>-- should return privileges only for users that have execute privilege >>get privileges on function gen_time; @@ -469,7 +673,9 @@ SQL_USER3 >>-- should return no rows for users other than sql_user1 >>get privileges on function gen_random for sql_user1; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>select customer_id, +> 'PHONE: ' || gen_phone(customer_id, customer_areacode) as phone, +> customer_areacode @@ -515,11 +721,13 @@ SQL_USER3 *** ERROR[8822] The statement was not prepared. +>> +>> >>exit; End of MXCI Session ->>sh sqlci -i "TEST144(cmds)" -u sql_user4; +>>sh sqlci -i "TEST144(cmds_user4)" -u sql_user4; >>values (user); (EXPR) @@ -531,6 +739,45 @@ SQL_USER4 >>set schema t144user1; --- SQL operation complete. +>>get functions for user sql_user4; + +Functions for User SQL_USER4 +============================ + +TRAFODION."T144USER1".T144_TRANSLATEPRIVSBITMAP + +======================= + 1 row(s) returned + +--- SQL operation complete. +>>get table_mapping functions for user sql_user4; + +Table mapping functions for User SQL_USER4 +========================================== + +TRAFODION."_LIBMGR_".EVENT_LOG_READER +TRAFODION."_LIBMGR_".JDBC + +======================= + 2 row(s) returned + +--- SQL operation complete. +>>get procedures for user sql_user4; + +--- SQL operation complete. +>>get libraries for user sql_user4; + +--- SQL operation complete. +>>get libraries for role t144role1; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>> +>>obey TEST144(cmds); +>>-- ============================================================================ +>>-- execute functions +>>-- ============================================================================ >>-- should return privileges only for users that have execute privilege >>get privileges on function gen_time; @@ -538,7 +785,9 @@ SQL_USER4 >>-- should return no rows for users other than sql_user1 >>get privileges on function gen_random for sql_user1; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>select customer_id, +> 'PHONE: ' || gen_phone(customer_id, customer_areacode) as phone, +> customer_areacode @@ -584,6 +833,8 @@ SQL_USER4 *** ERROR[8822] The statement was not prepared. +>> +>> >>exit; End of MXCI Session @@ -646,6 +897,43 @@ Privileges on Routine _LIBMGR_.HELP 1 row(s) returned --- SQL operation complete. +>>get functions for user sql_user2; + +Functions for User SQL_USER2 +============================ + +TRAFODION."T144USER1".GEN_PHONE +TRAFODION."T144USER1".GEN_RANDOM +TRAFODION."T144USER1".GEN_TIME +TRAFODION."T144USER1".T144_TRANSLATEPRIVSBITMAP + +======================= + 4 row(s) returned + +--- SQL operation complete. +>>get table_mapping functions for user sql_user2; + +Table mapping functions for User SQL_USER2 +========================================== + +TRAFODION."_LIBMGR_".EVENT_LOG_READER +TRAFODION."_LIBMGR_".JDBC + +======================= + 2 row(s) returned + +--- SQL operation complete. +>>get procedures for user sql_user2; + +Procedures for User SQL_USER2 +============================= + +TRAFODION."_LIBMGR_".HELP + +======================= + 1 row(s) returned + +--- SQL operation complete. >>execute get_privs; OBJECT_NAME GRANTOR_ID GRANTEE_ID GRANTED_PRIVS GRANTABLE_PRIVS @@ -660,16 +948,17 @@ GEN_RANDOM 333 GEN_TIME -2 33334 ------E ------E GEN_TIME 33334 33335 ------E NONE T144_L1 -2 33334 ---UG-- ---UG-- +T144_L1 33334 33335 ---UG-- NONE T144_L2 -2 33334 ---UG-- ---UG-- +T144_L2 33334 1000002 ----G-- NONE T144_TRANSLATEP -2 33334 ------E ------E T144_TRANSLATEP 33334 -1 ------E NONE _TRAFODION_T144 -2 33334 ----G-- ----G-- ---- 13 row(s) selected. ->> +--- 15 row(s) selected. >> >>-- user2 can execute ->>sh sqlci -i "TEST144(cmds)" -u sql_user2; +>>sh sqlci -i "TEST144(cmds_user2)" -u sql_user2; >>values (user); (EXPR) @@ -681,6 +970,76 @@ SQL_USER2 >>set schema t144user1; --- SQL operation complete. +>>get functions for user sql_user2; + +Functions for User SQL_USER2 +============================ + +TRAFODION."T144USER1".GEN_PHONE +TRAFODION."T144USER1".GEN_RANDOM +TRAFODION."T144USER1".GEN_TIME +TRAFODION."T144USER1".T144_TRANSLATEPRIVSBITMAP + +======================= + 4 row(s) returned + +--- SQL operation complete. +>>get table_mapping functions for user sql_user2; + +Table mapping functions for User SQL_USER2 +========================================== + +TRAFODION."_LIBMGR_".EVENT_LOG_READER +TRAFODION."_LIBMGR_".JDBC + +======================= + 2 row(s) returned + +--- SQL operation complete. +>>get procedures for user sql_user2; + +Procedures for User SQL_USER2 +============================= + +TRAFODION."_LIBMGR_".HELP + +======================= + 1 row(s) returned + +--- SQL operation complete. +>>get libraries for user sql_user2; + +Libraries for User SQL_USER2 +============================ + +TRAFODION."T144USER1".T144_L1 + +======================= + 1 row(s) returned + +--- SQL operation complete. +>> +>>-- no privs +>>get table_mapping functions for user sql_user1; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get libraries for user sql_user3; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get libraries for role t144role1; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>> +>>obey TEST144(cmds); +>>-- ============================================================================ +>>-- execute functions +>>-- ============================================================================ >>-- should return privileges only for users that have execute privilege >>get privileges on function gen_time; @@ -696,7 +1055,9 @@ Privileges on Routine T144USER1.GEN_TIME >>-- should return no rows for users other than sql_user1 >>get privileges on function gen_random for sql_user1; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>select customer_id, +> 'PHONE: ' || gen_phone(customer_id, customer_areacode) as phone, +> customer_areacode @@ -762,13 +1123,15 @@ 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; +>>sh sqlci -i "TEST144(cmds_user3)" -u sql_user3; >>values (user); (EXPR) @@ -780,22 +1143,59 @@ SQL_USER3 >>set schema t144user1; --- SQL operation complete. ->>-- should return privileges only for users that have execute privilege ->>get privileges on function gen_time; +>>get functions for user sql_user3; ---- SQL operation complete. ->>-- should return no rows for users other than sql_user1 ->>get privileges on function gen_random for sql_user1; +Functions for User SQL_USER3 +============================ + +TRAFODION."T144USER1".T144_TRANSLATEPRIVSBITMAP + +======================= + 1 row(s) returned --- SQL operation complete. ->>select customer_id, -+> 'PHONE: ' || gen_phone(customer_id, customer_areacode) as phone, -+> customer_areacode -+>from customers; +>>get table_mapping functions for user sql_user3; -*** ERROR[4482] The user does not have EXECUTE privilege on user-defined routine TRAFODION.T144USER1.GEN_PHONE. +Table mapping functions for User SQL_USER3 +========================================== -*** ERROR[8822] The statement was not prepared. +TRAFODION."_LIBMGR_".EVENT_LOG_READER +TRAFODION."_LIBMGR_".JDBC + +======================= + 2 row(s) returned + +--- SQL operation complete. +>>get procedures for user sql_user3; + +--- SQL operation complete. +>>get procedures for user sql_user1; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>obey TEST144(cmds); +>>-- ============================================================================ +>>-- execute functions +>>-- ============================================================================ +>>-- 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; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>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 @@ -833,6 +1233,8 @@ SQL_USER3 *** ERROR[8822] The statement was not prepared. +>> +>> >>exit; End of MXCI Session @@ -891,6 +1293,42 @@ Privileges on Routine _LIBMGR_.HELP 1 row(s) returned --- SQL operation complete. +>>get functions for user sql_user3; + +Functions for User SQL_USER3 +============================ + +TRAFODION."T144USER1".GEN_PHONE +TRAFODION."T144USER1".GEN_RANDOM +TRAFODION."T144USER1".T144_TRANSLATEPRIVSBITMAP + +======================= + 3 row(s) returned + +--- SQL operation complete. +>>get table_mapping functions for user sql_user3; + +Table mapping functions for User SQL_USER3 +========================================== + +TRAFODION."_LIBMGR_".EVENT_LOG_READER +TRAFODION."_LIBMGR_".JDBC + +======================= + 2 row(s) returned + +--- SQL operation complete. +>>get procedures for user sql_user3; + +Procedures for User SQL_USER3 +============================= + +TRAFODION."_LIBMGR_".HELP + +======================= + 1 row(s) returned + +--- SQL operation complete. >>execute get_privs; OBJECT_NAME GRANTOR_ID GRANTEE_ID GRANTED_PRIVS GRANTABLE_PRIVS @@ -907,15 +1345,17 @@ GEN_RANDOM 333 GEN_TIME -2 33334 ------E ------E GEN_TIME 33334 33335 ------E NONE T144_L1 -2 33334 ---UG-- ---UG-- +T144_L1 33334 33335 ---UG-- NONE T144_L2 -2 33334 ---UG-- ---UG-- +T144_L2 33334 1000002 ----G-- NONE T144_TRANSLATEP -2 33334 ------E ------E T144_TRANSLATEP 33334 -1 ------E NONE _TRAFODION_T144 -2 33334 ----G-- ----G-- ---- 15 row(s) selected. +--- 17 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; +>>sh sqlci -i "TEST144(cmds_user3)" -u sql_user3; >>values (user); (EXPR) @@ -927,6 +1367,51 @@ SQL_USER3 >>set schema t144user1; --- SQL operation complete. +>>get functions for user sql_user3; + +Functions for User SQL_USER3 +============================ + +TRAFODION."T144USER1".GEN_PHONE +TRAFODION."T144USER1".GEN_RANDOM +TRAFODION."T144USER1".T144_TRANSLATEPRIVSBITMAP + +======================= + 3 row(s) returned + +--- SQL operation complete. +>>get table_mapping functions for user sql_user3; + +Table mapping functions for User SQL_USER3 +========================================== + +TRAFODION."_LIBMGR_".EVENT_LOG_READER +TRAFODION."_LIBMGR_".JDBC + +======================= + 2 row(s) returned + +--- SQL operation complete. +>>get procedures for user sql_user3; + +Procedures for User SQL_USER3 +============================= + +TRAFODION."_LIBMGR_".HELP + +======================= + 1 row(s) returned + +--- SQL operation complete. +>>get procedures for user sql_user1; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>obey TEST144(cmds); +>>-- ============================================================================ +>>-- execute functions +>>-- ============================================================================ >>-- should return privileges only for users that have execute privilege >>get privileges on function gen_time; @@ -934,7 +1419,9 @@ SQL_USER3 >>-- should return no rows for users other than sql_user1 >>get privileges on function gen_random for sql_user1; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>select customer_id, +> 'PHONE: ' || gen_phone(customer_id, customer_areacode) as phone, +> customer_areacode @@ -995,6 +1482,8 @@ COMMANDNAME RM - Remove a library file. SHOWDDL PROCEDURE [SCHEMA NAME.]RM for more info. --- SQL operation complete. +>> +>> >>exit; End of MXCI Session @@ -1018,9 +1507,36 @@ End of MXCI Session >>grant execute on procedure "_LIBMGR_".help to t144role1; --- SQL operation complete. +>>get functions for role t144role1; + +Functions for Role T144ROLE1 +============================ + +TRAFODION."T144USER1".GEN_RANDOM +TRAFODION."T144USER1".GEN_TIME + +======================= + 2 row(s) returned + +--- SQL operation complete. +>>get table_mapping functions for role t144role1; + +--- SQL operation complete. +>>get procedures for role t144role1; + +Procedures for Role T144ROLE1 +============================= + +TRAFODION."_LIBMGR_".HELP + +======================= + 1 row(s) returned + +--- 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. @@ -1068,6 +1584,53 @@ Privileges on Routine T144USER1.GEN_RANDOM 1 row(s) returned --- SQL operation complete. +>>get functions for user sql_user4; + +Functions for User SQL_USER4 +============================ + +TRAFODION."T144USER1".GEN_RANDOM +TRAFODION."T144USER1".GEN_TIME +TRAFODION."T144USER1".T144_TRANSLATEPRIVSBITMAP + +======================= + 3 row(s) returned + +--- SQL operation complete. +>>get table_mapping functions for user sql_user4; + +Table mapping functions for User SQL_USER4 +========================================== + +TRAFODION."_LIBMGR_".EVENT_LOG_READER +TRAFODION."_LIBMGR_".JDBC + +======================= + 2 row(s) returned + +--- SQL operation complete. +>>get procedures for user sql_user4; + +Procedures for User SQL_USER4 +============================= + +TRAFODION."_LIBMGR_".HELP + +======================= + 1 row(s) returned + +--- SQL operation complete. +>>get libraries for user sql_user4; + +Libraries for User SQL_USER4 +============================ + +TRAFODION."T144USER1".T144_L2 + +======================= + 1 row(s) returned + +--- SQL operation complete. >>execute get_privs; OBJECT_NAME GRANTOR_ID GRANTEE_ID GRANTED_PRIVS GRANTABLE_PRIVS @@ -1087,15 +1650,17 @@ GEN_TIME GEN_TIME 33334 33335 ------E NONE GEN_TIME 33334 1000002 ------E NONE T144_L1 -2 33334 ---UG-- ---UG-- +T144_L1 33334 33335 ---UG-- NONE T144_L2 -2 33334 ---UG-- ---UG-- +T144_L2 33334 1000002 ----G-- NONE T144_TRANSLATEP -2 33334 ------E ------E T144_TRANSLATEP 33334 -1 ------E NONE _TRAFODION_T144 -2 33334 ----G-- ----G-- ---- 18 row(s) selected. +--- 20 row(s) selected. >> >>-- user4 can execute through role t144role1 ->>sh sqlci -i "TEST144(cmds)" -u sql_user4; +>>sh sqlci -i "TEST144(cmds_user4)" -u sql_user4; >>values (user); (EXPR) @@ -1107,6 +1672,69 @@ SQL_USER4 >>set schema t144user1; --- SQL operation complete. +>>get functions for user sql_user4; + +Functions for User SQL_USER4 +============================ + +TRAFODION."T144USER1".GEN_RANDOM +TRAFODION."T144USER1".GEN_TIME +TRAFODION."T144USER1".T144_TRANSLATEPRIVSBITMAP + +======================= + 3 row(s) returned + +--- SQL operation complete. +>>get table_mapping functions for user sql_user4; + +Table mapping functions for User SQL_USER4 +========================================== + +TRAFODION."_LIBMGR_".EVENT_LOG_READER +TRAFODION."_LIBMGR_".JDBC + +======================= + 2 row(s) returned + +--- SQL operation complete. +>>get procedures for user sql_user4; + +Procedures for User SQL_USER4 +============================= + +TRAFODION."_LIBMGR_".HELP + +======================= + 1 row(s) returned + +--- SQL operation complete. +>>get libraries for user sql_user4; + +Libraries for User SQL_USER4 +============================ + +TRAFODION."T144USER1".T144_L2 + +======================= + 1 row(s) returned + +--- SQL operation complete. +>>get libraries for role t144role1; + +Libraries for Role T144ROLE1 +============================ + +TRAFODION."T144USER1".T144_L2 + +======================= + 1 row(s) returned + +--- SQL operation complete. +>> +>>obey TEST144(cmds); +>>-- ============================================================================ +>>-- execute functions +>>-- ============================================================================ >>-- should return privileges only for users that have execute privilege >>get privileges on function gen_time; @@ -1122,7 +1750,9 @@ Privileges on Routine T144USER1.GEN_TIME >>-- should return no rows for users other than sql_user1 >>get privileges on function gen_random for sql_user1; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>select customer_id, +> 'PHONE: ' || gen_phone(customer_id, customer_areacode) as phone, +> customer_areacode @@ -1178,6 +1808,8 @@ COMMANDNAME RM - Remove a library file. SHOWDDL PROCEDURE [SCHEMA NAME.]RM for more info. --- SQL operation complete. +>> +>> >>exit; End of MXCI Session @@ -1265,12 +1897,18 @@ GEN_TIME GEN_TIME 33334 33335 ------E NONE GEN_TIME 33334 1000002 ------E NONE T144_L1 -2 33334 ---UG-- ---UG-- +T144_L1 33334 33335 ---UG-- NONE T144_L2 -2 33334 ---UG-- ---UG-- +T144_L2 33334 1000002 ----G-- NONE T144_TRANSLATEP -2 33334 ------E ------E T144_TRANSLATEP 33334 -1 ------E NONE _TRAFODION_T144 -2 33334 ----G-- ----G-- ---- 18 row(s) selected. +--- 20 row(s) selected. +>> +>>revoke usage on library t144_l2 from t144role1; + +--- SQL operation complete. >> >>revoke grant option for execute on function gen_phone from sql_user3 by >>sql_user2; @@ -1336,16 +1974,13 @@ _TRAFODION_T144 --- 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 ++>from "_PRIVMGR_MD_".object_privileges p, "_MD_".objects o +>where p.object_uid in +> (select object_uid +> from "_MD_".objects @@ -1368,12 +2003,157 @@ GEN_PHONE GEN_RANDOM -2 33334 ------E ------E GEN_TIME -2 33334 ------E ------E T144_L1 -2 33334 ---UG-- ---UG-- +T144_L1 33334 33335 ---UG-- NONE T144_L2 -2 33334 ---UG-- ---UG-- T144_TRANSLATEP -2 33334 ------E ------E T144_TRANSLATEP 33334 -1 ------E NONE _TRAFODION_T144 -2 33334 ----G-- ----G-- +--- 11 row(s) selected. +>> +>>revoke execute on function t144_translatePrivsBitmap from "PUBLIC"; + +--- 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_L1 33334 33335 ---UG-- NONE +T144_L2 -2 33334 ---UG-- ---UG-- +T144_TRANSLATEP -2 33334 ------E ------E +_TRAFODION_T144 -2 33334 ----G-- ----G-- + --- 10 row(s) selected. +>>sh sqlci -i "TEST144(cmds_user2)" -u sql_user2; +>>values (user); + +(EXPR) +--------------------------------------------------------------------------------------------------------------------------------- + +SQL_USER2 + +--- 1 row(s) selected. +>>set schema t144user1; + +--- SQL operation complete. +>>get functions for user sql_user2; + +--- SQL operation complete. +>>get table_mapping functions for user sql_user2; + +Table mapping functions for User SQL_USER2 +========================================== + +TRAFODION."_LIBMGR_".EVENT_LOG_READER +TRAFODION."_LIBMGR_".JDBC + +======================= + 2 row(s) returned + +--- SQL operation complete. +>>get procedures for user sql_user2; + +--- SQL operation complete. +>>get libraries for user sql_user2; + +Libraries for User SQL_USER2 +============================ + +TRAFODION."T144USER1".T144_L1 + +======================= + 1 row(s) returned + +--- SQL operation complete. >> +>>-- no privs +>>get table_mapping functions for user sql_user1; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get libraries for user sql_user3; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get libraries for role t144role1; + +*** ERROR[1339] T144ROLE1 is not a role. + +--- SQL operation failed with errors. +>> +>>obey TEST144(cmds); +>>-- ============================================================================ +>>-- execute functions +>>-- ============================================================================ +>>-- 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; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>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 + >> >>log; http://git-wip-us.apache.org/repos/asf/trafodion/blob/afff9935/core/sql/regress/privs2/EXPECTED146 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs2/EXPECTED146 b/core/sql/regress/privs2/EXPECTED146 index 0752634..f531f30 100644 --- a/core/sql/regress/privs2/EXPECTED146 +++ b/core/sql/regress/privs2/EXPECTED146 @@ -91,7 +91,7 @@ CREATE TABLE HBASE."_CELL_".T146T1 CREATE HBASE TABLE T146T1 ( COLUMN FAMILY '#1') REGISTER /*INTERNAL*/ HBASE TABLE T146T1; -/* ObjectUID = 7181713655023564985 */ +/* ObjectUID = 6699276640720809174 */ -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON HBASE."_CELL_".T146T1 TO DB__HBASEROLE WITH GRANT OPTION; @@ -114,7 +114,7 @@ CREATE TABLE HBASE."_ROW_".T146T1 CREATE HBASE TABLE T146T1 ( COLUMN FAMILY '#1') REGISTER /*INTERNAL*/ HBASE TABLE T146T1; -/* ObjectUID = 7181713655023565067 */ +/* ObjectUID = 6699276640720809244 */ -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON HBASE."_ROW_".T146T1 TO DB__HBASEROLE WITH GRANT OPTION; @@ -312,7 +312,7 @@ CREATE TABLE HBASE."_CELL_".T146T1 CREATE HBASE TABLE T146T1 ( COLUMN FAMILY '#1') REGISTER /*INTERNAL*/ HBASE TABLE T146T1; -/* ObjectUID = 7181713655023564985 */ +/* ObjectUID = 6699276640720809174 */ -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON HBASE."_CELL_".T146T1 TO DB__HBASEROLE WITH GRANT OPTION; GRANT SELECT ON HBASE."_CELL_".T146T1 TO SQL_USER3 GRANTED BY DB__HBASEROLE; @@ -340,7 +340,7 @@ CREATE TABLE HBASE."_ROW_".T146T1 CREATE HBASE TABLE T146T1 ( COLUMN FAMILY '#1') REGISTER /*INTERNAL*/ HBASE TABLE T146T1; -/* ObjectUID = 7181713655023565067 */ +/* ObjectUID = 6699276640720809244 */ -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON HBASE."_ROW_".T146T1 TO DB__HBASEROLE WITH GRANT OPTION; GRANT SELECT ON HBASE."_ROW_".T146T1 TO SQL_USER3 GRANTED BY DB__HBASEROLE; @@ -1687,7 +1687,9 @@ SQL_USER3 --- SQL operation failed with errors. >>get privileges for role db__hbaserole, match '%146%'; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >> >>drop hbase table t146t2; @@ -1696,7 +1698,9 @@ SQL_USER3 --- SQL operation failed with errors. >>get privileges for role db__hbaserole, match '%146%'; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >> >>exit; http://git-wip-us.apache.org/repos/asf/trafodion/blob/afff9935/core/sql/regress/privs2/LOG144 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs2/LOG144 b/core/sql/regress/privs2/LOG144 index 5738ebc..edc27c2 100644 Binary files a/core/sql/regress/privs2/LOG144 and b/core/sql/regress/privs2/LOG144 differ http://git-wip-us.apache.org/repos/asf/trafodion/blob/afff9935/core/sql/regress/privs2/TEST144 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs2/TEST144 b/core/sql/regress/privs2/TEST144 index 910e120..f5e1934 100755 --- a/core/sql/regress/privs2/TEST144 +++ b/core/sql/regress/privs2/TEST144 @@ -23,6 +23,9 @@ -- @@@ 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'; @@ -40,6 +43,8 @@ exit; -- 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; @@ -49,6 +54,7 @@ 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; @@ -115,18 +121,20 @@ 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 -set schema "_PRIVMGR_MD_"; 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 +from "_PRIVMGR_MD_".object_privileges p, "_MD_".objects o where p.object_uid in (select object_uid from "_MD_".objects @@ -153,12 +161,22 @@ get privileges on table_mapping function "_LIBMGR_".event_log_reader; 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; -sh sqlci -i "TEST144(cmds)" -u 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)" -u sql_user2; -sh sqlci -i "TEST144(cmds)" -u sql_user3; -sh sqlci -i "TEST144(cmds)" -u sql_user4; +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; @@ -169,14 +187,16 @@ 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)" -u sql_user2; +sh sqlci -i "TEST144(cmds_user2)" -u sql_user2; -- user3 still cannot execute -sh sqlci -i "TEST144(cmds)" -u sql_user3; +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; @@ -189,10 +209,13 @@ 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)" -u sql_user3; +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; @@ -200,16 +223,24 @@ grant execute on procedure "_LIBMGR_".help to sql_user5 by sql_user3; 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)" -u sql_user4; +sh sqlci -i "TEST144(cmds_user4)" -u sql_user4; get privileges on function gen_phone; get privileges on function gen_random; @@ -222,6 +253,8 @@ get privileges on procedure "_LIBMGR_".help; 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; @@ -246,14 +279,74 @@ 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 +?section cmds_user1 -- ============================================================================ --- execute functions +-- 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 @@ -276,3 +369,4 @@ from customers; set param ?proc 'rm'; call "_LIBMGR_".help (?proc); + http://git-wip-us.apache.org/repos/asf/trafodion/blob/afff9935/core/sql/sqlcomp/PrivMgrCommands.cpp ---------------------------------------------------------------------- diff --git a/core/sql/sqlcomp/PrivMgrCommands.cpp b/core/sql/sqlcomp/PrivMgrCommands.cpp index 05aaec9..499f544 100644 --- a/core/sql/sqlcomp/PrivMgrCommands.cpp +++ b/core/sql/sqlcomp/PrivMgrCommands.cpp @@ -404,7 +404,7 @@ PrivStatus PrivMgrCommands::getPrivileges( // With all bits set, privilege checks will always succeed if (!authorizationEnabled()) { - privsOfTheUser.setAllTableGrantPrivileges(true); + privsOfTheUser.setAllTableGrantPrivileges(true /*priv*/, true/*wgo*/); userPrivs.initUserPrivs(privsOfTheUser); return STATUS_GOOD; } http://git-wip-us.apache.org/repos/asf/trafodion/blob/afff9935/core/sql/sqlcomp/PrivMgrDesc.cpp ---------------------------------------------------------------------- diff --git a/core/sql/sqlcomp/PrivMgrDesc.cpp b/core/sql/sqlcomp/PrivMgrDesc.cpp index ec55129..6a41978 100644 --- a/core/sql/sqlcomp/PrivMgrDesc.cpp +++ b/core/sql/sqlcomp/PrivMgrDesc.cpp @@ -120,109 +120,6 @@ void PrivMgrCoreDesc::setWgo(const PrivType which, } } -// Set all privilege indicators for Grant to Table -// (Sel/Ins/Upd/Del/Ref only) (Sets these "priv" to True, -// with wgo indicators set as specified). -// If updatable=False, suppress Insert,Delete,Update. -// If insertable=False, suppress Insert. -void PrivMgrCoreDesc::setAllDMLGrantPrivileges(const bool wgo, - const bool updatable, - const bool insertable, - const bool deletable) -{ - this->setPriv(SELECT_PRIV, true); - this->setWgo(SELECT_PRIV, wgo); - - if (updatable) - { - this->setPriv(UPDATE_PRIV, true); - this->setWgo(UPDATE_PRIV, wgo); - this->setPriv(REFERENCES_PRIV, true); - this->setWgo(REFERENCES_PRIV, wgo); - - if ( insertable) - { - this->setPriv(INSERT_PRIV, true); - this->setWgo(INSERT_PRIV, wgo); - } - if ( deletable ) - { - this->setPriv(DELETE_PRIV, true); - this->setWgo(DELETE_PRIV, wgo); - } - } -} - -void PrivMgrCoreDesc::setAllDDLGrantPrivileges(const bool wgo) - -{ - this->setPriv(ALL_DDL, true); - this->setWgo (ALL_DDL, wgo); - -} - -/* - * The following setAllRevoke.. functions are used to set a mask in revoking privileges. - * - * When grantOptionFor is specified, we set the wgo bits because we want to use the mask - * in revoking the with grant option for those privileges. - * - * Otherwise, we set the priv bits because we want to use the mask to revoke those - * privileges. - * -*/ - -void PrivMgrCoreDesc::setAllDMLRevokePrivileges(const bool grantOption) -{ - if (grantOption) - { - //set all dml privs in wgo to true - //set all dml privs in priv to false - this->setPriv(ALL_DML, false); - this->setWgo(ALL_DML, true); - } - else - { - //set all dml privs in wgo to false - //set all dml privs in priv to true - this->setPriv(ALL_DML, true); - this->setWgo(ALL_DML, false); - } - } - -void PrivMgrCoreDesc::setAllDDLRevokePrivileges(const bool grantOption) -{ - if (grantOption) - { - //set all ddl privs in wgo to true - //set all ddl privs in priv to false - this->setPriv(ALL_DDL, false); - this->setWgo(ALL_DDL, true); - } - else - { - //set all ddl privs in wgo to false - //set all ddl privs in priv to true - this->setPriv(ALL_DDL, true); - this->setWgo(ALL_DDL, false); - } -} - -// Set all privilege indicators for Revoke. -void PrivMgrCoreDesc::setAllRevokePrivileges(const bool grantOption) -{ - if (grantOption) - { - priv_.reset(); // For "Revoke Grant Option for.." - wgo_.set(); // get priv=F, wgo=T. - } - else - { - priv_.set(); // For "Revoke ..." - wgo_.reset(); // get priv=T, wgo=F. - } -} - // ---------------------------------------------------------------------------- // method: setAllObjectGrantPrivilege // @@ -233,33 +130,36 @@ void PrivMgrCoreDesc::setAllRevokePrivileges(const bool grantOption) // Params: // objectType - The type of object. Based on the object type (e.g. table, // routine, sequence, etc.) all the relevant privs are set +// priv - privilege setting. If true, the corresponding priv bits are set. // wgo - WITH GRANT OPTION. If true, the corresponding WGO bits are set. // // ---------------------------------------------------------------------------- -void PrivMgrCoreDesc::setAllObjectGrantPrivilege( +void PrivMgrCoreDesc::setAllObjectPrivileges( const ComObjectType objectType, + const bool priv, const bool wgo) { - switch (objectType) { case COM_BASE_TABLE_OBJECT: - setAllTableGrantPrivileges(wgo); + setAllTableGrantPrivileges(priv, wgo); break; case COM_LIBRARY_OBJECT: - setAllLibraryGrantPrivileges(wgo); + setAllLibraryGrantPrivileges(priv, wgo); break; case COM_SEQUENCE_GENERATOR_OBJECT: - setAllSequenceGrantPrivileges(wgo); + setAllSequenceGrantPrivileges(priv, wgo); break; + // all spjs, functions, and table_mapping functions + // are USER_DEFINED_ROUTINE_OBJECT case COM_USER_DEFINED_ROUTINE_OBJECT: - case COM_STORED_PROCEDURE_OBJECT: - setAllUdrGrantPrivileges(wgo); + case COM_STORED_PROCEDURE_OBJECT: /*TBD: remove?*/ + setAllUdrGrantPrivileges(priv, wgo); break; case COM_VIEW_OBJECT: // will reach here for native hive views - setAllTableGrantPrivileges(wgo); + setAllTableGrantPrivileges(priv, wgo); break; default: ; //TODO: internal error? http://git-wip-us.apache.org/repos/asf/trafodion/blob/afff9935/core/sql/sqlcomp/PrivMgrDesc.h ---------------------------------------------------------------------- diff --git a/core/sql/sqlcomp/PrivMgrDesc.h b/core/sql/sqlcomp/PrivMgrDesc.h index 13e83a2..0ff26af 100644 --- a/core/sql/sqlcomp/PrivMgrDesc.h +++ b/core/sql/sqlcomp/PrivMgrDesc.h @@ -289,47 +289,50 @@ class PrivMgrCoreDesc const bool value); void setAllPrivAndWgo(const bool val); - void setAllDMLGrantPrivileges(const bool wgo, - const bool updatable=true, - const bool insertable=true, - const bool deletable=true); - void setAllDDLGrantPrivileges(const bool wgo); - void setAllObjectGrantPrivilege(const ComObjectType objectType,const bool wgo); - void setAllRevokePrivileges(const bool grantOptionFor); - void setAllDMLRevokePrivileges(const bool grantOptionFor); - void setAllDDLRevokePrivileges(const bool grantOption); + void setAllObjectPrivileges( + const ComObjectType objectType, + const bool priv, + const bool wgo); - inline void setAllLibraryGrantPrivileges(const bool wgo) + inline void setAllLibraryGrantPrivileges( + const bool priv, + const bool wgo) { - setPriv(UPDATE_PRIV, TRUE); + setPriv(UPDATE_PRIV, priv); setWgo(UPDATE_PRIV, wgo); - setPriv(USAGE_PRIV, TRUE); + setPriv(USAGE_PRIV, priv); setWgo(USAGE_PRIV, wgo); } - inline void setAllTableGrantPrivileges(const bool wgo) + inline void setAllTableGrantPrivileges( + const bool priv, + const bool wgo) { - setPriv(SELECT_PRIV, TRUE); + setPriv(SELECT_PRIV, priv); setWgo(SELECT_PRIV, wgo); - setPriv(INSERT_PRIV, TRUE); + setPriv(INSERT_PRIV, priv); setWgo(INSERT_PRIV, wgo); - setPriv(DELETE_PRIV, TRUE); + setPriv(DELETE_PRIV, priv); setWgo(DELETE_PRIV, wgo); - setPriv(UPDATE_PRIV, TRUE); + setPriv(UPDATE_PRIV, priv); setWgo(UPDATE_PRIV, wgo); - setPriv(REFERENCES_PRIV, TRUE); + setPriv(REFERENCES_PRIV, priv); setWgo(REFERENCES_PRIV, wgo); } - inline void setAllSequenceGrantPrivileges(const bool wgo) + inline void setAllSequenceGrantPrivileges( + const bool priv, + const bool wgo) { - setPriv(USAGE_PRIV, TRUE); + setPriv(USAGE_PRIV, priv); setWgo(USAGE_PRIV, wgo); } - inline void setAllUdrGrantPrivileges(const bool wgo) + inline void setAllUdrGrantPrivileges( + const bool priv, + const bool wgo) { - setPriv(EXECUTE_PRIV, TRUE); + setPriv(EXECUTE_PRIV, priv); setWgo(EXECUTE_PRIV, wgo); } @@ -510,14 +513,6 @@ public: bool getOneTablePriv(const PrivType which) const; bool getOneTableWgo(const PrivType which) const; - //bool getOneColPriv(const PrivType which, - // const int32_t ordinal - // ) const; - //bool getOneColWgo(const PrivType which, - // const int32_t ordinal - // ) const; - //PrivMgrCoreDesc& getOneColOrdPriv(const int32_t ordinal) const; - // Mutators void setGrantee(const int32_t&grantee) { grantee_ = grantee; } @@ -525,58 +520,52 @@ public: void resetTablePrivs() { tableLevel_.setAllPrivAndWgo(0); } void setColumnPrivs(const NAList<PrivMgrCoreDesc> &privs) { columnLevel_ = privs; } - // This will replace setAllDMLGrantPrivileges for the table level. This function - // is also used to set a view's privileges as well. - void setAllTableGrantPrivileges(const bool wgo) + void setAllObjectPrivileges( + const ComObjectType objectType, + const bool priv, + const bool wgo) { - PrivMgrCoreDesc tableCorePrivs; - - tableCorePrivs.setAllTableGrantPrivileges(wgo); + PrivMgrCoreDesc objectCorePrivs; + objectCorePrivs.setAllObjectPrivileges(objectType, priv, wgo); + setTablePrivs(objectCorePrivs); + } + void setAllTableGrantPrivileges(const bool priv, const bool wgo) + { + PrivMgrCoreDesc tableCorePrivs; + tableCorePrivs.setAllTableGrantPrivileges(priv, wgo); setTablePrivs(tableCorePrivs); } - void setAllLibraryGrantPrivileges(const bool wgo) + void setAllLibraryGrantPrivileges(const bool priv, const bool wgo) { PrivMgrCoreDesc tableCorePrivs; - - tableCorePrivs.setAllLibraryGrantPrivileges(wgo); - + tableCorePrivs.setAllLibraryGrantPrivileges(priv, wgo); setTablePrivs(tableCorePrivs); } - void setAllUdrGrantPrivileges(const bool wgo) + void setAllUdrGrantPrivileges(const bool priv, const bool wgo) { PrivMgrCoreDesc tableCorePrivs; - - tableCorePrivs.setAllUdrGrantPrivileges(wgo); - + tableCorePrivs.setAllUdrGrantPrivileges(priv, wgo); setTablePrivs(tableCorePrivs); } - void setAllSequenceGrantPrivileges(const bool wgo) + void setAllSequenceGrantPrivileges(const bool priv, const bool wgo) { PrivMgrCoreDesc corePrivs; - - corePrivs.setAllSequenceGrantPrivileges(wgo); - + corePrivs.setAllSequenceGrantPrivileges(priv, wgo); setTablePrivs(corePrivs); } - void setAllTableRevokePrivileges(const bool grantOption); - void setAllLibraryRevokePrivileges(const bool grantOption); - void setAllUdrRevokePrivileges(const bool grantOption); - void setAllSequenceRevokePrivileges(const bool grantOption); bool getHasPublicPriv() { return hasPublicPriv_; } void setHasPublicPriv(bool hasPublicPriv) { hasPublicPriv_ = hasPublicPriv; } PrivMgrCoreDesc::PrivResult grantTablePrivs(PrivMgrCoreDesc& priv) { return tableLevel_.grantPrivs(priv); } - //PrivMgrCoreDesc::PrivResult grantColumnPrivs(PrivMgrCoreDesc& priv, const int32_t ordinal); PrivMgrCoreDesc::PrivResult revokeTablePrivs(PrivMgrCoreDesc& priv) { return tableLevel_.revokePrivs(priv); } - //PrivMgrCoreDesc::PrivResult revokeColumnPrivs(PrivMgrCoreDesc& priv, const int32_t ordinal); void pTrace() const; // Debug trace http://git-wip-us.apache.org/repos/asf/trafodion/blob/afff9935/core/sql/sqlcomp/PrivMgrPrivileges.cpp ---------------------------------------------------------------------- diff --git a/core/sql/sqlcomp/PrivMgrPrivileges.cpp b/core/sql/sqlcomp/PrivMgrPrivileges.cpp index 81e6b37..451b32a 100644 --- a/core/sql/sqlcomp/PrivMgrPrivileges.cpp +++ b/core/sql/sqlcomp/PrivMgrPrivileges.cpp @@ -1661,7 +1661,7 @@ PrivMgrCoreDesc corePrivs; PrivObjectBitmap privsBitmap; PrivObjectBitmap grantableBitmap; - corePrivs.setAllObjectGrantPrivilege(objectType,true); + corePrivs.setAllObjectPrivileges(objectType,true/*priv*/,true/*wgo*/); privsBitmap = corePrivs.getPrivBitmap(); grantableBitmap = corePrivs.getWgoBitmap(); @@ -1741,6 +1741,7 @@ PrivStatus PrivMgrPrivileges::initGrantRevoke( // Generate the list of privilege descriptors that were requested PrivStatus retcode = convertPrivsToDesc(objectType, isAllSpecified, + isGrant, (isGrant) ? isGOSpecified : true, // WGO (isGrant) ? false : isGOSpecified, // GOF privList, @@ -2320,9 +2321,9 @@ PrivStatus PrivMgrPrivileges::gatherViewPrivileges( // views have same privileges as tables bool setWGOtrue = true; PrivMgrDesc summarizedOriginalPrivs; - summarizedOriginalPrivs.setAllTableGrantPrivileges(setWGOtrue); + summarizedOriginalPrivs.setAllTableGrantPrivileges(true/*priv*/, setWGOtrue); PrivMgrDesc summarizedCurrentPrivs; - summarizedCurrentPrivs.setAllTableGrantPrivileges(setWGOtrue); + summarizedCurrentPrivs.setAllTableGrantPrivileges(true/*priv*/, setWGOtrue); // Get list of objects referenced by the view std::vector<ObjectReference *> objectList; @@ -2417,8 +2418,8 @@ PrivStatus PrivMgrPrivileges::gatherViewPrivileges( } // Turn on bits to prepare for intersecting with object privileges - originalPrivs.setAllTableGrantPrivileges(setWGOtrue); - currentPrivs.setAllTableGrantPrivileges(setWGOtrue); + originalPrivs.setAllTableGrantPrivileges(true/*priv*/, setWGOtrue); + currentPrivs.setAllTableGrantPrivileges(true/*priv*/, setWGOtrue); std::vector<ColumnReference *> summarizedColRefs; @@ -4972,6 +4973,7 @@ bool PrivMgrPrivileges::isAuthIDGrantedPrivs( PrivStatus PrivMgrPrivileges::convertPrivsToDesc( const ComObjectType objectType, const bool isAllSpecified, + const bool isGrant, const bool isWgoSpecified, const bool isGofSpecified, const std::vector<PrivType> privsList, @@ -5012,14 +5014,7 @@ PrivStatus PrivMgrPrivileges::convertPrivsToDesc( // If all is specified, set bits appropriate for the object type and return if (isAllSpecified) { - if (isLibrary) - privsToProcess.setAllLibraryGrantPrivileges(isWgoSpecified); - else if (isUdr) - privsToProcess.setAllUdrGrantPrivileges(isWgoSpecified); - else if (isSequence) - privsToProcess.setAllSequenceGrantPrivileges(isWgoSpecified); - else - privsToProcess.setAllTableGrantPrivileges(isWgoSpecified); + privsToProcess.setAllObjectPrivileges(objectType, isGrant, isWgoSpecified); return STATUS_GOOD; } @@ -6254,18 +6249,18 @@ PrivStatus ObjectPrivsMDTable::insertSelect( } // Create bitmaps for all supported object types; - PrivMgrDesc privDesc; - privDesc.setAllTableGrantPrivileges(true); - int64_t tableBits = privDesc.getTablePrivs().getPrivBitmap().to_ulong(); + PrivMgrCoreDesc privCoreDesc; + privCoreDesc.setAllTableGrantPrivileges(true, true); + int64_t tableBits = privCoreDesc.getPrivBitmap().to_ulong(); - privDesc.setAllLibraryGrantPrivileges(true); - int64_t libraryBits = privDesc.getTablePrivs().getPrivBitmap().to_ulong(); + privCoreDesc.setAllLibraryGrantPrivileges(true, true); + int64_t libraryBits = privCoreDesc.getPrivBitmap().to_ulong(); - privDesc.setAllUdrGrantPrivileges(true); - int64_t udrBits = privDesc.getTablePrivs().getPrivBitmap().to_ulong(); + privCoreDesc.setAllUdrGrantPrivileges(true, true); + int64_t udrBits = privCoreDesc.getPrivBitmap().to_ulong(); - privDesc.setAllSequenceGrantPrivileges(true); - int64_t sequenceBits = privDesc.getTablePrivs().getPrivBitmap().to_ulong(); + privCoreDesc.setAllSequenceGrantPrivileges(true, true); + int64_t sequenceBits = privCoreDesc.getPrivBitmap().to_ulong(); // for views, privilegesBitmap is set to 1 (SELECT), wgo to 0 (no) std::string systemGrantor(SYSTEM_AUTH_NAME); http://git-wip-us.apache.org/repos/asf/trafodion/blob/afff9935/core/sql/sqlcomp/PrivMgrPrivileges.h ---------------------------------------------------------------------- diff --git a/core/sql/sqlcomp/PrivMgrPrivileges.h b/core/sql/sqlcomp/PrivMgrPrivileges.h index e0597a2..4aa8b3e 100644 --- a/core/sql/sqlcomp/PrivMgrPrivileges.h +++ b/core/sql/sqlcomp/PrivMgrPrivileges.h @@ -231,6 +231,7 @@ protected: PrivStatus convertPrivsToDesc( const ComObjectType objectType, const bool isAllSpecified, + const bool isGrant, const bool isWGOSpecified, const bool isGOFSpecified, const std::vector<PrivType> privsList,
