You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by rm...@apache.org on 2018/09/26 18:20:25 UTC
[1/4] trafodion git commit: Fixes for TRAFODION-3194 && TRAFODION-3195
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,
[4/4] trafodion git commit: Merge [TRAFODION-3194] [TRAFODION-3195]
pr 1717 privilege fixes
Posted by rm...@apache.org.
Merge [TRAFODION-3194] [TRAFODION-3195] pr 1717 privilege fixes
Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/1fd06515
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/1fd06515
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/1fd06515
Branch: refs/heads/master
Commit: 1fd06515194937a12c1a22ff17adc930dce92089
Parents: fb269de afff993
Author: Roberta Marton <ro...@apache.org>
Authored: Wed Sep 26 18:19:57 2018 +0000
Committer: Roberta Marton <ro...@apache.org>
Committed: Wed Sep 26 18:19:57 2018 +0000
----------------------------------------------------------------------
core/sql/bin/SqlciErrors.txt | 2 +-
core/sql/executor/ExExeUtilGet.cpp | 1026 ++++++++++++++++++---------
core/sql/regress/privs1/EXPECTED123 | 815 ++++++++++-----------
core/sql/regress/privs1/EXPECTED125 | 251 ++++++-
core/sql/regress/privs1/EXPECTED141 | 143 +++-
core/sql/regress/privs1/TEST123 | 24 +-
core/sql/regress/privs1/TEST125 | 9 +-
core/sql/regress/privs1/TEST141 | 22 +-
core/sql/regress/privs2/EXPECTED135 | 4 +-
core/sql/regress/privs2/EXPECTED144 | 858 +++++++++++++++++++++-
core/sql/regress/privs2/EXPECTED146 | 16 +-
core/sql/regress/privs2/LOG144 | Bin 17980 -> 140175 bytes
core/sql/regress/privs2/TEST144 | 120 +++-
core/sql/sqlcomp/PrivMgrCommands.cpp | 2 +-
core/sql/sqlcomp/PrivMgrDesc.cpp | 122 +---
core/sql/sqlcomp/PrivMgrDesc.h | 97 ++-
core/sql/sqlcomp/PrivMgrPrivileges.cpp | 39 +-
core/sql/sqlcomp/PrivMgrPrivileges.h | 1 +
18 files changed, 2515 insertions(+), 1036 deletions(-)
----------------------------------------------------------------------
[3/4] trafodion git commit: Fixes for TRAFODION-3194 && TRAFODION-3195
Posted by rm...@apache.org.
Fixes for TRAFODION-3194 && TRAFODION-3195
TRAFODION-3194 Revoke grant option on objects revokes more that grant option
changed Privilege Manager to set bitmaps correctly
removed unused methods from PrivMgrDesc
TRAFODION-3195: Fixes for get commands:
get schemas for user <user>:
returns schemas owned by the specified user
if current user does not have elevated privilege,
returns error if current user does not match <user>.
get schemas for role <role>:
returns schemas owned by the role,
if current user does not have elevated privilege,
returns error if current user has not been granted <role>
get [tables | views | indexes | libraries ] for user <user>:
get [functions | table_mapping_functions | procedures] for user <user>:
get [privileges | roles] for user <user>:
returns objects where <user> has at least one privilege
if current user does not have eleveted privilege
returns error if current user does not match <user>.
get [tables | views | indexes | libraries ] for role <role>:
get [functions | table_mapping_functions | procedures] for role <role>:
get [privileges | users] for <role>:
returns objects where <role> has at least one privilege
if current user does not have eleveted privilege
returns error if current user has not been granted <role>
Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/afff9935
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/afff9935
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/afff9935
Branch: refs/heads/master
Commit: afff9935ecd40b0beef8156c773110b40025721f
Parents: 1bc3d1a
Author: Roberta Marton <ro...@apache.org>
Authored: Fri Sep 21 22:29:01 2018 +0000
Committer: Roberta Marton <ro...@apache.org>
Committed: Fri Sep 21 22:29:01 2018 +0000
----------------------------------------------------------------------
core/sql/bin/SqlciErrors.txt | 2 +-
core/sql/executor/ExExeUtilGet.cpp | 1026 ++++++++++++++++++---------
core/sql/regress/privs1/EXPECTED123 | 815 ++++++++++-----------
core/sql/regress/privs1/EXPECTED125 | 251 ++++++-
core/sql/regress/privs1/EXPECTED141 | 143 +++-
core/sql/regress/privs1/TEST123 | 24 +-
core/sql/regress/privs1/TEST125 | 9 +-
core/sql/regress/privs1/TEST141 | 22 +-
core/sql/regress/privs2/EXPECTED135 | 4 +-
core/sql/regress/privs2/EXPECTED144 | 858 +++++++++++++++++++++-
core/sql/regress/privs2/EXPECTED146 | 16 +-
core/sql/regress/privs2/LOG144 | Bin 17980 -> 140175 bytes
core/sql/regress/privs2/TEST144 | 120 +++-
core/sql/sqlcomp/PrivMgrCommands.cpp | 2 +-
core/sql/sqlcomp/PrivMgrDesc.cpp | 122 +---
core/sql/sqlcomp/PrivMgrDesc.h | 97 ++-
core/sql/sqlcomp/PrivMgrPrivileges.cpp | 39 +-
core/sql/sqlcomp/PrivMgrPrivileges.h | 1 +
18 files changed, 2515 insertions(+), 1036 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/trafodion/blob/afff9935/core/sql/bin/SqlciErrors.txt
----------------------------------------------------------------------
diff --git a/core/sql/bin/SqlciErrors.txt b/core/sql/bin/SqlciErrors.txt
index 7032bf7..0de8a4e 100644
--- a/core/sql/bin/SqlciErrors.txt
+++ b/core/sql/bin/SqlciErrors.txt
@@ -334,7 +334,7 @@
1336 ZZZZZ 99999 ADVANCED MAJOR DBADMIN INITIALIZE SQL must specify a $0~String0 statement for the $1~String1. INITIALIZE SQL fails.
1337 ZZZZZ 99999 BEGINNER MINOR DBADMIN $0~String0 is a reserved authorization identifier.
1338 ZZZZZ 99999 ADVANCED MAJOR DBADMIN Role $0~string0 is not defined in the database.
-1339 ZZZZZ 99999 BEGINNER MINOR DBADMIN $0~string0 is not a grantable role.
+1339 ZZZZZ 99999 BEGINNER MINOR DBADMIN $0~string0 is not a role.
1340 ZZZZZ 99999 BEGINNER MINOR DBADMIN $0~string0 is not a user.
1341 ZZZZZ 99999 BEGINNER MINOR DBADMIN --- unused ---
1342 ZZZZZ 99999 BEGINNER MINOR DBADMIN --- unused ---
http://git-wip-us.apache.org/repos/asf/trafodion/blob/afff9935/core/sql/executor/ExExeUtilGet.cpp
----------------------------------------------------------------------
diff --git a/core/sql/executor/ExExeUtilGet.cpp b/core/sql/executor/ExExeUtilGet.cpp
index b9d4c68..590b068 100644
--- a/core/sql/executor/ExExeUtilGet.cpp
+++ b/core/sql/executor/ExExeUtilGet.cpp
@@ -177,7 +177,7 @@ static const QueryString getRolesForUserQuery[] =
{" select translate(rtrim(RU.role_name) using ucs2toutf8) "},
{" from %s.\"%s\".%s RU "},
{" where (RU.grantor_ID != -2) and "},
- {" (RU.grantee_name='%s') %s "},
+ {" (RU.grantee_name='%s') "},
{" union select * from (values ('PUBLIC')) "},
{" order by 1 "},
{" ; "}
@@ -348,7 +348,7 @@ static const QueryString getTrafIndexesOnTableQuery[] =
{" ; "}
};
-static const QueryString getTrafIndexesForUser[] =
+static const QueryString getTrafIndexesForAuth[] =
{
{" select trim(T2.catalog_name) || '.\"' || trim(T2.schema_name) || '\".' || trim(T2.object_name) "},
{" from %s.\"%s\".%s I, "},
@@ -385,20 +385,21 @@ static const QueryString getTrafLibrariesInSchemaQuery[] =
{" ; "}
};
-static const QueryString getTrafLibrariesForUser[] =
+static const QueryString getTrafLibrariesForAuthQuery[] =
{
- {" select distinct object_name from "},
- {" %s.\"%s\".%s T, %s.\"%s\".%s R "},
- {" where T.catalog_name = '%s' and T.object_type = 'LB' and "},
- {" T.object_uid = R.library_uid %s "},
+ {" select distinct trim(catalog_name) || '.\"' || "},
+ {" trim(schema_name) || '\".' || trim(object_name) "},
+ {" from %s.\"%s\".%s "},
+ {" where catalog_name = '%s' and object_type = 'LB' %s "},
{" order by 1 "},
{" ; "}
};
static const QueryString getTrafRoutinesForAuthQuery[] =
{
- {" select distinct object_name from "},
- {" %s.\"%s\".%s T, %s.\"%s\".%s R "},
+ {" select distinct trim(catalog_name) || '.\"' || "},
+ {" trim(schema_name) || '\".' || trim(object_name) "},
+ {" from %s.\"%s\".%s T, %s.\"%s\".%s R "},
{" where T.catalog_name = '%s' and "},
{" T.object_type = 'UR' and "},
{" T.object_uid = R.udr_uid and "},
@@ -536,8 +537,9 @@ static const QueryString getTrafSchemasForAuthIDQuery[] =
{" select T.schema_name "},
{" from %s.\"%s\".%s T, "},
{" %s.\"%s\".%s A "},
- {" where (T.object_type = 'PS' or T.object_type = 'SS') and "},
- {" A.auth_db_name = '%s' and T.object_owner = A.auth_id "},
+ {" where T.object_type in ('PS', 'SS') and "},
+ {" T.object_owner = A.auth_id and "},
+ {" A.auth_id in %s "},
{" order by 1 "},
{" ; "}
};
@@ -1273,7 +1275,7 @@ short ExExeUtilGetMetadataInfoTcb::displayHeading()
break;
case ComTdbExeUtilGetMetadataInfo::LIBRARIES_FOR_ROLE_:
- str_sprintf(headingBuf_,"Libraries for User %s", getMItdb().getParam1());
+ str_sprintf(headingBuf_,"Libraries for Role %s", getMItdb().getParam1());
break;
case ComTdbExeUtilGetMetadataInfo::PROCEDURES_FOR_LIBRARY_:
@@ -1308,6 +1310,10 @@ short ExExeUtilGetMetadataInfoTcb::displayHeading()
str_sprintf(headingBuf_,"Roles for User %s",getMItdb().getParam1());
break;
+ case ComTdbExeUtilGetMetadataInfo::SCHEMAS_FOR_ROLE_:
+ str_sprintf(headingBuf_,"Schemas for Role %s",getMItdb().getParam1());
+ break;
+
case ComTdbExeUtilGetMetadataInfo::SCHEMAS_FOR_USER_:
str_sprintf(headingBuf_,"Schemas for User %s",getMItdb().getParam1());
break;
@@ -1507,6 +1513,12 @@ short ExExeUtilGetMetadataInfoTcb::displayHeading()
// Reads the "_MD_".auths table to get the auth_id from the passed in authName.
// If relationship not found for any reason, return 0, otherwise return
// the authID.
+//
+// TBD - should replace this with a call to currContext->getAuthIDFromName
+// this function checks for special authID and looks at cache before
+// calling metadata. Currently there is an issue because privilege
+// error are returned when trying to read AUTHS table. Need to set
+// parserflag 131072.
// ----------------------------------------------------------------------------
Int32 ExExeUtilGetMetadataInfoTcb::getAuthID(
const char *authName,
@@ -1593,6 +1605,9 @@ char * ExExeUtilGetMetadataInfoTcb::getRoleList(
const char *schName,
const char *objName)
{
+ // Always include PUBLIC
+ NAString roleList("(-1");
+
short rc = 0;
Lng32 cliRC = 0;
@@ -1604,23 +1619,22 @@ char * ExExeUtilGetMetadataInfoTcb::getRoleList(
numOutputEntries_ = 1;
cliRC = fetchAllRows(infoList_, queryBuf_, numOutputEntries_, FALSE, rc);
if (cliRC < 0)
- {
- cliInterface()->allocAndRetrieveSQLDiagnostics(diagsArea_);
- return NULL;
- }
+ {
+ cliInterface()->allocAndRetrieveSQLDiagnostics(diagsArea_);
+ return NULL;
+ }
- NAString roleList("(-1");
char buf[30];
infoList_->position();
while (NOT infoList_->atEnd())
{
OutputInfo * vi = (OutputInfo*)infoList_->getCurr();
if (vi)
- {
- str_sprintf(buf, ", %d", *(Lng32*)vi->get(0));
- roleList += buf;
- }
- infoList_->advance();
+ {
+ str_sprintf(buf, ", %d", *(Lng32*)vi->get(0));
+ roleList += buf;
+ }
+ infoList_->advance();
}
str_sprintf(buf, ", %d)", userID);
roleList += buf;
@@ -1672,8 +1686,7 @@ NABoolean ExExeUtilGetMetadataInfoTcb::checkUserPrivs(
privMDLoc += "\"";
PrivMgrComponentPrivileges componentPrivileges(privMDLoc,getDiagsArea());
- if (componentPrivileges.hasSQLPriv(ComUser::getCurrentUser(),SQLOperation::SHOW,true))
- return FALSE;
+ if (componentPrivileges.hasSQLPriv(ComUser::getCurrentUser(),SQLOperation::SHOW,true)) return FALSE;
// Check component privilege based on QueryType
switch (queryType)
@@ -1976,6 +1989,7 @@ short ExExeUtilGetMetadataInfoTcb::work()
// used in a select "IN" clause. Include the current user
NAString authList;
NAString colPrivsStmt;
+ NAString var;
if (CmpCommon::context()->isAuthorizationEnabled())
{
@@ -2284,8 +2298,8 @@ short ExExeUtilGetMetadataInfoTcb::work()
param_[10] = getMItdb().sch_;
param_[11] = getMItdb().obj_;
if (getMItdb().queryType_ == ComTdbExeUtilGetMetadataInfo::VIEWS_ON_TABLE_)
- strcpy(ausStr, " and T1.object_type = 'BT' ");
- param_[12] = ausStr;
+ var = " and T1.object_type = 'BT' ";
+ param_[12] = (char *)var.data();
param_[13] = (char *)privWhereClause.data();
}
@@ -2306,20 +2320,98 @@ short ExExeUtilGetMetadataInfoTcb::work()
param_[4] = (char *) privWhereClause.data();
}
break;
+
case ComTdbExeUtilGetMetadataInfo::SCHEMAS_FOR_USER_:
- {
- qs = getTrafSchemasForAuthIDQuery;
- sizeOfqs = sizeof(getTrafSchemasForAuthIDQuery);
+ case ComTdbExeUtilGetMetadataInfo::SCHEMAS_FOR_ROLE_:
+ {
+ qs = getTrafSchemasForAuthIDQuery;
+ sizeOfqs = sizeof(getTrafSchemasForAuthIDQuery);
- param_[0] = cat;
- param_[1] = sch;
- param_[2] = tab;
- param_[3] = cat;
- param_[4] = sch;
- param_[5] = auths;
- param_[6] = getMItdb().getParam1();
+ bool isRole = (getMItdb().queryType_ == ComTdbExeUtilGetMetadataInfo::SCHEMAS_FOR_ROLE_);
+
+ Int32 authID = *currContext->getDatabaseUserID();
+ if (!(strcmp(getMItdb().getParam1(), currContext->getDatabaseUserName()) == 0))
+ authID = getAuthID(getMItdb().getParam1(), cat, sch, auths);
+
+ if (isRole)
+ {
+ // if incorrect auth type, return error
+ if (!CmpSeabaseDDLauth::isRoleID(authID) && !ComUser::isPublicUserID(authID))
+ {
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_IS_NOT_A_ROLE,
+ NULL, NULL, NULL,
+ getMItdb().getParam1());
+ step_ = HANDLE_ERROR_;
+ break;
+ }
+
+ // Cannot get schemas if current user not granted role
+ if (doPrivCheck && !ComUser::currentUserHasRole(authID))
+ {
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_NOT_AUTHORIZED);
+ step_ = HANDLE_ERROR_;
+ break;
+ }
+
+ // Return schemas that are owned by the specified role -> authID = roleID
+ char buf[30];
+ str_sprintf(buf, "(%d)", authID);
+ privWhereClause = buf;
+ }
+
+ else /* isUser*/
+ {
+ // if incorrect auth type, return error
+ if (!CmpSeabaseDDLauth::isUserID(authID))
+ {
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_IS_NOT_A_USER,
+ NULL, NULL, NULL,
+ getMItdb().getParam1());
+ step_ = HANDLE_ERROR_;
+ break;
+ }
+
+ // Cannot get schemas for user other than the current user
+ if (doPrivCheck && authID != ComUser::getCurrentUser())
+ {
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_NOT_AUTHORIZED);
+ step_ = HANDLE_ERROR_;
+ break;
+ }
+
+ // Get list of roles assigned to user, return all schemas
+ // owned by user and user's roles
+
+ if (authID == ComUser::getCurrentUser())
+ privWhereClause = authList;
+ else
+ {
+ char *userRoleList = getRoleList(authID, cat, pmsch, role_usage);
+ if (userRoleList)
+ {
+ privWhereClause = userRoleList;
+ NADELETEBASIC(userRoleList, getHeap());
+ }
+ else
+ {
+ // Unable to read metadata
+ ExRaiseSqlError(getHeap(), &diagsArea_, -8001);
+ step_ = HANDLE_ERROR_;
+ break;
+ }
+ }
+ }
+
+ param_[0] = cat;
+ param_[1] = sch;
+ param_[2] = tab;
+ param_[3] = cat;
+ param_[4] = sch;
+ param_[5] = auths;
+ param_[6] = (char *) privWhereClause.data();
}
break;
+
case ComTdbExeUtilGetMetadataInfo::USERS_:
{
qs = getTrafUsers;
@@ -2422,89 +2514,126 @@ short ExExeUtilGetMetadataInfoTcb::work()
break ;
case ComTdbExeUtilGetMetadataInfo::PROCEDURES_FOR_USER_:
- case ComTdbExeUtilGetMetadataInfo::PROCEDURES_FOR_ROLE_:
case ComTdbExeUtilGetMetadataInfo::FUNCTIONS_FOR_USER_:
- case ComTdbExeUtilGetMetadataInfo::FUNCTIONS_FOR_ROLE_:
case ComTdbExeUtilGetMetadataInfo::TABLE_FUNCTIONS_FOR_USER_:
- case ComTdbExeUtilGetMetadataInfo::TABLE_FUNCTIONS_FOR_ROLE_:
{
- NABoolean isUser =
- (getMItdb().queryType_ == ComTdbExeUtilGetMetadataInfo::PROCEDURES_FOR_USER_ ||
- getMItdb().queryType_ == ComTdbExeUtilGetMetadataInfo::TABLE_FUNCTIONS_FOR_USER_ ||
- getMItdb().queryType_ == ComTdbExeUtilGetMetadataInfo::FUNCTIONS_FOR_USER_);
+ qs = getTrafRoutinesForAuthQuery;
+ sizeOfqs = sizeof(getTrafRoutinesForAuthQuery);
// Get the authID associated with the specified user
Int32 authID = *currContext->getDatabaseUserID();
if (!(strcmp(getMItdb().getParam1(), currContext->getDatabaseUserName()) == 0))
authID = getAuthID(getMItdb().getParam1(), cat, sch, auths);
- // Verify that the user is a user, or the role is a role
- if (isUser && !CmpSeabaseDDLauth::isUserID(authID) ||
- !isUser && !CmpSeabaseDDLauth::isRoleID(authID))
+ // If not a user, we are done, don't return data
+ if (!CmpSeabaseDDLauth::isUserID(authID))
{
- NAString type = (isUser ? "user" : "role");
- ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_IS_NOT_CORRECT_AUTHID,
- NULL, NULL, NULL,
- getMItdb().getParam1(),
- type.data());
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_IS_NOT_A_USER);
step_ = HANDLE_ERROR_;
break;
}
- qs = getTrafRoutinesForAuthQuery;
- sizeOfqs = sizeof(getTrafRoutinesForAuthQuery);
-
- NAString routineType;
- if ((getMItdb().queryType_ == ComTdbExeUtilGetMetadataInfo::PROCEDURES_FOR_USER_) ||
- (getMItdb().queryType_ == ComTdbExeUtilGetMetadataInfo::PROCEDURES_FOR_ROLE_))
- routineType = COM_PROCEDURE_TYPE_LIT;
- else if ((getMItdb().queryType_ == ComTdbExeUtilGetMetadataInfo::FUNCTIONS_FOR_USER_) ||
- (getMItdb().queryType_ == ComTdbExeUtilGetMetadataInfo::FUNCTIONS_FOR_ROLE_))
- routineType = COM_SCALAR_UDF_TYPE_LIT;
- else
- routineType = COM_TABLE_UDF_TYPE_LIT;
+ // Non elevated user cannot view routines for another user
+ if (doPrivCheck && authID != ComUser::getCurrentUser())
+ {
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_NOT_AUTHORIZED);
+ step_ = HANDLE_ERROR_;
+ break;
+ }
+
+ // Determine routine type
+ if (getMItdb().queryType_ == ComTdbExeUtilGetMetadataInfo::PROCEDURES_FOR_USER_)
+ var = COM_PROCEDURE_TYPE_LIT;
+ else if (getMItdb().queryType_ == ComTdbExeUtilGetMetadataInfo::FUNCTIONS_FOR_USER_)
+ var = COM_SCALAR_UDF_TYPE_LIT;
+ else
+ var = COM_TABLE_UDF_TYPE_LIT;
- // Getting objects for the current user
- if (strcmp(getMItdb().getParam1(), currContext->getDatabaseUserName()) == 0)
- privWhereClause = getGrantedPrivCmd(authList, cat, NAString ("T.object_uid"));
+ // Limit results to privileges allowed for specified user
- // Getting objects for a user other than the current user
+ if (authID == ComUser::getCurrentUser())
+ privWhereClause = getGrantedPrivCmd(authList, cat);
else
{
- if (doPrivCheck)
+ char *userRoleList = getRoleList(authID, cat, pmsch, role_usage);
+ if (userRoleList)
{
- // User cannot view privileges for another user
- ExRaiseSqlError(getHeap(), &diagsArea_, -1017);
- step_ = HANDLE_ERROR_;
- break;
+ privWhereClause = getGrantedPrivCmd(userRoleList, cat, NAString ("T.object_uid"));
+ NADELETEBASIC(userRoleList, getHeap());
}
else
{
- // get the list of roles for this other user.
- char *userRoleList = getRoleList(authID, cat, pmsch, role_usage);
- if (userRoleList)
- {
- privWhereClause = getGrantedPrivCmd(userRoleList, cat, NAString ("T.object_uid"));
- NADELETEBASIC(userRoleList, getHeap());
- }
- else
- {
- // Unable to read metadata
- ExRaiseSqlError(getHeap(), &diagsArea_, -8001);
- step_ = HANDLE_ERROR_;
- break;
- }
+ // Unable to read metadata
+ ExRaiseSqlError(getHeap(), &diagsArea_, -8001);
+ step_ = HANDLE_ERROR_;
+ break;
}
}
- param_[0] = cat;
- param_[1] = sch;
- param_[2] = tab;
+ param_[0] = cat;
+ param_[1] = sch;
+ param_[2] = tab;
param_[3] = cat;
- param_[4] = sch;
- param_[5] = routine;
- param_[6] = getMItdb().cat_;
- param_[7] = (char *)routineType.data();
+ param_[4] = sch;
+ param_[5] = routine;
+ param_[6] = getMItdb().cat_;
+ param_[7] = (char *)var.data();
+ param_[8] = (char *) privWhereClause.data();
+ }
+ break ;
+
+ case ComTdbExeUtilGetMetadataInfo::PROCEDURES_FOR_ROLE_:
+ case ComTdbExeUtilGetMetadataInfo::FUNCTIONS_FOR_ROLE_:
+ case ComTdbExeUtilGetMetadataInfo::TABLE_FUNCTIONS_FOR_ROLE_:
+ {
+ qs = getTrafRoutinesForAuthQuery;
+ sizeOfqs = sizeof(getTrafRoutinesForAuthQuery);
+
+ // Get the authID associated with the specified role
+ Int32 authID = *currContext->getDatabaseUserID();
+ if (!(strcmp(getMItdb().getParam1(), currContext->getDatabaseUserName()) == 0))
+ authID = getAuthID(getMItdb().getParam1(), cat, sch, auths);
+
+ // If not a role, we are done, don't return data
+ if (!CmpSeabaseDDLauth::isRoleID(authID) && !ComUser::isPublicUserID(authID))
+ {
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_IS_NOT_A_ROLE,
+ NULL, NULL, NULL,
+ getMItdb().getParam1());
+ step_ = HANDLE_ERROR_;
+ break;
+ }
+
+ // non elevated user has to be granted role
+ if (doPrivCheck && !ComUser::currentUserHasRole(authID))
+ {
+ // No priv
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_NOT_AUTHORIZED);
+ step_ = HANDLE_ERROR_;
+ break;
+ }
+
+ // determine routine type
+ if (getMItdb().queryType_ == ComTdbExeUtilGetMetadataInfo::PROCEDURES_FOR_ROLE_)
+ var = COM_PROCEDURE_TYPE_LIT;
+ else if (getMItdb().queryType_ == ComTdbExeUtilGetMetadataInfo::FUNCTIONS_FOR_ROLE_)
+ var = COM_SCALAR_UDF_TYPE_LIT;
+ else
+ var = COM_TABLE_UDF_TYPE_LIT;
+
+ // Only return rows where role (authID) has been granted privs
+ char buf[30];
+ str_sprintf(buf, "(%d)", authID);
+ privWhereClause = getGrantedPrivCmd(buf, cat, NAString ("T.object_uid"));
+
+ param_[0] = cat;
+ param_[1] = sch;
+ param_[2] = tab;
+ param_[3] = cat;
+ param_[4] = sch;
+ param_[5] = routine;
+ param_[6] = getMItdb().cat_;
+ param_[7] = (char *)var.data();
param_[8] = (char *) privWhereClause.data();
}
break ;
@@ -2532,12 +2661,12 @@ short ExExeUtilGetMetadataInfoTcb::work()
param_[10] = getMItdb().sch_;
param_[11] = getMItdb().obj_;
if (getMItdb().queryType_ == ComTdbExeUtilGetMetadataInfo::PROCEDURES_FOR_LIBRARY_)
- strcpy(ausStr, " R.udr_type = 'P ' ");
+ var = " R.udr_type = 'P ' ";
else if (getMItdb().queryType_ == ComTdbExeUtilGetMetadataInfo::FUNCTIONS_FOR_LIBRARY_)
- strcpy(ausStr, " R.udr_type = 'F ' ");
+ var = " R.udr_type = 'F ' ";
else
- strcpy(ausStr, " R.udr_type = 'T ' ");
- param_[12] = ausStr;
+ var = " R.udr_type = 'T ' ";
+ param_[12] = (char *) var.data();
param_[13] = (char *) privWhereClause.data();
}
break ;
@@ -2550,6 +2679,7 @@ short ExExeUtilGetMetadataInfoTcb::work()
if (doPrivCheck)
{
+ // return roles granted to current user
char buf[authList.length() + 100];
str_sprintf(buf, " and auth_id in %s", authList.data());
privWhereClause = buf;
@@ -2566,10 +2696,23 @@ short ExExeUtilGetMetadataInfoTcb::work()
{
qs = getUsersForRoleQuery;
sizeOfqs = sizeof(getUsersForRoleQuery);
-
- // return the name if the role has been granted to the current user
- if (doPrivCheck)
- privWhereClause = " and grantee_name = CURRENT_USER ";
+
+ if (doPrivCheck)
+ {
+ // If user not granted role, return an error
+ Int32 authID = *currContext->getDatabaseUserID();
+ if (!(strcmp(getMItdb().getParam1(), currContext->getDatabaseUserName()) == 0))
+ authID = getAuthID(getMItdb().getParam1(), cat, sch, auths);
+ if (!ComUser::currentUserHasRole(authID))
+ {
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_NOT_AUTHORIZED);
+ step_ = HANDLE_ERROR_;
+ break;
+ }
+
+ // limit users to the current user only
+ privWhereClause = " and grantee_name = CURRENT_USER ";
+ }
param_[0] = cat;
param_[1] = pmsch;
@@ -2584,115 +2727,75 @@ short ExExeUtilGetMetadataInfoTcb::work()
qs = getRolesForUserQuery;
sizeOfqs = sizeof(getRolesForUserQuery);
- // return the name if the current user was granted the role
if (doPrivCheck)
- privWhereClause = " and CURRENT_USER=RU.grantee_name ";
+ {
+ // If user not the current user, return an error
+ // TBD - the current context contains a list of roles,
+ // return list to avoid metadata I/O
+ if (strcmp(getMItdb().getParam1(), currContext->getDatabaseUserName()) != 0)
+ {
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_NOT_AUTHORIZED);
+ step_ = HANDLE_ERROR_;
+ break;
+ }
+ }
+ else
+ {
+ // Get the authID for the request
+ Int32 authID = *currContext->getDatabaseUserID();
+ if (!(strcmp(getMItdb().getParam1(), currContext->getDatabaseUserName()) == 0))
+ authID = getAuthID(getMItdb().getParam1(), cat, sch, auths);
+ if (!CmpSeabaseDDLauth::isUserID(authID))
+ {
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_IS_NOT_A_USER,
+ NULL, NULL, NULL,
+ getMItdb().getParam1());
+ step_ = HANDLE_ERROR_;
+ break;
+ }
+ }
param_[0] = cat;
param_[1] = pmsch;
param_[2] = role_usage;
param_[3] = getMItdb().getParam1();
- param_[4] = (char *) privWhereClause.data();
}
break;
case ComTdbExeUtilGetMetadataInfo::PRIVILEGES_FOR_ROLE_:
- case ComTdbExeUtilGetMetadataInfo::PRIVILEGES_FOR_USER_:
{
- NABoolean isRole =
- (getMItdb().queryType_ == ComTdbExeUtilGetMetadataInfo::PRIVILEGES_FOR_ROLE_);
-
- // Get the authID associated with the current user
- Int32 authID = getAuthID(getMItdb().getParam1(), cat, sch, auths);
+ // Get the authID for the request
+ Int32 authID = *currContext->getDatabaseUserID();
+ if (!(strcmp(getMItdb().getParam1(), currContext->getDatabaseUserName()) == 0))
+ authID = getAuthID(getMItdb().getParam1(), cat, sch, auths);
- // If the authID was not found for various reasons just return
- // Other "get" commands continue and return no rows but it is
- // easier to just return with an error.
- if (authID == 0)
- {
- ExRaiseSqlError(getHeap(), &diagsArea_, -8732,
+ char buf[authList.length() + 100];
+
+ // Verify that requested authID is actually a role
+ if (!CmpSeabaseDDLauth::isRoleID(authID) && !ComUser::isPublicUserID(authID))
+ {
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_IS_NOT_A_ROLE,
NULL, NULL, NULL,
getMItdb().getParam1());
step_ = HANDLE_ERROR_;
break;
}
- if (isRole)
+ // Non elevated users need to be granted role
+ if (doPrivCheck && !ComUser::currentUserHasRole(authID))
{
- // treating PUBLIC (-1) as a role in this instance, return
- // error if authID is not a role
- if (!CmpSeabaseDDLauth::isRoleID(authID) && authID != PUBLIC_USER)
- {
- ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_IS_NOT_A_ROLE,
- NULL, NULL, NULL,
- getMItdb().getParam1());
- step_ = HANDLE_ERROR_;
- break;
- }
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_NOT_AUTHORIZED);
+ step_ = HANDLE_ERROR_;
+ break;
}
- else
- {
- // Return an error if authID is not a user
- if (!CmpSeabaseDDLauth::isUserID(authID))
- {
- ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_IS_NOT_A_USER,
- NULL, NULL, NULL,
- getMItdb().getParam1());
- step_ = HANDLE_ERROR_;
- break;
- }
- }
-
+ // return all privs for the role
+ str_sprintf(buf, " = %d ", authID);
+ privWhereClause = buf;
+
qs = getPrivsForAuthsQuery;
sizeOfqs = sizeof(getPrivsForAuthsQuery);
- char buf[authList.length() + 100];
- if (isRole)
- {
- if (doPrivCheck)
- // Only return name if current user has been
- // granted the role
- str_sprintf(buf, " = %d and %d in %s ",
- authID, authID, authList.data());
- else
- // return all role grantees
- str_sprintf(buf, " = %d ", authID );
- }
- else
- {
- if (doPrivCheck)
- {
- // If asking for privileges for a user other than CURRENT_USER,
- // use -2 (special user called _SYSTEM), privileges can never
- // be granted to _SYSTEM. This returns an empty list
- // which match other "get" statements behavior.
- if (strcmp(getMItdb().getParam1(), currContext->getDatabaseUserName()) != 0)
- str_sprintf(buf, " = -2 ");
- else
- // return all privs for the user and the users roles
- str_sprintf(buf, " in %s ", authList.data());
- }
-
- else
- if (*currContext->getDatabaseUserID() == authID)
- str_sprintf(buf, " in %s ", authList.data());
- else
- {
- // If getting privileges for a user other than the CURRENT USER,
- // get the list of roles for this other user.
- char *userRoleList = getRoleList(authID, cat, pmsch, role_usage);
- if (userRoleList)
- {
- str_sprintf(buf, " in %s ", userRoleList);
- NADELETEBASIC(userRoleList, getHeap());
- }
- else
- str_sprintf(buf, " = %d ", authID);
- }
- }
- privWhereClause = buf;
-
// This request performs a union between 4 entities:
// 1. object_privileges table
// 2. schema_privileges table
@@ -2726,8 +2829,91 @@ short ExExeUtilGetMetadataInfoTcb::work()
}
break;
-
- case ComTdbExeUtilGetMetadataInfo::PRIVILEGES_ON_TABLE_:
+ case ComTdbExeUtilGetMetadataInfo::PRIVILEGES_FOR_USER_:
+ {
+ // Get the authID for the request
+ Int32 authID = *currContext->getDatabaseUserID();
+ if (!(strcmp(getMItdb().getParam1(), currContext->getDatabaseUserName()) == 0))
+ authID = getAuthID(getMItdb().getParam1(), cat, sch, auths);
+
+ // Verify that authID is a user
+ if (!CmpSeabaseDDLauth::isUserID(authID))
+ {
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_IS_NOT_A_USER,
+ NULL, NULL, NULL,
+ getMItdb().getParam1());
+ step_ = HANDLE_ERROR_;
+ break;
+ }
+
+ // Non elevated user cannot get privileges for another user
+ char buf[authList.length() + 100];
+ if (doPrivCheck)
+ {
+ if (authID != ComUser::getCurrentUser())
+ {
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_NOT_AUTHORIZED);
+ step_ = HANDLE_ERROR_;
+ break;
+ }
+
+ // return privs for the current user and their roles
+ str_sprintf(buf, " in %s ", authList.data());
+ }
+
+ else
+ {
+ // Get role list for requested user
+ char *userRoleList = getRoleList(authID, cat, pmsch, role_usage);
+ if (userRoleList)
+ {
+ str_sprintf(buf, " in %s ", userRoleList);
+ NADELETEBASIC(userRoleList, getHeap());
+ }
+ else
+ str_sprintf(buf, " = %d ", authID);
+ }
+ privWhereClause = buf;
+
+ qs = getPrivsForAuthsQuery;
+ sizeOfqs = sizeof(getPrivsForAuthsQuery);
+
+ // This request performs a union between 4 entities:
+ // 1. object_privileges table
+ // 2. schema_privileges table
+ // 3. column privileges table
+ // 4. hive metadata tables to retrieve column details
+ // The call to colPrivsFrag returns the required the union
+ // statement(s) for items 3 and 4. See colPrivsFrag for details
+ if (colPrivsFrag(getMItdb().getParam1(), cat, privWhereClause, colPrivsStmt) < 0)
+ {
+ step_ = HANDLE_ERROR_;
+ break;
+ }
+
+ // Union privileges between object, column and schema
+ // object privs
+ param_[0] = cat;
+ param_[1] = pmsch;
+ param_[2] = objPrivs;
+ param_[3] = (char *) privWhereClause.data();
+
+ // schema privs
+ param_[4] = cat;
+ param_[5] = pmsch;
+ param_[6] = schPrivs;
+ param_[7] = (char *) privWhereClause.data();
+
+ // column privs
+ param_[8] = (char *) colPrivsStmt.data();
+
+ numOutputEntries_ = 2;
+ }
+ break;
+
+
+
+ case ComTdbExeUtilGetMetadataInfo::PRIVILEGES_ON_TABLE_:
case ComTdbExeUtilGetMetadataInfo::PRIVILEGES_ON_VIEW_:
case ComTdbExeUtilGetMetadataInfo::PRIVILEGES_ON_SEQUENCE_:
case ComTdbExeUtilGetMetadataInfo::PRIVILEGES_ON_LIBRARY_:
@@ -2737,24 +2923,40 @@ short ExExeUtilGetMetadataInfoTcb::work()
sizeOfqs = sizeof(getTrafPrivsOnObject);
// Determine the type of object
- NAString objType;
if (getMItdb().queryType_ == ComTdbExeUtilGetMetadataInfo::PRIVILEGES_ON_TABLE_)
- objType = COM_BASE_TABLE_OBJECT_LIT;
+ var = COM_BASE_TABLE_OBJECT_LIT;
else if (getMItdb().queryType_ == ComTdbExeUtilGetMetadataInfo::PRIVILEGES_ON_VIEW_)
- objType = COM_VIEW_OBJECT_LIT;
+ var = COM_VIEW_OBJECT_LIT;
else if (getMItdb().queryType_ == ComTdbExeUtilGetMetadataInfo::PRIVILEGES_ON_SEQUENCE_)
- objType = COM_SEQUENCE_GENERATOR_OBJECT_LIT;
+ var = COM_SEQUENCE_GENERATOR_OBJECT_LIT;
else if (getMItdb().queryType_ == ComTdbExeUtilGetMetadataInfo::PRIVILEGES_ON_LIBRARY_)
- objType = COM_LIBRARY_OBJECT_LIT;
+ var = COM_LIBRARY_OBJECT_LIT;
else
- objType = COM_USER_DEFINED_ROUTINE_OBJECT_LIT;
+ var = COM_USER_DEFINED_ROUTINE_OBJECT_LIT;
char buf[authList.length() + 100];
+
+ Int32 authID = 0;
+ if (getMItdb().getParam1())
+ {
+ authID = *currContext->getDatabaseUserID();
+ if (!(strcmp(getMItdb().getParam1(), currContext->getDatabaseUserName()) == 0))
+ authID = getAuthID(getMItdb().getParam1(), cat, sch, auths);
+ }
+
if (doPrivCheck)
{
- if (getMItdb().getParam1() &&
- (strcmp(getMItdb().getParam1(), currContext->getDatabaseUserName()) != 0))
- str_sprintf(buf, "and grantee_id = -2 ");
+ if (getMItdb().getParam1())
+ {
+ if ((CmpSeabaseDDLauth::isRoleID(authID) && !ComUser::currentUserHasRole(authID)) ||
+ (CmpSeabaseDDLauth::isUserID(authID) && authID != ComUser::getCurrentUser()))
+ {
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_NOT_AUTHORIZED);
+ step_ = HANDLE_ERROR_;
+ break;
+ }
+ str_sprintf(buf, " and grantee_id in %s ", authList.data());
+ }
else
str_sprintf(buf, " and grantee_id in %s ", authList.data());
privWhereClause = buf;
@@ -2767,7 +2969,6 @@ short ExExeUtilGetMetadataInfoTcb::work()
str_sprintf(buf, " and grantee_id in %s ", authList.data());
else
{
- Int32 authID = getAuthID(getMItdb().getParam1(), cat, sch, auths);
char *userRoleList = getRoleList(authID, cat, pmsch, role_usage);
if (userRoleList)
{
@@ -2790,7 +2991,7 @@ short ExExeUtilGetMetadataInfoTcb::work()
param_[6] = getMItdb().cat_;
param_[7] = getMItdb().sch_;
param_[8] = getMItdb().obj_;
- param_[9] = (char *)objType.data();
+ param_[9] = (char *)var.data();
param_[10] = (char *)privWhereClause.data();
param_[11] = cat;
param_[12] = pmsch;
@@ -2801,7 +3002,7 @@ short ExExeUtilGetMetadataInfoTcb::work()
param_[17] = getMItdb().cat_;
param_[18] = getMItdb().sch_;
param_[19] = getMItdb().obj_;
- param_[20] = (char *)objType.data();
+ param_[20] = (char *)var.data();
param_[21] = (char *)privWhereClause.data();
numOutputEntries_ = 2;
@@ -2809,46 +3010,101 @@ short ExExeUtilGetMetadataInfoTcb::work()
}
case ComTdbExeUtilGetMetadataInfo::INDEXES_FOR_USER_:
- case ComTdbExeUtilGetMetadataInfo::INDEXES_FOR_ROLE_:
{
- qs = getTrafIndexesForUser;
- sizeOfqs = sizeof(getTrafIndexesForUser);
+ qs = getTrafIndexesForAuth;
+ sizeOfqs = sizeof(getTrafIndexesForAuth);
- // Getting objects for the current user
- if (strcmp(getMItdb().getParam1(), currContext->getDatabaseUserName()) == 0)
- privWhereClause = getGrantedPrivCmd(authList, cat, NAString ("T.object_uid"));
+ // Get the authID associated with the specified user
+ Int32 authID = *currContext->getDatabaseUserID();
+ if (!(strcmp(getMItdb().getParam1(), currContext->getDatabaseUserName()) == 0))
+ authID = getAuthID(getMItdb().getParam1(), cat, sch, auths);
- // Getting objects for a user other than the current user
+ // If not a user, we are done, don't return data
+ if (!CmpSeabaseDDLauth::isUserID(authID))
+ {
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_IS_NOT_A_USER,
+ NULL, NULL, NULL,
+ getMItdb().getParam1());
+ step_ = HANDLE_ERROR_;
+ break;
+ }
+
+ // Non elevated user cannot view indexes for another user
+ if (doPrivCheck && authID != ComUser::getCurrentUser())
+ {
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_NOT_AUTHORIZED);
+ step_ = HANDLE_ERROR_;
+ break;
+ }
+
+ // Limit results to privileges allowed for specified user
+
+ if (authID == ComUser::getCurrentUser())
+ privWhereClause = getGrantedPrivCmd(authList, cat, NAString("T.object_uid"));
else
{
- if (doPrivCheck)
+ char *userRoleList = getRoleList(authID, cat, pmsch, role_usage);
+ if (userRoleList)
{
- // User cannot view privileges for another user
- ExRaiseSqlError(getHeap(), &diagsArea_, -1017);
- step_ = HANDLE_ERROR_;
- break;
+ privWhereClause = getGrantedPrivCmd(userRoleList, cat, NAString ("T.object_uid"));
+ NADELETEBASIC(userRoleList, getHeap());
}
else
{
- // Get the authID associated with the requested user
- Int32 authID = getAuthID(getMItdb().getParam1(), cat, sch, auths);
-
- // get the list of roles for this other user.
- char *userRoleList = getRoleList(authID, cat, pmsch, role_usage);
- if (userRoleList)
- {
- privWhereClause = getGrantedPrivCmd(userRoleList, cat, NAString ("T.object_uid"));
- NADELETEBASIC(userRoleList, getHeap());
- }
- else
- {
- // Unable to read metadata
- ExRaiseSqlError(getHeap(), &diagsArea_, -8001);
- step_ = HANDLE_ERROR_;
- break;
- }
+ // Unable to read metadata
+ ExRaiseSqlError(getHeap(), &diagsArea_, -8001);
+ step_ = HANDLE_ERROR_;
+ break;
}
}
+
+ param_[0] = cat;
+ param_[1] = sch;
+ param_[2] = indexes;
+ param_[3] = cat;
+ param_[4] = sch;
+ param_[5] = tab;
+ param_[6] = cat;
+ param_[7] = sch;
+ param_[8] = tab;
+ param_[9] = getMItdb().cat_;
+ param_[10] = (char *)privWhereClause.data();
+ }
+ break;
+
+ case ComTdbExeUtilGetMetadataInfo::INDEXES_FOR_ROLE_:
+ {
+ qs = getTrafIndexesForAuth;
+ sizeOfqs = sizeof(getTrafIndexesForAuth);
+
+ // Get the authID associated with the specified role
+ Int32 authID = *currContext->getDatabaseUserID();
+ if (!(strcmp(getMItdb().getParam1(), currContext->getDatabaseUserName()) == 0))
+ authID = getAuthID(getMItdb().getParam1(), cat, sch, auths);
+
+ // Verify that the authID is actually a role
+ if (!CmpSeabaseDDLauth::isRoleID(authID) && !ComUser::isPublicUserID(authID))
+ {
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_IS_NOT_A_ROLE,
+ NULL, NULL, NULL,
+ getMItdb().getParam1());
+ step_ = HANDLE_ERROR_;
+ break;
+ }
+
+ // Non elevated users need to be granted role
+ if (doPrivCheck && !ComUser::currentUserHasRole(authID))
+ {
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_NOT_AUTHORIZED);
+ step_ = HANDLE_ERROR_;
+ break;
+ }
+
+ // Only return indexes that role (authID) has been granted privs
+ char buf[30];
+ str_sprintf(buf, "(%d)", authID);
+ privWhereClause = getGrantedPrivCmd(buf, cat, NAString ("T.object_uid"));
+
param_[0] = cat;
param_[1] = sch;
param_[2] = indexes;
@@ -2863,78 +3119,61 @@ short ExExeUtilGetMetadataInfoTcb::work()
}
break;
+
case ComTdbExeUtilGetMetadataInfo::TABLES_FOR_USER_:
- case ComTdbExeUtilGetMetadataInfo::TABLES_FOR_ROLE_:
case ComTdbExeUtilGetMetadataInfo::VIEWS_FOR_USER_:
- case ComTdbExeUtilGetMetadataInfo::VIEWS_FOR_ROLE_:
{
- NABoolean isUser =
- (getMItdb().queryType_ == ComTdbExeUtilGetMetadataInfo::TABLES_FOR_USER_ ||
- getMItdb().queryType_ == ComTdbExeUtilGetMetadataInfo::VIEWS_FOR_USER_);
+ qs = getTrafObjectsForUser;
+ sizeOfqs = sizeof(getTrafObjectsForUser);
// Get the authID associated with the specified user
Int32 authID = *currContext->getDatabaseUserID();
if (!(strcmp(getMItdb().getParam1(), currContext->getDatabaseUserName()) == 0))
authID = getAuthID(getMItdb().getParam1(), cat, sch, auths);
- // Verify that the user is a user, or the role is a role
- NABoolean validAuth = FALSE;
- if (isUser && CmpSeabaseDDLauth::isUserID(authID))
- validAuth = TRUE;
- if (!isUser && CmpSeabaseDDLauth::isRoleID(authID))
- validAuth = TRUE;
-
- if (!validAuth)
+ // Verify that the authID is actually a user
+ if (!CmpSeabaseDDLauth::isUserID(authID))
{
- NAString authName = (isUser) ? "user" : "role";
- ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_IS_NOT_CORRECT_AUTHID,
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_IS_NOT_A_USER,
NULL, NULL, NULL,
- getMItdb().getParam1(),
- authName.data());
+ getMItdb().getParam1());
step_ = HANDLE_ERROR_;
break;
}
- qs = getTrafObjectsForUser;
- sizeOfqs = sizeof(getTrafObjectsForUser);
+ // Non elevated user cannot view objects for another user
+ if (doPrivCheck && authID != ComUser::getCurrentUser())
+ {
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_NOT_AUTHORIZED);
+ step_ = HANDLE_ERROR_;
+ break;
+ }
- NAString objType;
- if ((getMItdb().queryType_ == ComTdbExeUtilGetMetadataInfo::TABLES_FOR_USER_) ||
- (getMItdb().queryType_ == ComTdbExeUtilGetMetadataInfo::TABLES_FOR_ROLE_))
- objType = COM_BASE_TABLE_OBJECT_LIT;
+ if (getMItdb().queryType_ == ComTdbExeUtilGetMetadataInfo::TABLES_FOR_USER_)
+ var = COM_BASE_TABLE_OBJECT_LIT;
else
- objType = COM_VIEW_OBJECT_LIT;
+ var = COM_VIEW_OBJECT_LIT;
- // Getting objects for the current user
- if (strcmp(getMItdb().getParam1(), currContext->getDatabaseUserName()) == 0)
+ // Limit results to privileges allowed for specified user
+
+ if (authID == ComUser::getCurrentUser())
privWhereClause = getGrantedPrivCmd(authList, cat, NAString ("T.object_uid"));
// Getting objects for a user other than the current user
else
{
- if (doPrivCheck)
+ char *userRoleList = getRoleList(authID, cat, pmsch, role_usage);
+ if (userRoleList)
{
- // User cannot view privileges for another user
- ExRaiseSqlError(getHeap(), &diagsArea_, -1017);
- step_ = HANDLE_ERROR_;
- break;
+ privWhereClause = getGrantedPrivCmd(userRoleList, cat, NAString ("T.object_uid"));
+ NADELETEBASIC(userRoleList, getHeap());
}
- else
+ else
{
- // get the list of roles for this other user.
- char *userRoleList = getRoleList(authID, cat, pmsch, role_usage);
- if (userRoleList)
- {
- privWhereClause = getGrantedPrivCmd(userRoleList, cat, NAString ("T.object_uid"));
- NADELETEBASIC(userRoleList, getHeap());
- }
- else
- {
- // Unable to read metadata
- ExRaiseSqlError(getHeap(), &diagsArea_, -8001);
- step_ = HANDLE_ERROR_;
- break;
- }
+ // Unable to read metadata
+ ExRaiseSqlError(getHeap(), &diagsArea_, -8001);
+ step_ = HANDLE_ERROR_;
+ break;
}
}
@@ -2942,84 +3181,172 @@ short ExExeUtilGetMetadataInfoTcb::work()
param_[1] = sch;
param_[2] = tab;
param_[3] = getMItdb().cat_;
- param_[4] = (char *)objType.data();
+ param_[4] = (char *)var.data();
param_[5] = (char *)privWhereClause.data();
}
break;
- case ComTdbExeUtilGetMetadataInfo::LIBRARIES_FOR_USER_:
- case ComTdbExeUtilGetMetadataInfo::LIBRARIES_FOR_ROLE_:
+ case ComTdbExeUtilGetMetadataInfo::TABLES_FOR_ROLE_:
+ case ComTdbExeUtilGetMetadataInfo::VIEWS_FOR_ROLE_:
{
- NABoolean isUser =
- (getMItdb().queryType_ == ComTdbExeUtilGetMetadataInfo::LIBRARIES_FOR_USER_);
+ qs = getTrafObjectsForUser;
+ sizeOfqs = sizeof(getTrafObjectsForUser);
// Get the authID associated with the specified user
Int32 authID = *currContext->getDatabaseUserID();
if (!(strcmp(getMItdb().getParam1(), currContext->getDatabaseUserName()) == 0))
authID = getAuthID(getMItdb().getParam1(), cat, sch, auths);
- // Verify that the user is a user, or the role is a role
- NABoolean validAuth = FALSE;
- if (isUser && CmpSeabaseDDLauth::isUserID(authID))
- validAuth = TRUE;
- if (!isUser && CmpSeabaseDDLauth::isRoleID(authID))
- validAuth = TRUE;
-
- if (!validAuth)
+ // Verify that the specified authID is actually a role
+ if (!CmpSeabaseDDLauth::isRoleID(authID) && !ComUser::isPublicUserID(authID))
{
- NAString authName = (isUser) ? "user" : "role";
- ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_IS_NOT_CORRECT_AUTHID,
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_IS_NOT_A_ROLE,
NULL, NULL, NULL,
- getMItdb().getParam1(),
- authName.data());
+ getMItdb().getParam1());
+ step_ = HANDLE_ERROR_;
+ break;
+ }
+
+ // Non elevated users must be granted the specified role
+ if (doPrivCheck && !ComUser::currentUserHasRole(authID))
+ {
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_NOT_AUTHORIZED);
step_ = HANDLE_ERROR_;
break;
}
- qs = getTrafLibrariesForUser;
- sizeOfqs = sizeof(getTrafLibrariesForUser);
+ if (getMItdb().queryType_ == ComTdbExeUtilGetMetadataInfo::TABLES_FOR_ROLE_)
+ var = COM_BASE_TABLE_OBJECT_LIT;
+ else
+ var = COM_VIEW_OBJECT_LIT;
- // Getting libraries for the current user
- if (strcmp(getMItdb().getParam1(), currContext->getDatabaseUserName()) == 0)
- privWhereClause += getGrantedPrivCmd(authList, cat, NAString("R.udr_uid"));
+ // Only return objects where role (authID) has been granted privs
+ char buf[30];
+ str_sprintf(buf, "(%d)", authID);
+ privWhereClause = getGrantedPrivCmd(buf, cat, NAString ("T.object_uid"));
+
+ param_[0] = cat;
+ param_[1] = sch;
+ param_[2] = tab;
+ param_[3] = getMItdb().cat_;
+ param_[4] = (char *)var.data();
+ param_[5] = (char *)privWhereClause.data();
+ }
+ break;
+
+ case ComTdbExeUtilGetMetadataInfo::LIBRARIES_FOR_USER_:
+ {
+ qs = getTrafLibrariesForAuthQuery;
+ sizeOfqs = sizeof(getTrafLibrariesForAuthQuery);
- // Getting libraries for a user other than the current user
+ // Get the authID associated with the specified user
+ Int32 authID = *currContext->getDatabaseUserID();
+ if (!(strcmp(getMItdb().getParam1(), currContext->getDatabaseUserName()) == 0))
+ authID = getAuthID(getMItdb().getParam1(), cat, sch, auths);
+
+ // Verify that the specified authID is actually a user
+ if (!CmpSeabaseDDLauth::isUserID(authID))
+ {
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_IS_NOT_A_USER,
+ NULL, NULL, NULL,
+ getMItdb().getParam1());
+ step_ = HANDLE_ERROR_;
+ break;
+ }
+
+ // Non elevated user cannot view libraries for another user
+ if (doPrivCheck && authID != ComUser::getCurrentUser())
+ {
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_NOT_AUTHORIZED);
+ step_ = HANDLE_ERROR_;
+ break;
+ }
+
+ // Return libraries that are owned by the user/user's roles
+ // or that the user/user's role have been granted privileges
+
+ if (authID == ComUser::getCurrentUser())
+ privWhereClause += getGrantedPrivCmd(authList, cat);
else
{
- if (doPrivCheck)
+ char *userRoleList = getRoleList(authID, cat, pmsch, role_usage);
+ if (userRoleList)
{
- // User cannot view privileges for another user
- ExRaiseSqlError(getHeap(), &diagsArea_, -1017);
+ privWhereClause = getGrantedPrivCmd(userRoleList, cat);
+ NADELETEBASIC(userRoleList, getHeap());
+ }
+ else
+ {
+ // Unable to read metadata
+ ExRaiseSqlError(getHeap(), &diagsArea_, -8001);
step_ = HANDLE_ERROR_;
break;
}
+ }
+
+ param_[0] = cat;
+ param_[1] = sch;
+ param_[2] = tab;
+ param_[3] = getMItdb().cat_;
+ param_[4] = (char *) privWhereClause.data();
+ }
+ break ;
+
+ case ComTdbExeUtilGetMetadataInfo::LIBRARIES_FOR_ROLE_:
+ {
+ qs = getTrafLibrariesForAuthQuery;
+ sizeOfqs = sizeof(getTrafLibrariesForAuthQuery);
+
+ // Get the authID associated with the specified role
+ Int32 authID = *currContext->getDatabaseUserID();
+ if (!(strcmp(getMItdb().getParam1(), currContext->getDatabaseUserName()) == 0))
+ authID = getAuthID(getMItdb().getParam1(), cat, sch, auths);
+
+ // Verify that the specified authID is actually a role
+ if (!CmpSeabaseDDLauth::isRoleID(authID) && !ComUser::isPublicUserID(authID))
+ {
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_IS_NOT_A_ROLE,
+ NULL, NULL, NULL,
+ getMItdb().getParam1());
+ step_ = HANDLE_ERROR_;
+ break;
+ }
+
+ // Non elevated users must be granted role
+ if (doPrivCheck && !ComUser::currentUserHasRole(authID))
+ {
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_NOT_AUTHORIZED);
+ step_ = HANDLE_ERROR_;
+ break;
+ }
+
+ // Return libraries that are owned by the user/user's roles
+ // or that the user/user's role have been granted privileges
+
+ if (authID == ComUser::getCurrentUser())
+ privWhereClause += getGrantedPrivCmd(authList, cat);
+ else
+ {
+ char *userRoleList = getRoleList(authID, cat, pmsch, role_usage);
+ if (userRoleList)
+ {
+ privWhereClause = getGrantedPrivCmd(userRoleList, cat);
+ NADELETEBASIC(userRoleList, getHeap());
+ }
else
{
- // Get the list of roles for this other user.
- char *userRoleList = getRoleList(authID, cat, pmsch, role_usage);
- if (userRoleList)
- {
- privWhereClause = getGrantedPrivCmd(userRoleList, cat, NAString ("R.udr_uid"));
- NADELETEBASIC(userRoleList, getHeap());
- }
- else // return error?
- {
- // Unable to read metadata
- ExRaiseSqlError(getHeap(), &diagsArea_, -8001);
- step_ = HANDLE_ERROR_;
- break;
- }
+ // Unable to read metadata
+ ExRaiseSqlError(getHeap(), &diagsArea_, -8001);
+ step_ = HANDLE_ERROR_;
+ break;
}
}
param_[0] = cat;
param_[1] = sch;
param_[2] = tab;
- param_[3] = cat;
- param_[4] = sch;
- param_[5] = routine;
- param_[6] = getMItdb().cat_;
- param_[7] = (char *) privWhereClause.data();
+ param_[3] = getMItdb().cat_;
+ param_[4] = (char *) privWhereClause.data();
}
break ;
@@ -3056,38 +3383,41 @@ short ExExeUtilGetMetadataInfoTcb::work()
// Get the authID associated with the request's auth name
// If can't find authID, NA_UserIdDefault is returned which
// indicates an invalid authID.
- Int32 authID = getAuthID(getMItdb().getParam1(), cat, sch, auths);
+ Int32 authID = *currContext->getDatabaseUserID();
+ if (!(strcmp(getMItdb().getParam1(), currContext->getDatabaseUserName()) == 0))
+ authID = getAuthID(getMItdb().getParam1(), cat, sch, auths);
+
+ // if incorrect auth type, return error
+ if (!CmpSeabaseDDLauth::isRoleID(authID) && !CmpSeabaseDDLauth::isUserID(authID) &&
+ !ComUser::isPublicUserID(authID))
+ {
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_IS_NOT_CORRECT_AUTHID,
+ NULL, NULL, NULL,
+ getMItdb().getParam1(), "user or role");
+ step_ = HANDLE_ERROR_;
+ break;
+ }
if (doPrivCheck)
{
- // If asking for privileges for a user that has no privs
- // authName is invalid
- // authName is a user and not the current user
- // authName is a role and not one of the current user roles
- // add a predicate to make operation fail with no rows
- // This matches other "get" statement's behavior.
- NABoolean hasPriv = TRUE;
- if ((authID == NA_UserIdDefault) ||
- (CmpSeabaseDDLauth::isUserID(authID) &&
- (strcmp(getMItdb().getParam1(), currContext->getDatabaseUserName()) != 0)) ||
- (CmpSeabaseDDLauth::isRoleID(authID) &&
- !ComUser::currentUserHasRole(authID)))
- {
- privWhereClause += "and (grantee_id = -2) ";
- hasPriv = FALSE;
- }
- if (hasPriv)
- {
- privWhereClause += "and (grantee_name = '";
- privWhereClause += getMItdb().getParam1();
- privWhereClause += "'";
- if (CmpSeabaseDDLauth::isUserID(authID) && getMItdb().cascade())
- {
- privWhereClause += " or grantee_id in ";
- privWhereClause += authList.data();
- }
- privWhereClause += ")";
+ // If asking for privileges for a user that has no privs, return error
+ if ((CmpSeabaseDDLauth::isRoleID(authID) && !ComUser::currentUserHasRole(authID)) ||
+ (CmpSeabaseDDLauth::isUserID(authID) && authID != ComUser::getCurrentUser()))
+ {
+ ExRaiseSqlError(getHeap(), &diagsArea_, -CAT_NOT_AUTHORIZED);
+ step_ = HANDLE_ERROR_;
+ break;
}
+
+ privWhereClause += "and (grantee_name = '";
+ privWhereClause += getMItdb().getParam1();
+ privWhereClause += "'";
+ if (CmpSeabaseDDLauth::isUserID(authID) && getMItdb().cascade())
+ {
+ privWhereClause += " or grantee_id in ";
+ privWhereClause += authList.data();
+ }
+ privWhereClause += ")";
}
else
{
[2/4] trafodion git commit: Fixes for TRAFODION-3194 && TRAFODION-3195
Posted by rm...@apache.org.
http://git-wip-us.apache.org/repos/asf/trafodion/blob/afff9935/core/sql/regress/privs1/EXPECTED123
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs1/EXPECTED123 b/core/sql/regress/privs1/EXPECTED123
index dd8211b..ffc42d4 100644
--- a/core/sql/regress/privs1/EXPECTED123
+++ b/core/sql/regress/privs1/EXPECTED123
@@ -324,6 +324,9 @@ SQL_USER5
1 row(s) returned
--- SQL operation complete.
+>>get users for role "PUBLIC";
+
+--- SQL operation complete.
>>
>>get privileges for user sql_user1;
@@ -565,6 +568,34 @@ TRAFODION."T123SCH".TEAMS
6 row(s) returned
--- SQL operation complete.
+>>get tables for role t123_adminrole;
+
+Tables for Role T123_ADMINROLE
+==============================
+
+TRAFODION."T123SCH".GAMES
+TRAFODION."T123SCH".TEAMS
+
+=======================
+ 2 row(s) returned
+
+--- SQL operation complete.
+>>get tables for role t123_ownerrole;
+
+Tables for Role T123_OWNERROLE
+==============================
+
+TRAFODION."T123SCH".GAMES
+TRAFODION."T123SCH".PLAYERS
+TRAFODION."T123SCH".SB_HISTOGRAMS
+TRAFODION."T123SCH".SB_HISTOGRAM_INTERVALS
+TRAFODION."T123SCH".SB_PERSISTENT_SAMPLES
+TRAFODION."T123SCH".TEAMS
+
+=======================
+ 6 row(s) returned
+
+--- SQL operation complete.
>>
>>get indexes for user sql_user1;
@@ -613,6 +644,28 @@ TRAFODION."T123SCH".GAMES_VISITOR
1 row(s) returned
--- SQL operation complete.
+>>get indexes for role t123_adminrole;
+
+Indexes for Role T123_ADMINROLE
+===============================
+
+TRAFODION."T123SCH".GAMES_VISITOR
+
+=======================
+ 1 row(s) returned
+
+--- SQL operation complete.
+>>get indexes for role t123_ownerrole;
+
+Indexes for Role T123_OWNERROLE
+===============================
+
+TRAFODION."T123SCH".GAMES_VISITOR
+
+=======================
+ 1 row(s) returned
+
+--- SQL operation complete.
>>
>>get views for user sql_user1;
@@ -639,65 +692,20 @@ TRAFODION."T123SCH".PLAYERS_ON_TEAM
3 row(s) returned
--- SQL operation complete.
->>
->>get libraries for user sql_user1;
-
-Libraries for User SQL_USER1
-============================
-
-DB__LIBMGRNAME
-DB__LIBMGR_LIB_CPP
-
-=======================
- 2 row(s) returned
-
---- SQL operation complete.
->>get libraries for user sql_user2;
-
-Libraries for User SQL_USER2
-============================
-
-DB__LIBMGRNAME
-DB__LIBMGR_LIB_CPP
-
-=======================
- 2 row(s) returned
-
---- SQL operation complete.
->>get libraries for user sql_user3;
-
-Libraries for User SQL_USER3
-============================
-
-DB__LIBMGRNAME
-DB__LIBMGR_LIB_CPP
-
-=======================
- 2 row(s) returned
-
---- SQL operation complete.
->>get libraries for user sql_user4;
-
-Libraries for User SQL_USER4
-============================
-
-DB__LIBMGRNAME
-DB__LIBMGR_LIB_CPP
-
-=======================
- 2 row(s) returned
+>>get views for role t123_adminrole;
--- SQL operation complete.
->>get libraries for user sql_user5;
+>>get views for role t123_ownerrole;
-Libraries for User SQL_USER5
-============================
+Views for Role T123_OWNERROLE
+=============================
-DB__LIBMGRNAME
-DB__LIBMGR_LIB_CPP
+TRAFODION."T123SCH".GAMES_BY_PLAYER
+TRAFODION."T123SCH".HOME_TEAMS_GAMES
+TRAFODION."T123SCH".PLAYERS_ON_TEAM
=======================
- 2 row(s) returned
+ 3 row(s) returned
--- SQL operation complete.
>>
@@ -754,48 +762,24 @@ T123_PLANNERROLE
--- SQL operation complete.
>>get roles for user sql_user2;
-Roles for User SQL_USER2
-========================
-
-PUBLIC
-
-=======================
- 1 row(s) returned
+*** ERROR[1017] You are not authorized to perform this operation.
---- SQL operation complete.
+--- SQL operation failed with errors.
>>get roles for user sql_user3;
-Roles for User SQL_USER3
-========================
-
-PUBLIC
-
-=======================
- 1 row(s) returned
+*** ERROR[1017] You are not authorized to perform this operation.
---- SQL operation complete.
+--- SQL operation failed with errors.
>>get roles for user sql_user4;
-Roles for User SQL_USER4
-========================
-
-PUBLIC
-
-=======================
- 1 row(s) returned
+*** ERROR[1017] You are not authorized to perform this operation.
---- SQL operation complete.
+--- SQL operation failed with errors.
>>get roles for user sql_user5;
-Roles for User SQL_USER5
-========================
-
-PUBLIC
-
-=======================
- 1 row(s) returned
+*** ERROR[1017] You are not authorized to perform this operation.
---- SQL operation complete.
+--- SQL operation failed with errors.
>>
>>get users for role t123_adminrole;
@@ -821,9 +805,16 @@ SQL_USER1
--- SQL operation complete.
>>get users for role t123_dummyrole;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get users for role t123_ownerrole;
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>get users for role "PUBLIC";
+
--- SQL operation complete.
>>
>>get privileges for user sql_user1;
@@ -851,16 +842,24 @@ S------ TRAFODION.T123SCH.TEAMS <Column> TEAM_NUMBER
--- SQL operation complete.
>>get privileges for user sql_user2;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for user sql_user3;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for user sql_user4;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for user sql_user5;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>
>>get privileges for role t123_adminrole;
@@ -889,10 +888,14 @@ Privileges for Role T123_PLANNERROLE
--- SQL operation complete.
>>get privileges for role t123_dummyrole;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for role t123_ownerrole;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for role "PUBLIC";
Privileges for Role PUBLIC
@@ -944,6 +947,23 @@ TRAFODION."T123SCH".TEAMS
*** ERROR[1017] You are not authorized to perform this operation.
--- SQL operation failed with errors.
+>>get tables for role t123_adminrole;
+
+Tables for Role T123_ADMINROLE
+==============================
+
+TRAFODION."T123SCH".GAMES
+TRAFODION."T123SCH".TEAMS
+
+=======================
+ 2 row(s) returned
+
+--- SQL operation complete.
+>>get tables for role t123_ownerrole;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>
>>get indexes for user sql_user1;
@@ -976,6 +996,22 @@ TRAFODION."T123SCH".GAMES_VISITOR
*** ERROR[1017] You are not authorized to perform this operation.
--- SQL operation failed with errors.
+>>get indexes for role t123_adminrole;
+
+Indexes for Role T123_ADMINROLE
+===============================
+
+TRAFODION."T123SCH".GAMES_VISITOR
+
+=======================
+ 1 row(s) returned
+
+--- SQL operation complete.
+>>get indexes for role t123_ownerrole;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>
>>get views for user sql_user1;
@@ -1000,35 +1036,10 @@ TRAFODION."T123SCH".GAMES_VISITOR
*** ERROR[1017] You are not authorized to perform this operation.
--- SQL operation failed with errors.
->>
->>get libraries for user sql_user1;
-
-Libraries for User SQL_USER1
-============================
-
-DB__LIBMGRNAME
-DB__LIBMGR_LIB_CPP
-
-=======================
- 2 row(s) returned
+>>get views for role t123_adminrole;
--- SQL operation complete.
->>get libraries for user sql_user2;
-
-*** 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 user sql_user4;
-
-*** ERROR[1017] You are not authorized to perform this operation.
-
---- SQL operation failed with errors.
->>get libraries for user sql_user5;
+>>get views for role t123_ownerrole;
*** ERROR[1017] You are not authorized to perform this operation.
@@ -1077,15 +1088,9 @@ T123_PLANNERROLE
--- SQL operation complete.
>>get roles for user sql_user1;
-Roles for User SQL_USER1
-========================
-
-PUBLIC
-
-=======================
- 1 row(s) returned
+*** ERROR[1017] You are not authorized to perform this operation.
---- SQL operation complete.
+--- SQL operation failed with errors.
>>get roles for user sql_user2;
Roles for User SQL_USER2
@@ -1100,41 +1105,25 @@ T123_PLANNERROLE
--- SQL operation complete.
>>get roles for user sql_user3;
-Roles for User SQL_USER3
-========================
-
-PUBLIC
-
-=======================
- 1 row(s) returned
+*** ERROR[1017] You are not authorized to perform this operation.
---- SQL operation complete.
+--- SQL operation failed with errors.
>>get roles for user sql_user4;
-Roles for User SQL_USER4
-========================
-
-PUBLIC
-
-=======================
- 1 row(s) returned
+*** ERROR[1017] You are not authorized to perform this operation.
---- SQL operation complete.
+--- SQL operation failed with errors.
>>get roles for user sql_user5;
-Roles for User SQL_USER5
-========================
-
-PUBLIC
-
-=======================
- 1 row(s) returned
+*** ERROR[1017] You are not authorized to perform this operation.
---- SQL operation complete.
+--- SQL operation failed with errors.
>>
>>get users for role t123_adminrole;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get users for role t123_plannerrole;
Users granted Role T123_PLANNERROLE
@@ -1148,14 +1137,23 @@ SQL_USER2
--- SQL operation complete.
>>get users for role t123_dummyrole;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get users for role t123_ownerrole;
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>get users for role "PUBLIC";
+
--- SQL operation complete.
>>
>>get privileges for user sql_user1;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for user sql_user2;
Privileges for User SQL_USER2
@@ -1178,17 +1176,25 @@ S------ TRAFODION.T123SCH.TEAMS <Column> TEAM_NUMBER
--- SQL operation complete.
>>get privileges for user sql_user3;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for user sql_user4;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for user sql_user5;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>
>>get privileges for role t123_adminrole;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for role t123_plannerrole;
Privileges for Role T123_PLANNERROLE
@@ -1204,10 +1210,14 @@ Privileges for Role T123_PLANNERROLE
--- SQL operation complete.
>>get privileges for role t123_dummyrole;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for role t123_ownerrole;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for role "PUBLIC";
Privileges for Role PUBLIC
@@ -1259,6 +1269,16 @@ TRAFODION."T123SCH".TEAMS
*** ERROR[1017] You are not authorized to perform this operation.
--- SQL operation failed with errors.
+>>get tables for role t123_adminrole;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>get tables for role t123_ownerrole;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>
>>get indexes for user sql_user1;
@@ -1291,59 +1311,46 @@ TRAFODION."T123SCH".GAMES_VISITOR
*** ERROR[1017] You are not authorized to perform this operation.
--- SQL operation failed with errors.
->>
->>get views for user sql_user1;
+>>get indexes for role t123_adminrole;
*** ERROR[1017] You are not authorized to perform this operation.
--- SQL operation failed with errors.
->>get views for user sql_user2;
-
---- SQL operation complete.
->>get views for user sql_user3;
+>>get indexes for role t123_ownerrole;
*** ERROR[1017] You are not authorized to perform this operation.
--- SQL operation failed with errors.
->>get views for user sql_user4;
+>>
+>>get views for user sql_user1;
*** ERROR[1017] You are not authorized to perform this operation.
--- SQL operation failed with errors.
->>get views for user sql_user5;
+>>get views for user sql_user2;
+
+--- SQL operation complete.
+>>get views for user sql_user3;
*** ERROR[1017] You are not authorized to perform this operation.
--- SQL operation failed with errors.
->>
->>get libraries for user sql_user1;
+>>get views for user sql_user4;
*** ERROR[1017] You are not authorized to perform this operation.
--- SQL operation failed with errors.
->>get libraries for user sql_user2;
-
-Libraries for User SQL_USER2
-============================
-
-DB__LIBMGRNAME
-DB__LIBMGR_LIB_CPP
-
-=======================
- 2 row(s) returned
-
---- SQL operation complete.
->>get libraries for user sql_user3;
+>>get views for user sql_user5;
*** ERROR[1017] You are not authorized to perform this operation.
--- SQL operation failed with errors.
->>get libraries for user sql_user4;
+>>get views for role t123_adminrole;
*** ERROR[1017] You are not authorized to perform this operation.
--- SQL operation failed with errors.
->>get libraries for user sql_user5;
+>>get views for role t123_ownerrole;
*** ERROR[1017] You are not authorized to perform this operation.
@@ -1391,26 +1398,14 @@ PUBLIC
--- SQL operation complete.
>>get roles for user sql_user1;
-Roles for User SQL_USER1
-========================
-
-PUBLIC
-
-=======================
- 1 row(s) returned
+*** ERROR[1017] You are not authorized to perform this operation.
---- SQL operation complete.
+--- SQL operation failed with errors.
>>get roles for user sql_user2;
-Roles for User SQL_USER2
-========================
-
-PUBLIC
-
-=======================
- 1 row(s) returned
+*** ERROR[1017] You are not authorized to perform this operation.
---- SQL operation complete.
+--- SQL operation failed with errors.
>>get roles for user sql_user3;
Roles for User SQL_USER3
@@ -1424,46 +1419,49 @@ PUBLIC
--- SQL operation complete.
>>get roles for user sql_user4;
-Roles for User SQL_USER4
-========================
-
-PUBLIC
-
-=======================
- 1 row(s) returned
+*** ERROR[1017] You are not authorized to perform this operation.
---- SQL operation complete.
+--- SQL operation failed with errors.
>>get roles for user sql_user5;
-Roles for User SQL_USER5
-========================
-
-PUBLIC
-
-=======================
- 1 row(s) returned
+*** ERROR[1017] You are not authorized to perform this operation.
---- SQL operation complete.
+--- SQL operation failed with errors.
>>
>>get users for role t123_adminrole;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get users for role t123_plannerrole;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get users for role t123_dummyrole;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get users for role t123_ownerrole;
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>get users for role "PUBLIC";
+
--- SQL operation complete.
>>
>>get privileges for user sql_user1;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for user sql_user2;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for user sql_user3;
Privileges for User SQL_USER3
@@ -1483,23 +1481,35 @@ S------ TRAFODION.T123SCH.TEAMS <Column> TEAM_NUMBER
--- SQL operation complete.
>>get privileges for user sql_user4;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for user sql_user5;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>
>>get privileges for role t123_adminrole;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for role t123_plannerrole;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for role t123_dummyrole;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for role t123_ownerrole;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for role "PUBLIC";
Privileges for Role PUBLIC
@@ -1550,6 +1560,16 @@ TRAFODION."T123SCH".TEAMS
*** ERROR[1017] You are not authorized to perform this operation.
--- SQL operation failed with errors.
+>>get tables for role t123_adminrole;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>get tables for role t123_ownerrole;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>
>>get indexes for user sql_user1;
@@ -1574,6 +1594,16 @@ TRAFODION."T123SCH".TEAMS
*** ERROR[1017] You are not authorized to perform this operation.
--- SQL operation failed with errors.
+>>get indexes for role t123_adminrole;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>get indexes for role t123_ownerrole;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>
>>get views for user sql_user1;
@@ -1598,35 +1628,12 @@ TRAFODION."T123SCH".TEAMS
*** ERROR[1017] You are not authorized to perform this operation.
--- SQL operation failed with errors.
->>
->>get libraries for user sql_user1;
+>>get views for role t123_adminrole;
*** ERROR[1017] You are not authorized to perform this operation.
--- SQL operation failed with errors.
->>get libraries for user sql_user2;
-
-*** ERROR[1017] You are not authorized to perform this operation.
-
---- SQL operation failed with errors.
->>get libraries for user sql_user3;
-
-Libraries for User SQL_USER3
-============================
-
-DB__LIBMGRNAME
-DB__LIBMGR_LIB_CPP
-
-=======================
- 2 row(s) returned
-
---- SQL operation complete.
->>get libraries for user sql_user4;
-
-*** ERROR[1017] You are not authorized to perform this operation.
-
---- SQL operation failed with errors.
->>get libraries for user sql_user5;
+>>get views for role t123_ownerrole;
*** ERROR[1017] You are not authorized to perform this operation.
@@ -1674,37 +1681,19 @@ PUBLIC
--- SQL operation complete.
>>get roles for user sql_user1;
-Roles for User SQL_USER1
-========================
-
-PUBLIC
-
-=======================
- 1 row(s) returned
+*** ERROR[1017] You are not authorized to perform this operation.
---- SQL operation complete.
+--- SQL operation failed with errors.
>>get roles for user sql_user2;
-Roles for User SQL_USER2
-========================
-
-PUBLIC
-
-=======================
- 1 row(s) returned
+*** ERROR[1017] You are not authorized to perform this operation.
---- SQL operation complete.
+--- SQL operation failed with errors.
>>get roles for user sql_user3;
-Roles for User SQL_USER3
-========================
-
-PUBLIC
-
-=======================
- 1 row(s) returned
+*** ERROR[1017] You are not authorized to perform this operation.
---- SQL operation complete.
+--- SQL operation failed with errors.
>>get roles for user sql_user4;
Roles for User SQL_USER4
@@ -1718,38 +1707,49 @@ PUBLIC
--- SQL operation complete.
>>get roles for user sql_user5;
-Roles for User SQL_USER5
-========================
-
-PUBLIC
-
-=======================
- 1 row(s) returned
+*** ERROR[1017] You are not authorized to perform this operation.
---- SQL operation complete.
+--- SQL operation failed with errors.
>>
>>get users for role t123_adminrole;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get users for role t123_plannerrole;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get users for role t123_dummyrole;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get users for role t123_ownerrole;
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>get users for role "PUBLIC";
+
--- SQL operation complete.
>>
>>get privileges for user sql_user1;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for user sql_user2;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for user sql_user3;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for user sql_user4;
Privileges for User SQL_USER4
@@ -1771,20 +1771,30 @@ S------ TRAFODION.T123SCH.TEAMS <Column> TEAM_NUMBER
--- SQL operation complete.
>>get privileges for user sql_user5;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>
>>get privileges for role t123_adminrole;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for role t123_plannerrole;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for role t123_dummyrole;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for role t123_ownerrole;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for role "PUBLIC";
Privileges for Role PUBLIC
@@ -1836,6 +1846,16 @@ TRAFODION."T123SCH".TEAMS
*** ERROR[1017] You are not authorized to perform this operation.
--- SQL operation failed with errors.
+>>get tables for role t123_adminrole;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>get tables for role t123_ownerrole;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>
>>get indexes for user sql_user1;
@@ -1868,59 +1888,46 @@ TRAFODION."T123SCH".GAMES_VISITOR
*** ERROR[1017] You are not authorized to perform this operation.
--- SQL operation failed with errors.
->>
->>get views for user sql_user1;
+>>get indexes for role t123_adminrole;
*** ERROR[1017] You are not authorized to perform this operation.
--- SQL operation failed with errors.
->>get views for user sql_user2;
+>>get indexes for role t123_ownerrole;
*** ERROR[1017] You are not authorized to perform this operation.
--- SQL operation failed with errors.
->>get views for user sql_user3;
+>>
+>>get views for user sql_user1;
*** ERROR[1017] You are not authorized to perform this operation.
--- SQL operation failed with errors.
->>get views for user sql_user4;
-
---- SQL operation complete.
->>get views for user sql_user5;
+>>get views for user sql_user2;
*** ERROR[1017] You are not authorized to perform this operation.
--- SQL operation failed with errors.
->>
->>get libraries for user sql_user1;
+>>get views for user sql_user3;
*** ERROR[1017] You are not authorized to perform this operation.
--- SQL operation failed with errors.
->>get libraries for user sql_user2;
+>>get views for user sql_user4;
+
+--- SQL operation complete.
+>>get views for user sql_user5;
*** ERROR[1017] You are not authorized to perform this operation.
--- SQL operation failed with errors.
->>get libraries for user sql_user3;
+>>get views for role t123_adminrole;
*** ERROR[1017] You are not authorized to perform this operation.
--- SQL operation failed with errors.
->>get libraries for user sql_user4;
-
-Libraries for User SQL_USER4
-============================
-
-DB__LIBMGRNAME
-DB__LIBMGR_LIB_CPP
-
-=======================
- 2 row(s) returned
-
---- SQL operation complete.
->>get libraries for user sql_user5;
+>>get views for role t123_ownerrole;
*** ERROR[1017] You are not authorized to perform this operation.
@@ -1969,48 +1976,24 @@ T123_OWNERROLE
--- SQL operation complete.
>>get roles for user sql_user1;
-Roles for User SQL_USER1
-========================
-
-PUBLIC
-
-=======================
- 1 row(s) returned
+*** ERROR[1017] You are not authorized to perform this operation.
---- SQL operation complete.
+--- SQL operation failed with errors.
>>get roles for user sql_user2;
-Roles for User SQL_USER2
-========================
-
-PUBLIC
-
-=======================
- 1 row(s) returned
+*** ERROR[1017] You are not authorized to perform this operation.
---- SQL operation complete.
+--- SQL operation failed with errors.
>>get roles for user sql_user3;
-Roles for User SQL_USER3
-========================
-
-PUBLIC
-
-=======================
- 1 row(s) returned
+*** ERROR[1017] You are not authorized to perform this operation.
---- SQL operation complete.
+--- SQL operation failed with errors.
>>get roles for user sql_user4;
-Roles for User SQL_USER4
-========================
-
-PUBLIC
-
-=======================
- 1 row(s) returned
+*** ERROR[1017] You are not authorized to perform this operation.
---- SQL operation complete.
+--- SQL operation failed with errors.
>>get roles for user sql_user5;
Roles for User SQL_USER5
@@ -2026,13 +2009,19 @@ T123_OWNERROLE
>>
>>get users for role t123_adminrole;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get users for role t123_plannerrole;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get users for role t123_dummyrole;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get users for role t123_ownerrole;
Users granted Role T123_OWNERROLE
@@ -2044,19 +2033,30 @@ SQL_USER5
1 row(s) returned
--- SQL operation complete.
+>>get users for role "PUBLIC";
+
+--- SQL operation complete.
>>
>>get privileges for user sql_user1;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for user sql_user2;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for user sql_user3;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for user sql_user4;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for user sql_user5;
Privileges for User SQL_USER5
@@ -2087,13 +2087,19 @@ S------ TRAFODION.T123SCH.TEAMS <Column> TEAM_NUMBER
>>
>>get privileges for role t123_adminrole;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for role t123_plannerrole;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for role t123_dummyrole;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get privileges for role t123_ownerrole;
Privileges for Role T123_OWNERROLE
@@ -2168,6 +2174,27 @@ TRAFODION."T123SCH".TEAMS
6 row(s) returned
--- SQL operation complete.
+>>get tables for role t123_adminrole;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>get tables for role t123_ownerrole;
+
+Tables for Role T123_OWNERROLE
+==============================
+
+TRAFODION."T123SCH".GAMES
+TRAFODION."T123SCH".PLAYERS
+TRAFODION."T123SCH".SB_HISTOGRAMS
+TRAFODION."T123SCH".SB_HISTOGRAM_INTERVALS
+TRAFODION."T123SCH".SB_PERSISTENT_SAMPLES
+TRAFODION."T123SCH".TEAMS
+
+=======================
+ 6 row(s) returned
+
+--- SQL operation complete.
>>
>>get indexes for user sql_user1;
@@ -2200,6 +2227,22 @@ TRAFODION."T123SCH".GAMES_VISITOR
1 row(s) returned
--- SQL operation complete.
+>>get indexes for role t123_adminrole;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>get indexes for role t123_ownerrole;
+
+Indexes for Role T123_OWNERROLE
+===============================
+
+TRAFODION."T123SCH".GAMES_VISITOR
+
+=======================
+ 1 row(s) returned
+
+--- SQL operation complete.
>>
>>get views for user sql_user1;
@@ -2234,37 +2277,22 @@ TRAFODION."T123SCH".PLAYERS_ON_TEAM
3 row(s) returned
--- SQL operation complete.
->>
->>get libraries for user sql_user1;
+>>get views for role t123_adminrole;
*** ERROR[1017] You are not authorized to perform this operation.
--- SQL operation failed with errors.
->>get libraries for user sql_user2;
+>>get views for role t123_ownerrole;
-*** 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 user sql_user4;
-
-*** ERROR[1017] You are not authorized to perform this operation.
-
---- SQL operation failed with errors.
->>get libraries for user sql_user5;
-
-Libraries for User SQL_USER5
-============================
+Views for Role T123_OWNERROLE
+=============================
-DB__LIBMGRNAME
-DB__LIBMGR_LIB_CPP
+TRAFODION."T123SCH".GAMES_BY_PLAYER
+TRAFODION."T123SCH".HOME_TEAMS_GAMES
+TRAFODION."T123SCH".PLAYERS_ON_TEAM
=======================
- 2 row(s) returned
+ 3 row(s) returned
--- SQL operation complete.
>>
@@ -2282,28 +2310,23 @@ End of MXCI Session
--- SQL operation failed with errors.
>>get privileges for user unknown_user;
-*** ERROR[8732] UNKNOWN_USER is not a registered database user or role.
+*** ERROR[1340] UNKNOWN_USER is not a user.
--- SQL operation failed with errors.
>>get privileges for role unknown_role;
-*** ERROR[8732] UNKNOWN_ROLE is not a registered database user or role.
+*** ERROR[1339] UNKNOWN_ROLE is not a role.
--- SQL operation failed with errors.
>>get roles for user unknown_user;
-Roles for User UNKNOWN_USER
-===========================
-
-PUBLIC
+*** ERROR[1340] UNKNOWN_USER is not a user.
-=======================
- 1 row(s) returned
-
---- SQL operation complete.
+--- SQL operation failed with errors.
>>get users for role unknown_role;
--- SQL operation complete.
+>>
>>grant component privilege "SHOW" on sql_operations to "PUBLIC";
--- SQL operation complete.
http://git-wip-us.apache.org/repos/asf/trafodion/blob/afff9935/core/sql/regress/privs1/EXPECTED125
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs1/EXPECTED125 b/core/sql/regress/privs1/EXPECTED125
index 49b5283..d2c3501 100644
--- a/core/sql/regress/privs1/EXPECTED125
+++ b/core/sql/regress/privs1/EXPECTED125
@@ -1311,6 +1311,23 @@ TESTHIVE
1 row(s) returned
--- SQL operation complete.
+>>get roles;
+
+Roles
+=====
+
+DB__HBASEROLE
+DB__HIVEROLE
+DB__LIBMGRROLE
+DB__ROOTROLE
+PUBLIC
+T125_ADMINROLE
+T125_ROLE1
+
+=======================
+ 7 row(s) returned
+
+--- SQL operation complete.
>>
>>get indexes on table players;
@@ -1477,7 +1494,7 @@ TESTHIVE
Procedures for User SQL_USER1
=============================
-TESTHIVE
+TRAFODION."T125SCH2".TESTHIVE
=======================
1 row(s) returned
@@ -1491,7 +1508,7 @@ TESTHIVE
Functions for Role T125_ROLE1
=============================
-TRANSLATEBITMAP
+TRAFODION."T125SCH3".TRANSLATEBITMAP
=======================
1 row(s) returned
@@ -1502,7 +1519,7 @@ TRANSLATEBITMAP
Functions for Role T125_ADMINROLE
=================================
-TRANSLATEBITMAP
+TRAFODION."T125SCH3".TRANSLATEBITMAP
=======================
1 row(s) returned
@@ -1683,6 +1700,21 @@ TESTHIVE
--- SQL operation complete.
>>
+>>get schemas for user sql_user1, match 'T125SCH%';
+
+--- SQL operation complete.
+>>get schemas for role t125_adminrole, match 'T125SCH%';
+
+Schemas for Role T125_ADMINROLE
+===============================
+
+T125SCH3
+
+=======================
+ 1 row(s) returned
+
+--- SQL operation complete.
+>>
>>get functions for library t125_l1;
Functions for Library T125SCH3.T125_L1
@@ -1711,7 +1743,7 @@ T125SCH3.TESTHIVE
Procedures for User SQL_USER1
=============================
-TESTHIVE
+TRAFODION."T125SCH2".TESTHIVE
=======================
1 row(s) returned
@@ -1725,7 +1757,7 @@ TESTHIVE
Functions for Role T125_ROLE1
=============================
-TRANSLATEBITMAP
+TRAFODION."T125SCH3".TRANSLATEBITMAP
=======================
1 row(s) returned
@@ -1736,7 +1768,7 @@ TRANSLATEBITMAP
Functions for Role T125_ADMINROLE
=================================
-TRANSLATEBITMAP
+TRAFODION."T125SCH3".TRANSLATEBITMAP
=======================
1 row(s) returned
@@ -1807,6 +1839,18 @@ T125SCH3.PLAYERS_SEQUENCE
>>get procedures;
--- SQL operation complete.
+>>get roles;
+
+Roles
+=====
+
+PUBLIC
+T125_ADMINROLE
+
+=======================
+ 2 row(s) returned
+
+--- SQL operation complete.
>>
>>get indexes on table players;
@@ -1870,9 +1914,15 @@ T125SCH3.PLAYERS_SEQUENCE
--- SQL operation failed with errors.
>>get functions for role t125_adminrole;
-*** ERROR[1017] You are not authorized to perform this operation.
+Functions for Role T125_ADMINROLE
+=================================
---- SQL operation failed with errors.
+TRAFODION."T125SCH3".TRANSLATEBITMAP
+
+=======================
+ 1 row(s) returned
+
+--- SQL operation complete.
>>
>>set schema t125sch3;
@@ -1906,7 +1956,9 @@ SIDU-R- T125_ADMINROLE
--- SQL operation complete.
>>get privileges on table games for t125_role1;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get views in catalog trafodion, match 'T125SCH%';
Views in Catalog TRAFODION
@@ -1969,7 +2021,9 @@ Privileges on Sequence T125SCH3.PLAYERS_SEQUENCE
--- SQL operation complete.
>>get privileges on sequence players_sequence for t125_role1;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get libraries, match 'T125%';
Libraries in Schema TRAFODION.T125SCH3
@@ -2027,6 +2081,23 @@ TESTHIVE
--- SQL operation complete.
>>
+>>get schemas for user sql_user1, match 'T125SCH%';
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>get schemas for role t125_adminrole, match 'T125SCH%';
+
+Schemas for Role T125_ADMINROLE
+===============================
+
+T125SCH3
+
+=======================
+ 1 row(s) returned
+
+--- SQL operation complete.
+>>
>>get functions for library t125_l1;
Functions for Library T125SCH3.T125_L1
@@ -2067,9 +2138,15 @@ T125SCH3.TESTHIVE
--- SQL operation failed with errors.
>>get functions for role t125_adminrole;
-*** ERROR[1017] You are not authorized to perform this operation.
+Functions for Role T125_ADMINROLE
+=================================
---- SQL operation failed with errors.
+TRAFODION."T125SCH3".TRANSLATEBITMAP
+
+=======================
+ 1 row(s) returned
+
+--- SQL operation complete.
>>
>>exit;
@@ -2134,6 +2211,17 @@ T125SCH2.PLAYERS_SEQUENCE
>>get procedures;
--- SQL operation complete.
+>>get roles;
+
+Roles
+=====
+
+PUBLIC
+
+=======================
+ 1 row(s) returned
+
+--- SQL operation complete.
>>
>>get indexes on table players;
@@ -2209,7 +2297,7 @@ TESTHIVE
Procedures for User SQL_USER1
=============================
-TESTHIVE
+TRAFODION."T125SCH2".TESTHIVE
=======================
1 row(s) returned
@@ -2258,7 +2346,9 @@ Privileges on Table T125SCH3.GAMES
--- SQL operation complete.
>>get privileges on table games for t125_role1;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get views in catalog trafodion, match 'T125SCH%';
Views in Catalog TRAFODION
@@ -2283,7 +2373,9 @@ S------ SQL_USER1
--- SQL operation complete.
>>get privileges on view games_by_player for user sql_user8;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get indexes in schema t125sch3;
--- SQL operation complete.
@@ -2303,7 +2395,9 @@ T125SCH2.PLAYERS_SEQUENCE
--- SQL operation complete.
>>get privileges on sequence players_sequence for t125_role1;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get libraries, match 'T125%';
--- SQL operation complete.
@@ -2312,7 +2406,9 @@ T125SCH2.PLAYERS_SEQUENCE
--- SQL operation complete.
>>get privileges on library t125_l1 for user sql_user8;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get functions in schema t125sch3;
--- SQL operation complete.
@@ -2320,6 +2416,15 @@ T125SCH2.PLAYERS_SEQUENCE
--- SQL operation complete.
>>
+>>get schemas for user sql_user1, match 'T125SCH%';
+
+--- SQL operation complete.
+>>get schemas for role t125_adminrole, match 'T125SCH%';
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>
>>get functions for library t125_l1;
--- SQL operation complete.
@@ -2332,7 +2437,7 @@ T125SCH2.PLAYERS_SEQUENCE
Procedures for User SQL_USER1
=============================
-TESTHIVE
+TRAFODION."T125SCH2".TESTHIVE
=======================
1 row(s) returned
@@ -2426,6 +2531,18 @@ GAMES_BY_PLAYER
>>get procedures;
--- SQL operation complete.
+>>get roles;
+
+Roles
+=====
+
+PUBLIC
+T125_ROLE1
+
+=======================
+ 2 row(s) returned
+
+--- SQL operation complete.
>>
>>get indexes on table players;
@@ -2528,9 +2645,15 @@ GAMES_BY_PLAYER
--- SQL operation failed with errors.
>>get functions for role t125_role1;
-*** ERROR[1017] You are not authorized to perform this operation.
+Functions for Role T125_ROLE1
+=============================
---- SQL operation failed with errors.
+TRAFODION."T125SCH3".TRANSLATEBITMAP
+
+=======================
+ 1 row(s) returned
+
+--- SQL operation complete.
>>get functions for role t125_adminrole;
*** ERROR[1017] You are not authorized to perform this operation.
@@ -2565,6 +2688,14 @@ SIDU-R- SQL_USER2
--- SQL operation complete.
>>get privileges on table games for t125_role1;
+Privileges on Table T125SCH3.GAMES
+==================================
+
+SIDU-R- SQL_USER2
+
+=======================
+ 1 row(s) returned
+
--- SQL operation complete.
>>get views in catalog trafodion, match 'T125SCH%';
@@ -2592,7 +2723,9 @@ S------ SQL_USER2
--- SQL operation complete.
>>get privileges on view games_by_player for user sql_user8;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get indexes in schema t125sch3;
--- SQL operation complete.
@@ -2629,7 +2762,9 @@ Privileges on Library T125SCH3.T125_L1
--- SQL operation complete.
>>get privileges on library t125_l1 for user sql_user8;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get functions in schema t125sch3;
Functions in Schema TRAFODION.T125SCH3
@@ -2645,6 +2780,17 @@ TRANSLATEBITMAP
--- SQL operation complete.
>>
+>>get schemas for user sql_user1, match 'T125SCH%';
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>get schemas for role t125_adminrole, match 'T125SCH%';
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>
>>get functions for library t125_l1;
Functions for Library T125SCH3.T125_L1
@@ -2672,9 +2818,15 @@ T125SCH3.TRANSLATEBITMAP
--- SQL operation failed with errors.
>>get functions for role t125_role1;
-*** ERROR[1017] You are not authorized to perform this operation.
+Functions for Role T125_ROLE1
+=============================
---- SQL operation failed with errors.
+TRAFODION."T125SCH3".TRANSLATEBITMAP
+
+=======================
+ 1 row(s) returned
+
+--- SQL operation complete.
>>get functions for role t125_adminrole;
*** ERROR[1017] You are not authorized to perform this operation.
@@ -2738,6 +2890,18 @@ T125SCH3
>>get procedures;
--- SQL operation complete.
+>>get roles;
+
+Roles
+=====
+
+PUBLIC
+T125_ROLE1
+
+=======================
+ 2 row(s) returned
+
+--- SQL operation complete.
>>
>>get indexes on table players;
@@ -2803,9 +2967,15 @@ TEAMS
--- SQL operation complete.
>>get functions for role t125_role1;
-*** ERROR[1017] You are not authorized to perform this operation.
+Functions for Role T125_ROLE1
+=============================
---- SQL operation failed with errors.
+TRAFODION."T125SCH3".TRANSLATEBITMAP
+
+=======================
+ 1 row(s) returned
+
+--- SQL operation complete.
>>get functions for role t125_adminrole;
*** ERROR[1017] You are not authorized to perform this operation.
@@ -2840,7 +3010,9 @@ PLAYERS
--- SQL operation complete.
>>get privileges on view games_by_player for user sql_user8;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get indexes in schema t125sch3;
--- SQL operation complete.
@@ -2877,7 +3049,9 @@ Privileges on Library T125SCH3.T125_L1
--- SQL operation complete.
>>get privileges on library t125_l1 for user sql_user8;
---- SQL operation complete.
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>get functions in schema t125sch3;
Functions in Schema TRAFODION.T125SCH3
@@ -2893,6 +3067,17 @@ TRANSLATEBITMAP
--- SQL operation complete.
>>
+>>get schemas for user sql_user1, match 'T125SCH%';
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>get schemas for role t125_adminrole, match 'T125SCH%';
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>
>>get functions for library t125_l1;
Functions for Library T125SCH3.T125_L1
@@ -2918,9 +3103,15 @@ T125SCH3.TRANSLATEBITMAP
--- SQL operation complete.
>>get functions for role t125_role1;
-*** ERROR[1017] You are not authorized to perform this operation.
+Functions for Role T125_ROLE1
+=============================
---- SQL operation failed with errors.
+TRAFODION."T125SCH3".TRANSLATEBITMAP
+
+=======================
+ 1 row(s) returned
+
+--- SQL operation complete.
>>get functions for role t125_adminrole;
*** ERROR[1017] You are not authorized to perform this operation.
http://git-wip-us.apache.org/repos/asf/trafodion/blob/afff9935/core/sql/regress/privs1/EXPECTED141
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs1/EXPECTED141 b/core/sql/regress/privs1/EXPECTED141
index 598dc8e..1e1fb06 100644
--- a/core/sql/regress/privs1/EXPECTED141
+++ b/core/sql/regress/privs1/EXPECTED141
@@ -142,6 +142,9 @@
>>grant component privilege "CREATE" on sql_operations to t141_role2;
--- SQL operation complete.
+>>revoke component privilege "SHOW" on sql_operations from "PUBLIC";
+
+--- SQL operation complete.
>>
>>-- ============================================================================
>>--obey TEST141(test_private_user);
@@ -319,6 +322,39 @@ T141_USER3.U3T1 BT -2 T141_ROLE3 SIDU-R-
>>execute get_col_privs;
--- 0 row(s) selected.
+>>get schemas for role t141_role1;
+
+Schemas for Role T141_ROLE1
+===========================
+
+T141_USER1
+
+=======================
+ 1 row(s) returned
+
+--- SQL operation complete.
+>>get schemas for role t141_role2;
+
+Schemas for Role T141_ROLE2
+===========================
+
+T141_USER2
+
+=======================
+ 1 row(s) returned
+
+--- SQL operation complete.
+>>get schemas for role t141_role3;
+
+Schemas for Role T141_ROLE3
+===========================
+
+T141_USER3
+
+=======================
+ 1 row(s) returned
+
+--- SQL operation complete.
>>
>>-- t141_role2 has create privilege on all schemas.
>>-- have sql_user2 who belongs to t141_role2 create some tables in
@@ -416,6 +452,45 @@ CREATE VIEW TRAFODION.T141_USER1.U2V2 (C1, C2) AS
--- SQL operation failed with errors.
>>
+>>-- test get schemas for user and role
+>>-- schema owner sql_user2:
+>>-- first test returns t144_user2
+>>-- second test returns no rows
+>>-- schema owner t141_role: both tests return t144_user2
+>>get schemas for user sql_user2;
+
+Schemas for User SQL_USER2
+==========================
+
+T141_USER2
+
+=======================
+ 1 row(s) returned
+
+--- SQL operation complete.
+>>get schemas for role t141_role2;
+
+Schemas for Role T141_ROLE2
+===========================
+
+T141_USER2
+
+=======================
+ 1 row(s) returned
+
+--- SQL operation complete.
+>>
+>>-- these should fail with no priv
+>>get schemas for user sql_user1;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>get schemas for role t141_role1;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>
>>-- ============================================================================
>>exit;
@@ -664,6 +739,39 @@ T141_USER1.U1T4 0 DB__ROOT SQL_USER3 S------ ----
T141_USER1.U1T4 2 DB__ROOT SQL_USER3 S------ -------
--- 4 row(s) selected.
+>>get schemas for user sql_user1;
+
+Schemas for User SQL_USER1
+==========================
+
+T141_USER1
+
+=======================
+ 1 row(s) returned
+
+--- SQL operation complete.
+>>get schemas for user sql_user2;
+
+Schemas for User SQL_USER2
+==========================
+
+T141_USER2
+
+=======================
+ 1 row(s) returned
+
+--- SQL operation complete.
+>>get schemas for user sql_user3;
+
+Schemas for User SQL_USER3
+==========================
+
+T141_USER3
+
+=======================
+ 1 row(s) returned
+
+--- SQL operation complete.
>>
>>-- have sql_user2 create some tables in sql_user1's schema
>>-- have sql_user2 create some views, views that reference user1's objects fail
@@ -757,6 +865,37 @@ CREATE VIEW TRAFODION.T141_USER1.U2V1 AS
--- SQL operation failed with errors.
>>
+>>-- test get schemas for user and role
+>>-- schema owner sql_user2:
+>>-- first test returns t144_user2
+>>-- second test returns no rows
+>>-- schema owner t141_role: both tests return t144_user2
+>>get schemas for user sql_user2;
+
+Schemas for User SQL_USER2
+==========================
+
+T141_USER2
+
+=======================
+ 1 row(s) returned
+
+--- SQL operation complete.
+>>get schemas for role t141_role2;
+
+--- SQL operation complete.
+>>
+>>-- these should fail with no priv
+>>get schemas for user sql_user1;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>get schemas for role t141_role1;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
>>
>>-- ============================================================================
>>exit;
@@ -1028,9 +1167,9 @@ CREATE VIEW TRAFODION.T141_USER3.U3V3 AS
*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T141_USER1.U1T3.
--- SQL operation failed with errors.
->>get tables, match 'U3T%';
+>>get tables in schema t141_user1, match 'U3T%';
-Tables in Schema TRAFODION.T141_USER3
+Tables in Schema TRAFODION.T141_USER1
=====================================
U3T1
http://git-wip-us.apache.org/repos/asf/trafodion/blob/afff9935/core/sql/regress/privs1/TEST123
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs1/TEST123 b/core/sql/regress/privs1/TEST123
index 6214a55..d24ab6c 100644
--- a/core/sql/regress/privs1/TEST123
+++ b/core/sql/regress/privs1/TEST123
@@ -29,7 +29,15 @@
-- get roles for user
-- get privileges for role
-- get privileges for user
--- get [tables | indexes | libraries | views] for user
+-- get [tables | indexes | views] for user
+-- get [tables | indexes | views] for role
+--
+-- see privs1/TEST141 for
+-- get schemas for user
+-- get schemas for role
+-- see privs2/TEST144 for
+-- get [libraries, functions, table_mapping functions, procedures] for user
+-- get [libraries, functions, table_mapping functions, procedures] for role
--
-- Users and roles used
-- t123_adminrole: granted update and delete on games/teams
@@ -179,6 +187,7 @@ get users for role t123_adminrole;
get users for role t123_plannerrole;
get users for role t123_dummyrole;
get users for role t123_ownerrole;
+get users for role "PUBLIC";
get privileges for user sql_user1;
get privileges for user sql_user2;
@@ -197,24 +206,24 @@ get tables for user sql_user2;
get tables for user sql_user3;
get tables for user sql_user4;
get tables for user sql_user5;
+get tables for role t123_adminrole;
+get tables for role t123_ownerrole;
get indexes for user sql_user1;
get indexes for user sql_user2;
get indexes for user sql_user3;
get indexes for user sql_user4;
get indexes for user sql_user5;
+get indexes for role t123_adminrole;
+get indexes for role t123_ownerrole;
get views for user sql_user1;
get views for user sql_user2;
get views for user sql_user3;
get views for user sql_user4;
get views for user sql_user5;
-
-get libraries 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 user sql_user5;
+get views for role t123_adminrole;
+get views for role t123_ownerrole;
?section other
get privileges for user "PUBLIC";
@@ -222,3 +231,4 @@ get privileges for user unknown_user;
get privileges for role unknown_role;
get roles for user unknown_user;
get users for role unknown_role;
+
http://git-wip-us.apache.org/repos/asf/trafodion/blob/afff9935/core/sql/regress/privs1/TEST125
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs1/TEST125 b/core/sql/regress/privs1/TEST125
index 5a8e6e3..7c2344e 100644
--- a/core/sql/regress/privs1/TEST125
+++ b/core/sql/regress/privs1/TEST125
@@ -24,6 +24,7 @@
--
-- Tests that get statements return only what the user can see
-- get schemas (in catalog)
+-- get schemas for role (get schemas for user is part of privs1/TEST144)
-- get tables (in schemas)
-- get views (in catalogs)
-- get views (in schemas)
@@ -35,8 +36,8 @@
-- get privileges on view
-- get privileges on sequence
-- get privileges on library
--- (tests for get privileges on functions, table_mapping functions, and
--- procedures is part of privs2/TEST144)
+-- (tests for get privileges on functions, table_mapping functions,
+-- libraries and procedures is part of privs2/TEST144)
--
-- ============================================================================
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
@@ -266,6 +267,7 @@ get sequences, match 'T125SCH%';
get libraries, match 'T125%';
get functions;
get procedures;
+get roles;
get indexes on table players;
get views on table players;
@@ -306,6 +308,9 @@ get privileges on library t125_l1 for user sql_user8;
get functions in schema t125sch3;
get procedures;
+get schemas for user sql_user1, match 'T125SCH%';
+get schemas for role t125_adminrole, match 'T125SCH%';
+
get functions for library t125_l1;
get procedures for library t125_l2;
http://git-wip-us.apache.org/repos/asf/trafodion/blob/afff9935/core/sql/regress/privs1/TEST141
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs1/TEST141 b/core/sql/regress/privs1/TEST141
index 8abf627..06da585 100755
--- a/core/sql/regress/privs1/TEST141
+++ b/core/sql/regress/privs1/TEST141
@@ -24,6 +24,7 @@
-- ============================================================================
-- Functionality: Extended support for views for all levels:
-- column, object, component
+-- get schema for {user | role} authID
--
-- Added in response to JIRA 1100
--
@@ -55,6 +56,7 @@ drop schema if exists t141_user3 cascade;
revoke component privilege "CREATE" on sql_operations from sql_user2;
revoke component privilege "CREATE" on sql_operations from t141_role2;
revoke component privilege "DML_SELECT_METADATA" on sql_operations from "PUBLIC";
+grant component privilege "SHOW" on sql_operations to "PUBLIC";
revoke role t141_role1 from sql_user1;
revoke role t141_role2 from sql_user2;
@@ -188,6 +190,7 @@ grant role t141_role_md to sql_user1, sql_user2, sql_user3;
-- set up component privilege infrastructure
grant component privilege "CREATE" on sql_operations to sql_user2;
grant component privilege "CREATE" on sql_operations to t141_role2;
+revoke component privilege "SHOW" on sql_operations from "PUBLIC";
-- ============================================================================
?section create_objects
@@ -292,6 +295,9 @@ grant update on u1t4 to sql_user3;
grant select(c1, c3) on u1t4 to sql_user3;
execute get_obj_privs;
execute get_col_privs;
+get schemas for user sql_user1;
+get schemas for user sql_user2;
+get schemas for user sql_user3;
-- have sql_user2 create some tables in sql_user1's schema
-- have sql_user2 create some views, views that reference user1's objects fail
@@ -336,6 +342,9 @@ obey TEST141(create_objects);
set schema t141_user1;
execute get_obj_privs;
execute get_col_privs;
+get schemas for role t141_role1;
+get schemas for role t141_role2;
+get schemas for role t141_role3;
-- t141_role2 has create privilege on all schemas.
-- have sql_user2 who belongs to t141_role2 create some tables in
@@ -428,6 +437,17 @@ create view u2v1 as select u1.c1, u2.c1 from u2t1 as u2, t141_user1.u1t2 as u1;
-- user2 has no privs on u3t1
create view u2v1 as select * from t141_user1.u3t1;
+-- test get schemas for user and role
+-- schema owner sql_user2:
+-- first test returns t144_user2
+-- second test returns no rows
+-- schema owner t141_role: both tests return t144_user2
+get schemas for user sql_user2;
+get schemas for role t141_role2;
+
+-- these should fail with no priv
+get schemas for user sql_user1;
+get schemas for role t141_role1;
-- ============================================================================
?section user3_objects
@@ -455,7 +475,7 @@ create view u3v4 as select c1, c3 from t141_user1.u1t4;
-- following fail
create view u3v5 as select c1, c3 from t141_user1.u1t3;
-get tables, match 'U3T%';
+get tables in schema t141_user1, match 'U3T%';
get views;
-- ============================================================================
http://git-wip-us.apache.org/repos/asf/trafodion/blob/afff9935/core/sql/regress/privs2/EXPECTED135
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs2/EXPECTED135 b/core/sql/regress/privs2/EXPECTED135
index 744b645..055e89a 100644
--- a/core/sql/regress/privs2/EXPECTED135
+++ b/core/sql/regress/privs2/EXPECTED135
@@ -568,9 +568,7 @@ End of MXCI Session
--- SQL operation failed with errors.
>>revoke all on t135_t1 from sql_user3;
-*** ERROR[1025] Request failed. Dependent object TRAFODION.T135SCH_USER3.T135_V1_USER3 exists.
-
---- SQL operation failed with errors.
+--- SQL operation complete.
>>
>>sh sqlci -i "TEST135(user3_drops)" -u sql_user3;
>>drop table t135_t3 cascade;