Repository: trafodion Updated Branches: refs/heads/master d6e29337a -> ad1c67618
http://git-wip-us.apache.org/repos/asf/trafodion/blob/021faec6/core/sql/regress/privs1/EXPECTED123 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs1/EXPECTED123 b/core/sql/regress/privs1/EXPECTED123 index 0251bda..45d5db9 100644 --- a/core/sql/regress/privs1/EXPECTED123 +++ b/core/sql/regress/privs1/EXPECTED123 @@ -93,6 +93,9 @@ SHOW +> ; --- SQL operation complete. +>>create index games_visitor on games(visitor_team_number) no populate; + +--- SQL operation complete. >> >>create table players +> (player_number int not null, @@ -109,6 +112,32 @@ SHOW --- SQL operation complete. >> +>>create view home_teams_games as ++> select t.team_number, g.game_number, g.game_time ++> from "TEAMS" t, ++> "GAMES" g ++> where t.team_number = g.home_team_number ++> order by 1, game_number, game_time; + +--- SQL operation complete. +>> +>>create view players_on_team as ++> select player_name, team_name ++> from teams t, players p ++> where p.player_team_number = t.team_number ++> order by t.team_name; + +--- SQL operation complete. +>> +>>create view games_by_player as ++> select player_name, game_time ++> from players_on_team p, games g, teams t ++> where p.player_name = t.team_name and ++> t.team_number = g.home_team_number ++> order by player_name, team_number; + +--- SQL operation complete. +>> >>grant select on games to sql_user4; --- SQL operation complete. @@ -332,10 +361,13 @@ Privileges for User SQL_USER5 ------E TRAFODION."_LIBMGR_".EVENT_LOG_READER ------E TRAFODION."_LIBMGR_".JDBC SIDU-R- TRAFODION.T123SCH.GAMES +S----R- TRAFODION.T123SCH.GAMES_BY_PLAYER +S----R- TRAFODION.T123SCH.HOME_TEAMS_GAMES SIDU-R- TRAFODION.T123SCH.PLAYERS S------ TRAFODION.T123SCH.PLAYERS <Column> PLAYER_NAME S------ TRAFODION.T123SCH.PLAYERS <Column> PLAYER_NUMBER S------ TRAFODION.T123SCH.PLAYERS <Column> PLAYER_TEAM_NUMBER +S----R- TRAFODION.T123SCH.PLAYERS_ON_TEAM ----G-- TRAFODION.T123SCH.PLAYERS_SEQUENCE SIDU-R- TRAFODION.T123SCH.SB_HISTOGRAMS SIDU-R- TRAFODION.T123SCH.SB_HISTOGRAM_INTERVALS @@ -374,7 +406,10 @@ Privileges for Role T123_OWNERROLE ================================== SIDU-R- TRAFODION.T123SCH.GAMES +S----R- TRAFODION.T123SCH.GAMES_BY_PLAYER +S----R- TRAFODION.T123SCH.HOME_TEAMS_GAMES SIDU-R- TRAFODION.T123SCH.PLAYERS +S----R- TRAFODION.T123SCH.PLAYERS_ON_TEAM ----G-- TRAFODION.T123SCH.PLAYERS_SEQUENCE SIDU-R- TRAFODION.T123SCH.SB_HISTOGRAMS SIDU-R- TRAFODION.T123SCH.SB_HISTOGRAM_INTERVALS @@ -397,6 +432,164 @@ S------ TRAFODION.T123SCH.TEAMS <Column> TEAM_NUMBER --- SQL operation complete. >> +>>get tables for user sql_user1; + +Tables for User SQL_USER1 +========================= + +TRAFODION."T123SCH".GAMES +TRAFODION."T123SCH".PLAYERS +TRAFODION."T123SCH".TEAMS + +--- SQL operation complete. +>>get tables for user sql_user2; + +Tables for User SQL_USER2 +========================= + +TRAFODION."T123SCH".GAMES +TRAFODION."T123SCH".PLAYERS +TRAFODION."T123SCH".TEAMS + +--- SQL operation complete. +>>get tables for user sql_user3; + +Tables for User SQL_USER3 +========================= + +TRAFODION."T123SCH".PLAYERS +TRAFODION."T123SCH".TEAMS + +--- SQL operation complete. +>>get tables for user sql_user4; + +Tables for User SQL_USER4 +========================= + +TRAFODION."T123SCH".GAMES +TRAFODION."T123SCH".PLAYERS +TRAFODION."T123SCH".TEAMS + +--- SQL operation complete. +>>get tables for user sql_user5; + +Tables for User SQL_USER5 +========================= + +TRAFODION."T123SCH".GAMES +TRAFODION."T123SCH".PLAYERS +TRAFODION."T123SCH".SB_HISTOGRAMS +TRAFODION."T123SCH".SB_HISTOGRAM_INTERVALS +TRAFODION."T123SCH".SB_PERSISTENT_SAMPLES +TRAFODION."T123SCH".TEAMS + +--- SQL operation complete. +>> +>>get indexes for user sql_user1; + +Indexes for User SQL_USER1 +========================== + +TRAFODION."T123SCH".GAMES_VISITOR + +--- SQL operation complete. +>>get indexes for user sql_user2; + +Indexes for User SQL_USER2 +========================== + +TRAFODION."T123SCH".GAMES_VISITOR + +--- SQL operation complete. +>>get indexes for user sql_user3; + +--- SQL operation complete. +>>get indexes for user sql_user4; + +Indexes for User SQL_USER4 +========================== + +TRAFODION."T123SCH".GAMES_VISITOR + +--- SQL operation complete. +>>get indexes for user sql_user5; + +Indexes for User SQL_USER5 +========================== + +TRAFODION."T123SCH".GAMES_VISITOR + +--- SQL operation complete. +>> +>>get views for user sql_user1; + +--- SQL operation complete. +>>get views for user sql_user2; + +--- SQL operation complete. +>>get views for user sql_user3; + +--- SQL operation complete. +>>get views for user sql_user4; + +--- SQL operation complete. +>>get views for user sql_user5; + +Views for User SQL_USER5 +======================== + +TRAFODION."T123SCH".GAMES_BY_PLAYER +TRAFODION."T123SCH".HOME_TEAMS_GAMES +TRAFODION."T123SCH".PLAYERS_ON_TEAM + +--- SQL operation complete. +>> +>>get libraries for user sql_user1; + +Libraries for User SQL_USER1 +============================ + +DB__LIBMGRNAME +DB__LIBMGR_LIB_CPP + +--- SQL operation complete. +>>get libraries for user sql_user2; + +Libraries for User SQL_USER2 +============================ + +DB__LIBMGRNAME +DB__LIBMGR_LIB_CPP + +--- SQL operation complete. +>>get libraries for user sql_user3; + +Libraries for User SQL_USER3 +============================ + +DB__LIBMGRNAME +DB__LIBMGR_LIB_CPP + +--- SQL operation complete. +>>get libraries for user sql_user4; + +Libraries for User SQL_USER4 +============================ + +DB__LIBMGRNAME +DB__LIBMGR_LIB_CPP + +--- SQL operation complete. +>>get libraries for user sql_user5; + +Libraries for User SQL_USER5 +============================ + +DB__LIBMGRNAME +DB__LIBMGR_LIB_CPP + +--- SQL operation complete. +>> >> >>sh sqlci -i "TEST123(get_tests)" -u sql_user1; >>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; @@ -568,6 +761,120 @@ S------ TRAFODION.T123SCH.TEAMS <Column> TEAM_NUMBER --- SQL operation complete. >> +>>get tables for user sql_user1; + +Tables for User SQL_USER1 +========================= + +TRAFODION."T123SCH".GAMES +TRAFODION."T123SCH".PLAYERS +TRAFODION."T123SCH".TEAMS + +--- SQL operation complete. +>>get tables for user sql_user2; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get tables for user sql_user3; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get tables for user sql_user4; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get tables for user sql_user5; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>> +>>get indexes for user sql_user1; + +Indexes for User SQL_USER1 +========================== + +TRAFODION."T123SCH".GAMES_VISITOR + +--- SQL operation complete. +>>get indexes for user sql_user2; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get indexes for user sql_user3; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get indexes for user sql_user4; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get indexes for user sql_user5; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>> +>>get views for user sql_user1; + +--- SQL operation complete. +>>get views for user sql_user2; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get views for user sql_user3; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get views for user sql_user4; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>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_user1; + +Libraries for User SQL_USER1 +============================ + +DB__LIBMGRNAME +DB__LIBMGR_LIB_CPP + +--- 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; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>> >> >>exit; @@ -727,6 +1034,120 @@ S------ TRAFODION.T123SCH.TEAMS <Column> TEAM_NUMBER --- SQL operation complete. >> +>>get tables for user sql_user1; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get tables for user sql_user2; + +Tables for User SQL_USER2 +========================= + +TRAFODION."T123SCH".GAMES +TRAFODION."T123SCH".PLAYERS +TRAFODION."T123SCH".TEAMS + +--- SQL operation complete. +>>get tables for user sql_user3; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get tables for user sql_user4; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get tables for user sql_user5; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>> +>>get indexes for user sql_user1; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get indexes for user sql_user2; + +Indexes for User SQL_USER2 +========================== + +TRAFODION."T123SCH".GAMES_VISITOR + +--- SQL operation complete. +>>get indexes for user sql_user3; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get indexes for user sql_user4; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get indexes for user sql_user5; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>> +>>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_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 views for user sql_user4; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>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_user1; + +*** 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 + +--- SQL operation complete. +>>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; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>> >> >>exit; @@ -869,6 +1290,114 @@ S------ TRAFODION.T123SCH.TEAMS <Column> TEAM_NUMBER --- SQL operation complete. >> +>>get tables for user sql_user1; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get tables for user sql_user2; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get tables for user sql_user3; + +Tables for User SQL_USER3 +========================= + +TRAFODION."T123SCH".PLAYERS +TRAFODION."T123SCH".TEAMS + +--- SQL operation complete. +>>get tables for user sql_user4; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get tables for user sql_user5; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>> +>>get indexes for user sql_user1; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get indexes for user sql_user2; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get indexes for user sql_user3; + +--- SQL operation complete. +>>get indexes for user sql_user4; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get indexes for user sql_user5; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>> +>>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_user2; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get views for user sql_user3; + +--- SQL operation complete. +>>get views for user sql_user4; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>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_user1; + +*** 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 + +--- 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; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>> >> >>exit; @@ -1013,6 +1542,120 @@ S------ TRAFODION.T123SCH.TEAMS <Column> TEAM_NUMBER --- SQL operation complete. >> +>>get tables for user sql_user1; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get tables for user sql_user2; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get tables for user sql_user3; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get tables for user sql_user4; + +Tables for User SQL_USER4 +========================= + +TRAFODION."T123SCH".GAMES +TRAFODION."T123SCH".PLAYERS +TRAFODION."T123SCH".TEAMS + +--- SQL operation complete. +>>get tables for user sql_user5; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>> +>>get indexes for user sql_user1; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get indexes for user sql_user2; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get indexes for user sql_user3; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get indexes for user sql_user4; + +Indexes for User SQL_USER4 +========================== + +TRAFODION."T123SCH".GAMES_VISITOR + +--- SQL operation complete. +>>get indexes for user sql_user5; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>> +>>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_user2; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get views for user sql_user3; + +*** 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; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>> +>>get libraries for user sql_user1; + +*** 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; + +*** 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 + +--- SQL operation complete. +>>get libraries for user sql_user5; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>> >> >>exit; @@ -1128,10 +1771,13 @@ Privileges for User SQL_USER5 ------E TRAFODION."_LIBMGR_".EVENT_LOG_READER ------E TRAFODION."_LIBMGR_".JDBC SIDU-R- TRAFODION.T123SCH.GAMES +S----R- TRAFODION.T123SCH.GAMES_BY_PLAYER +S----R- TRAFODION.T123SCH.HOME_TEAMS_GAMES SIDU-R- TRAFODION.T123SCH.PLAYERS S------ TRAFODION.T123SCH.PLAYERS <Column> PLAYER_NAME S------ TRAFODION.T123SCH.PLAYERS <Column> PLAYER_NUMBER S------ TRAFODION.T123SCH.PLAYERS <Column> PLAYER_TEAM_NUMBER +S----R- TRAFODION.T123SCH.PLAYERS_ON_TEAM ----G-- TRAFODION.T123SCH.PLAYERS_SEQUENCE SIDU-R- TRAFODION.T123SCH.SB_HISTOGRAMS SIDU-R- TRAFODION.T123SCH.SB_HISTOGRAM_INTERVALS @@ -1157,7 +1803,10 @@ Privileges for Role T123_OWNERROLE ================================== SIDU-R- TRAFODION.T123SCH.GAMES +S----R- TRAFODION.T123SCH.GAMES_BY_PLAYER +S----R- TRAFODION.T123SCH.HOME_TEAMS_GAMES SIDU-R- TRAFODION.T123SCH.PLAYERS +S----R- TRAFODION.T123SCH.PLAYERS_ON_TEAM ----G-- TRAFODION.T123SCH.PLAYERS_SEQUENCE SIDU-R- TRAFODION.T123SCH.SB_HISTOGRAMS SIDU-R- TRAFODION.T123SCH.SB_HISTOGRAM_INTERVALS @@ -1180,6 +1829,130 @@ S------ TRAFODION.T123SCH.TEAMS <Column> TEAM_NUMBER --- SQL operation complete. >> +>>get tables for user sql_user1; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get tables for user sql_user2; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get tables for user sql_user3; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get tables for user sql_user4; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get tables for user sql_user5; + +Tables for User SQL_USER5 +========================= + +TRAFODION."T123SCH".GAMES +TRAFODION."T123SCH".PLAYERS +TRAFODION."T123SCH".SB_HISTOGRAMS +TRAFODION."T123SCH".SB_HISTOGRAM_INTERVALS +TRAFODION."T123SCH".SB_PERSISTENT_SAMPLES +TRAFODION."T123SCH".TEAMS + +--- SQL operation complete. +>> +>>get indexes for user sql_user1; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get indexes for user sql_user2; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get indexes for user sql_user3; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get indexes for user sql_user4; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get indexes for user sql_user5; + +Indexes for User SQL_USER5 +========================== + +TRAFODION."T123SCH".GAMES_VISITOR + +--- SQL operation complete. +>> +>>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_user2; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get views for user sql_user3; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get views for user sql_user4; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get views for user sql_user5; + +Views for User SQL_USER5 +======================== + +TRAFODION."T123SCH".GAMES_BY_PLAYER +TRAFODION."T123SCH".HOME_TEAMS_GAMES +TRAFODION."T123SCH".PLAYERS_ON_TEAM + +--- SQL operation complete. +>> +>>get libraries for user sql_user1; + +*** 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; + +*** 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 +============================ + +DB__LIBMGRNAME +DB__LIBMGR_LIB_CPP + +--- SQL operation complete. +>> >> >>exit; http://git-wip-us.apache.org/repos/asf/trafodion/blob/021faec6/core/sql/regress/privs1/EXPECTED125 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs1/EXPECTED125 b/core/sql/regress/privs1/EXPECTED125 index 73f0d59..8473a1c 100644 --- a/core/sql/regress/privs1/EXPECTED125 +++ b/core/sql/regress/privs1/EXPECTED125 @@ -81,8 +81,8 @@ >> >>create view games_by_player as +> select player_name, game_time -+> from teams t, games g, players p -+> where p.player_team_number = t.team_number and ++> from players_on_team p, games g, teams t ++> where p.player_name = t.team_name and +> t.team_number = g.home_team_number +> order by player_name, team_number; @@ -213,8 +213,8 @@ >> >>create view games_by_player as +> select player_name, game_time -+> from teams t, games g, players p -+> where p.player_team_number = t.team_number and ++> from players_on_team p, games g, teams t ++> where p.player_name = t.team_name and +> t.team_number = g.home_team_number +> order by player_name, team_number; @@ -345,8 +345,8 @@ >> >>create view games_by_player as +> select player_name, game_time -+> from teams t, games g, players p -+> where p.player_team_number = t.team_number and ++> from players_on_team p, games g, teams t ++> where p.player_name = t.team_name and +> t.team_number = g.home_team_number +> order by player_name, team_number; @@ -616,7 +616,7 @@ T125SCH2.PLAYERS_SEQUENCE T125SCH3.PLAYERS_SEQUENCE --- SQL operation complete. ->>get libraries; +>>get libraries, match 'T125%'; Libraries in Schema TRAFODION.T125SCH1 ====================================== @@ -642,6 +642,59 @@ TESTHIVE --- SQL operation complete. >> +>>get indexes on table players; + +Indexes on Table T125SCH1.PLAYERS +================================= + +PLAYERS_TEAMS + +--- SQL operation complete. +>>get views on table players; + +Views on Table T125SCH1.PLAYERS +=============================== + +TRAFODION.T125SCH1.PLAYERS_ON_TEAM + +--- SQL operation complete. +>>get views on view players_on_team; + +Views ON View T125SCH1.PLAYERS_ON_TEAM +====================================== + +TRAFODION.T125SCH1.GAMES_BY_PLAYER + +--- SQL operation complete. +>> +>>get tables in view games_by_player; + +Tables in View T125SCH1.GAMES_BY_PLAYER +======================================= + +TRAFODION.T125SCH1.GAMES +TRAFODION.T125SCH1.TEAMS + +--- SQL operation complete. +>>get views in view games_by_player; + +Views in View T125SCH1.GAMES_BY_PLAYER +====================================== + +TRAFODION.T125SCH1.PLAYERS_ON_TEAM + +--- SQL operation complete. +>>get objects in view games_by_player; + +Objects in View T125SCH1.GAMES_BY_PLAYER +======================================== + +TRAFODION.T125SCH1.GAMES +TRAFODION.T125SCH1.PLAYERS_ON_TEAM +TRAFODION.T125SCH1.TEAMS + +--- SQL operation complete. +>> >>set schema t125sch2; --- SQL operation complete. @@ -760,7 +813,7 @@ T125SCH2.PLAYERS_SEQUENCE T125SCH3.PLAYERS_SEQUENCE --- SQL operation complete. ->>get libraries; +>>get libraries, match 'T125%'; Libraries in Schema TRAFODION.T125SCH3 ====================================== @@ -786,6 +839,24 @@ TESTHIVE --- SQL operation complete. >> +>>get functions for library t125_l1; + +Functions for Library T125SCH3.T125_L1 +====================================== + +T125SCH3.TRANSLATEBITMAP + +--- SQL operation complete. +>>get procedures for library t125_l2; + +Procedures for Library T125SCH3.T125_L2 +======================================= + +T125SCH3.TESTHIVE + +--- SQL operation complete. +>> +>> >> >>revoke component privilege "SHOW" on sql_operations from "PUBLIC"; @@ -833,7 +904,7 @@ Sequences in catalog TRAFODION T125SCH3.PLAYERS_SEQUENCE --- SQL operation complete. ->>get libraries; +>>get libraries, match 'T125%'; --- SQL operation complete. >>get functions; @@ -843,6 +914,26 @@ T125SCH3.PLAYERS_SEQUENCE --- SQL operation complete. >> +>>get indexes on table players; + +--- SQL operation complete. +>>get views on table players; + +--- SQL operation complete. +>>get views on view players_on_team; + +--- SQL operation complete. +>> +>>get tables in view games_by_player; + +--- SQL operation complete. +>>get views in view games_by_player; + +--- SQL operation complete. +>>get objects in view games_by_player; + +--- SQL operation complete. +>> >>set schema t125sch2; --- SQL operation complete. @@ -905,7 +996,7 @@ Sequences in catalog TRAFODION T125SCH3.PLAYERS_SEQUENCE --- SQL operation complete. ->>get libraries; +>>get libraries, match 'T125%'; Libraries in Schema TRAFODION.T125SCH3 ====================================== @@ -931,6 +1022,24 @@ TESTHIVE --- SQL operation complete. >> +>>get functions for library t125_l1; + +Functions for Library T125SCH3.T125_L1 +====================================== + +T125SCH3.TRANSLATEBITMAP + +--- SQL operation complete. +>>get procedures for library t125_l2; + +Procedures for Library T125SCH3.T125_L2 +======================================= + +T125SCH3.TESTHIVE + +--- SQL operation complete. +>> +>> >>exit; End of MXCI Session @@ -979,7 +1088,7 @@ Sequences in catalog TRAFODION T125SCH2.PLAYERS_SEQUENCE --- SQL operation complete. ->>get libraries; +>>get libraries, match 'T125%'; --- SQL operation complete. >>get functions; @@ -989,6 +1098,26 @@ T125SCH2.PLAYERS_SEQUENCE --- SQL operation complete. >> +>>get indexes on table players; + +--- SQL operation complete. +>>get views on table players; + +--- SQL operation complete. +>>get views on view players_on_team; + +--- SQL operation complete. +>> +>>get tables in view games_by_player; + +--- SQL operation complete. +>>get views in view games_by_player; + +--- SQL operation complete. +>>get objects in view games_by_player; + +--- SQL operation complete. +>> >>set schema t125sch2; --- SQL operation complete. @@ -1011,6 +1140,11 @@ PLAYERS_SEQUENCE --- SQL operation complete. >>get libraries in schema t125sch2; +Libraries in Schema TRAFODION.T125SCH2 +====================================== + +T125_L2 + --- SQL operation complete. >>get functions in schema t125sch2; @@ -1054,7 +1188,7 @@ Sequences in catalog TRAFODION T125SCH2.PLAYERS_SEQUENCE --- SQL operation complete. ->>get libraries; +>>get libraries, match 'T125%'; --- SQL operation complete. >>get functions in schema t125sch3; @@ -1064,6 +1198,14 @@ T125SCH2.PLAYERS_SEQUENCE --- SQL operation complete. >> +>>get functions for library t125_l1; + +--- SQL operation complete. +>>get procedures for library t125_l2; + +--- SQL operation complete. +>> +>> >>exit; End of MXCI Session @@ -1118,7 +1260,7 @@ GAMES_BY_PLAYER >>get sequences, match 'T125SCH%'; --- SQL operation complete. ->>get libraries; +>>get libraries, match 'T125%'; --- SQL operation complete. >>get functions; @@ -1128,6 +1270,44 @@ GAMES_BY_PLAYER --- SQL operation complete. >> +>>get indexes on table players; + +--- SQL operation complete. +>>get views on table players; + +--- SQL operation complete. +>>get views on view players_on_team; + +--- SQL operation complete. +>> +>>get tables in view games_by_player; + +Tables in View T125SCH1.GAMES_BY_PLAYER +======================================= + +TRAFODION.T125SCH1.GAMES +TRAFODION.T125SCH1.TEAMS + +--- SQL operation complete. +>>get views in view games_by_player; + +Views in View T125SCH1.GAMES_BY_PLAYER +====================================== + +TRAFODION.T125SCH1.PLAYERS_ON_TEAM + +--- SQL operation complete. +>>get objects in view games_by_player; + +Objects in View T125SCH1.GAMES_BY_PLAYER +======================================== + +TRAFODION.T125SCH1.GAMES +TRAFODION.T125SCH1.PLAYERS_ON_TEAM +TRAFODION.T125SCH1.TEAMS + +--- SQL operation complete. +>> >>set schema t125sch2; --- SQL operation complete. @@ -1192,7 +1372,12 @@ T125SCH3.GAMES_BY_PLAYER >>get sequences in catalog trafodion, match 'T125SCH%'; --- SQL operation complete. ->>get libraries; +>>get libraries, match 'T125%'; + +Libraries in Schema TRAFODION.T125SCH3 +====================================== + +T125_L1 --- SQL operation complete. >>get functions in schema t125sch3; @@ -1207,6 +1392,19 @@ TRANSLATEBITMAP --- SQL operation complete. >> +>>get functions for library t125_l1; + +Functions for Library T125SCH3.T125_L1 +====================================== + +T125SCH3.TRANSLATEBITMAP + +--- SQL operation complete. +>>get procedures for library t125_l2; + +--- SQL operation complete. +>> +>> >>exit; End of MXCI Session @@ -1251,7 +1449,7 @@ T125SCH3 >>get sequences, match 'T125SCH%'; --- SQL operation complete. ->>get libraries; +>>get libraries, match 'T125%'; --- SQL operation complete. >>get functions; @@ -1261,6 +1459,26 @@ T125SCH3 --- SQL operation complete. >> +>>get indexes on table players; + +--- SQL operation complete. +>>get views on table players; + +--- SQL operation complete. +>>get views on view players_on_team; + +--- SQL operation complete. +>> +>>get tables in view games_by_player; + +--- SQL operation complete. +>>get views in view games_by_player; + +--- SQL operation complete. +>>get objects in view games_by_player; + +--- SQL operation complete. +>> >>set schema t125sch2; --- SQL operation complete. @@ -1312,7 +1530,12 @@ PLAYERS >>get sequences in catalog trafodion, match 'T125SCH%'; --- SQL operation complete. ->>get libraries; +>>get libraries, match 'T125%'; + +Libraries in Schema TRAFODION.T125SCH3 +====================================== + +T125_L1 --- SQL operation complete. >>get functions in schema t125sch3; @@ -1327,6 +1550,19 @@ TRANSLATEBITMAP --- SQL operation complete. >> +>>get functions for library t125_l1; + +Functions for Library T125SCH3.T125_L1 +====================================== + +T125SCH3.TRANSLATEBITMAP + +--- SQL operation complete. +>>get procedures for library t125_l2; + +--- SQL operation complete. +>> +>> >>exit; End of MXCI Session http://git-wip-us.apache.org/repos/asf/trafodion/blob/021faec6/core/sql/regress/privs1/TEST123 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs1/TEST123 b/core/sql/regress/privs1/TEST123 index f32cf61..6214a55 100644 --- a/core/sql/regress/privs1/TEST123 +++ b/core/sql/regress/privs1/TEST123 @@ -29,6 +29,7 @@ -- get roles for user -- get privileges for role -- get privileges for user +-- get [tables | indexes | libraries | views] for user -- -- Users and roles used -- t123_adminrole: granted update and delete on games/teams @@ -99,6 +100,7 @@ create table games game_time timestamp not null, game_location varchar(50) not null) ; +create index games_visitor on games(visitor_team_number) no populate; create table players (player_number int not null, @@ -111,6 +113,26 @@ create table players create sequence players_sequence; +create view home_teams_games as + select t.team_number, g.game_number, g.game_time + from "TEAMS" t, + "GAMES" g + where t.team_number = g.home_team_number + order by 1, game_number, game_time; + +create view players_on_team as + select player_name, team_name + from teams t, players p + where p.player_team_number = t.team_number + order by t.team_name; + +create view games_by_player as + select player_name, game_time + from players_on_team p, games g, teams t + where p.player_name = t.team_name and + t.team_number = g.home_team_number + order by player_name, team_number; + grant select on games to sql_user4; grant select on teams to sql_user4; grant select(team_number, team_name) on teams to "PUBLIC"; @@ -170,6 +192,30 @@ get privileges for role t123_dummyrole; get privileges for role t123_ownerrole; get privileges for role "PUBLIC"; +get tables for user sql_user1; +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 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 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; + ?section other get privileges for user "PUBLIC"; get privileges for user unknown_user; http://git-wip-us.apache.org/repos/asf/trafodion/blob/021faec6/core/sql/regress/privs1/TEST125 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs1/TEST125 b/core/sql/regress/privs1/TEST125 index 0025c53..de667ea 100644 --- a/core/sql/regress/privs1/TEST125 +++ b/core/sql/regress/privs1/TEST125 @@ -154,8 +154,8 @@ create view players_on_team as create view games_by_player as select player_name, game_time - from teams t, games g, players p - where p.player_team_number = t.team_number and + from players_on_team p, games g, teams t + where p.player_name = t.team_name and t.team_number = g.home_team_number order by player_name, team_number; @@ -220,10 +220,18 @@ get tables; get views; get indexes; get sequences, match 'T125SCH%'; -get libraries; +get libraries, match 'T125%'; get functions; get procedures; +get indexes on table players; +get views on table players; +get views on view players_on_team; + +get tables in view games_by_player; +get views in view games_by_player; +get objects in view games_by_player; + set schema t125sch2; get tables in schema t125sch2; get views in schema t125sch2; @@ -238,6 +246,10 @@ get tables; get views in catalog trafodion, match 'T125SCH%'; get indexes in schema t125sch3; get sequences in catalog trafodion, match 'T125SCH%'; -get libraries; +get libraries, match 'T125%'; get functions in schema t125sch3; get procedures; + +get functions for library t125_l1; +get procedures for library t125_l2; +
