http://git-wip-us.apache.org/repos/asf/trafodion/blob/afff9935/core/sql/regress/privs1/EXPECTED123 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs1/EXPECTED123 b/core/sql/regress/privs1/EXPECTED123 index dd8211b..ffc42d4 100644 --- a/core/sql/regress/privs1/EXPECTED123 +++ b/core/sql/regress/privs1/EXPECTED123 @@ -324,6 +324,9 @@ SQL_USER5 1 row(s) returned --- SQL operation complete. +>>get users for role "PUBLIC"; + +--- SQL operation complete. >> >>get privileges for user sql_user1; @@ -565,6 +568,34 @@ TRAFODION."T123SCH".TEAMS 6 row(s) returned --- SQL operation complete. +>>get tables for role t123_adminrole; + +Tables for Role T123_ADMINROLE +============================== + +TRAFODION."T123SCH".GAMES +TRAFODION."T123SCH".TEAMS + +======================= + 2 row(s) returned + +--- SQL operation complete. +>>get tables for role t123_ownerrole; + +Tables for Role T123_OWNERROLE +============================== + +TRAFODION."T123SCH".GAMES +TRAFODION."T123SCH".PLAYERS +TRAFODION."T123SCH".SB_HISTOGRAMS +TRAFODION."T123SCH".SB_HISTOGRAM_INTERVALS +TRAFODION."T123SCH".SB_PERSISTENT_SAMPLES +TRAFODION."T123SCH".TEAMS + +======================= + 6 row(s) returned + +--- SQL operation complete. >> >>get indexes for user sql_user1; @@ -613,6 +644,28 @@ TRAFODION."T123SCH".GAMES_VISITOR 1 row(s) returned --- SQL operation complete. +>>get indexes for role t123_adminrole; + +Indexes for Role T123_ADMINROLE +=============================== + +TRAFODION."T123SCH".GAMES_VISITOR + +======================= + 1 row(s) returned + +--- SQL operation complete. +>>get indexes for role t123_ownerrole; + +Indexes for Role T123_OWNERROLE +=============================== + +TRAFODION."T123SCH".GAMES_VISITOR + +======================= + 1 row(s) returned + +--- SQL operation complete. >> >>get views for user sql_user1; @@ -639,65 +692,20 @@ TRAFODION."T123SCH".PLAYERS_ON_TEAM 3 row(s) returned --- SQL operation complete. ->> ->>get libraries for user sql_user1; - -Libraries for User SQL_USER1 -============================ - -DB__LIBMGRNAME -DB__LIBMGR_LIB_CPP - -======================= - 2 row(s) returned - ---- SQL operation complete. ->>get libraries for user sql_user2; - -Libraries for User SQL_USER2 -============================ - -DB__LIBMGRNAME -DB__LIBMGR_LIB_CPP - -======================= - 2 row(s) returned - ---- SQL operation complete. ->>get libraries for user sql_user3; - -Libraries for User SQL_USER3 -============================ - -DB__LIBMGRNAME -DB__LIBMGR_LIB_CPP - -======================= - 2 row(s) returned - ---- SQL operation complete. ->>get libraries for user sql_user4; - -Libraries for User SQL_USER4 -============================ - -DB__LIBMGRNAME -DB__LIBMGR_LIB_CPP - -======================= - 2 row(s) returned +>>get views for role t123_adminrole; --- SQL operation complete. ->>get libraries for user sql_user5; +>>get views for role t123_ownerrole; -Libraries for User SQL_USER5 -============================ +Views for Role T123_OWNERROLE +============================= -DB__LIBMGRNAME -DB__LIBMGR_LIB_CPP +TRAFODION."T123SCH".GAMES_BY_PLAYER +TRAFODION."T123SCH".HOME_TEAMS_GAMES +TRAFODION."T123SCH".PLAYERS_ON_TEAM ======================= - 2 row(s) returned + 3 row(s) returned --- SQL operation complete. >> @@ -754,48 +762,24 @@ T123_PLANNERROLE --- SQL operation complete. >>get roles for user sql_user2; -Roles for User SQL_USER2 -======================== - -PUBLIC - -======================= - 1 row(s) returned +*** ERROR[1017] You are not authorized to perform this operation. ---- SQL operation complete. +--- SQL operation failed with errors. >>get roles for user sql_user3; -Roles for User SQL_USER3 -======================== - -PUBLIC - -======================= - 1 row(s) returned +*** ERROR[1017] You are not authorized to perform this operation. ---- SQL operation complete. +--- SQL operation failed with errors. >>get roles for user sql_user4; -Roles for User SQL_USER4 -======================== - -PUBLIC - -======================= - 1 row(s) returned +*** ERROR[1017] You are not authorized to perform this operation. ---- SQL operation complete. +--- SQL operation failed with errors. >>get roles for user sql_user5; -Roles for User SQL_USER5 -======================== - -PUBLIC - -======================= - 1 row(s) returned +*** ERROR[1017] You are not authorized to perform this operation. ---- SQL operation complete. +--- SQL operation failed with errors. >> >>get users for role t123_adminrole; @@ -821,9 +805,16 @@ SQL_USER1 --- SQL operation complete. >>get users for role t123_dummyrole; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get users for role t123_ownerrole; +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get users for role "PUBLIC"; + --- SQL operation complete. >> >>get privileges for user sql_user1; @@ -851,16 +842,24 @@ S------ TRAFODION.T123SCH.TEAMS <Column> TEAM_NUMBER --- SQL operation complete. >>get privileges for user sql_user2; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for user sql_user3; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for user sql_user4; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for user sql_user5; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >> >>get privileges for role t123_adminrole; @@ -889,10 +888,14 @@ Privileges for Role T123_PLANNERROLE --- SQL operation complete. >>get privileges for role t123_dummyrole; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for role t123_ownerrole; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for role "PUBLIC"; Privileges for Role PUBLIC @@ -944,6 +947,23 @@ TRAFODION."T123SCH".TEAMS *** ERROR[1017] You are not authorized to perform this operation. --- SQL operation failed with errors. +>>get tables for role t123_adminrole; + +Tables for Role T123_ADMINROLE +============================== + +TRAFODION."T123SCH".GAMES +TRAFODION."T123SCH".TEAMS + +======================= + 2 row(s) returned + +--- SQL operation complete. +>>get tables for role t123_ownerrole; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >> >>get indexes for user sql_user1; @@ -976,6 +996,22 @@ TRAFODION."T123SCH".GAMES_VISITOR *** ERROR[1017] You are not authorized to perform this operation. --- SQL operation failed with errors. +>>get indexes for role t123_adminrole; + +Indexes for Role T123_ADMINROLE +=============================== + +TRAFODION."T123SCH".GAMES_VISITOR + +======================= + 1 row(s) returned + +--- SQL operation complete. +>>get indexes for role t123_ownerrole; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >> >>get views for user sql_user1; @@ -1000,35 +1036,10 @@ TRAFODION."T123SCH".GAMES_VISITOR *** ERROR[1017] You are not authorized to perform this operation. --- SQL operation failed with errors. ->> ->>get libraries for user sql_user1; - -Libraries for User SQL_USER1 -============================ - -DB__LIBMGRNAME -DB__LIBMGR_LIB_CPP - -======================= - 2 row(s) returned +>>get views for role t123_adminrole; --- SQL operation complete. ->>get libraries for user sql_user2; - -*** ERROR[1017] You are not authorized to perform this operation. - ---- SQL operation failed with errors. ->>get libraries for user sql_user3; - -*** ERROR[1017] You are not authorized to perform this operation. - ---- SQL operation failed with errors. ->>get libraries for user sql_user4; - -*** ERROR[1017] You are not authorized to perform this operation. - ---- SQL operation failed with errors. ->>get libraries for user sql_user5; +>>get views for role t123_ownerrole; *** ERROR[1017] You are not authorized to perform this operation. @@ -1077,15 +1088,9 @@ T123_PLANNERROLE --- SQL operation complete. >>get roles for user sql_user1; -Roles for User SQL_USER1 -======================== - -PUBLIC - -======================= - 1 row(s) returned +*** ERROR[1017] You are not authorized to perform this operation. ---- SQL operation complete. +--- SQL operation failed with errors. >>get roles for user sql_user2; Roles for User SQL_USER2 @@ -1100,41 +1105,25 @@ T123_PLANNERROLE --- SQL operation complete. >>get roles for user sql_user3; -Roles for User SQL_USER3 -======================== - -PUBLIC - -======================= - 1 row(s) returned +*** ERROR[1017] You are not authorized to perform this operation. ---- SQL operation complete. +--- SQL operation failed with errors. >>get roles for user sql_user4; -Roles for User SQL_USER4 -======================== - -PUBLIC - -======================= - 1 row(s) returned +*** ERROR[1017] You are not authorized to perform this operation. ---- SQL operation complete. +--- SQL operation failed with errors. >>get roles for user sql_user5; -Roles for User SQL_USER5 -======================== - -PUBLIC - -======================= - 1 row(s) returned +*** ERROR[1017] You are not authorized to perform this operation. ---- SQL operation complete. +--- SQL operation failed with errors. >> >>get users for role t123_adminrole; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get users for role t123_plannerrole; Users granted Role T123_PLANNERROLE @@ -1148,14 +1137,23 @@ SQL_USER2 --- SQL operation complete. >>get users for role t123_dummyrole; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get users for role t123_ownerrole; +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get users for role "PUBLIC"; + --- SQL operation complete. >> >>get privileges for user sql_user1; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for user sql_user2; Privileges for User SQL_USER2 @@ -1178,17 +1176,25 @@ S------ TRAFODION.T123SCH.TEAMS <Column> TEAM_NUMBER --- SQL operation complete. >>get privileges for user sql_user3; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for user sql_user4; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for user sql_user5; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >> >>get privileges for role t123_adminrole; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for role t123_plannerrole; Privileges for Role T123_PLANNERROLE @@ -1204,10 +1210,14 @@ Privileges for Role T123_PLANNERROLE --- SQL operation complete. >>get privileges for role t123_dummyrole; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for role t123_ownerrole; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for role "PUBLIC"; Privileges for Role PUBLIC @@ -1259,6 +1269,16 @@ TRAFODION."T123SCH".TEAMS *** ERROR[1017] You are not authorized to perform this operation. --- SQL operation failed with errors. +>>get tables for role t123_adminrole; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get tables for role t123_ownerrole; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >> >>get indexes for user sql_user1; @@ -1291,59 +1311,46 @@ TRAFODION."T123SCH".GAMES_VISITOR *** ERROR[1017] You are not authorized to perform this operation. --- SQL operation failed with errors. ->> ->>get views for user sql_user1; +>>get indexes for role t123_adminrole; *** ERROR[1017] You are not authorized to perform this operation. --- SQL operation failed with errors. ->>get views for user sql_user2; - ---- SQL operation complete. ->>get views for user sql_user3; +>>get indexes for role t123_ownerrole; *** ERROR[1017] You are not authorized to perform this operation. --- SQL operation failed with errors. ->>get views for user sql_user4; +>> +>>get views for user sql_user1; *** ERROR[1017] You are not authorized to perform this operation. --- SQL operation failed with errors. ->>get views for user sql_user5; +>>get views for user sql_user2; + +--- SQL operation complete. +>>get views for user sql_user3; *** ERROR[1017] You are not authorized to perform this operation. --- SQL operation failed with errors. ->> ->>get libraries for user sql_user1; +>>get views for user sql_user4; *** ERROR[1017] You are not authorized to perform this operation. --- SQL operation failed with errors. ->>get libraries for user sql_user2; - -Libraries for User SQL_USER2 -============================ - -DB__LIBMGRNAME -DB__LIBMGR_LIB_CPP - -======================= - 2 row(s) returned - ---- SQL operation complete. ->>get libraries for user sql_user3; +>>get views for user sql_user5; *** ERROR[1017] You are not authorized to perform this operation. --- SQL operation failed with errors. ->>get libraries for user sql_user4; +>>get views for role t123_adminrole; *** ERROR[1017] You are not authorized to perform this operation. --- SQL operation failed with errors. ->>get libraries for user sql_user5; +>>get views for role t123_ownerrole; *** ERROR[1017] You are not authorized to perform this operation. @@ -1391,26 +1398,14 @@ PUBLIC --- SQL operation complete. >>get roles for user sql_user1; -Roles for User SQL_USER1 -======================== - -PUBLIC - -======================= - 1 row(s) returned +*** ERROR[1017] You are not authorized to perform this operation. ---- SQL operation complete. +--- SQL operation failed with errors. >>get roles for user sql_user2; -Roles for User SQL_USER2 -======================== - -PUBLIC - -======================= - 1 row(s) returned +*** ERROR[1017] You are not authorized to perform this operation. ---- SQL operation complete. +--- SQL operation failed with errors. >>get roles for user sql_user3; Roles for User SQL_USER3 @@ -1424,46 +1419,49 @@ PUBLIC --- SQL operation complete. >>get roles for user sql_user4; -Roles for User SQL_USER4 -======================== - -PUBLIC - -======================= - 1 row(s) returned +*** ERROR[1017] You are not authorized to perform this operation. ---- SQL operation complete. +--- SQL operation failed with errors. >>get roles for user sql_user5; -Roles for User SQL_USER5 -======================== - -PUBLIC - -======================= - 1 row(s) returned +*** ERROR[1017] You are not authorized to perform this operation. ---- SQL operation complete. +--- SQL operation failed with errors. >> >>get users for role t123_adminrole; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get users for role t123_plannerrole; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get users for role t123_dummyrole; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get users for role t123_ownerrole; +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get users for role "PUBLIC"; + --- SQL operation complete. >> >>get privileges for user sql_user1; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for user sql_user2; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for user sql_user3; Privileges for User SQL_USER3 @@ -1483,23 +1481,35 @@ S------ TRAFODION.T123SCH.TEAMS <Column> TEAM_NUMBER --- SQL operation complete. >>get privileges for user sql_user4; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for user sql_user5; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >> >>get privileges for role t123_adminrole; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for role t123_plannerrole; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for role t123_dummyrole; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for role t123_ownerrole; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for role "PUBLIC"; Privileges for Role PUBLIC @@ -1550,6 +1560,16 @@ TRAFODION."T123SCH".TEAMS *** ERROR[1017] You are not authorized to perform this operation. --- SQL operation failed with errors. +>>get tables for role t123_adminrole; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get tables for role t123_ownerrole; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >> >>get indexes for user sql_user1; @@ -1574,6 +1594,16 @@ TRAFODION."T123SCH".TEAMS *** ERROR[1017] You are not authorized to perform this operation. --- SQL operation failed with errors. +>>get indexes for role t123_adminrole; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get indexes for role t123_ownerrole; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >> >>get views for user sql_user1; @@ -1598,35 +1628,12 @@ TRAFODION."T123SCH".TEAMS *** ERROR[1017] You are not authorized to perform this operation. --- SQL operation failed with errors. ->> ->>get libraries for user sql_user1; +>>get views for role t123_adminrole; *** ERROR[1017] You are not authorized to perform this operation. --- SQL operation failed with errors. ->>get libraries for user sql_user2; - -*** ERROR[1017] You are not authorized to perform this operation. - ---- SQL operation failed with errors. ->>get libraries for user sql_user3; - -Libraries for User SQL_USER3 -============================ - -DB__LIBMGRNAME -DB__LIBMGR_LIB_CPP - -======================= - 2 row(s) returned - ---- SQL operation complete. ->>get libraries for user sql_user4; - -*** ERROR[1017] You are not authorized to perform this operation. - ---- SQL operation failed with errors. ->>get libraries for user sql_user5; +>>get views for role t123_ownerrole; *** ERROR[1017] You are not authorized to perform this operation. @@ -1674,37 +1681,19 @@ PUBLIC --- SQL operation complete. >>get roles for user sql_user1; -Roles for User SQL_USER1 -======================== - -PUBLIC - -======================= - 1 row(s) returned +*** ERROR[1017] You are not authorized to perform this operation. ---- SQL operation complete. +--- SQL operation failed with errors. >>get roles for user sql_user2; -Roles for User SQL_USER2 -======================== - -PUBLIC - -======================= - 1 row(s) returned +*** ERROR[1017] You are not authorized to perform this operation. ---- SQL operation complete. +--- SQL operation failed with errors. >>get roles for user sql_user3; -Roles for User SQL_USER3 -======================== - -PUBLIC - -======================= - 1 row(s) returned +*** ERROR[1017] You are not authorized to perform this operation. ---- SQL operation complete. +--- SQL operation failed with errors. >>get roles for user sql_user4; Roles for User SQL_USER4 @@ -1718,38 +1707,49 @@ PUBLIC --- SQL operation complete. >>get roles for user sql_user5; -Roles for User SQL_USER5 -======================== - -PUBLIC - -======================= - 1 row(s) returned +*** ERROR[1017] You are not authorized to perform this operation. ---- SQL operation complete. +--- SQL operation failed with errors. >> >>get users for role t123_adminrole; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get users for role t123_plannerrole; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get users for role t123_dummyrole; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get users for role t123_ownerrole; +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get users for role "PUBLIC"; + --- SQL operation complete. >> >>get privileges for user sql_user1; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for user sql_user2; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for user sql_user3; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for user sql_user4; Privileges for User SQL_USER4 @@ -1771,20 +1771,30 @@ S------ TRAFODION.T123SCH.TEAMS <Column> TEAM_NUMBER --- SQL operation complete. >>get privileges for user sql_user5; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >> >>get privileges for role t123_adminrole; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for role t123_plannerrole; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for role t123_dummyrole; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for role t123_ownerrole; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for role "PUBLIC"; Privileges for Role PUBLIC @@ -1836,6 +1846,16 @@ TRAFODION."T123SCH".TEAMS *** ERROR[1017] You are not authorized to perform this operation. --- SQL operation failed with errors. +>>get tables for role t123_adminrole; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get tables for role t123_ownerrole; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >> >>get indexes for user sql_user1; @@ -1868,59 +1888,46 @@ TRAFODION."T123SCH".GAMES_VISITOR *** ERROR[1017] You are not authorized to perform this operation. --- SQL operation failed with errors. ->> ->>get views for user sql_user1; +>>get indexes for role t123_adminrole; *** ERROR[1017] You are not authorized to perform this operation. --- SQL operation failed with errors. ->>get views for user sql_user2; +>>get indexes for role t123_ownerrole; *** ERROR[1017] You are not authorized to perform this operation. --- SQL operation failed with errors. ->>get views for user sql_user3; +>> +>>get views for user sql_user1; *** ERROR[1017] You are not authorized to perform this operation. --- SQL operation failed with errors. ->>get views for user sql_user4; - ---- SQL operation complete. ->>get views for user sql_user5; +>>get views for user sql_user2; *** ERROR[1017] You are not authorized to perform this operation. --- SQL operation failed with errors. ->> ->>get libraries for user sql_user1; +>>get views for user sql_user3; *** ERROR[1017] You are not authorized to perform this operation. --- SQL operation failed with errors. ->>get libraries for user sql_user2; +>>get views for user sql_user4; + +--- SQL operation complete. +>>get views for user sql_user5; *** ERROR[1017] You are not authorized to perform this operation. --- SQL operation failed with errors. ->>get libraries for user sql_user3; +>>get views for role t123_adminrole; *** ERROR[1017] You are not authorized to perform this operation. --- SQL operation failed with errors. ->>get libraries for user sql_user4; - -Libraries for User SQL_USER4 -============================ - -DB__LIBMGRNAME -DB__LIBMGR_LIB_CPP - -======================= - 2 row(s) returned - ---- SQL operation complete. ->>get libraries for user sql_user5; +>>get views for role t123_ownerrole; *** ERROR[1017] You are not authorized to perform this operation. @@ -1969,48 +1976,24 @@ T123_OWNERROLE --- SQL operation complete. >>get roles for user sql_user1; -Roles for User SQL_USER1 -======================== - -PUBLIC - -======================= - 1 row(s) returned +*** ERROR[1017] You are not authorized to perform this operation. ---- SQL operation complete. +--- SQL operation failed with errors. >>get roles for user sql_user2; -Roles for User SQL_USER2 -======================== - -PUBLIC - -======================= - 1 row(s) returned +*** ERROR[1017] You are not authorized to perform this operation. ---- SQL operation complete. +--- SQL operation failed with errors. >>get roles for user sql_user3; -Roles for User SQL_USER3 -======================== - -PUBLIC - -======================= - 1 row(s) returned +*** ERROR[1017] You are not authorized to perform this operation. ---- SQL operation complete. +--- SQL operation failed with errors. >>get roles for user sql_user4; -Roles for User SQL_USER4 -======================== - -PUBLIC - -======================= - 1 row(s) returned +*** ERROR[1017] You are not authorized to perform this operation. ---- SQL operation complete. +--- SQL operation failed with errors. >>get roles for user sql_user5; Roles for User SQL_USER5 @@ -2026,13 +2009,19 @@ T123_OWNERROLE >> >>get users for role t123_adminrole; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get users for role t123_plannerrole; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get users for role t123_dummyrole; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get users for role t123_ownerrole; Users granted Role T123_OWNERROLE @@ -2044,19 +2033,30 @@ SQL_USER5 1 row(s) returned --- SQL operation complete. +>>get users for role "PUBLIC"; + +--- SQL operation complete. >> >>get privileges for user sql_user1; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for user sql_user2; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for user sql_user3; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for user sql_user4; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for user sql_user5; Privileges for User SQL_USER5 @@ -2087,13 +2087,19 @@ S------ TRAFODION.T123SCH.TEAMS <Column> TEAM_NUMBER >> >>get privileges for role t123_adminrole; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for role t123_plannerrole; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for role t123_dummyrole; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get privileges for role t123_ownerrole; Privileges for Role T123_OWNERROLE @@ -2168,6 +2174,27 @@ TRAFODION."T123SCH".TEAMS 6 row(s) returned --- SQL operation complete. +>>get tables for role t123_adminrole; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get tables for role t123_ownerrole; + +Tables for Role T123_OWNERROLE +============================== + +TRAFODION."T123SCH".GAMES +TRAFODION."T123SCH".PLAYERS +TRAFODION."T123SCH".SB_HISTOGRAMS +TRAFODION."T123SCH".SB_HISTOGRAM_INTERVALS +TRAFODION."T123SCH".SB_PERSISTENT_SAMPLES +TRAFODION."T123SCH".TEAMS + +======================= + 6 row(s) returned + +--- SQL operation complete. >> >>get indexes for user sql_user1; @@ -2200,6 +2227,22 @@ TRAFODION."T123SCH".GAMES_VISITOR 1 row(s) returned --- SQL operation complete. +>>get indexes for role t123_adminrole; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get indexes for role t123_ownerrole; + +Indexes for Role T123_OWNERROLE +=============================== + +TRAFODION."T123SCH".GAMES_VISITOR + +======================= + 1 row(s) returned + +--- SQL operation complete. >> >>get views for user sql_user1; @@ -2234,37 +2277,22 @@ TRAFODION."T123SCH".PLAYERS_ON_TEAM 3 row(s) returned --- SQL operation complete. ->> ->>get libraries for user sql_user1; +>>get views for role t123_adminrole; *** ERROR[1017] You are not authorized to perform this operation. --- SQL operation failed with errors. ->>get libraries for user sql_user2; +>>get views for role t123_ownerrole; -*** ERROR[1017] You are not authorized to perform this operation. - ---- SQL operation failed with errors. ->>get libraries for user sql_user3; - -*** ERROR[1017] You are not authorized to perform this operation. - ---- SQL operation failed with errors. ->>get libraries for user sql_user4; - -*** ERROR[1017] You are not authorized to perform this operation. - ---- SQL operation failed with errors. ->>get libraries for user sql_user5; - -Libraries for User SQL_USER5 -============================ +Views for Role T123_OWNERROLE +============================= -DB__LIBMGRNAME -DB__LIBMGR_LIB_CPP +TRAFODION."T123SCH".GAMES_BY_PLAYER +TRAFODION."T123SCH".HOME_TEAMS_GAMES +TRAFODION."T123SCH".PLAYERS_ON_TEAM ======================= - 2 row(s) returned + 3 row(s) returned --- SQL operation complete. >> @@ -2282,28 +2310,23 @@ End of MXCI Session --- SQL operation failed with errors. >>get privileges for user unknown_user; -*** ERROR[8732] UNKNOWN_USER is not a registered database user or role. +*** ERROR[1340] UNKNOWN_USER is not a user. --- SQL operation failed with errors. >>get privileges for role unknown_role; -*** ERROR[8732] UNKNOWN_ROLE is not a registered database user or role. +*** ERROR[1339] UNKNOWN_ROLE is not a role. --- SQL operation failed with errors. >>get roles for user unknown_user; -Roles for User UNKNOWN_USER -=========================== - -PUBLIC +*** ERROR[1340] UNKNOWN_USER is not a user. -======================= - 1 row(s) returned - ---- SQL operation complete. +--- SQL operation failed with errors. >>get users for role unknown_role; --- SQL operation complete. +>> >>grant component privilege "SHOW" on sql_operations to "PUBLIC"; --- SQL operation complete.
http://git-wip-us.apache.org/repos/asf/trafodion/blob/afff9935/core/sql/regress/privs1/EXPECTED125 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs1/EXPECTED125 b/core/sql/regress/privs1/EXPECTED125 index 49b5283..d2c3501 100644 --- a/core/sql/regress/privs1/EXPECTED125 +++ b/core/sql/regress/privs1/EXPECTED125 @@ -1311,6 +1311,23 @@ TESTHIVE 1 row(s) returned --- SQL operation complete. +>>get roles; + +Roles +===== + +DB__HBASEROLE +DB__HIVEROLE +DB__LIBMGRROLE +DB__ROOTROLE +PUBLIC +T125_ADMINROLE +T125_ROLE1 + +======================= + 7 row(s) returned + +--- SQL operation complete. >> >>get indexes on table players; @@ -1477,7 +1494,7 @@ TESTHIVE Procedures for User SQL_USER1 ============================= -TESTHIVE +TRAFODION."T125SCH2".TESTHIVE ======================= 1 row(s) returned @@ -1491,7 +1508,7 @@ TESTHIVE Functions for Role T125_ROLE1 ============================= -TRANSLATEBITMAP +TRAFODION."T125SCH3".TRANSLATEBITMAP ======================= 1 row(s) returned @@ -1502,7 +1519,7 @@ TRANSLATEBITMAP Functions for Role T125_ADMINROLE ================================= -TRANSLATEBITMAP +TRAFODION."T125SCH3".TRANSLATEBITMAP ======================= 1 row(s) returned @@ -1683,6 +1700,21 @@ TESTHIVE --- SQL operation complete. >> +>>get schemas for user sql_user1, match 'T125SCH%'; + +--- SQL operation complete. +>>get schemas for role t125_adminrole, match 'T125SCH%'; + +Schemas for Role T125_ADMINROLE +=============================== + +T125SCH3 + +======================= + 1 row(s) returned + +--- SQL operation complete. +>> >>get functions for library t125_l1; Functions for Library T125SCH3.T125_L1 @@ -1711,7 +1743,7 @@ T125SCH3.TESTHIVE Procedures for User SQL_USER1 ============================= -TESTHIVE +TRAFODION."T125SCH2".TESTHIVE ======================= 1 row(s) returned @@ -1725,7 +1757,7 @@ TESTHIVE Functions for Role T125_ROLE1 ============================= -TRANSLATEBITMAP +TRAFODION."T125SCH3".TRANSLATEBITMAP ======================= 1 row(s) returned @@ -1736,7 +1768,7 @@ TRANSLATEBITMAP Functions for Role T125_ADMINROLE ================================= -TRANSLATEBITMAP +TRAFODION."T125SCH3".TRANSLATEBITMAP ======================= 1 row(s) returned @@ -1807,6 +1839,18 @@ T125SCH3.PLAYERS_SEQUENCE >>get procedures; --- SQL operation complete. +>>get roles; + +Roles +===== + +PUBLIC +T125_ADMINROLE + +======================= + 2 row(s) returned + +--- SQL operation complete. >> >>get indexes on table players; @@ -1870,9 +1914,15 @@ T125SCH3.PLAYERS_SEQUENCE --- SQL operation failed with errors. >>get functions for role t125_adminrole; -*** ERROR[1017] You are not authorized to perform this operation. +Functions for Role T125_ADMINROLE +================================= ---- SQL operation failed with errors. +TRAFODION."T125SCH3".TRANSLATEBITMAP + +======================= + 1 row(s) returned + +--- SQL operation complete. >> >>set schema t125sch3; @@ -1906,7 +1956,9 @@ SIDU-R- T125_ADMINROLE --- SQL operation complete. >>get privileges on table games for t125_role1; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get views in catalog trafodion, match 'T125SCH%'; Views in Catalog TRAFODION @@ -1969,7 +2021,9 @@ Privileges on Sequence T125SCH3.PLAYERS_SEQUENCE --- SQL operation complete. >>get privileges on sequence players_sequence for t125_role1; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get libraries, match 'T125%'; Libraries in Schema TRAFODION.T125SCH3 @@ -2027,6 +2081,23 @@ TESTHIVE --- SQL operation complete. >> +>>get schemas for user sql_user1, match 'T125SCH%'; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get schemas for role t125_adminrole, match 'T125SCH%'; + +Schemas for Role T125_ADMINROLE +=============================== + +T125SCH3 + +======================= + 1 row(s) returned + +--- SQL operation complete. +>> >>get functions for library t125_l1; Functions for Library T125SCH3.T125_L1 @@ -2067,9 +2138,15 @@ T125SCH3.TESTHIVE --- SQL operation failed with errors. >>get functions for role t125_adminrole; -*** ERROR[1017] You are not authorized to perform this operation. +Functions for Role T125_ADMINROLE +================================= ---- SQL operation failed with errors. +TRAFODION."T125SCH3".TRANSLATEBITMAP + +======================= + 1 row(s) returned + +--- SQL operation complete. >> >>exit; @@ -2134,6 +2211,17 @@ T125SCH2.PLAYERS_SEQUENCE >>get procedures; --- SQL operation complete. +>>get roles; + +Roles +===== + +PUBLIC + +======================= + 1 row(s) returned + +--- SQL operation complete. >> >>get indexes on table players; @@ -2209,7 +2297,7 @@ TESTHIVE Procedures for User SQL_USER1 ============================= -TESTHIVE +TRAFODION."T125SCH2".TESTHIVE ======================= 1 row(s) returned @@ -2258,7 +2346,9 @@ Privileges on Table T125SCH3.GAMES --- SQL operation complete. >>get privileges on table games for t125_role1; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get views in catalog trafodion, match 'T125SCH%'; Views in Catalog TRAFODION @@ -2283,7 +2373,9 @@ S------ SQL_USER1 --- SQL operation complete. >>get privileges on view games_by_player for user sql_user8; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get indexes in schema t125sch3; --- SQL operation complete. @@ -2303,7 +2395,9 @@ T125SCH2.PLAYERS_SEQUENCE --- SQL operation complete. >>get privileges on sequence players_sequence for t125_role1; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get libraries, match 'T125%'; --- SQL operation complete. @@ -2312,7 +2406,9 @@ T125SCH2.PLAYERS_SEQUENCE --- SQL operation complete. >>get privileges on library t125_l1 for user sql_user8; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get functions in schema t125sch3; --- SQL operation complete. @@ -2320,6 +2416,15 @@ T125SCH2.PLAYERS_SEQUENCE --- SQL operation complete. >> +>>get schemas for user sql_user1, match 'T125SCH%'; + +--- SQL operation complete. +>>get schemas for role t125_adminrole, match 'T125SCH%'; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>> >>get functions for library t125_l1; --- SQL operation complete. @@ -2332,7 +2437,7 @@ T125SCH2.PLAYERS_SEQUENCE Procedures for User SQL_USER1 ============================= -TESTHIVE +TRAFODION."T125SCH2".TESTHIVE ======================= 1 row(s) returned @@ -2426,6 +2531,18 @@ GAMES_BY_PLAYER >>get procedures; --- SQL operation complete. +>>get roles; + +Roles +===== + +PUBLIC +T125_ROLE1 + +======================= + 2 row(s) returned + +--- SQL operation complete. >> >>get indexes on table players; @@ -2528,9 +2645,15 @@ GAMES_BY_PLAYER --- SQL operation failed with errors. >>get functions for role t125_role1; -*** ERROR[1017] You are not authorized to perform this operation. +Functions for Role T125_ROLE1 +============================= ---- SQL operation failed with errors. +TRAFODION."T125SCH3".TRANSLATEBITMAP + +======================= + 1 row(s) returned + +--- SQL operation complete. >>get functions for role t125_adminrole; *** ERROR[1017] You are not authorized to perform this operation. @@ -2565,6 +2688,14 @@ SIDU-R- SQL_USER2 --- SQL operation complete. >>get privileges on table games for t125_role1; +Privileges on Table T125SCH3.GAMES +================================== + +SIDU-R- SQL_USER2 + +======================= + 1 row(s) returned + --- SQL operation complete. >>get views in catalog trafodion, match 'T125SCH%'; @@ -2592,7 +2723,9 @@ S------ SQL_USER2 --- SQL operation complete. >>get privileges on view games_by_player for user sql_user8; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get indexes in schema t125sch3; --- SQL operation complete. @@ -2629,7 +2762,9 @@ Privileges on Library T125SCH3.T125_L1 --- SQL operation complete. >>get privileges on library t125_l1 for user sql_user8; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get functions in schema t125sch3; Functions in Schema TRAFODION.T125SCH3 @@ -2645,6 +2780,17 @@ TRANSLATEBITMAP --- SQL operation complete. >> +>>get schemas for user sql_user1, match 'T125SCH%'; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get schemas for role t125_adminrole, match 'T125SCH%'; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>> >>get functions for library t125_l1; Functions for Library T125SCH3.T125_L1 @@ -2672,9 +2818,15 @@ T125SCH3.TRANSLATEBITMAP --- SQL operation failed with errors. >>get functions for role t125_role1; -*** ERROR[1017] You are not authorized to perform this operation. +Functions for Role T125_ROLE1 +============================= ---- SQL operation failed with errors. +TRAFODION."T125SCH3".TRANSLATEBITMAP + +======================= + 1 row(s) returned + +--- SQL operation complete. >>get functions for role t125_adminrole; *** ERROR[1017] You are not authorized to perform this operation. @@ -2738,6 +2890,18 @@ T125SCH3 >>get procedures; --- SQL operation complete. +>>get roles; + +Roles +===== + +PUBLIC +T125_ROLE1 + +======================= + 2 row(s) returned + +--- SQL operation complete. >> >>get indexes on table players; @@ -2803,9 +2967,15 @@ TEAMS --- SQL operation complete. >>get functions for role t125_role1; -*** ERROR[1017] You are not authorized to perform this operation. +Functions for Role T125_ROLE1 +============================= ---- SQL operation failed with errors. +TRAFODION."T125SCH3".TRANSLATEBITMAP + +======================= + 1 row(s) returned + +--- SQL operation complete. >>get functions for role t125_adminrole; *** ERROR[1017] You are not authorized to perform this operation. @@ -2840,7 +3010,9 @@ PLAYERS --- SQL operation complete. >>get privileges on view games_by_player for user sql_user8; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get indexes in schema t125sch3; --- SQL operation complete. @@ -2877,7 +3049,9 @@ Privileges on Library T125SCH3.T125_L1 --- SQL operation complete. >>get privileges on library t125_l1 for user sql_user8; ---- SQL operation complete. +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >>get functions in schema t125sch3; Functions in Schema TRAFODION.T125SCH3 @@ -2893,6 +3067,17 @@ TRANSLATEBITMAP --- SQL operation complete. >> +>>get schemas for user sql_user1, match 'T125SCH%'; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get schemas for role t125_adminrole, match 'T125SCH%'; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>> >>get functions for library t125_l1; Functions for Library T125SCH3.T125_L1 @@ -2918,9 +3103,15 @@ T125SCH3.TRANSLATEBITMAP --- SQL operation complete. >>get functions for role t125_role1; -*** ERROR[1017] You are not authorized to perform this operation. +Functions for Role T125_ROLE1 +============================= ---- SQL operation failed with errors. +TRAFODION."T125SCH3".TRANSLATEBITMAP + +======================= + 1 row(s) returned + +--- SQL operation complete. >>get functions for role t125_adminrole; *** ERROR[1017] You are not authorized to perform this operation. http://git-wip-us.apache.org/repos/asf/trafodion/blob/afff9935/core/sql/regress/privs1/EXPECTED141 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs1/EXPECTED141 b/core/sql/regress/privs1/EXPECTED141 index 598dc8e..1e1fb06 100644 --- a/core/sql/regress/privs1/EXPECTED141 +++ b/core/sql/regress/privs1/EXPECTED141 @@ -142,6 +142,9 @@ >>grant component privilege "CREATE" on sql_operations to t141_role2; --- SQL operation complete. +>>revoke component privilege "SHOW" on sql_operations from "PUBLIC"; + +--- SQL operation complete. >> >>-- >>============================================================================ >>--obey TEST141(test_private_user); @@ -319,6 +322,39 @@ T141_USER3.U3T1 BT -2 T141_ROLE3 SIDU-R- >>execute get_col_privs; --- 0 row(s) selected. +>>get schemas for role t141_role1; + +Schemas for Role T141_ROLE1 +=========================== + +T141_USER1 + +======================= + 1 row(s) returned + +--- SQL operation complete. +>>get schemas for role t141_role2; + +Schemas for Role T141_ROLE2 +=========================== + +T141_USER2 + +======================= + 1 row(s) returned + +--- SQL operation complete. +>>get schemas for role t141_role3; + +Schemas for Role T141_ROLE3 +=========================== + +T141_USER3 + +======================= + 1 row(s) returned + +--- SQL operation complete. >> >>-- t141_role2 has create privilege on all schemas. >>-- have sql_user2 who belongs to t141_role2 create some tables in @@ -416,6 +452,45 @@ CREATE VIEW TRAFODION.T141_USER1.U2V2 (C1, C2) AS --- SQL operation failed with errors. >> +>>-- test get schemas for user and role +>>-- schema owner sql_user2: +>>-- first test returns t144_user2 +>>-- second test returns no rows +>>-- schema owner t141_role: both tests return t144_user2 +>>get schemas for user sql_user2; + +Schemas for User SQL_USER2 +========================== + +T141_USER2 + +======================= + 1 row(s) returned + +--- SQL operation complete. +>>get schemas for role t141_role2; + +Schemas for Role T141_ROLE2 +=========================== + +T141_USER2 + +======================= + 1 row(s) returned + +--- SQL operation complete. +>> +>>-- these should fail with no priv +>>get schemas for user sql_user1; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get schemas for role t141_role1; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >> >>-- >>============================================================================ >>exit; @@ -664,6 +739,39 @@ T141_USER1.U1T4 0 DB__ROOT SQL_USER3 S------ ---- T141_USER1.U1T4 2 DB__ROOT SQL_USER3 S------ ------- --- 4 row(s) selected. +>>get schemas for user sql_user1; + +Schemas for User SQL_USER1 +========================== + +T141_USER1 + +======================= + 1 row(s) returned + +--- SQL operation complete. +>>get schemas for user sql_user2; + +Schemas for User SQL_USER2 +========================== + +T141_USER2 + +======================= + 1 row(s) returned + +--- SQL operation complete. +>>get schemas for user sql_user3; + +Schemas for User SQL_USER3 +========================== + +T141_USER3 + +======================= + 1 row(s) returned + +--- SQL operation complete. >> >>-- have sql_user2 create some tables in sql_user1's schema >>-- have sql_user2 create some views, views that reference user1's objects >>fail @@ -757,6 +865,37 @@ CREATE VIEW TRAFODION.T141_USER1.U2V1 AS --- SQL operation failed with errors. >> +>>-- test get schemas for user and role +>>-- schema owner sql_user2: +>>-- first test returns t144_user2 +>>-- second test returns no rows +>>-- schema owner t141_role: both tests return t144_user2 +>>get schemas for user sql_user2; + +Schemas for User SQL_USER2 +========================== + +T141_USER2 + +======================= + 1 row(s) returned + +--- SQL operation complete. +>>get schemas for role t141_role2; + +--- SQL operation complete. +>> +>>-- these should fail with no priv +>>get schemas for user sql_user1; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>get schemas for role t141_role1; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. >> >>-- >>============================================================================ >>exit; @@ -1028,9 +1167,9 @@ CREATE VIEW TRAFODION.T141_USER3.U3V3 AS *** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T141_USER1.U1T3. --- SQL operation failed with errors. ->>get tables, match 'U3T%'; +>>get tables in schema t141_user1, match 'U3T%'; -Tables in Schema TRAFODION.T141_USER3 +Tables in Schema TRAFODION.T141_USER1 ===================================== U3T1 http://git-wip-us.apache.org/repos/asf/trafodion/blob/afff9935/core/sql/regress/privs1/TEST123 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs1/TEST123 b/core/sql/regress/privs1/TEST123 index 6214a55..d24ab6c 100644 --- a/core/sql/regress/privs1/TEST123 +++ b/core/sql/regress/privs1/TEST123 @@ -29,7 +29,15 @@ -- get roles for user -- get privileges for role -- get privileges for user --- get [tables | indexes | libraries | views] for user +-- get [tables | indexes | views] for user +-- get [tables | indexes | views] for role +-- +-- see privs1/TEST141 for +-- get schemas for user +-- get schemas for role +-- see privs2/TEST144 for +-- get [libraries, functions, table_mapping functions, procedures] for user +-- get [libraries, functions, table_mapping functions, procedures] for role -- -- Users and roles used -- t123_adminrole: granted update and delete on games/teams @@ -179,6 +187,7 @@ get users for role t123_adminrole; get users for role t123_plannerrole; get users for role t123_dummyrole; get users for role t123_ownerrole; +get users for role "PUBLIC"; get privileges for user sql_user1; get privileges for user sql_user2; @@ -197,24 +206,24 @@ get tables for user sql_user2; get tables for user sql_user3; get tables for user sql_user4; get tables for user sql_user5; +get tables for role t123_adminrole; +get tables for role t123_ownerrole; get indexes for user sql_user1; get indexes for user sql_user2; get indexes for user sql_user3; get indexes for user sql_user4; get indexes for user sql_user5; +get indexes for role t123_adminrole; +get indexes for role t123_ownerrole; get views for user sql_user1; get views for user sql_user2; get views for user sql_user3; get views for user sql_user4; get views for user sql_user5; - -get libraries for user sql_user1; -get libraries for user sql_user2; -get libraries for user sql_user3; -get libraries for user sql_user4; -get libraries for user sql_user5; +get views for role t123_adminrole; +get views for role t123_ownerrole; ?section other get privileges for user "PUBLIC"; @@ -222,3 +231,4 @@ get privileges for user unknown_user; get privileges for role unknown_role; get roles for user unknown_user; get users for role unknown_role; + http://git-wip-us.apache.org/repos/asf/trafodion/blob/afff9935/core/sql/regress/privs1/TEST125 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs1/TEST125 b/core/sql/regress/privs1/TEST125 index 5a8e6e3..7c2344e 100644 --- a/core/sql/regress/privs1/TEST125 +++ b/core/sql/regress/privs1/TEST125 @@ -24,6 +24,7 @@ -- -- Tests that get statements return only what the user can see -- get schemas (in catalog) +-- get schemas for role (get schemas for user is part of privs1/TEST144) -- get tables (in schemas) -- get views (in catalogs) -- get views (in schemas) @@ -35,8 +36,8 @@ -- get privileges on view -- get privileges on sequence -- get privileges on library --- (tests for get privileges on functions, table_mapping functions, and --- procedures is part of privs2/TEST144) +-- (tests for get privileges on functions, table_mapping functions, +-- libraries and procedures is part of privs2/TEST144) -- -- ============================================================================ cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; @@ -266,6 +267,7 @@ get sequences, match 'T125SCH%'; get libraries, match 'T125%'; get functions; get procedures; +get roles; get indexes on table players; get views on table players; @@ -306,6 +308,9 @@ get privileges on library t125_l1 for user sql_user8; get functions in schema t125sch3; get procedures; +get schemas for user sql_user1, match 'T125SCH%'; +get schemas for role t125_adminrole, match 'T125SCH%'; + get functions for library t125_l1; get procedures for library t125_l2; http://git-wip-us.apache.org/repos/asf/trafodion/blob/afff9935/core/sql/regress/privs1/TEST141 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs1/TEST141 b/core/sql/regress/privs1/TEST141 index 8abf627..06da585 100755 --- a/core/sql/regress/privs1/TEST141 +++ b/core/sql/regress/privs1/TEST141 @@ -24,6 +24,7 @@ -- ============================================================================ -- Functionality: Extended support for views for all levels: -- column, object, component +-- get schema for {user | role} authID -- -- Added in response to JIRA 1100 -- @@ -55,6 +56,7 @@ drop schema if exists t141_user3 cascade; revoke component privilege "CREATE" on sql_operations from sql_user2; revoke component privilege "CREATE" on sql_operations from t141_role2; revoke component privilege "DML_SELECT_METADATA" on sql_operations from "PUBLIC"; +grant component privilege "SHOW" on sql_operations to "PUBLIC"; revoke role t141_role1 from sql_user1; revoke role t141_role2 from sql_user2; @@ -188,6 +190,7 @@ grant role t141_role_md to sql_user1, sql_user2, sql_user3; -- set up component privilege infrastructure grant component privilege "CREATE" on sql_operations to sql_user2; grant component privilege "CREATE" on sql_operations to t141_role2; +revoke component privilege "SHOW" on sql_operations from "PUBLIC"; -- ============================================================================ ?section create_objects @@ -292,6 +295,9 @@ grant update on u1t4 to sql_user3; grant select(c1, c3) on u1t4 to sql_user3; execute get_obj_privs; execute get_col_privs; +get schemas for user sql_user1; +get schemas for user sql_user2; +get schemas for user sql_user3; -- have sql_user2 create some tables in sql_user1's schema -- have sql_user2 create some views, views that reference user1's objects fail @@ -336,6 +342,9 @@ obey TEST141(create_objects); set schema t141_user1; execute get_obj_privs; execute get_col_privs; +get schemas for role t141_role1; +get schemas for role t141_role2; +get schemas for role t141_role3; -- t141_role2 has create privilege on all schemas. -- have sql_user2 who belongs to t141_role2 create some tables in @@ -428,6 +437,17 @@ create view u2v1 as select u1.c1, u2.c1 from u2t1 as u2, t141_user1.u1t2 as u1; -- user2 has no privs on u3t1 create view u2v1 as select * from t141_user1.u3t1; +-- test get schemas for user and role +-- schema owner sql_user2: +-- first test returns t144_user2 +-- second test returns no rows +-- schema owner t141_role: both tests return t144_user2 +get schemas for user sql_user2; +get schemas for role t141_role2; + +-- these should fail with no priv +get schemas for user sql_user1; +get schemas for role t141_role1; -- ============================================================================ ?section user3_objects @@ -455,7 +475,7 @@ create view u3v4 as select c1, c3 from t141_user1.u1t4; -- following fail create view u3v5 as select c1, c3 from t141_user1.u1t3; -get tables, match 'U3T%'; +get tables in schema t141_user1, match 'U3T%'; get views; -- ============================================================================ http://git-wip-us.apache.org/repos/asf/trafodion/blob/afff9935/core/sql/regress/privs2/EXPECTED135 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs2/EXPECTED135 b/core/sql/regress/privs2/EXPECTED135 index 744b645..055e89a 100644 --- a/core/sql/regress/privs2/EXPECTED135 +++ b/core/sql/regress/privs2/EXPECTED135 @@ -568,9 +568,7 @@ End of MXCI Session --- SQL operation failed with errors. >>revoke all on t135_t1 from sql_user3; -*** ERROR[1025] Request failed. Dependent object TRAFODION.T135SCH_USER3.T135_V1_USER3 exists. - ---- SQL operation failed with errors. +--- SQL operation complete. >> >>sh sqlci -i "TEST135(user3_drops)" -u sql_user3; >>drop table t135_t3 cascade;
