Privs2 - TEST140 Fixed regression issue with TEST140. TEST140 assumes standard schema "sch" exists. In this case it does not. So changed requests to use a different schema
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/5af07d67 Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/5af07d67 Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/5af07d67 Branch: refs/heads/master Commit: 5af07d67b5301be0cf0c59f15345056496253f80 Parents: 8652aeb Author: Roberta Marton <[email protected]> Authored: Mon Jan 25 20:01:55 2016 +0000 Committer: Roberta Marton <[email protected]> Committed: Mon Jan 25 20:01:55 2016 +0000 ---------------------------------------------------------------------- core/sql/regress/privs2/EXPECTED140 | 56 +++++++++++++++++++++----------- core/sql/regress/privs2/TEST140 | 13 ++++---- 2 files changed, 44 insertions(+), 25 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5af07d67/core/sql/regress/privs2/EXPECTED140 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs2/EXPECTED140 b/core/sql/regress/privs2/EXPECTED140 index 052709f..dc4a445 100644 --- a/core/sql/regress/privs2/EXPECTED140 +++ b/core/sql/regress/privs2/EXPECTED140 @@ -11,7 +11,7 @@ MESSAGEFILE /mnt/rmarton/gitws/incubator-trafodion/core/sqf/export/ ... MESSAGEFILE LANG US English MESSAGEFILE VRSN {2016-01-21 17:33 LINUX:EDEV05/rmarton} SQL CATALOG TRAFODION -SQL SCHEMA SCH +SQL SCHEMA T140_SHARED_VIEWS SQL USER CONNECTED user not connected SQL USER DB NAME DB__ROOT SQL USER ID 33333 @@ -57,6 +57,9 @@ WARNINGS on >>create shared schema t140_shared_views; --- SQL operation complete. +>>set schema t140_shared_views; + +--- SQL operation complete. >> >>-- compile cpp program for function >>sh rm -f ./etest140.dll; @@ -66,9 +69,6 @@ WARNINGS on >>set pattern $$QUOTE$$ ''''; >> >>-- create the library and udf ->>set schema sch; - ---- SQL operation complete. >>create library t140_l1 file $$QUOTE$$ $$REGRRUNDIR$$/$$DLL$$ $$QUOTE$$ ; --- SQL operation complete. @@ -78,7 +78,7 @@ WARNINGS on +>deterministic no sql final call allow any parallelism state area size 1024 ; --- SQL operation complete. ->>grant execute on function sch.t140_translatePrivsBitmap to "PUBLIC"; +>>grant execute on function t140_translatePrivsBitmap to "PUBLIC"; --- SQL operation complete. >> @@ -234,8 +234,8 @@ TEAMS +> object_type as type, +> substring(authname(grantor_id),1,10) as grantor, +> substring(authname(grantee_id),1,10) as grantee, -+> sch.t140_translatePrivsBitmap(privileges_bitmap) as granted_privs, -+> sch.t140_translatePrivsBitmap(grantable_bitmap) as grantable_privs ++> t140_shared_views.t140_translatePrivsBitmap(privileges_bitmap) as granted_privs, ++> t140_shared_views.t140_translatePrivsBitmap(grantable_bitmap) as grantable_privs +>from "_PRIVMGR_MD_".object_privileges +>where object_uid in +> (select object_uid @@ -294,8 +294,8 @@ SQL_USER1 +> object_type as type, +> substring(authname(grantor_id),1,10) as grantor, +> substring(authname(grantee_id),1,10) as grantee, -+> sch.t140_translatePrivsBitmap(privileges_bitmap) as granted_privs, -+> sch.t140_translatePrivsBitmap(grantable_bitmap) as grantable_privs ++> t140_shared_views.t140_translatePrivsBitmap(privileges_bitmap) as granted_privs, ++> t140_shared_views.t140_translatePrivsBitmap(grantable_bitmap) as grantable_privs +>from "_PRIVMGR_MD_".object_privileges +>where object_uid in +> (select object_uid @@ -314,6 +314,9 @@ OBJECT_NAME TRAFODION.T140_SHARED_VIEWS.HOME_TEAM_GA VI -2 DB__ROOT S----R- S----R- TRAFODION.T140_SHARED_VIEWS.HOME_TEAM_GA VI DB__ROOT SQL_USER1 S------ S------ TRAFODION.T140_SHARED_VIEWS.HOME_TEAM_GA VI SQL_USER1 SQL_USER2 S------ NONE +TRAFODION.T140_SHARED_VIEWS.T140_L1 LB -2 DB__ROOT ---UG-- ---UG-- +TRAFODION.T140_SHARED_VIEWS.T140_TRANSLA UR -2 DB__ROOT ------E ------E +TRAFODION.T140_SHARED_VIEWS.T140_TRANSLA UR DB__ROOT -1 ------E NONE TRAFODION.T140_USER1_PRIVATE.GAMES BT -2 SQL_USER1 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.GAMES BT SQL_USER1 SQL_USER2 S------ NONE TRAFODION.T140_USER1_PRIVATE.PLAYERS BT -2 SQL_USER1 SIDU-R- SIDU-R- @@ -321,7 +324,7 @@ TRAFODION.T140_USER1_PRIVATE.PLAYERS TRAFODION.T140_USER1_PRIVATE.TEAMS BT -2 SQL_USER1 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.TEAMS BT SQL_USER1 SQL_USER2 SIDU-R- SIDU-R- ---- 9 row(s) selected. +--- 12 row(s) selected. >> >>exit; @@ -454,8 +457,8 @@ Zachary +> object_type as type, +> substring(authname(grantor_id),1,10) as grantor, +> substring(authname(grantee_id),1,10) as grantee, -+> sch.t140_translatePrivsBitmap(privileges_bitmap) as granted_privs, -+> sch.t140_translatePrivsBitmap(grantable_bitmap) as grantable_privs ++> t140_shared_views.t140_translatePrivsBitmap(privileges_bitmap) as granted_privs, ++> t140_shared_views.t140_translatePrivsBitmap(grantable_bitmap) as grantable_privs +>from "_PRIVMGR_MD_".object_privileges +>where object_uid in +> (select object_uid @@ -475,6 +478,9 @@ TRAFODION.T140_SHARED_VIEWS.GAMES_BY_PLA TRAFODION.T140_SHARED_VIEWS.HOME_TEAM_GA VI -2 DB__ROOT S----R- S----R- TRAFODION.T140_SHARED_VIEWS.HOME_TEAM_GA VI DB__ROOT SQL_USER1 S------ S------ TRAFODION.T140_SHARED_VIEWS.HOME_TEAM_GA VI SQL_USER1 SQL_USER2 S------ NONE +TRAFODION.T140_SHARED_VIEWS.T140_L1 LB -2 DB__ROOT ---UG-- ---UG-- +TRAFODION.T140_SHARED_VIEWS.T140_TRANSLA UR -2 DB__ROOT ------E ------E +TRAFODION.T140_SHARED_VIEWS.T140_TRANSLA UR DB__ROOT -1 ------E NONE TRAFODION.T140_USER1_PRIVATE.GAMES BT -2 SQL_USER1 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.GAMES BT SQL_USER1 SQL_USER2 S------ NONE TRAFODION.T140_USER1_PRIVATE.PLAYERS BT -2 SQL_USER1 SIDU-R- SIDU-R- @@ -484,7 +490,7 @@ TRAFODION.T140_USER1_PRIVATE.TEAMS TRAFODION.T140_USER1_PRIVATE.TEAMS BT SQL_USER1 SQL_USER2 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.TEAMS BT SQL_USER2 SQL_USER3 S-D---- S-D---- ---- 12 row(s) selected. +--- 15 row(s) selected. >> >>exit; @@ -612,6 +618,9 @@ TRAFODION.T140_SHARED_VIEWS.HOME_TEAM_GA TRAFODION.T140_SHARED_VIEWS.HOME_TEAM_GA VI DB__ROOT SQL_USER1 S------ S------ TRAFODION.T140_SHARED_VIEWS.HOME_TEAM_GA VI SQL_USER1 SQL_USER2 S------ NONE TRAFODION.T140_SHARED_VIEWS.PLAYERS_ON_T VI -2 SQL_USER3 S------ NONE +TRAFODION.T140_SHARED_VIEWS.T140_L1 LB -2 DB__ROOT ---UG-- ---UG-- +TRAFODION.T140_SHARED_VIEWS.T140_TRANSLA UR -2 DB__ROOT ------E ------E +TRAFODION.T140_SHARED_VIEWS.T140_TRANSLA UR DB__ROOT -1 ------E NONE TRAFODION.T140_USER1_PRIVATE.GAMES BT -2 SQL_USER1 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.GAMES BT SQL_USER1 SQL_USER2 S------ NONE TRAFODION.T140_USER1_PRIVATE.PLAYERS BT -2 SQL_USER1 SIDU-R- SIDU-R- @@ -623,7 +632,7 @@ TRAFODION.T140_USER1_PRIVATE.TEAMS TRAFODION.T140_USER1_PRIVATE.TEAMS BT SQL_USER2 SQL_USER3 S-D---- S-D---- TRAFODION.T140_USER1_PRIVATE.TEAMS BT SQL_USER3 SQL_USER4 S------ NONE ---- 15 row(s) selected. +--- 18 row(s) selected. >> >>exit; @@ -711,6 +720,9 @@ TRAFODION.T140_SHARED_VIEWS.HOME_TEAM_GA TRAFODION.T140_SHARED_VIEWS.HOME_TEAM_GA VI DB__ROOT SQL_USER1 S------ S------ TRAFODION.T140_SHARED_VIEWS.HOME_TEAM_GA VI SQL_USER1 SQL_USER2 S------ NONE TRAFODION.T140_SHARED_VIEWS.PLAYERS_ON_T VI -2 SQL_USER3 S------ NONE +TRAFODION.T140_SHARED_VIEWS.T140_L1 LB -2 DB__ROOT ---UG-- ---UG-- +TRAFODION.T140_SHARED_VIEWS.T140_TRANSLA UR -2 DB__ROOT ------E ------E +TRAFODION.T140_SHARED_VIEWS.T140_TRANSLA UR DB__ROOT -1 ------E NONE TRAFODION.T140_SHARED_VIEWS.TEAM_NAMES VI -2 SQL_USER4 S------ NONE TRAFODION.T140_USER1_PRIVATE.GAMES BT -2 SQL_USER1 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.GAMES BT SQL_USER1 SQL_USER2 S------ NONE @@ -723,7 +735,7 @@ TRAFODION.T140_USER1_PRIVATE.TEAMS TRAFODION.T140_USER1_PRIVATE.TEAMS BT SQL_USER2 SQL_USER3 S-D---- S-D---- TRAFODION.T140_USER1_PRIVATE.TEAMS BT SQL_USER3 SQL_USER4 S------ NONE ---- 16 row(s) selected. +--- 19 row(s) selected. >> >>exit; @@ -745,8 +757,8 @@ SQL_USER5 +> object_type as type, +> substring(authname(grantor_id),1,10) as grantor, +> substring(authname(grantee_id),1,10) as grantee, -+> sch.t140_translatePrivsBitmap(privileges_bitmap) as granted_privs, -+> sch.t140_translatePrivsBitmap(grantable_bitmap) as grantable_privs ++> t140_shared_views.t140_translatePrivsBitmap(privileges_bitmap) as granted_privs, ++> t140_shared_views.t140_translatePrivsBitmap(grantable_bitmap) as grantable_privs +>from "_PRIVMGR_MD_".object_privileges +>where object_uid in +> (select object_uid @@ -835,6 +847,9 @@ TRAFODION.T140_SHARED_VIEWS.HOME_TEAM_GA TRAFODION.T140_SHARED_VIEWS.HOME_TEAM_GA VI DB__ROOT SQL_USER1 S------ S------ TRAFODION.T140_SHARED_VIEWS.HOME_TEAM_GA VI SQL_USER1 SQL_USER2 S------ NONE TRAFODION.T140_SHARED_VIEWS.PLAYERS_ON_T VI -2 SQL_USER3 S------ NONE +TRAFODION.T140_SHARED_VIEWS.T140_L1 LB -2 DB__ROOT ---UG-- ---UG-- +TRAFODION.T140_SHARED_VIEWS.T140_TRANSLA UR -2 DB__ROOT ------E ------E +TRAFODION.T140_SHARED_VIEWS.T140_TRANSLA UR DB__ROOT -1 ------E NONE TRAFODION.T140_SHARED_VIEWS.TEAM_NAMES VI -2 SQL_USER4 S------ NONE TRAFODION.T140_USER1_PRIVATE.GAMES BT -2 SQL_USER1 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.GAMES BT SQL_USER1 SQL_USER2 SI----- SI----- @@ -854,7 +869,7 @@ TRAFODION.T140_USER1_PRIVATE.TEAMS TRAFODION.T140_USER1_PRIVATE.TEAMS BT SQL_USER2 SQL_USER3 S-D---- S-D---- TRAFODION.T140_USER1_PRIVATE.TEAMS BT SQL_USER3 SQL_USER4 S------ NONE ---- 23 row(s) selected. +--- 26 row(s) selected. >> >>-- user6 tries to grant select to user7 >>grant select on games to sql_user7 by sql_user6; @@ -904,6 +919,9 @@ TRAFODION.T140_SHARED_VIEWS.HOME_TEAM_GA TRAFODION.T140_SHARED_VIEWS.HOME_TEAM_GA VI DB__ROOT SQL_USER1 S------ S------ TRAFODION.T140_SHARED_VIEWS.HOME_TEAM_GA VI SQL_USER1 SQL_USER2 S------ NONE TRAFODION.T140_SHARED_VIEWS.PLAYERS_ON_T VI -2 SQL_USER3 S------ NONE +TRAFODION.T140_SHARED_VIEWS.T140_L1 LB -2 DB__ROOT ---UG-- ---UG-- +TRAFODION.T140_SHARED_VIEWS.T140_TRANSLA UR -2 DB__ROOT ------E ------E +TRAFODION.T140_SHARED_VIEWS.T140_TRANSLA UR DB__ROOT -1 ------E NONE TRAFODION.T140_SHARED_VIEWS.TEAM_NAMES VI -2 SQL_USER4 S------ NONE TRAFODION.T140_USER1_PRIVATE.GAMES BT -2 SQL_USER1 SIDU-R- SIDU-R- TRAFODION.T140_USER1_PRIVATE.GAMES BT SQL_USER1 SQL_USER2 SI----- SI----- @@ -921,6 +939,6 @@ TRAFODION.T140_USER1_PRIVATE.TEAMS TRAFODION.T140_USER1_PRIVATE.TEAMS BT SQL_USER2 SQL_USER3 S-D---- S-D---- TRAFODION.T140_USER1_PRIVATE.TEAMS BT SQL_USER3 SQL_USER4 S------ NONE ---- 21 row(s) selected. +--- 24 row(s) selected. >> >>log; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5af07d67/core/sql/regress/privs2/TEST140 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs2/TEST140 b/core/sql/regress/privs2/TEST140 index 153c19d..08b9f28 100755 --- a/core/sql/regress/privs2/TEST140 +++ b/core/sql/regress/privs2/TEST140 @@ -48,8 +48,9 @@ exit; revoke select on "_MD_".objects from sql_user1; revoke select on "_PRIVMGR_MD_".object_privileges from sql_user1; -drop function sch.t140_translatePrivsBitmap; -drop library sch.t140_l1; +set schema t140_shared_views; +drop function t140_translatePrivsBitmap; +drop library t140_l1; drop schema t140_user1_private cascade; drop schema t140_user1_shared cascade; @@ -86,6 +87,7 @@ grant select on "_PRIVMGR_MD_".object_privileges to metadata_access; grant select on "_MD_".auths to metadata_access; create shared schema t140_shared_views; +set schema t140_shared_views; -- compile cpp program for function sh rm -f ./etest140.dll; @@ -95,13 +97,12 @@ set pattern $$DLL$$ etest140.dll; set pattern $$QUOTE$$ ''''; -- create the library and udf -set schema sch; create library t140_l1 file $$QUOTE$$ $$REGRRUNDIR$$/$$DLL$$ $$QUOTE$$ ; create function t140_translatePrivsBitmap(bitmap largeint) returns (bitmap_string char (20)) language c parameter style sql external name 'translateBitmap' library t140_l1 deterministic no sql final call allow any parallelism state area size 1024 ; -grant execute on function sch.t140_translatePrivsBitmap to "PUBLIC"; +grant execute on function t140_translatePrivsBitmap to "PUBLIC"; -- private schema owned by sql_user1 create schema t140_user1_private authorization sql_user1; @@ -217,8 +218,8 @@ select distinct object_type as type, substring(authname(grantor_id),1,10) as grantor, substring(authname(grantee_id),1,10) as grantee, - sch.t140_translatePrivsBitmap(privileges_bitmap) as granted_privs, - sch.t140_translatePrivsBitmap(grantable_bitmap) as grantable_privs + t140_shared_views.t140_translatePrivsBitmap(privileges_bitmap) as granted_privs, + t140_shared_views.t140_translatePrivsBitmap(grantable_bitmap) as grantable_privs from "_PRIVMGR_MD_".object_privileges where object_uid in (select object_uid
