Repository: trafodion Updated Branches: refs/heads/master 2c5f48018 -> 1b9cb1b85
http://git-wip-us.apache.org/repos/asf/trafodion/blob/88e2a742/core/sql/regress/privs1/EXPECTED125 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs1/EXPECTED125 b/core/sql/regress/privs1/EXPECTED125 index 8473a1c..d464042 100644 --- a/core/sql/regress/privs1/EXPECTED125 +++ b/core/sql/regress/privs1/EXPECTED125 @@ -98,6 +98,9 @@ >>create library t125_l1 file $$QUOTE$$ $$REGRRUNDIR$$/$$DLL$$ $$QUOTE$$ ; --- SQL operation complete. +>>grant usage on library t125_l1 to t125_role1; + +--- SQL operation complete. >>create function translateBitmap(bitmap largeint) returns (bitmap_string char >>(20)) +>language c parameter style sql external name 'translateBitmap' +>library t125_l1 @@ -127,6 +130,9 @@ +> file $$QUOTE$$ $$REGRRUNDIR$$/$$JARF$$ $$QUOTE$$; --- SQL operation complete. +>>grant all on library t125_l2 to sql_user8; + +--- SQL operation complete. >> >>create procedure TestHive( +> IN operation char(20), @@ -230,6 +236,9 @@ >>create library t125_l1 file $$QUOTE$$ $$REGRRUNDIR$$/$$DLL$$ $$QUOTE$$ ; --- SQL operation complete. +>>grant usage on library t125_l1 to t125_role1; + +--- SQL operation complete. >>create function translateBitmap(bitmap largeint) returns (bitmap_string char >>(20)) +>language c parameter style sql external name 'translateBitmap' +>library t125_l1 @@ -259,6 +268,9 @@ +> file $$QUOTE$$ $$REGRRUNDIR$$/$$JARF$$ $$QUOTE$$; --- SQL operation complete. +>>grant all on library t125_l2 to sql_user8; + +--- SQL operation complete. >> >>create procedure TestHive( +> IN operation char(20), @@ -362,6 +374,9 @@ >>create library t125_l1 file $$QUOTE$$ $$REGRRUNDIR$$/$$DLL$$ $$QUOTE$$ ; --- SQL operation complete. +>>grant usage on library t125_l1 to t125_role1; + +--- SQL operation complete. >>create function translateBitmap(bitmap largeint) returns (bitmap_string char >>(20)) +>language c parameter style sql external name 'translateBitmap' +>library t125_l1 @@ -391,6 +406,9 @@ +> file $$QUOTE$$ $$REGRRUNDIR$$/$$JARF$$ $$QUOTE$$; --- SQL operation complete. +>>grant all on library t125_l2 to sql_user8; + +--- SQL operation complete. >> >>create procedure TestHive( +> IN operation char(20), @@ -471,9 +489,12 @@ Privileges for Role T125_ROLE1 ============================== +----G-- TRAFODION.T125SCH1.T125_L1 S------ TRAFODION.T125SCH2.GAMES <Column> GAME_NUMBER +----G-- TRAFODION.T125SCH2.T125_L1 S------ TRAFODION.T125SCH2.TEAMS <Column> TEAM_NUMBER SIDU-R- TRAFODION.T125SCH3.PLAYERS +----G-- TRAFODION.T125SCH3.T125_L1 ------E TRAFODION.T125SCH3.TRANSLATEBITMAP --- SQL operation complete. @@ -500,13 +521,16 @@ Privileges for User SQL_USER2 ------E TRAFODION."_LIBMGR_".JDBC SIDU-R- TRAFODION.T125SCH1.GAMES S------ TRAFODION.T125SCH1.GAMES_BY_PLAYER +----G-- TRAFODION.T125SCH1.T125_L1 SIDU-R- TRAFODION.T125SCH2.GAMES S------ TRAFODION.T125SCH2.GAMES <Column> GAME_NUMBER S------ TRAFODION.T125SCH2.GAMES_BY_PLAYER +----G-- TRAFODION.T125SCH2.T125_L1 S------ TRAFODION.T125SCH2.TEAMS <Column> TEAM_NUMBER SIDU-R- TRAFODION.T125SCH3.GAMES S------ TRAFODION.T125SCH3.GAMES_BY_PLAYER SIDU-R- TRAFODION.T125SCH3.PLAYERS +----G-- TRAFODION.T125SCH3.T125_L1 ------E TRAFODION.T125SCH3.TRANSLATEBITMAP --- SQL operation complete. @@ -517,9 +541,12 @@ Privileges for User SQL_USER7 ------E TRAFODION."_LIBMGR_".EVENT_LOG_READER ------E TRAFODION."_LIBMGR_".JDBC +----G-- TRAFODION.T125SCH1.T125_L1 S------ TRAFODION.T125SCH2.GAMES <Column> GAME_NUMBER +----G-- TRAFODION.T125SCH2.T125_L1 S------ TRAFODION.T125SCH2.TEAMS <Column> TEAM_NUMBER SIDU-R- TRAFODION.T125SCH3.PLAYERS +----G-- TRAFODION.T125SCH3.T125_L1 ------E TRAFODION.T125SCH3.TRANSLATEBITMAP --- SQL operation complete. @@ -530,6 +557,8 @@ Privileges for User SQL_USER8 ------E TRAFODION."_LIBMGR_".EVENT_LOG_READER ------E TRAFODION."_LIBMGR_".JDBC +---UG-- TRAFODION.T125SCH1.T125_L2 +---UG-- TRAFODION.T125SCH2.T125_L2 SIDU-R- TRAFODION.T125SCH3.GAMES S----R- TRAFODION.T125SCH3.GAMES_BY_PLAYER S----R- TRAFODION.T125SCH3.HOME_TEAMS_GAMES @@ -547,6 +576,488 @@ SIDU-R- TRAFODION.T125SCH3.TEAMS --- SQL operation complete. >> +>>set schema t125sch1; + +--- SQL operation complete. +>>obey TEST125(get_privs); +>>get privileges on table games; + +Privileges on Table T125SCH1.GAMES +================================== + +SIDU-R- DB__ROOT +SIDU-R- SQL_USER2 + +--- SQL operation complete. +>>get privileges on table games for sql_user1; + +--- SQL operation complete. +>>get privileges on table games for sql_user2; + +Privileges on Table T125SCH1.GAMES +================================== + +SIDU-R- SQL_USER2 + +--- SQL operation complete. +>>get privileges on table games for sql_user7; + +--- SQL operation complete. +>>get privileges on table games for sql_user8; + +--- SQL operation complete. +>>get privileges on table games for t125_role1; + +--- SQL operation complete. +>>get privileges on table games for t125_adminrole; + +--- SQL operation complete. +>>get privileges on view games_by_player; + +Privileges on View T125SCH1.GAMES_BY_PLAYER +=========================================== + +S----R- DB__ROOT +S------ SQL_USER2 + +--- SQL operation complete. +>>get privileges on view games_by_player for sql_user1; + +--- SQL operation complete. +>>get privileges on view games_by_player for sql_user2; + +Privileges on View T125SCH1.GAMES_BY_PLAYER +=========================================== + +S------ SQL_USER2 + +--- SQL operation complete. +>>get privileges on view games_by_player for sql_user7; + +--- SQL operation complete. +>>get privileges on view games_by_player for sql_user8; + +--- SQL operation complete. +>>get privileges on view games_by_player for t125_role1; + +--- SQL operation complete. +>>get privileges on view games_by_player for t125_adminrole; + +--- SQL operation complete. +>>get privileges on library t125_l1; + +Privileges on Sequence T125SCH1.T125_L1 +======================================= + +---UG-- DB__ROOT +----G-- T125_ROLE1 + +--- SQL operation complete. +>>get privileges on library t125_l1 for sql_user1; + +--- SQL operation complete. +>>get privileges on library t125_l1 for sql_user2; + +Privileges on Sequence T125SCH1.T125_L1 +======================================= + +----G-- T125_ROLE1 + +--- SQL operation complete. +>>get privileges on library t125_l1 for sql_user7; + +Privileges on Sequence T125SCH1.T125_L1 +======================================= + +----G-- T125_ROLE1 + +--- SQL operation complete. +>>get privileges on library t125_l1 for sql_user8; + +--- SQL operation complete. +>>get privileges on library t125_l1 for t125_role1; + +Privileges on Sequence T125SCH1.T125_L1 +======================================= + +----G-- T125_ROLE1 + +--- SQL operation complete. +>>get privileges on library t125_l1 for t125_adminrole; + +--- SQL operation complete. +>>get privileges on sequence players_sequence; + +Privileges on Sequence T125SCH1.PLAYERS_SEQUENCE +================================================ + +----G-- DB__ROOT + +--- SQL operation complete. +>>get privileges on sequence players_sequence for sql_user1; + +--- SQL operation complete. +>>get privileges on sequence players_sequence for sql_user2; + +--- SQL operation complete. +>>get privileges on sequence players_sequence for sql_user7; + +--- SQL operation complete. +>>get privileges on sequence players_sequence for sql_user8; + +--- SQL operation complete. +>>get privileges on sequence players_sequence for t125_role1; + +--- SQL operation complete. +>>get privileges on sequence players_sequence for t125_adminrole; + +--- SQL operation complete. +>> +>>set schema t125sch2; + +--- SQL operation complete. +>>obey TEST125(get_privs); +>>get privileges on table games; + +Privileges on Table T125SCH2.GAMES +================================== + +SIDU-R- DB__ROOT +SIDU-R- SQL_USER2 +S------ T125_ROLE1 + +--- SQL operation complete. +>>get privileges on table games for sql_user1; + +--- SQL operation complete. +>>get privileges on table games for sql_user2; + +Privileges on Table T125SCH2.GAMES +================================== + +SIDU-R- SQL_USER2 +S------ T125_ROLE1 + +--- SQL operation complete. +>>get privileges on table games for sql_user7; + +Privileges on Table T125SCH2.GAMES +================================== + +S------ T125_ROLE1 + +--- SQL operation complete. +>>get privileges on table games for sql_user8; + +--- SQL operation complete. +>>get privileges on table games for t125_role1; + +Privileges on Table T125SCH2.GAMES +================================== + +S------ T125_ROLE1 + +--- SQL operation complete. +>>get privileges on table games for t125_adminrole; + +--- SQL operation complete. +>>get privileges on view games_by_player; + +Privileges on View T125SCH2.GAMES_BY_PLAYER +=========================================== + +S----R- DB__ROOT +S------ SQL_USER2 + +--- SQL operation complete. +>>get privileges on view games_by_player for sql_user1; + +--- SQL operation complete. +>>get privileges on view games_by_player for sql_user2; + +Privileges on View T125SCH2.GAMES_BY_PLAYER +=========================================== + +S------ SQL_USER2 + +--- SQL operation complete. +>>get privileges on view games_by_player for sql_user7; + +--- SQL operation complete. +>>get privileges on view games_by_player for sql_user8; + +--- SQL operation complete. +>>get privileges on view games_by_player for t125_role1; + +--- SQL operation complete. +>>get privileges on view games_by_player for t125_adminrole; + +--- SQL operation complete. +>>get privileges on library t125_l1; + +Privileges on Sequence T125SCH2.T125_L1 +======================================= + +---UG-- DB__ROOT +----G-- T125_ROLE1 + +--- SQL operation complete. +>>get privileges on library t125_l1 for sql_user1; + +--- SQL operation complete. +>>get privileges on library t125_l1 for sql_user2; + +Privileges on Sequence T125SCH2.T125_L1 +======================================= + +----G-- T125_ROLE1 + +--- SQL operation complete. +>>get privileges on library t125_l1 for sql_user7; + +Privileges on Sequence T125SCH2.T125_L1 +======================================= + +----G-- T125_ROLE1 + +--- SQL operation complete. +>>get privileges on library t125_l1 for sql_user8; + +--- SQL operation complete. +>>get privileges on library t125_l1 for t125_role1; + +Privileges on Sequence T125SCH2.T125_L1 +======================================= + +----G-- T125_ROLE1 + +--- SQL operation complete. +>>get privileges on library t125_l1 for t125_adminrole; + +--- SQL operation complete. +>>get privileges on sequence players_sequence; + +Privileges on Sequence T125SCH2.PLAYERS_SEQUENCE +================================================ + +----G-- DB__ROOT +----G-- SQL_USER1 + +--- SQL operation complete. +>>get privileges on sequence players_sequence for sql_user1; + +Privileges on Sequence T125SCH2.PLAYERS_SEQUENCE +================================================ + +----G-- SQL_USER1 + +--- SQL operation complete. +>>get privileges on sequence players_sequence for sql_user2; + +--- SQL operation complete. +>>get privileges on sequence players_sequence for sql_user7; + +--- SQL operation complete. +>>get privileges on sequence players_sequence for sql_user8; + +--- SQL operation complete. +>>get privileges on sequence players_sequence for t125_role1; + +--- SQL operation complete. +>>get privileges on sequence players_sequence for t125_adminrole; + +--- SQL operation complete. +>> +>>set schema t125sch3; + +--- SQL operation complete. +>>obey TEST125(get_privs); +>>get privileges on table games; + +Privileges on Table T125SCH3.GAMES +================================== + +-I----- SQL_USER1 +SIDU-R- SQL_USER2 +SIDU-R- T125_ADMINROLE + +--- SQL operation complete. +>>get privileges on table games for sql_user1; + +Privileges on Table T125SCH3.GAMES +================================== + +-I----- SQL_USER1 + +--- SQL operation complete. +>>get privileges on table games for sql_user2; + +Privileges on Table T125SCH3.GAMES +================================== + +SIDU-R- SQL_USER2 + +--- SQL operation complete. +>>get privileges on table games for sql_user7; + +--- SQL operation complete. +>>get privileges on table games for sql_user8; + +Privileges on Table T125SCH3.GAMES +================================== + +SIDU-R- T125_ADMINROLE + +--- SQL operation complete. +>>get privileges on table games for t125_role1; + +--- SQL operation complete. +>>get privileges on table games for t125_adminrole; + +Privileges on Table T125SCH3.GAMES +================================== + +SIDU-R- T125_ADMINROLE + +--- SQL operation complete. +>>get privileges on view games_by_player; + +Privileges on View T125SCH3.GAMES_BY_PLAYER +=========================================== + +S------ SQL_USER1 +S------ SQL_USER2 +S----R- T125_ADMINROLE + +--- SQL operation complete. +>>get privileges on view games_by_player for sql_user1; + +Privileges on View T125SCH3.GAMES_BY_PLAYER +=========================================== + +S------ SQL_USER1 + +--- SQL operation complete. +>>get privileges on view games_by_player for sql_user2; + +Privileges on View T125SCH3.GAMES_BY_PLAYER +=========================================== + +S------ SQL_USER2 + +--- SQL operation complete. +>>get privileges on view games_by_player for sql_user7; + +--- SQL operation complete. +>>get privileges on view games_by_player for sql_user8; + +Privileges on View T125SCH3.GAMES_BY_PLAYER +=========================================== + +S----R- T125_ADMINROLE + +--- SQL operation complete. +>>get privileges on view games_by_player for t125_role1; + +--- SQL operation complete. +>>get privileges on view games_by_player for t125_adminrole; + +Privileges on View T125SCH3.GAMES_BY_PLAYER +=========================================== + +S----R- T125_ADMINROLE + +--- SQL operation complete. +>>get privileges on library t125_l1; + +Privileges on Sequence T125SCH3.T125_L1 +======================================= + +---UG-- T125_ADMINROLE +----G-- T125_ROLE1 + +--- SQL operation complete. +>>get privileges on library t125_l1 for sql_user1; + +--- SQL operation complete. +>>get privileges on library t125_l1 for sql_user2; + +Privileges on Sequence T125SCH3.T125_L1 +======================================= + +----G-- T125_ROLE1 + +--- SQL operation complete. +>>get privileges on library t125_l1 for sql_user7; + +Privileges on Sequence T125SCH3.T125_L1 +======================================= + +----G-- T125_ROLE1 + +--- SQL operation complete. +>>get privileges on library t125_l1 for sql_user8; + +Privileges on Sequence T125SCH3.T125_L1 +======================================= + +---UG-- T125_ADMINROLE + +--- SQL operation complete. +>>get privileges on library t125_l1 for t125_role1; + +Privileges on Sequence T125SCH3.T125_L1 +======================================= + +----G-- T125_ROLE1 + +--- SQL operation complete. +>>get privileges on library t125_l1 for t125_adminrole; + +Privileges on Sequence T125SCH3.T125_L1 +======================================= + +---UG-- T125_ADMINROLE + +--- SQL operation complete. +>>get privileges on sequence players_sequence; + +Privileges on Sequence T125SCH3.PLAYERS_SEQUENCE +================================================ + +----G-- T125_ADMINROLE + +--- SQL operation complete. +>>get privileges on sequence players_sequence for sql_user1; + +--- SQL operation complete. +>>get privileges on sequence players_sequence for sql_user2; + +--- SQL operation complete. +>>get privileges on sequence players_sequence for sql_user7; + +--- SQL operation complete. +>>get privileges on sequence players_sequence for sql_user8; + +Privileges on Sequence T125SCH3.PLAYERS_SEQUENCE +================================================ + +----G-- T125_ADMINROLE + +--- SQL operation complete. +>>get privileges on sequence players_sequence for t125_role1; + +--- SQL operation complete. +>>get privileges on sequence players_sequence for t125_adminrole; + +Privileges on Sequence T125SCH3.PLAYERS_SEQUENCE +================================================ + +----G-- T125_ADMINROLE + +--- SQL operation complete. +>> +>> >>obey TEST125(get_tests); >>log LOG125; >>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; @@ -779,6 +1290,19 @@ SB_PERSISTENT_SAMPLES TEAMS --- SQL operation complete. +>>get privileges on table games; + +Privileges on Table T125SCH3.GAMES +================================== + +-I----- SQL_USER1 +SIDU-R- SQL_USER2 +SIDU-R- T125_ADMINROLE + +--- SQL operation complete. +>>get privileges on table games for t125_role1; + +--- SQL operation complete. >>get views in catalog trafodion, match 'T125SCH%'; Views in Catalog TRAFODION @@ -795,6 +1319,24 @@ T125SCH3.HOME_TEAMS_GAMES T125SCH3.PLAYERS_ON_TEAM --- SQL operation complete. +>>get privileges on view games_by_player; + +Privileges on View T125SCH3.GAMES_BY_PLAYER +=========================================== + +S------ SQL_USER1 +S------ SQL_USER2 +S----R- T125_ADMINROLE + +--- SQL operation complete. +>>get privileges on view games_by_player for user sql_user8; + +Privileges on View T125SCH3.GAMES_BY_PLAYER +=========================================== + +S----R- T125_ADMINROLE + +--- SQL operation complete. >>get indexes in schema t125sch3; Indexes in Schema TRAFODION.T125SCH3 @@ -813,6 +1355,17 @@ T125SCH2.PLAYERS_SEQUENCE T125SCH3.PLAYERS_SEQUENCE --- SQL operation complete. +>>get privileges on sequence players_sequence; + +Privileges on Sequence T125SCH3.PLAYERS_SEQUENCE +================================================ + +----G-- T125_ADMINROLE + +--- SQL operation complete. +>>get privileges on sequence players_sequence for t125_role1; + +--- SQL operation complete. >>get libraries, match 'T125%'; Libraries in Schema TRAFODION.T125SCH3 @@ -822,6 +1375,23 @@ T125_L1 T125_L2 --- SQL operation complete. +>>get privileges on library t125_l1; + +Privileges on Sequence T125SCH3.T125_L1 +======================================= + +---UG-- T125_ADMINROLE +----G-- T125_ROLE1 + +--- SQL operation complete. +>>get privileges on library t125_l1 for user sql_user8; + +Privileges on Sequence T125SCH3.T125_L1 +======================================= + +---UG-- T125_ADMINROLE + +--- SQL operation complete. >>get functions in schema t125sch3; Functions in Schema TRAFODION.T125SCH3 @@ -880,6 +1450,8 @@ SQL_USER8 Schemas in Catalog TRAFODION ============================ +T125SCH1 +T125SCH2 T125SCH3 --- SQL operation complete. @@ -975,6 +1547,17 @@ SB_PERSISTENT_SAMPLES TEAMS --- SQL operation complete. +>>get privileges on table games; + +Privileges on Table T125SCH3.GAMES +================================== + +SIDU-R- T125_ADMINROLE + +--- SQL operation complete. +>>get privileges on table games for t125_role1; + +--- SQL operation complete. >>get views in catalog trafodion, match 'T125SCH%'; Views in Catalog TRAFODION @@ -985,6 +1568,22 @@ T125SCH3.HOME_TEAMS_GAMES T125SCH3.PLAYERS_ON_TEAM --- SQL operation complete. +>>get privileges on view games_by_player; + +Privileges on View T125SCH3.GAMES_BY_PLAYER +=========================================== + +S----R- T125_ADMINROLE + +--- SQL operation complete. +>>get privileges on view games_by_player for user sql_user8; + +Privileges on View T125SCH3.GAMES_BY_PLAYER +=========================================== + +S----R- T125_ADMINROLE + +--- SQL operation complete. >>get indexes in schema t125sch3; --- SQL operation complete. @@ -996,6 +1595,17 @@ Sequences in catalog TRAFODION T125SCH3.PLAYERS_SEQUENCE --- SQL operation complete. +>>get privileges on sequence players_sequence; + +Privileges on Sequence T125SCH3.PLAYERS_SEQUENCE +================================================ + +----G-- T125_ADMINROLE + +--- SQL operation complete. +>>get privileges on sequence players_sequence for t125_role1; + +--- SQL operation complete. >>get libraries, match 'T125%'; Libraries in Schema TRAFODION.T125SCH3 @@ -1005,6 +1615,22 @@ T125_L1 T125_L2 --- SQL operation complete. +>>get privileges on library t125_l1; + +Privileges on Sequence T125SCH3.T125_L1 +======================================= + +---UG-- T125_ADMINROLE + +--- SQL operation complete. +>>get privileges on library t125_l1 for user sql_user8; + +Privileges on Sequence T125SCH3.T125_L1 +======================================= + +---UG-- T125_ADMINROLE + +--- SQL operation complete. >>get functions in schema t125sch3; Functions in Schema TRAFODION.T125SCH3 @@ -1169,6 +1795,17 @@ Tables in Schema TRAFODION.T125SCH3 GAMES --- SQL operation complete. +>>get privileges on table games; + +Privileges on Table T125SCH3.GAMES +================================== + +-I----- SQL_USER1 + +--- SQL operation complete. +>>get privileges on table games for t125_role1; + +--- SQL operation complete. >>get views in catalog trafodion, match 'T125SCH%'; Views in Catalog TRAFODION @@ -1177,6 +1814,17 @@ Views in Catalog TRAFODION T125SCH3.GAMES_BY_PLAYER --- SQL operation complete. +>>get privileges on view games_by_player; + +Privileges on View T125SCH3.GAMES_BY_PLAYER +=========================================== + +S------ SQL_USER1 + +--- SQL operation complete. +>>get privileges on view games_by_player for user sql_user8; + +--- SQL operation complete. >>get indexes in schema t125sch3; --- SQL operation complete. @@ -1188,9 +1836,21 @@ Sequences in catalog TRAFODION T125SCH2.PLAYERS_SEQUENCE --- SQL operation complete. +>>get privileges on sequence players_sequence; + +--- SQL operation complete. +>>get privileges on sequence players_sequence for t125_role1; + +--- SQL operation complete. >>get libraries, match 'T125%'; --- SQL operation complete. +>>get privileges on library t125_l1; + +--- SQL operation complete. +>>get privileges on library t125_l1 for user sql_user8; + +--- SQL operation complete. >>get functions in schema t125sch3; --- SQL operation complete. @@ -1356,6 +2016,17 @@ GAMES PLAYERS --- SQL operation complete. +>>get privileges on table games; + +Privileges on Table T125SCH3.GAMES +================================== + +SIDU-R- SQL_USER2 + +--- SQL operation complete. +>>get privileges on table games for t125_role1; + +--- SQL operation complete. >>get views in catalog trafodion, match 'T125SCH%'; Views in Catalog TRAFODION @@ -1366,12 +2037,29 @@ T125SCH2.GAMES_BY_PLAYER T125SCH3.GAMES_BY_PLAYER --- SQL operation complete. +>>get privileges on view games_by_player; + +Privileges on View T125SCH3.GAMES_BY_PLAYER +=========================================== + +S------ SQL_USER2 + +--- SQL operation complete. +>>get privileges on view games_by_player for user sql_user8; + +--- SQL operation complete. >>get indexes in schema t125sch3; --- SQL operation complete. >>get sequences in catalog trafodion, match 'T125SCH%'; --- SQL operation complete. +>>get privileges on sequence players_sequence; + +--- SQL operation complete. +>>get privileges on sequence players_sequence for t125_role1; + +--- SQL operation complete. >>get libraries, match 'T125%'; Libraries in Schema TRAFODION.T125SCH3 @@ -1380,6 +2068,17 @@ Libraries in Schema TRAFODION.T125SCH3 T125_L1 --- SQL operation complete. +>>get privileges on library t125_l1; + +Privileges on Sequence T125SCH3.T125_L1 +======================================= + +----G-- T125_ROLE1 + +--- SQL operation complete. +>>get privileges on library t125_l1 for user sql_user8; + +--- SQL operation complete. >>get functions in schema t125sch3; Functions in Schema TRAFODION.T125SCH3 @@ -1429,6 +2128,7 @@ SQL_USER7 Schemas in Catalog TRAFODION ============================ +T125SCH1 T125SCH2 T125SCH3 @@ -1521,15 +2221,33 @@ Tables in Schema TRAFODION.T125SCH3 PLAYERS --- SQL operation complete. +>>get privileges on table games; + +--- SQL operation complete. +>>get privileges on table games for t125_role1; + +--- SQL operation complete. >>get views in catalog trafodion, match 'T125SCH%'; --- SQL operation complete. +>>get privileges on view games_by_player; + +--- SQL operation complete. +>>get privileges on view games_by_player for user sql_user8; + +--- SQL operation complete. >>get indexes in schema t125sch3; --- SQL operation complete. >>get sequences in catalog trafodion, match 'T125SCH%'; --- SQL operation complete. +>>get privileges on sequence players_sequence; + +--- SQL operation complete. +>>get privileges on sequence players_sequence for t125_role1; + +--- SQL operation complete. >>get libraries, match 'T125%'; Libraries in Schema TRAFODION.T125SCH3 @@ -1538,6 +2256,17 @@ Libraries in Schema TRAFODION.T125SCH3 T125_L1 --- SQL operation complete. +>>get privileges on library t125_l1; + +Privileges on Sequence T125SCH3.T125_L1 +======================================= + +----G-- T125_ROLE1 + +--- SQL operation complete. +>>get privileges on library t125_l1 for user sql_user8; + +--- SQL operation complete. >>get functions in schema t125sch3; Functions in Schema TRAFODION.T125SCH3 http://git-wip-us.apache.org/repos/asf/trafodion/blob/88e2a742/core/sql/regress/privs1/TEST125 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs1/TEST125 b/core/sql/regress/privs1/TEST125 index de667ea..c29eb36 100644 --- a/core/sql/regress/privs1/TEST125 +++ b/core/sql/regress/privs1/TEST125 @@ -33,6 +33,10 @@ -- get functions, procedures, table mapping functions, libraries (in schema) -- get privileges on table -- 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) -- -- ============================================================================ cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; @@ -104,6 +108,13 @@ get privileges for user sql_user2; get privileges for user sql_user7; get privileges for user sql_user8; +set schema t125sch1; +obey TEST125(get_privs); +set schema t125sch2; +obey TEST125(get_privs); +set schema t125sch3; +obey TEST125(get_privs); + obey TEST125(get_tests); ?section create_db @@ -167,6 +178,7 @@ set pattern $$DLL$$ etest141.dll; set pattern $$QUOTE$$ ''''; create library t125_l1 file $$QUOTE$$ $$REGRRUNDIR$$/$$DLL$$ $$QUOTE$$ ; +grant usage on library t125_l1 to t125_role1; create function translateBitmap(bitmap largeint) returns (bitmap_string char (20)) language c parameter style sql external name 'translateBitmap' library t125_l1 @@ -179,6 +191,7 @@ set pattern $$JARF$$ TEST125_procs.jar; create library t125_l2 file $$QUOTE$$ $$REGRRUNDIR$$/$$JARF$$ $$QUOTE$$; +grant all on library t125_l2 to sql_user8; create procedure TestHive( IN operation char(20), @@ -192,6 +205,36 @@ create procedure TestHive( ISOLATE ; +?section get_privs +get privileges on table games; +get privileges on table games for sql_user1; +get privileges on table games for sql_user2; +get privileges on table games for sql_user7; +get privileges on table games for sql_user8; +get privileges on table games for t125_role1; +get privileges on table games for t125_adminrole; +get privileges on view games_by_player; +get privileges on view games_by_player for sql_user1; +get privileges on view games_by_player for sql_user2; +get privileges on view games_by_player for sql_user7; +get privileges on view games_by_player for sql_user8; +get privileges on view games_by_player for t125_role1; +get privileges on view games_by_player for t125_adminrole; +get privileges on library t125_l1; +get privileges on library t125_l1 for sql_user1; +get privileges on library t125_l1 for sql_user2; +get privileges on library t125_l1 for sql_user7; +get privileges on library t125_l1 for sql_user8; +get privileges on library t125_l1 for t125_role1; +get privileges on library t125_l1 for t125_adminrole; +get privileges on sequence players_sequence; +get privileges on sequence players_sequence for sql_user1; +get privileges on sequence players_sequence for sql_user2; +get privileges on sequence players_sequence for sql_user7; +get privileges on sequence players_sequence for sql_user8; +get privileges on sequence players_sequence for t125_role1; +get privileges on sequence players_sequence for t125_adminrole; + ?section clean_up drop schema t125sch1 cascade; drop schema t125sch2 cascade; @@ -243,10 +286,18 @@ get procedures in schema t125sch2; set schema t125sch3; get tables; +get privileges on table games; +get privileges on table games for t125_role1; get views in catalog trafodion, match 'T125SCH%'; +get privileges on view games_by_player; +get privileges on view games_by_player for user sql_user8; get indexes in schema t125sch3; get sequences in catalog trafodion, match 'T125SCH%'; +get privileges on sequence players_sequence; +get privileges on sequence players_sequence for t125_role1; get libraries, match 'T125%'; +get privileges on library t125_l1; +get privileges on library t125_l1 for user sql_user8; get functions in schema t125sch3; get procedures; http://git-wip-us.apache.org/repos/asf/trafodion/blob/88e2a742/core/sql/regress/privs2/EXPECTED144 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs2/EXPECTED144 b/core/sql/regress/privs2/EXPECTED144 index 6beefa1..d25b4a8 100644 --- a/core/sql/regress/privs2/EXPECTED144 +++ b/core/sql/regress/privs2/EXPECTED144 @@ -130,6 +130,18 @@ CREATE TABLE TRAFODION.T144USER1.CUSTOMERS --- SQL operation complete. >> +>>revoke component privilege "SHOW" on sql_operations from "PUBLIC"; + +--- SQL operation complete. +>>get privileges on component sql_operations for "PUBLIC"; + +Privilege information on Component SQL_OPERATIONS for PUBLIC +============================================================ + +CREATE_SCHEMA + +--- SQL operation complete. +>> >>obey TEST144(set_up); >>set schema "_PRIVMGR_MD_"; @@ -176,8 +188,70 @@ T144_TRANSLATEP 333 _TRAFODION_T144 -2 33334 ----G-- ----G-- --- 10 row(s) selected. +>>get privileges on function gen_phone; + +Privileges on Routine T144USER1.GEN_PHONE +========================================= + +------E SQL_USER1 + +--- SQL operation complete. +>>get privileges on function gen_random; + +Privileges on Routine T144USER1.GEN_RANDOM +========================================== + +------E SQL_USER1 + +--- SQL operation complete. +>>get privileges on function gen_time; + +Privileges on Routine T144USER1.GEN_TIME +======================================== + +------E SQL_USER1 + +--- SQL operation complete. +>>get privileges on procedure "_LIBMGR_".help; + +Privileges on Routine _LIBMGR_.HELP +=================================== + +------E DB__LIBMGRROLE +------E DB__ROOT + +--- SQL operation complete. +>>get privileges on table_mapping function "_LIBMGR_".event_log_reader; + +Privileges on Routine _LIBMGR_.EVENT_LOG_READER +=============================================== + +------E DB__ROOT +------E PUBLIC + +--- SQL operation complete. >> ->>-- user1 owns the udfs but does not have execute on procedure help +>>-- user1 can execute udfs (as owner), but cannot execute procedure help, +>>-- can execute table_mapping function event_log_reader through public grant +>>get privileges on function gen_phone for sql_user1; + +Privileges on Routine T144USER1.GEN_PHONE +========================================= + +------E SQL_USER1 + +--- SQL operation complete. +>>get privileges on procedure "_LIBMGR_".help for sql_user1; + +--- SQL operation complete. +>>get privileges on table_mapping function "_LIBMGR_".event_log_reader for sql_user1; + +Privileges on Routine _LIBMGR_.EVENT_LOG_READER +=============================================== + +------E PUBLIC + +--- SQL operation complete. >>sh sqlci -i "TEST144(cmds)" -u sql_user1; >>values (user); @@ -190,6 +264,24 @@ SQL_USER1 >>set schema t144user1; --- SQL operation complete. +>>-- should return privileges only for users that have execute privilege +>>get privileges on function gen_time; + +Privileges on Routine T144USER1.GEN_TIME +======================================== + +------E SQL_USER1 + +--- SQL operation complete. +>>-- should return no rows for users other than sql_user1 +>>get privileges on function gen_random for sql_user1; + +Privileges on Routine T144USER1.GEN_RANDOM +========================================== + +------E SQL_USER1 + +--- SQL operation complete. >>select customer_id, +> 'PHONE: ' || gen_phone(customer_id, customer_areacode) as phone, +> customer_areacode @@ -271,6 +363,14 @@ SQL_USER2 >>set schema t144user1; --- SQL operation complete. +>>-- should return privileges only for users that have execute privilege +>>get privileges on function gen_time; + +--- SQL operation complete. +>>-- should return no rows for users other than sql_user1 +>>get privileges on function gen_random for sql_user1; + +--- SQL operation complete. >>select customer_id, +> 'PHONE: ' || gen_phone(customer_id, customer_areacode) as phone, +> customer_areacode @@ -332,6 +432,14 @@ SQL_USER3 >>set schema t144user1; --- SQL operation complete. +>>-- should return privileges only for users that have execute privilege +>>get privileges on function gen_time; + +--- SQL operation complete. +>>-- should return no rows for users other than sql_user1 +>>get privileges on function gen_random for sql_user1; + +--- SQL operation complete. >>select customer_id, +> 'PHONE: ' || gen_phone(customer_id, customer_areacode) as phone, +> customer_areacode @@ -393,6 +501,14 @@ SQL_USER4 >>set schema t144user1; --- SQL operation complete. +>>-- should return privileges only for users that have execute privilege +>>get privileges on function gen_time; + +--- SQL operation complete. +>>-- should return no rows for users other than sql_user1 +>>get privileges on function gen_random for sql_user1; + +--- SQL operation complete. >>select customer_id, +> 'PHONE: ' || gen_phone(customer_id, customer_areacode) as phone, +> customer_areacode @@ -456,6 +572,38 @@ End of MXCI Session >>grant execute on procedure "_LIBMGR_".help to sql_user2 with grant option; --- SQL operation complete. +>>get privileges on function gen_phone for sql_user2; + +Privileges on Routine T144USER1.GEN_PHONE +========================================= + +------E SQL_USER2 + +--- SQL operation complete. +>>get privileges on function gen_random for sql_user2; + +Privileges on Routine T144USER1.GEN_RANDOM +========================================== + +------E SQL_USER2 + +--- SQL operation complete. +>>get privileges on function gen_time for user sql_user2; + +Privileges on Routine T144USER1.GEN_TIME +======================================== + +------E SQL_USER2 + +--- SQL operation complete. +>>get privileges on procedure "_LIBMGR_".help for user sql_user2; + +Privileges on Routine _LIBMGR_.HELP +=================================== + +------E SQL_USER2 + +--- SQL operation complete. >>execute get_privs; OBJECT_NAME GRANTOR_ID GRANTEE_ID GRANTED_PRIVS GRANTABLE_PRIVS @@ -477,6 +625,7 @@ _TRAFODION_T144 --- 13 row(s) selected. >> +>> >>-- user2 can execute >>sh sqlci -i "TEST144(cmds)" -u sql_user2; >>values (user); @@ -490,6 +639,19 @@ SQL_USER2 >>set schema t144user1; --- SQL operation complete. +>>-- should return privileges only for users that have execute privilege +>>get privileges on function gen_time; + +Privileges on Routine T144USER1.GEN_TIME +======================================== + +------E SQL_USER2 + +--- SQL operation complete. +>>-- should return no rows for users other than sql_user1 +>>get privileges on function gen_random for sql_user1; + +--- SQL operation complete. >>select customer_id, +> 'PHONE: ' || gen_phone(customer_id, customer_areacode) as phone, +> customer_areacode @@ -573,6 +735,14 @@ SQL_USER3 >>set schema t144user1; --- SQL operation complete. +>>-- should return privileges only for users that have execute privilege +>>get privileges on function gen_time; + +--- SQL operation complete. +>>-- should return no rows for users other than sql_user1 +>>get privileges on function gen_random for sql_user1; + +--- SQL operation complete. >>select customer_id, +> 'PHONE: ' || gen_phone(customer_id, customer_areacode) as phone, +> customer_areacode @@ -640,6 +810,33 @@ End of MXCI Session *** ERROR[1012] No privileges were granted. SQL_USER2 lacks grant option on the specified privileges. --- SQL operation failed with errors. +>>get privileges on function gen_phone for user sql_user3; + +Privileges on Routine T144USER1.GEN_PHONE +========================================= + +------E SQL_USER3 + +--- SQL operation complete. +>>get privileges on function gen_random for user sql_user3; + +Privileges on Routine T144USER1.GEN_RANDOM +========================================== + +------E SQL_USER3 + +--- SQL operation complete. +>>get privileges on function gen_time for user sql_user3; + +--- SQL operation complete. +>>get privileges on procedure "_LIBMGR_".help for user sql_user3; + +Privileges on Routine _LIBMGR_.HELP +=================================== + +------E SQL_USER3 + +--- SQL operation complete. >>execute get_privs; OBJECT_NAME GRANTOR_ID GRANTEE_ID GRANTED_PRIVS GRANTABLE_PRIVS @@ -676,6 +873,14 @@ SQL_USER3 >>set schema t144user1; --- SQL operation complete. +>>-- should return privileges only for users that have execute privilege +>>get privileges on function gen_time; + +--- SQL operation complete. +>>-- should return no rows for users other than sql_user1 +>>get privileges on function gen_random for sql_user1; + +--- SQL operation complete. >>select customer_id, +> 'PHONE: ' || gen_phone(customer_id, customer_areacode) as phone, +> customer_areacode @@ -762,6 +967,41 @@ End of MXCI Session >>grant role t144role1 to sql_user4; --- SQL operation complete. +>>get privileges on function gen_phone for user sql_user4; + +--- SQL operation complete. +>>get privileges on function gen_random for user sql_user4; + +Privileges on Routine T144USER1.GEN_RANDOM +========================================== + +------E T144ROLE1 + +--- SQL operation complete. +>>get privileges on function gen_time for user sql_user4; + +Privileges on Routine T144USER1.GEN_TIME +======================================== + +------E T144ROLE1 + +--- SQL operation complete. +>>get privileges on procedure "_LIBMGR_".help for user sql_user4; + +Privileges on Routine _LIBMGR_.HELP +=================================== + +------E T144ROLE1 + +--- SQL operation complete. +>>get privileges on function gen_random for t144role1; + +Privileges on Routine T144USER1.GEN_RANDOM +========================================== + +------E T144ROLE1 + +--- SQL operation complete. >>execute get_privs; OBJECT_NAME GRANTOR_ID GRANTEE_ID GRANTED_PRIVS GRANTABLE_PRIVS @@ -801,6 +1041,19 @@ SQL_USER4 >>set schema t144user1; --- SQL operation complete. +>>-- should return privileges only for users that have execute privilege +>>get privileges on function gen_time; + +Privileges on Routine T144USER1.GEN_TIME +======================================== + +------E T144ROLE1 + +--- SQL operation complete. +>>-- should return no rows for users other than sql_user1 +>>get privileges on function gen_random for sql_user1; + +--- SQL operation complete. >>select customer_id, +> 'PHONE: ' || gen_phone(customer_id, customer_areacode) as phone, +> customer_areacode @@ -861,6 +1114,50 @@ RM - Remove a library file. SHOWDDL PROCEDURE [SCHEMA NAME.]RM for more info. End of MXCI Session >> +>>get privileges on function gen_phone; + +Privileges on Routine T144USER1.GEN_PHONE +========================================= + +------E SQL_USER1 +------E SQL_USER2 +------E SQL_USER3 +------E SQL_USER5 + +--- SQL operation complete. +>>get privileges on function gen_random; + +Privileges on Routine T144USER1.GEN_RANDOM +========================================== + +------E SQL_USER1 +------E SQL_USER2 +------E SQL_USER3 +------E T144ROLE1 + +--- SQL operation complete. +>>get privileges on function gen_time; + +Privileges on Routine T144USER1.GEN_TIME +======================================== + +------E SQL_USER1 +------E SQL_USER2 +------E T144ROLE1 + +--- SQL operation complete. +>>get privileges on procedure "_LIBMGR_".help; + +Privileges on Routine _LIBMGR_.HELP +=================================== + +------E DB__LIBMGRROLE +------E DB__ROOT +------E SQL_USER2 +------E SQL_USER3 +------E T144ROLE1 + +--- SQL operation complete. >>obey TEST144(test_revokes); >>-- >>============================================================================ >>-- verify that revoking privileges handle EXECUTE privilege correctly @@ -919,7 +1216,7 @@ _TRAFODION_T144 --- SQL operation complete. >>drop role t144role1; -*** ERROR[1228] Cannot drop role. Role T144ROLE1 has been granted privileges on TRAFODION.T144USER1.GEN_RANDOM. +*** ERROR[1228] Cannot drop role. Role T144ROLE1 has been granted privileges on TRAFODION._LIBMGR_.HELP. --- SQL operation failed with errors. >>revoke execute on function gen_random from t144role1; http://git-wip-us.apache.org/repos/asf/trafodion/blob/88e2a742/core/sql/regress/privs2/EXPECTED146 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs2/EXPECTED146 b/core/sql/regress/privs2/EXPECTED146 index 2baa2f8..7bf43b0 100644 --- a/core/sql/regress/privs2/EXPECTED146 +++ b/core/sql/regress/privs2/EXPECTED146 @@ -50,6 +50,10 @@ --- SQL operation complete. >> +>>create hbase table t146t3 ( column family 'cf'); + +--- SQL operation complete. +>> >>-- These operations are not allowed on hbase tables >>update hbase."_CELL_".t146t1 set col_value = '4' where row_id = '2' and >>col_name = 'visitor_team'; @@ -87,7 +91,7 @@ CREATE TABLE HBASE."_CELL_".T146T1 CREATE HBASE TABLE T146T1 ( COLUMN FAMILY '#1') REGISTER /*INTERNAL*/ HBASE TABLE T146T1; -/* ObjectUID = 2009891765934276385 */ +/* ObjectUID = 7181713655023564985 */ -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON HBASE."_CELL_".T146T1 TO DB__HBASEROLE WITH GRANT OPTION; @@ -110,7 +114,7 @@ CREATE TABLE HBASE."_ROW_".T146T1 CREATE HBASE TABLE T146T1 ( COLUMN FAMILY '#1') REGISTER /*INTERNAL*/ HBASE TABLE T146T1; -/* ObjectUID = 2009891765934276410 */ +/* ObjectUID = 7181713655023565067 */ -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON HBASE."_ROW_".T146T1 TO DB__HBASEROLE WITH GRANT OPTION; @@ -138,7 +142,9 @@ HBase Registered Tables in Catalog TRAFODION ============================================ "_CELL_".T146T1 +"_CELL_".T146T3 "_ROW_".T146T1 +"_ROW_".T146T3 --- SQL operation complete. >> @@ -163,6 +169,15 @@ Privilege information on Component SQL_OPERATIONS for PUBLIC CREATE_SCHEMA --- SQL operation complete. +>>get external hbase objects, match '%T146%'; + +External HBase objects +====================== + +T146T1 +T146T3 + +--- SQL operation complete. >> >>obey TEST146(test_grants); >>-- ================================================================= @@ -291,7 +306,7 @@ CREATE TABLE HBASE."_CELL_".T146T1 CREATE HBASE TABLE T146T1 ( COLUMN FAMILY '#1') REGISTER /*INTERNAL*/ HBASE TABLE T146T1; -/* ObjectUID = 2009891765934276385 */ +/* ObjectUID = 7181713655023564985 */ -- 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; @@ -319,7 +334,7 @@ CREATE TABLE HBASE."_ROW_".T146T1 CREATE HBASE TABLE T146T1 ( COLUMN FAMILY '#1') REGISTER /*INTERNAL*/ HBASE TABLE T146T1; -/* ObjectUID = 2009891765934276410 */ +/* ObjectUID = 7181713655023565067 */ -- 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; @@ -492,6 +507,18 @@ ROW_ID COL_VALUE --- 6 row(s) selected. >> +>>get external hbase objects, match '%T146%'; + +--- SQL operation complete. +>>get hbase registered tables in catalog trafodion, match '%T146%'; + +HBase Registered Tables in Catalog TRAFODION +============================================ + +"_CELL_".T146T1 +"_ROW_".T146T1 + +--- SQL operation complete. >>exit; End of MXCI Session @@ -630,6 +657,18 @@ ROW_ID COL_VALUE --- 6 row(s) selected. >> +>>get external hbase objects, match '%T146%'; + +--- SQL operation complete. +>>get hbase registered tables in catalog trafodion, match '%T146%'; + +HBase Registered Tables in Catalog TRAFODION +============================================ + +"_CELL_".T146T1 +"_ROW_".T146T1 + +--- SQL operation complete. >>exit; End of MXCI Session @@ -773,6 +812,18 @@ ROW_ID COL_VALUE --- 6 row(s) selected. >> +>>get external hbase objects, match '%T146%'; + +--- SQL operation complete. +>>get hbase registered tables in catalog trafodion, match '%T146%'; + +HBase Registered Tables in Catalog TRAFODION +============================================ + +"_CELL_".T146T1 +"_ROW_".T146T1 + +--- SQL operation complete. >>exit; End of MXCI Session @@ -877,6 +928,12 @@ SQL_USER4 *** ERROR[15017] Statement S1 was not found. >> +>>get external hbase objects, match '%T146%'; + +--- SQL operation complete. +>>get hbase registered tables in catalog trafodion, match '%T146%'; + +--- SQL operation complete. >>exit; End of MXCI Session @@ -1018,6 +1075,26 @@ ROW_ID COL_VALUE --- 6 row(s) selected. >> +>>get external hbase objects, match '%T146%'; + +External HBase objects +====================== + +T146T1 +T146T3 + +--- SQL operation complete. +>>get hbase registered tables in catalog trafodion, match '%T146%'; + +HBase Registered Tables in Catalog TRAFODION +============================================ + +"_CELL_".T146T1 +"_CELL_".T146T3 +"_ROW_".T146T1 +"_ROW_".T146T3 + +--- SQL operation complete. >>exit; End of MXCI Session @@ -1172,6 +1249,18 @@ ROW_ID COL_VALUE --- 6 row(s) selected. >> +>>get external hbase objects, match '%T146%'; + +--- SQL operation complete. +>>get hbase registered tables in catalog trafodion, match '%T146%'; + +HBase Registered Tables in Catalog TRAFODION +============================================ + +"_CELL_".T146T1 +"_ROW_".T146T1 + +--- SQL operation complete. >>exit; End of MXCI Session @@ -1279,6 +1368,12 @@ SQL_USER1 *** ERROR[15017] Statement S1 was not found. >> +>>get external hbase objects, match '%T146%'; + +--- SQL operation complete. +>>get hbase registered tables in catalog trafodion, match '%T146%'; + +--- SQL operation complete. >>exit; End of MXCI Session @@ -1419,6 +1514,17 @@ ROW_ID COL_VALUE --- 6 row(s) selected. >> +>>get external hbase objects, match '%T146%'; + +--- SQL operation complete. +>>get hbase registered tables in catalog trafodion, match '%T146%'; + +HBase Registered Tables in Catalog TRAFODION +============================================ + +"_CELL_".T146T1 + +--- SQL operation complete. >>exit; End of MXCI Session @@ -1473,8 +1579,10 @@ Privileges for Role DB__HBASEROLE SIDU-R- HBASE._CELL_.T146T1 SIDU-R- HBASE._CELL_.T146T2 +SIDU-R- HBASE._CELL_.T146T3 SIDU-R- HBASE._ROW_.T146T1 SIDU-R- HBASE._ROW_.T146T2 +SIDU-R- HBASE._ROW_.T146T3 SIDU-R- TRAFODION._HB_MAP_.T146T1 SIDU-R- TRAFODION._HB_MAP_.T146T2 @@ -1489,7 +1597,9 @@ Privileges for Role DB__HBASEROLE ================================= SIDU-R- HBASE._CELL_.T146T1 +SIDU-R- HBASE._CELL_.T146T3 SIDU-R- HBASE._ROW_.T146T1 +SIDU-R- HBASE._ROW_.T146T3 SIDU-R- TRAFODION._HB_MAP_.T146T1 --- SQL operation complete. @@ -1605,8 +1715,10 @@ Privileges for Role DB__HBASEROLE SIDU-R- HBASE._CELL_.T146T1 SIDU-R- HBASE._CELL_.T146T2 +SIDU-R- HBASE._CELL_.T146T3 SIDU-R- HBASE._ROW_.T146T1 SIDU-R- HBASE._ROW_.T146T2 +SIDU-R- HBASE._ROW_.T146T3 SIDU-R- TRAFODION._HB_MAP_.T146T1 SIDU-R- TRAFODION._HB_MAP_.T146T2 @@ -1621,7 +1733,9 @@ Privileges for Role DB__HBASEROLE ================================= SIDU-R- HBASE._CELL_.T146T1 +SIDU-R- HBASE._CELL_.T146T3 SIDU-R- HBASE._ROW_.T146T1 +SIDU-R- HBASE._ROW_.T146T3 SIDU-R- TRAFODION._HB_MAP_.T146T1 --- SQL operation complete. @@ -1652,5 +1766,8 @@ End of MXCI Session *** WARNING[1004] Object T146T2 does not exist or object type is invalid for the current operation. --- SQL operation complete. +>>drop hbase table t146t3; + +--- SQL operation complete. >> >>log; http://git-wip-us.apache.org/repos/asf/trafodion/blob/88e2a742/core/sql/regress/privs2/TEST144 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs2/TEST144 b/core/sql/regress/privs2/TEST144 index fd971cf..910e120 100755 --- a/core/sql/regress/privs2/TEST144 +++ b/core/sql/regress/privs2/TEST144 @@ -48,6 +48,7 @@ revoke execute on procedure "_LIBMGR_".help from sql_user5 by sql_user3; revoke execute on procedure "_LIBMGR_".help from sql_user3 by sql_user2; revoke execute on procedure "_LIBMGR_".help from sql_user2; +grant component privilege "SHOW" on sql_operations to "PUBLIC"; ?section create_db create schema t144user1 authorization sql_user1; set schema t144user1; @@ -114,6 +115,9 @@ grant select, insert on customers to "PUBLIC"; showddl customers; create role 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 @@ -138,8 +142,17 @@ order by 1, 2, 3, 4 -- ================================================================= set schema t144user1; execute get_privs; - --- user1 owns the udfs but does not have execute on procedure help +get privileges on function gen_phone; +get privileges on function gen_random; +get privileges on function gen_time; +get privileges on procedure "_LIBMGR_".help; +get privileges on table_mapping function "_LIBMGR_".event_log_reader; + +-- user1 can execute udfs (as owner), but cannot execute procedure help, +-- can execute table_mapping function event_log_reader through public grant +get privileges on function gen_phone for sql_user1; +get privileges on procedure "_LIBMGR_".help for sql_user1; +get privileges on table_mapping function "_LIBMGR_".event_log_reader for sql_user1; sh sqlci -i "TEST144(cmds)" -u sql_user1; -- no other user or role has privileges @@ -152,8 +165,13 @@ grant execute on function gen_phone to sql_user2 with grant option; grant execute on function gen_random to sql_user2 with grant option; grant execute on function gen_time to sql_user2; grant execute on procedure "_LIBMGR_".help to sql_user2 with grant option; +get privileges on function gen_phone for sql_user2; +get privileges on function gen_random for sql_user2; +get privileges on function gen_time for user sql_user2; +get privileges on procedure "_LIBMGR_".help for user sql_user2; execute get_privs; + -- user2 can execute sh sqlci -i "TEST144(cmds)" -u sql_user2; @@ -167,6 +185,10 @@ grant execute on procedure "_LIBMGR_".help to sql_user3 by sql_user2; -- user2 does not have WGO on gen_time grant execute on function gen_time to sql_user3 by sql_user2; +get privileges on function gen_phone for user sql_user3; +get privileges on function gen_random for user sql_user3; +get privileges on function gen_time for user sql_user3; +get privileges on procedure "_LIBMGR_".help for user sql_user3; execute get_privs; -- user 3 can execute gen_phone, gen_random, and help but not gen_time @@ -179,11 +201,20 @@ grant execute on function gen_random to t144role1; grant execute on function gen_time to t144role1; grant execute on procedure "_LIBMGR_".help to 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; execute get_privs; -- user4 can execute through role t144role1 sh sqlci -i "TEST144(cmds)" -u sql_user4; +get privileges on function gen_phone; +get privileges on function gen_random; +get privileges on function gen_time; +get privileges on procedure "_LIBMGR_".help; ?section test_revokes -- ============================================================================ -- verify that revoking privileges handle EXECUTE privilege correctly @@ -223,6 +254,10 @@ execute get_privs; log LOG144; values (user); set schema t144user1; +-- should return privileges only for users that have execute privilege +get privileges on function gen_time; +-- should return no rows for users other than sql_user1 +get privileges on function gen_random for sql_user1; select customer_id, 'PHONE: ' || gen_phone(customer_id, customer_areacode) as phone, customer_areacode http://git-wip-us.apache.org/repos/asf/trafodion/blob/88e2a742/core/sql/regress/privs2/TEST146 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs2/TEST146 b/core/sql/regress/privs2/TEST146 index 9f3f6d0..124aa9f 100755 --- a/core/sql/regress/privs2/TEST146 +++ b/core/sql/regress/privs2/TEST146 @@ -59,6 +59,7 @@ drop external table if exists t146t1; drop hbase table t146t1; drop hbase table t146t2; +drop hbase table t146t3; drop role t146_role1; revoke role db__hbaserole from sql_user5; @@ -87,6 +88,8 @@ create external table t146t1 attribute default column family 'cf' map to hbase table t146t1; +create hbase table t146t3 ( column family 'cf'); + -- These operations are not allowed on hbase tables update hbase."_CELL_".t146t1 set col_value = '4' where row_id = '2' and col_name = 'visitor_team'; delete from hbase."_CELL_".t146t1 where row_id = '3'; @@ -102,6 +105,7 @@ grant role db__hbaserole to sql_user5; revoke component privilege "SHOW" on sql_operations from "PUBLIC"; get privileges on component sql_operations for "PUBLIC"; +get external hbase objects, match '%T146%'; ?section test_grants -- ================================================================= @@ -168,6 +172,7 @@ create hbase table t146t2 ( column family 'cf'); drop hbase table t146t2; drop hbase table t146t2; +drop hbase table t146t3; ?section perform_ddl -- ============================================================================ @@ -246,3 +251,5 @@ execute s1; delete from hbase."_ROW_".t146t1 where row_id = '3'; execute s1; +get external hbase objects, match '%T146%'; +get hbase registered tables in catalog trafodion, match '%T146%'; http://git-wip-us.apache.org/repos/asf/trafodion/blob/88e2a742/core/sql/sqlci/sqlci_lex.ll ---------------------------------------------------------------------- diff --git a/core/sql/sqlci/sqlci_lex.ll b/core/sql/sqlci/sqlci_lex.ll index 40d891a..ac4376d 100755 --- a/core/sql/sqlci/sqlci_lex.ll +++ b/core/sql/sqlci/sqlci_lex.ll @@ -271,8 +271,6 @@ B [ \t\n]+ [Ss][Hh][Oo][Ww] return_IDENT_or_TOKEN(SHOW, 0); [Ss][Hh][Oo][Ww][Cc][Oo][Nn][Tt][Rr][Oo][Ll] return_IDENT_or_TOKEN(SHOWCONTROL, 0); [Ss][Hh][Oo][Ww][Dd][Dd][Ll] return_IDENT_or_TOKEN(SHOWDDL, 0); -[Ss][Hh][Oo][Ww][Ll][Aa][Bb][Ee][Ll] return_IDENT_or_TOKEN(SHOWLABEL, 0); -[Ss][Hh][Oo][Ww][Ll][Ee][Aa][Kk][Ss] return_IDENT_or_TOKEN(SHOWLEAKS, 0); [Ss][Hh][Oo][Ww][Pp][Ll][Aa][Nn] return_IDENT_or_TOKEN(SHOWPLAN, 0); [Ss][Hh][Oo][Ww][Ss][Hh][Aa][Pp][Ee] return_IDENT_or_TOKEN(SHOWSHAPE, 0); [Ss][Hh][Oo][Ww][Ss][Ee][Tt] return_IDENT_or_TOKEN(SHOWSET, 0); http://git-wip-us.apache.org/repos/asf/trafodion/blob/88e2a742/core/sql/sqlci/sqlci_yacc.y ---------------------------------------------------------------------- diff --git a/core/sql/sqlci/sqlci_yacc.y b/core/sql/sqlci/sqlci_yacc.y index 33e926a..dc97892 100644 --- a/core/sql/sqlci/sqlci_yacc.y +++ b/core/sql/sqlci/sqlci_yacc.y @@ -460,8 +460,6 @@ static char * FCString (const char *idString, int isFC) %token SHOW %token SHOWCONTROL %token SHOWDDL -%token SHOWLABEL -%token SHOWLEAKS %token SHOWPLAN %token SHOWSHAPE %token SHOWSET @@ -1247,14 +1245,6 @@ sql_cmd : $$ = new DML(SqlciParse_OriginalStr, $1, NULL); REPOSITION_SqlciParse_InputPos; } - | SHOWLEAKS - { // not ready to log. - HEAPLOG_CONTROL(LOG_DELETE_ONLY); - $$ = new DML(SqlciParse_OriginalStr, - DML_DESCRIBE_TYPE, - "__SQLCI_DML_SHOWLEAKS__"); - REPOSITION_SqlciParse_InputPos; - } | DECLAREtoken IDENTIFIER { @@ -1916,7 +1906,6 @@ dml_type : | UNREGISTER {$$ = DML_DDL_TYPE;} | SHOWCONTROL {$$ = DML_DESCRIBE_TYPE;} | SHOWDDL {$$ = DML_DESCRIBE_TYPE;} - | SHOWLABEL {$$ = DML_DESCRIBE_TYPE;} | SHOWSTATS {$$ = DML_DESCRIBE_TYPE;} | SHOWTRANSACTION {$$ = DML_DESCRIBE_TYPE;} | INVOKE {$$ = DML_DESCRIBE_TYPE;} http://git-wip-us.apache.org/repos/asf/trafodion/blob/88e2a742/core/sql/sqlcomp/CmpSeabaseDDLauth.cpp ---------------------------------------------------------------------- diff --git a/core/sql/sqlcomp/CmpSeabaseDDLauth.cpp b/core/sql/sqlcomp/CmpSeabaseDDLauth.cpp index 18b1865..3c9939d 100644 --- a/core/sql/sqlcomp/CmpSeabaseDDLauth.cpp +++ b/core/sql/sqlcomp/CmpSeabaseDDLauth.cpp @@ -358,7 +358,7 @@ Int32 CmpSeabaseDDLauth::getUniqueAuthID( Int32 len = snprintf(buf, 300, "SELECT [FIRST 1] auth_id FROM (SELECT auth_id, " "LEAD(auth_id) OVER (ORDER BY auth_id) L FROM %s.%s ) " - "WHERE L - auth_id > 1 and auth_id >= %d ", + "WHERE (L - auth_id > 1 or L is null) and auth_id >= %d ", MDSchema_.data(),SEABASE_AUTHS, minValue); assert (len <= 300); @@ -1207,20 +1207,22 @@ void CmpSeabaseDDLuser::unregisterUser(StmtDDLRegisterUser * pNode) return; } - // User does not own any roles, but may have been granted roles. NAString privMgrMDLoc; - CONCAT_CATSCH(privMgrMDLoc,systemCatalog_.data(),SEABASE_PRIVMGR_SCHEMA); + + // User does not own any roles, but may have been granted roles. + if (CmpCommon::context()->isAuthorizationEnabled()) + { - PrivMgrRoles role(std::string(MDSchema_.data()), - std::string(privMgrMDLoc.data()), - CmpCommon::diags()); + PrivMgrRoles role(std::string(MDSchema_.data()), + std::string(privMgrMDLoc.data()), + CmpCommon::diags()); - if (CmpCommon::context()->isAuthorizationEnabled() && - role.isUserGrantedAnyRole(getAuthID())) - { - *CmpCommon::diags() << DgSqlCode(-CAT_NO_UNREG_USER_GRANTED_ROLES); - return; + if (role.isUserGrantedAnyRole(getAuthID())) + { + *CmpCommon::diags() << DgSqlCode(-CAT_NO_UNREG_USER_GRANTED_ROLES); + return; + } } // Does user own any objects? @@ -1249,32 +1251,39 @@ void CmpSeabaseDDLuser::unregisterUser(StmtDDLRegisterUser * pNode) return; } - PrivMgr privMgr(std::string(privMgrMDLoc),CmpCommon::diags()); - std::vector<PrivClass> privClasses; + // Is user granted any privileges? + if (CmpCommon::context()->isAuthorizationEnabled()) + { + PrivMgr privMgr(std::string(privMgrMDLoc),CmpCommon::diags()); + std::vector<PrivClass> privClasses; - privClasses.push_back(PrivClass::ALL); + privClasses.push_back(PrivClass::ALL); - std::vector<int64_t> objectUIDs; - if (privMgr.isAuthIDGrantedPrivs(getAuthID(),privClasses, objectUIDs)) - { - NAString objectName = getObjectName(objectUIDs); - if (objectName.length() > 0) - { - *CmpCommon::diags() << DgSqlCode(-CAT_NO_UNREG_USER_HAS_PRIVS) - << DgString0(dbUserName.data()) - << DgString1(objectName.data()); + std::vector<int64_t> objectUIDs; + if (privMgr.isAuthIDGrantedPrivs(getAuthID(),privClasses, objectUIDs)) + { + NAString objectName = getObjectName(objectUIDs); + if (objectName.length() > 0) + { + *CmpCommon::diags() << DgSqlCode(-CAT_NO_UNREG_USER_HAS_PRIVS) + << DgString0(dbUserName.data()) + << DgString1(objectName.data()); - return; - } + return; + } + } } // remove any component privileges granted to this user - PrivMgrComponentPrivileges componentPrivileges(privMgrMDLoc.data(),CmpCommon::diags()); - std::string componentUIDString = "1"; - if (!componentPrivileges.dropAllForGrantee(getAuthID())) + if (CmpCommon::context()->isAuthorizationEnabled()) { - UserException excp (NULL, 0); - throw excp; + PrivMgrComponentPrivileges componentPrivileges(privMgrMDLoc.data(),CmpCommon::diags()); + std::string componentUIDString = "1"; + if (!componentPrivileges.dropAllForGrantee(getAuthID())) + { + UserException excp (NULL, 0); + throw excp; + } } // delete the row http://git-wip-us.apache.org/repos/asf/trafodion/blob/88e2a742/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp ---------------------------------------------------------------------- diff --git a/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp b/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp index 46c92b3..234d2d1 100644 --- a/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp +++ b/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp @@ -10246,6 +10246,7 @@ void CmpSeabaseDDL::createNativeHbaseTable( // or granted the DB__HBASEROLE if (isAuthorizationEnabled() && !ComUser::isRootUserID() && + !ComUser::currentUserHasRole(ROOT_ROLE_ID) && !ComUser::currentUserHasRole(HBASE_ROLE_ID)) { *CmpCommon::diags() << DgSqlCode (-CAT_NOT_AUTHORIZED); @@ -10336,6 +10337,7 @@ void CmpSeabaseDDL::dropNativeHbaseTable( // or granted the DB__HBASEROLE if (isAuthorizationEnabled() && !ComUser::isRootUserID() && + !ComUser::currentUserHasRole(ROOT_ROLE_ID) && !ComUser::currentUserHasRole(HBASE_ROLE_ID)) { *CmpCommon::diags() << DgSqlCode (-CAT_NOT_AUTHORIZED);
