You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by rm...@apache.org on 2016/01/12 23:47:11 UTC
[11/20] incubator-trafodion git commit: TRAFODION-1087 &
TRAFODION-1671 fixes
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/e3d65c15/core/sql/regress/catman1/TEST140
----------------------------------------------------------------------
diff --git a/core/sql/regress/catman1/TEST140 b/core/sql/regress/catman1/TEST140
deleted file mode 100755
index 153c19d..0000000
--- a/core/sql/regress/catman1/TEST140
+++ /dev/null
@@ -1,433 +0,0 @@
--- ============================================================================
--- TEST140 - tests initializing, dropping and upgrading privilege metadata
---
--- @@@ START COPYRIGHT @@@
---
--- Licensed to the Apache Software Foundation (ASF) under one
--- or more contributor license agreements. See the NOTICE file
--- distributed with this work for additional information
--- regarding copyright ownership. The ASF licenses this file
--- to you under the Apache License, Version 2.0 (the
--- "License"); you may not use this file except in compliance
--- with the License. You may obtain a copy of the License at
---
--- http://www.apache.org/licenses/LICENSE-2.0
---
--- Unless required by applicable law or agreed to in writing,
--- software distributed under the License is distributed on an
--- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
--- KIND, either express or implied. See the License for the
--- specific language governing permissions and limitations
--- under the License.
---
--- @@@ END COPYRIGHT @@@
---
--- This tests the following commands:
---
--- GRANT using the WGO command
--- REVOKE using the WGO command
---
--- Sections:
--- clean_up - removes database setup
--- set_up - prepares for test
--- tests - runs tests
--- ============================================================================
-
-obey TEST140(clean_up);
-log LOG140 clear;
-env;
-obey TEST140(create_db);
-obey TEST140(set_up);
-obey TEST140(tests);
-obey TEST140(revoke_tests);
-log;
-obey TEST140(clean_up);
-exit;
-
-?section clean_up
-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;
-
-drop schema t140_user1_private cascade;
-drop schema t140_user1_shared cascade;
-drop schema t140_priv1_private cascade;
-drop schema t140_priv2_shared cascade;
-drop schema t140_shared_views cascade;
-
-revoke role priv1, priv2, priv3, priv4 from sql_user5;
-revoke role metadata_access from sql_user1, sql_user2, sql_user3,
- sql_user4, sql_user5, sql_user6, sql_user7, sql_user8, sql_user9;
-revoke select on "_MD_".objects from metadata_access;
-revoke select on "_PRIVMGR_MD_".object_privileges from metadata_access;
-revoke select on "_MD_".auths from metadata_access;
-
-drop role priv1;
-drop role priv2;
-drop role priv3;
-drop role priv4;
-drop role metadata_access;
-
-?section create_db
--- create roles
-create role priv1;
-create role priv2;
-create role priv3;
-create role priv4;
-create role metadata_access;
-grant role priv1, priv2, priv3, priv4 to sql_user5;
-grant role metadata_access to sql_user1, sql_user2, sql_user3,
- sql_user4, sql_user5, sql_user6, sql_user7, sql_user8, sql_user9;
-
-grant select on "_MD_".objects to metadata_access;
-grant select on "_PRIVMGR_MD_".object_privileges to metadata_access;
-grant select on "_MD_".auths to metadata_access;
-
-create shared schema t140_shared_views;
-
--- compile cpp program for function
-sh rm -f ./etest140.dll;
-sh sh $$scriptsdir$$/tools/dll-compile.ksh etest140.cpp
- 2>&1 | tee LOG140-SECONDARY;
-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";
-
--- private schema owned by sql_user1
-create schema t140_user1_private authorization sql_user1;
-set schema t140_user1_private;
-obey TEST140(create_tables);
-get tables in schema t140_user1_private;
-
-
--- shared schema owned by sql_user5
---create shared schema t140_user1_shared authorization sql_user5;
---set schema t140_user5_shared;
---obey TEST140(create_tables);
---get tables in schema t140_user5_shared;
-
--- private schema owned by role priv1
---create private schema t140_priv1_private authorization priv1;
---set schema t140_priv1_private;
---obey TEST140(create_tables);
---get tables in schema t140_priv1_private;
-
--- shared schema owner by role priv2
---create shared schema t140_priv2_shared authorization priv2;
---set schema t140_priv2_shared;
---obey TEST140(create_tables);
---get tables in schema t140_priv2_shared;
-
-?section create_tables
-
--- Creates and loads three tables: teams, games, players
--- All tables have a check constraint (other than NOT NULL)
--- Table games has an index
--- Table players has a RI contraint referencing teams
--- A view exists between games & teams
-
-create table teams
- (team_number int not null primary key,
- team_name char(20) not null,
- team_contact varchar(50) not null,
- team_contact_number char (10) not null
- )
- ;
-
-alter table teams add constraint valid_team_no check (team_number > 0);
-
-insert into teams values
- (1, 'White Socks', 'Sam','4082282222'),
- (2, 'Giants', 'Joe', '5102839483'),
- (3, 'Cardinals', 'Stella', '9513849384'),
- (4, 'Indians', 'Matt', '5128383748'),
- (5, 'Tigers', 'Ronit', '6198273827');
-
-create table games
- ( home_team_number int not null,
- visitor_team_number int not null,
- game_number int not null primary key,
- game_time timestamp not null,
- game_location varchar(50) not null)
- ;
-create index home_games on games (home_team_number);
-
-alter table games add constraint valid_game_number check (game_number > 0);
-
-insert into games values
- (1, 2, 1, timestamp '2009-04-23 19:30:00', 'California'),
- (1, 3, 2, timestamp '2009-04-24 19:30:00', 'California'),
- (1, 4, 3, timestamp '2009-04-25 10:00:00', 'Oklahoma'),
- (2, 3, 4, timestamp '2009-04-25 13:30:00', 'Michigan'),
- (1, 5, 5, timestamp '2009-04-25 15:00:00', 'Oklahoma'),
- (2, 5, 6, timestamp '2009-04-27 17:00:00', 'New York'),
- (3, 4, 7, timestamp '2009-04-28 17:00:00', 'Florida'),
- (4, 2, 8, current_timestamp, 'Missouri');
-
-create view t140_shared_views.home_team_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;
-grant select on t140_shared_views.home_team_games to sql_user1 with grant option;
-
-create table players
- (player_number int not null,
- player_name varchar (50) not null,
- player_team_number int not null,
- player_phone_number char (10) not null,
- player_details varchar(50),
- primary key (player_number, player_team_number))
- no partition;
-
-alter table players add constraint valid_player_number check(player_number > 0);
-
-alter table players add constraint players_teams
- foreign key (player_team_number) references teams (team_number);
-
-insert into players values
- (1, 'Tom', 1, '4083948394', null),
- (2, 'Bob', 1, '4089483948', null),
- (3, 'Toby',1, '4082938493', 'pitcher'),
- (3, 'Toby',2, '4082938493', null),
- (4, 'Julie', 2, '5108394839', 'catcher'),
- (5, 'Joanne', 2, '5103849384', null),
- (6, 'Pete', 2, '5102839483', null),
- (6, 'Pete', 3, '5102839483', 'third base'),
- (7, 'Jared',4, '9518293849', 'short stop'),
- (8, 'Zachary', 4, '9518293840', null),
- (9, 'Lynne', 5, '9518293892', 'pitcher'),
- (10, 'Omar', 5, '5128394893', null);
-
-?section set_up
-prepare get_privs from
-select distinct
- substring (object_name,1,40) as object_name,
- 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
-from "_PRIVMGR_MD_".object_privileges
-where object_uid in
- (select object_uid
- from "_MD_".objects
- where schema_name like 'T140_%')
- order by 1, 2, 3, 4, 5
-;
-
-?section tests
-values (user);
-
--- user1 owns everything, start of by doing initial grants
-sh sqlci -i "TEST140(user1_cmds)" -u sql_user1;
-
--- Go see what user2, user3, user4 and user5 can do
-sh sqlci -i "TEST140(user2_cmds)" -u sql_user2;
-sh sqlci -i "TEST140(user3_cmds)" -u sql_user3;
-sh sqlci -i "TEST140(user4_cmds)" -u sql_user4;
-sh sqlci -i "TEST140(user5_cmds)" -u sql_user5;
-
-?section user1_cmds
-obey TEST140(set_up);
-log LOG140;
-values (user);
-set schema t140_user1_private;
-
-grant select on games to sql_user2;
-
--- grants to handle table requests
-grant all on teams to sql_user2 with grant option;
-grant select, insert on players to sql_user2 with grant option;
-
--- grants to handle view requests
-grant select on t140_shared_views.home_team_games to sql_user2;
-
-obey TEST140(set_up);
-execute get_privs;
-
-?section user2_cmds
-log LOG140;
-values (user);
-set schema t140_user1_private;
-
--- user2 has insert privilege on teams and players
-insert into teams values (6, 'Mets', 'Harry', '8007218888');
-insert into players values (11, 'Barry', 3, '2342342345', 'left field');
-
--- user2 does not have insert privilege on games
-insert into games values (5,6,9, current_timestamp, 'Michigan');
-
--- user2 can select from all tables
-select count(*) from teams;
-select count(*) from games;
-select count(*) from players;
-
--- user2 can select from home_team_games
-select team_number, game_number from t140_shared_views.home_team_games;
-
--- user2 can create a view that spans all tables
-create view t140_shared_views.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
- t.team_number = g.home_team_number
- order by player_name, team_number;
-select distinct player_name from t140_shared_views.games_by_player order by 1;
-
--- user2 cannot propagate select privilege on games
-grant select on games to sql_user3;
-
--- user2 can propagate all privileges on teams
-grant select, delete on teams to sql_user3 with grant option;
-
--- user2 can propagate select and insert privilege on players
-grant select on players to sql_user3;
-grant insert on players to sql_user3 with grant option;
-
-obey TEST140(set_up);
-execute get_privs;
-
-?section user3_cmds
-obey TEST140(set_up);
-log LOG140;
-values (user);
-set schema t140_user1_private;
-
--- user3 cannot select from games
-select count(*) from games;
-
--- user3 can select from teams and players
-select count(*) from teams;
-select count(*) from players;
-
--- user3 can create a view between teams and players
-create view t140_shared_views.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;
-select * from t140_shared_views.players_on_team;
-
--- user3 cannot select from view games_by_player or home_team_games;
-select * from t140_shared_views.games_by_player;
-select team_number, game_number from t140_shared_views.home_team_games;
-
--- user3 can delete from teams;
-delete from teams where team_number = 6;
-
--- user3 cannot insert into teams
-insert into teams values (6, 'Mets', 'Harry', '8007218888');
-
--- user3 cannot propagate select on table games
-grant select on games to sql_user4;
-
--- user3 can propagate select but no insert on table teams
-grant select, insert on teams to sql_user4;
-
--- user3 can propagate insert on table players
-grant insert on players to sql_user4;
-
-execute get_privs;
-
-?section user4_cmds
-obey TEST140(set_up);
-log LOG140;
-values (user);
-set schema t140_user1_private;
-
--- user4 cannot select from games or players
-select count(*) from games;
-select count(*) from players;
-
--- user4 can create a view on teams;
-create view t140_shared_views.team_names as
-select team_name from teams;
-
--- user4 cannot select from other views
-select * from t140_shared_views.players_on_team;
-
--- user4 can select from teams;
-select count(*) from teams;
-
--- user3 has insert privilege into players but not references for the
--- associated RI constraint
-insert into players values (12, 'Aaron', 4, '3453453456', 'right field');
-
---user4 cannot grant any privileges
-grant select on teams to sql_user5;
-grant insert on players to sql_user5;
-
-execute get_privs;
-
-?section user5_cmds
-log LOG140;
-values (user);
-obey TEST140(set_up);
-set schema t140_user1_private;
-
--- user 5 has no privs
-select count(*) from teams;
-
-?section revoke_tests
-set schema t140_user1_private;
-
--- set up grant tree
--- user1 grants select, insert WGO to user2
--- user2 grants select, insert WGO to user3
--- user3 grants select to user4 and user5
--- user2 grants select WGO to user4
--- user4 grants select WGO to user5
--- user5 grants to user6
--- user2 grants insert to user4
--- user1 grants select, insert to user4
-
--- owner (user1) grants
-grant select, insert on games to sql_user2 with grant option;
-grant select, insert on games to sql_user4;
-
--- user2 grants
-grant select, insert on games to sql_user3 with grant option by sql_user2;
-grant select on games to sql_user4 with grant option by sql_user2;
-grant insert on games to sql_user4 by sql_user2;
-
--- user3 grants
-grant select on games to sql_user4 granted by sql_user3;
-grant select on games to sql_user5 with grant option granted by sql_user3;
-
--- user4 grants
-grant select on games to sql_user5 with grant option granted by sql_user4;
-
--- user5 grante
-grant select on games to sql_user6 by sql_user5;
-execute get_privs;
-
--- user6 tries to grant select to user7
-grant select on games to sql_user7 by sql_user6;
-
--- user5 tries to grant to user2 (circular)
-grant select on games to sql_user2 by sql_user5;
-
--- user2 tries to remove WGO from user3,
--- fails because of user3->user4 and user3->user5 grants
-revoke grant option for select on games from sql_user3 by sql_user2;
-
--- remove user3->user5 grant
-revoke select on games from sql_user4 by sql_user3;
-revoke grant option for select on games from sql_user3 by sql_user2;
-revoke select on games from sql_user5 by sql_user3;
-revoke grant option for select on games from sql_user3 by sql_user2;
-
-execute get_privs;
-
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/e3d65c15/core/sql/regress/catman1/TEST141
----------------------------------------------------------------------
diff --git a/core/sql/regress/catman1/TEST141 b/core/sql/regress/catman1/TEST141
deleted file mode 100755
index 461a3b6..0000000
--- a/core/sql/regress/catman1/TEST141
+++ /dev/null
@@ -1,394 +0,0 @@
--- ============================================================================
--- Test: TEST141
--- @@@ START COPYRIGHT @@@
---
--- Licensed to the Apache Software Foundation (ASF) under one
--- or more contributor license agreements. See the NOTICE file
--- distributed with this work for additional information
--- regarding copyright ownership. The ASF licenses this file
--- to you under the Apache License, Version 2.0 (the
--- "License"); you may not use this file except in compliance
--- with the License. You may obtain a copy of the License at
---
--- http://www.apache.org/licenses/LICENSE-2.0
---
--- Unless required by applicable law or agreed to in writing,
--- software distributed under the License is distributed on an
--- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
--- KIND, either express or implied. See the License for the
--- specific language governing permissions and limitations
--- under the License.
---
--- @@@ END COPYRIGHT @@@
---
--- ============================================================================
--- Functionality: Extended support for views for all levels:
--- column, object, component
---
--- Added in response to JIRA 1100
---
--- Expected files: EXPECTED141
--- ============================================================================
-
-cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
-obey TEST141(clean_up);
-log LOG141 clear ;
-obey TEST141(set_up);
-obey TEST141(test_private_user);
-obey TEST141(test_private_role);
-obey TEST141(test_shared_user);
-obey TEST141(test_shared_role);
-log;
-obey TEST141(clean_up);
-exit;
-
--- ============================================================================
-?section clean_up
--- ============================================================================
--- Cleaning up test environment
-drop schema if exists t141_udr cascade;
-drop schema if exists t141_user1 cascade;
-drop schema if exists t141_user2 cascade;
-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 user2_role;
-
-revoke role user1_role from sql_user1;
-revoke role user2_role from sql_user2;
-revoke role user3_role from sql_user3;
-drop role user1_role;
-drop role user2_role;
-drop role user3_role;
-
-
--- ============================================================================
-?section set_up
--- ============================================================================
--- Setup the test environment
-
--- create function to display bitmaps as a bitmap rather than longs
--- use the same function from TEST140
-sh rm -f ./etest140.dll;
-sh sh $$scriptsdir$$/tools/dll-compile.ksh etest140.cpp
- 2>&1 | tee LOG140-SECONDARY;
-set pattern $$DLL$$ etest140.dll;
-set pattern $$QUOTE$$ '''';
-
-create schema t141_udr;
-set schema t141_udr;
-create library t141_l1 file $$QUOTE$$ $$REGRRUNDIR$$/$$DLL$$ $$QUOTE$$ ;
-create function translateBitmap(bitmap largeint) returns (bitmap_string char (20))
-language c parameter style sql external name 'translateBitmap'
-library t141_l1
-deterministic no sql final call allow any parallelism state area size 1024 ;
-grant execute on function t141_udr.translateBitmap to "PUBLIC";
-
--- query to read privs from metadata
-prepare get_obj_privs from
-select distinct
- substring (object_name,1,40) as object_name,
- object_type as type,
- substring(authname(grantor_id),1,10) as grantor,
- substring(authname(grantee_id),1,10) as grantee,
- t141_udr.translateBitmap(privileges_bitmap) as granted_privs,
- t141_udr.translateBitmap(grantable_bitmap) as grantable_privs
-from "_PRIVMGR_MD_".object_privileges
-where object_uid in
- (select object_uid
- from "_MD_".objects
- where schema_name like 'T141_USER%')
- order by 1, 2, 3, 4, 5
-;
-
-prepare get_col_privs from
-select distinct
- substring (object_name,1,40) as object_name,
- column_number,
- substring(authname(grantor_id),1,10) as grantor,
- substring(authname(grantee_id),1,10) as grantee,
- t141_udr.translateBitmap(privileges_bitmap) as granted_privs,
- t141_udr.translateBitmap(grantable_bitmap) as grantable_privs
-from "_PRIVMGR_MD_".column_privileges
-where object_uid in
- (select object_uid
- from "_MD_".objects
- where schema_name like 'T141_%')
- order by 1, 2, 3, 4, 5
-;
-
--- set up role infrastructure
-create role user1_role;
-create role user2_role;
-create role user3_role;
-grant role user1_role to sql_user1;
-grant role user2_role to sql_user2;
-grant role user3_role to 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 user2_role;
-
--- ============================================================================
-?section create_objects
--- ============================================================================
-set schema t141_user1;
-create table u1t1 (c1 int not null primary key, c2 int, c3 int);
-insert into u1t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);
-create table u1t2 (c1 int not null primary key, c2 int, c3 int);
-insert into u1t2 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);
-create table u1t3 (c1 int not null primary key, c2 int, c3 int);
-insert into u1t3 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);
-create table u1t4 (c1 int not null primary key, c2 int, c3 int);
-insert into u1t4 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);
-get tables;
-
-set schema t141_user2;
-create table u2t1 (c1 int not null primary key, c2 int, c3 int);
-insert into u2t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);
-create table u2t2 (c1 int not null primary key, c2 int, c3 int);
-insert into u2t2 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);
-get tables;
-
-set schema t141_user3;
-create table u3t1 (c1 int not null primary key, c2 int, c3 int);
-insert into u3t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);
-get tables;
-
-
--- ============================================================================
-?section test_private_user
--- ============================================================================
--- verify someone with CREATE permission can create objects in someone elses
--- private schema. Make sure the schema owner owns the object and the creator
--- has appropriate privileges.
-
-values (user);
-
--- setup database with private schemas owned by users
-drop schema if exists t141_user1 cascade;
-create schema t141_user1 authorization sql_user1;
-drop schema if exists t141_user2 cascade;
-create schema t141_user2 authorization sql_user2;
-drop schema if exists t141_user3 cascade;
-create schema t141_user3 authorization sql_user3;
-
-obey TEST141(create_objects);
-set schema t141_user1;
-grant select on u1t1 to sql_user3;
-grant select (c1, c2) on u1t2 to sql_user3;
-grant update (c1) on u1t2 to sql_user3;
-grant update, delete, insert on u1t3 to sql_user3;
-grant update on u1t4 to sql_user3;
-grant select(c1, c3) on u1t4 to sql_user3;
-execute get_obj_privs;
-execute get_col_privs;
-
--- sql_user2 has create privilege on all schemas.
--- have sql_user2 create some tables in sql_user1's schema
--- have sql_user2 create some views
-sh sqlci -i "TEST141(user2_objects)" -u sql_user2;
-execute get_obj_privs;
-
--- sql_user1 can create views on all tables
-sh sqlci -i "TEST141(user1_objects)" -u sql_user1;
-execute get_obj_privs;
-
--- sql_user3 does not have the create privilege.
--- have sql_user3 attempt to create a table in sql_user1's schema
-sh sqlci -i "TEST141(user3_objects)" -u sql_user3;
-
--- ============================================================================
-?section test_shared_user
--- ============================================================================
--- verify someone with CREATE permission can create objects in someone elses
--- shared schema. Make sure the schema owner owns the object and the creator
--- has appropriate privileges.
-
-values (user);
-revoke component privilege "CREATE" on sql_operations from sql_user2;
-
--- setup database with shared schemas owned by users
-drop schema if exists t141_user1 cascade;
-create shared schema t141_user1 authorization sql_user1;
-drop schema if exists t141_user2 cascade;
-create shared schema t141_user2 authorization sql_user2;
-drop schema if exists t141_user3 cascade;
-create shared schema t141_user3 authorization sql_user3;
-showddl schema t141_user1;
-showddl schema t141_user2;
-showddl schema t141_user3;
-
--- schemas are owned by the authID specified in the authorization clause but
--- tables are owned by the creator. The objects created in create_objects are
--- owned by DB__ROOT since DB__ROOT is the current user.
-obey TEST141(create_objects);
-set schema t141_user1;
-grant select on u1t1 to sql_user3;
-grant select (c1, c2) on u1t2 to sql_user3;
-grant update (c1) on u1t2 to sql_user3;
-grant update, delete, insert on u1t3 to sql_user3;
-grant update on u1t4 to sql_user3;
-grant select(c1, c3) on u1t4 to sql_user3;
-execute get_obj_privs;
-execute get_col_privs;
-
--- have sql_user2 create some tables in sql_user1's schema
--- have sql_user2 create some views, views that reference user1's objects fail
-sh sqlci -i "TEST141(user2_objects)" -u sql_user2;
-execute get_obj_privs;
-
--- In a shared schema sql_user1 does not have privs on sql_user2 objects
--- creates should fail
-sh sqlci -i "TEST141(user1_objects)" -u sql_user1;
-
--- grant privileges to sql_user1 and retry
-grant select on t141_user1.u1t1 to sql_user1;
-grant select on t141_user1.u2t1 to sql_user1;
-sh sqlci -i "TEST141(user1_objects)" -u sql_user1;
-execute get_obj_privs;
-
--- sql_user3 does not have the create privilege.
--- have sql_user3 attempt to create a table in sql_user1's schema
-sh sqlci -i "TEST141(user3_objects)" -u sql_user3;
-
--- ============================================================================
-?section test_private_role
--- ============================================================================
--- verify someone with CREATE permission can create objects in someone elses
--- private schema. Make sure the schema owner owns the object and the creator
--- has appropriate privileges.
-
-values (user);
-
--- setup database with private schemas owned by roles
-drop schema if exists t141_user1 cascade;
-create schema t141_user1 authorization user1_role;
-drop schema if exists t141_user2 cascade;
-create schema t141_user2 authorization user2_role;
-drop schema if exists t141_user3 cascade;
-create schema t141_user3 authorization user3_role;
-showddl schema t141_user1;
-showddl schema t141_user2;
-
-obey TEST141(create_objects);
-set schema t141_user1;
-execute get_obj_privs;
-execute get_col_privs;
-
--- user2_role has create privilege on all schemas.
--- have sql_user2 who belongs to user2_role create some tables in
--- t141_user1's schema, also have sql_user2 create some views
-sh sqlci -i "TEST141(user2_objects)" -u sql_user2;
-execute get_obj_privs;
-
--- ============================================================================
-?section test_shared_role
--- ============================================================================
--- verify someone with CREATE permission can create objects in someone elses
--- shared schema. Make sure the schema owner owns the object and the creator
--- has appropriate privileges.
-
-values (user);
-revoke component privilege "CREATE" on sql_operations from user2_role;
-
--- setup database with shared schemas owned by role
-drop schema if exists t141_user1 cascade;
-create shared schema t141_user1 authorization user1_role;
-drop schema if exists t141_user2 cascade;
-create shared schema t141_user2 authorization user2_role;
-drop schema if exists t141_user3 cascade;
-create shared schema t141_user3 authorization user3_role;
-showddl schema t141_user1;
-showddl schema t141_user2;
-
--- schemas are owned by the authID specified in the authorization clause but
--- tables are owned by the creator. The following objects are owned by DB__ROOT
-obey TEST141(create_objects);
-set schema t141_user1;
-execute get_obj_privs;
-execute get_col_privs;
-
--- have sql_user2 create some tables in user2_role's schema
--- have sql_user2 create some views, views that reference user1's objects fail
-sh sqlci -i "TEST141(user2_objects)" -u sql_user2;
-execute get_obj_privs;
-
--- ============================================================================
-?section user1_objects
--- ============================================================================
--- executed by sql_user1
-log LOG141;
-cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
-values (user);
-
-set schema t141_user1;
-create view u1v1 as select * from u1t1;
-create view u1v2 as select * from u2t1;
-create view u1v3(c1, c2) as select u1.c1, u2.c1 from u1t1 u1, u2t1 u2;
-
--- ============================================================================
-?section user2_objects
--- ============================================================================
--- executed by sql_user2
--- sql_user2 has the CREATE component privilege for sql_operations.
--- All creates should succeed. The owner of the table is the schema owner
--- (sql_user1) and sql_user2 should get all DML privileges WGO
-log LOG141;
-cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
-values (user);
-set schema t141_user1;
-create table u2t1 (c1 int not null primary key, c2 int, c3 int);
-create table u2t2 (c1 int not null primary key, c2 int, c3 int);
-
-get tables;
-
--- create a view on user1's table
-create view u2v1 as select c1, c2 from u2t1;
-showddl u2v1;
-
--- for shared schemas u1t1 is owned by DB__ROOT so create fails.
--- for private schemas this succeeds
-create view u2v2(c1, c2) as select u1.c1, u2.c1 from t141_user2.u2t1 as u2, u2t2 as u1;
-showddl u2v2;
-
--- these creates should fail
--- user2 has no privs on u1t1
-create view u1v3 as select * from u1t1;
-set schema t141_user2;
-
--- user2 has no privs on u1t2, for shared schema also u1t1
-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;
-
-
--- ============================================================================
-?section user3_objects
--- ============================================================================
--- executed by sql_user2
-log LOG141;
-cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
-values (user);
-set schema t141_user1;
-
---fails for private schemas - user3 has no create privs in schema user1's schemas
---works for shared schemas - user3 can create objects
-create table u3t1 (c1 int not null primary key, c2 int, c3 int);
-
--- following works based on granted privs
-set schema t141_user3;
-create view u3v1 as select * from t141_user1.u1t1;
-showddl u3v1;
-create view u3v2 as select c1, c2 from t141_user1.u1t2;
-showddl u3v2;
-create view u3v3 as select c1 from t141_user1.u1t2;
-showddl u3v3;
-create view u3v4 as select c1, c2 from t141_user1.u1t4;
-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;
-get views;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/e3d65c15/core/sql/regress/catman1/cidefs
----------------------------------------------------------------------
diff --git a/core/sql/regress/catman1/cidefs b/core/sql/regress/catman1/cidefs
deleted file mode 100755
index 39df9fa..0000000
--- a/core/sql/regress/catman1/cidefs
+++ /dev/null
@@ -1,116 +0,0 @@
--- @@@ START COPYRIGHT @@@
---
--- Licensed to the Apache Software Foundation (ASF) under one
--- or more contributor license agreements. See the NOTICE file
--- distributed with this work for additional information
--- regarding copyright ownership. The ASF licenses this file
--- to you under the Apache License, Version 2.0 (the
--- "License"); you may not use this file except in compliance
--- with the License. You may obtain a copy of the License at
---
--- http://www.apache.org/licenses/LICENSE-2.0
---
--- Unless required by applicable law or agreed to in writing,
--- software distributed under the License is distributed on an
--- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
--- KIND, either express or implied. See the License for the
--- specific language governing permissions and limitations
--- under the License.
---
--- @@@ END COPYRIGHT @@@
---
--- This is the sqlci analogue of the shell-script "setupenv".
-
-set SCHEMA $$TEST_SCHEMA$$;
--- control query default HIST_USE_SAMPLE_FOR_CARDINALITY_ESTIMATION 'ON';
--- control query default HIST_ON_DEMAND_STATS_SIZE '10000';
-
-control query default SKIP_METADATA_VIEWS 'ON';
-
-#ifMX
-control query default REF_CONSTRAINT_NO_ACTION_LIKE_RESTRICT 'ON';
-#ifMX
-
--- Suppress the Row Format information from the SHOWDDL output so
--- we don't have to update hundreds of existing Expected Results
--- files in our regression test suites. 'OFF' is the default.
-control query default SQLMX_SHOWDDL_SUPPRESS_ROW_FORMAT 'ON';
-
-#ifNT
--- These substitutes partitions for DDL requests
--- When making changes, make sure volume names are upper cased
-set pattern $$partition0$$ $DATA;
-set pattern $$partition1$$ $DATA1;
-set pattern $$partition2$$ $DATA2;
-set pattern $$partition3$$ $SYSTEM;
-set pattern $$audit_vol$$ $AUDIT;
-
-set pattern $$PosTablePartition$$ '''$DATA''';
-set pattern $$PosTablePartitions2$$ '''$DATA, $DATA1''';
-set pattern $$PosTablePartitions3$$ '''$DATA, $DATA1, $DATA2''';
-set pattern $$PosTablePartitions4$$ '''$DATA, $DATA1, $DATA2, $SYSTEM''';
-
-#ifNT
-
-#ifNSK
--- These substitutes partitions for DDL requests
--- When making changes, make sure volume names are upper cased
-set pattern $$partition0$$ $DATA06;
-set pattern $$partition1$$ $DATA07;
-set pattern $$partition1_len$$ 25;
-set pattern $$partition2$$ $DATA08;
-set pattern $$partition3$$ $DATA09;
-set pattern $$partition4$$ $DATA05;
-set pattern $$partition5$$ $DATA10;
-set pattern $$audit_vol$$ $AUDIT;
-
-set pattern $$PosTablePartition$$ '''$DATA06''';
-set pattern $$PosTablePartitions2$$ '''$DATA06, $DATA07''';
-set pattern $$PosTablePartitions3$$ '''$DATA06, $DATA07, $DATA08''';
-set pattern $$PosTablePartitions4$$ '''$DATA06, $DATA07, $DATA08, $DATA09''';
-
-#ifNSK
-
--- This pattern is needed for both NSK and NT to handle the quote in Metadata
--- queries
-set pattern $$quote$$ '''';
--- This CQD is added for creating a table with no
--- partition or primary key when the POS is enabled.
-control query default POS_ALLOW_NON_PK_TABLES 'ON';
-
--- Specify a unique 3-alphanumeric-character identifier
--- (e.g. your initial). The first character must be a letter.
--- The id will be used by the test run to form the subvolume
--- name for the location of a schema, the name of a partition,
--- etc. So more than one user can run the same test
--- simultaneously even when it accesses the same disk.
--- This will be overwritten by the value in userdefs on NSK.
-set pattern $$runid$$ CAT;
-
-
--- Only on a Linux cluster
-#ifdef CLUSTERNAME
-set pattern $$partition$$ $DB0000;
-set pattern $$partition0$$ $DB0000;
-set pattern $$partition1$$ $DB0001;
-set pattern $$partition2$$ $DB0002;
-set pattern $$partition3$$ $DB0003;
-set pattern $$partition4$$ $DB0004;
-set pattern $$partition5$$ $DB0005;
-set pattern $$partition6$$ $DB0006;
-set pattern $$partition7$$ $DB0007;
-set pattern $$partition8$$ $DB0008;
-set pattern $$audit_vol$$ $AUD000;
-set pattern $$PosTablePartition$$ '''$DB0000''';
-set pattern $$PosTablePartitions2$$ '''$DB0001, $DB0002''';
-set pattern $$PosTablePartitions3$$ '''$DB0003, $DB0004, $DB0005''';
-set pattern $$PosTablePartitions4$$ '''$DB0006, $DB0007, $DB0008, $DB0009''';
-set pattern $$TrigTempTablePartition$$ '''$DB0003:$DB0004,$DB0005:$DB0001''';
-set pattern $$TrigTempTablePartition1$$ '''$DB0001: $DB0004''';
-set pattern $$TrigTempTablePartition2$$ '''$DB0001''';
-set pattern $$TrigTempTablePartition3$$ '''$DB0004''';
-set pattern $$TrigTempTablePartition4$$ '''$DB0001, $DB0004,$NINEX: $DB0005''';
-set pattern $$TrigTempTablePartition5$$ '''$DB0005, $DB0005, $DB0005: $DB0005, $DB0005''';
-set pattern $$TrigTempTablePartition6$$ '''$DB0001,XXXX64 $DB0005''';
-#endif
--- eof
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/e3d65c15/core/sql/regress/catman1/etest140.cpp
----------------------------------------------------------------------
diff --git a/core/sql/regress/catman1/etest140.cpp b/core/sql/regress/catman1/etest140.cpp
deleted file mode 100644
index 45416bd..0000000
--- a/core/sql/regress/catman1/etest140.cpp
+++ /dev/null
@@ -1,98 +0,0 @@
-// @@@ START COPYRIGHT @@@
-//
-// Licensed to the Apache Software Foundation (ASF) under one
-// or more contributor license agreements. See the NOTICE file
-// distributed with this work for additional information
-// regarding copyright ownership. The ASF licenses this file
-// to you under the Apache License, Version 2.0 (the
-// "License"); you may not use this file except in compliance
-// with the License. You may obtain a copy of the License at
-//
-// http://www.apache.org/licenses/LICENSE-2.0
-//
-// Unless required by applicable law or agreed to in writing,
-// software distributed under the License is distributed on an
-// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-// KIND, either express or implied. See the License for the
-// specific language governing permissions and limitations
-// under the License.
-//
-// @@@ END COPYRIGHT @@@
-
-
-#include <stdio.h>
-#include <stdlib.h>
-#include <string.h>
-#include <bitset>
-#include "sqludr.h"
-
-extern "C" {
-
-/* TRANSLATEBITMAP */
-SQLUDR_LIBFUNC SQLUDR_INT32 translateBitmap(SQLUDR_INT32 *in1,
- SQLUDR_CHAR *out,
- SQLUDR_INT16 *in1Ind,
- SQLUDR_INT16 *outInd,
- SQLUDR_TRAIL_ARGS)
-{
- enum UDR_PRIVILEGE { SELECT_PRIV = 0,
- INSERT_PRIV,
- DELETE_PRIV,
- UPDATE_PRIV,
- USAGE_PRIV,
- REFERENCES_PRIV,
- EXECUTE_PRIV };
-
- if (calltype == SQLUDR_CALLTYPE_FINAL)
- return SQLUDR_SUCCESS;
-
- std::string result;
- if (*in1Ind == SQLUDR_NULL)
- {
- *outInd = SQLUDR_NULL;
- }
- else
- {
- std::bitset<7> privs;
- privs = *in1;
- if (privs.none())
- result = "NONE";
- else
- {
- if (privs.test(SELECT_PRIV))
- result += "S";
- else
- result += '-';
- if (privs.test(INSERT_PRIV))
- result += "I";
- else
- result += '-';
- if (privs.test(DELETE_PRIV))
- result += "D";
- else
- result += '-';
- if (privs.test(UPDATE_PRIV))
- result += "U";
- else
- result += '-';
- if (privs.test(USAGE_PRIV))
- result += "G";
- else
- result += '-';
- if (privs.test(REFERENCES_PRIV))
- result += "R";
- else
- result += '-';
- if (privs.test(EXECUTE_PRIV))
- result += "E";
- else
- result += '-';
- }
- }
-
- strcpy(out, result.c_str());
- return SQLUDR_SUCCESS;
-}
-
-
-} /* extern "C" */
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/e3d65c15/core/sql/regress/catman1/runregr
----------------------------------------------------------------------
diff --git a/core/sql/regress/catman1/runregr b/core/sql/regress/catman1/runregr
deleted file mode 100755
index baff929..0000000
--- a/core/sql/regress/catman1/runregr
+++ /dev/null
@@ -1,31 +0,0 @@
-#! /bin/sh
-# @@@ START COPYRIGHT @@@
-#
-# Licensed to the Apache Software Foundation (ASF) under one
-# or more contributor license agreements. See the NOTICE file
-# distributed with this work for additional information
-# regarding copyright ownership. The ASF licenses this file
-# to you under the Apache License, Version 2.0 (the
-# "License"); you may not use this file except in compliance
-# with the License. You may obtain a copy of the License at
-#
-# http://www.apache.org/licenses/LICENSE-2.0
-#
-# Unless required by applicable law or agreed to in writing,
-# software distributed under the License is distributed on an
-# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-# KIND, either express or implied. See the License for the
-# specific language governing permissions and limitations
-# under the License.
-#
-# @@@ END COPYRIGHT @@@
-
-if [ "$scriptsdir" != "" ]; then
- $scriptsdir/tools/runregr $*
-elif [ -x ../tools/runregr ]; then
- ../tools/runregr $*
-else
- echo "run runregr from regress/tools directory"
-fi
-
-
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/e3d65c15/core/sql/regress/catman1/udrtest135.cpp
----------------------------------------------------------------------
diff --git a/core/sql/regress/catman1/udrtest135.cpp b/core/sql/regress/catman1/udrtest135.cpp
deleted file mode 100644
index 3c9ee80..0000000
--- a/core/sql/regress/catman1/udrtest135.cpp
+++ /dev/null
@@ -1,55 +0,0 @@
-// @@@ START COPYRIGHT @@@
-//
-// Licensed to the Apache Software Foundation (ASF) under one
-// or more contributor license agreements. See the NOTICE file
-// distributed with this work for additional information
-// regarding copyright ownership. The ASF licenses this file
-// to you under the Apache License, Version 2.0 (the
-// "License"); you may not use this file except in compliance
-// with the License. You may obtain a copy of the License at
-//
-// http://www.apache.org/licenses/LICENSE-2.0
-//
-// Unless required by applicable law or agreed to in writing,
-// software distributed under the License is distributed on an
-// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-// KIND, either express or implied. See the License for the
-// specific language governing permissions and limitations
-// under the License.
-//
-// @@@ END COPYRIGHT @@@
-
-#include "sqludr.h"
-
-using namespace tmudr;
-
-class Sessionize : public UDR
-{
-public:
-
- // override the runtime method
- void processData(UDRInvocationInfo &info,
- UDRPlanInfo &plan);
-};
-
-extern "C" UDR * SESSIONIZE()
-{
- return new Sessionize();
-}
-
-void Sessionize::processData(UDRInvocationInfo &info,
- UDRPlanInfo &plan)
-{
- // this is just a dummy implementation, the test
- // does not rely on the generated results
-
- // loop over input rows
- while (getNextRow(info))
- {
- info.out().setString(0, "userid");
- info.out().setLong(1, 999);
- info.out().setLong(2, 9999);
-
- emitRow(info);
- }
-}
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/e3d65c15/core/sql/regress/core/EXPECTED131
----------------------------------------------------------------------
diff --git a/core/sql/regress/core/EXPECTED131 b/core/sql/regress/core/EXPECTED131
new file mode 100644
index 0000000..c5a3fb5
--- /dev/null
+++ b/core/sql/regress/core/EXPECTED131
@@ -0,0 +1,491 @@
+>>
+>>-- run the test in sqlci sessions which start after authorization
+>>-- is enabled.
+>>log;
+>>
+>>obey TEST131(setup);
+>>
+>>create table T131a
++> (uniq int not null,
++> c10K int ,
++> c1K int,
++> c100 int,
++> c10 int,
++> c1 int,
++> primary key (uniq)
++> ) no partition;
+
+--- SQL operation complete.
+>>
+>>create table T131b
++> (uniq int not null,
++> c10K int ,
++> c1K int,
++> c100 int,
++> c10 int,
++> c1 int,
++> primary key (uniq)
++> ) no partition;
+
+--- SQL operation complete.
+>>
+>>create table T131c
++> (uniq int not null,
++> c10K int ,
++> c1K int,
++> c100 int,
++> c10 int,
++> c1 int,
++> primary key (uniq)
++> ) no partition;
+
+--- SQL operation complete.
+>>
+>>grant all on t131a to SQL_USER7;
+
+--- SQL operation complete.
+>>grant all on t131b to SQL_USER7;
+
+--- SQL operation complete.
+>>grant all on t131c to SQL_USER7;
+
+--- SQL operation complete.
+>>
+>>
+>>log;
+>>
+>>cqd AUTO_QUERY_RETRY 'OFF';
+
+--- SQL operation complete.
+>>
+>>cqd CAT_ENABLE_QUERY_INVALIDATION 'ON';
+
+--- SQL operation complete.
+>>
+>>prepare sel_abc from select * from t131a, t131b, t131c;
+
+--- SQL command prepared.
+>>
+>>log;
+Query_Invalidation_Keys explain output
+ Query_Invalidation_Keys {
+>>
+>>-- run a session to revoke privs for this user.
+>>
+>>log;
+>>
+>>revoke all on t131c from SQL_USER7;
+
+--- SQL operation complete.
+>>
+>>-- Test that identical revokes do not make new RMS siks.
+>>grant all on t131a to SQL_USER3;
+
+--- SQL operation complete.
+>>grant all on t131b to SQL_USER3;
+
+--- SQL operation complete.
+>>grant all on T131c to SQL_USER3;
+
+--- SQL operation complete.
+>>
+>>revoke all on t131a from SQL_USER3;
+
+--- SQL operation complete.
+>>revoke all on t131b from SQL_USER3;
+
+--- SQL operation complete.
+>>revoke all on t131c from SQL_USER3;
+
+--- SQL operation complete.
+>>
+>>log;
+>>sh grep "Query Invalidation Keys *[1-9]" STATS131 >> STATS131_1;
+>>
+>>grant all on t131a to SQL_USER3;
+
+--- SQL operation complete.
+>>grant all on t131b to SQL_USER3;
+
+--- SQL operation complete.
+>>grant all on T131c to SQL_USER3;
+
+--- SQL operation complete.
+>>
+>>revoke all on t131a from SQL_USER3;
+
+--- SQL operation complete.
+>>revoke all on t131b from SQL_USER3;
+
+--- SQL operation complete.
+>>revoke all on t131c from SQL_USER3;
+
+--- SQL operation complete.
+>>
+>>log;
+>>
+>>execute sel_abc;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.SCH.T131C.
+
+*** ERROR[8822] The statement was not prepared.
+
+--- 0 row(s) selected.
+>>
+>>-- test the GET STATISTICS reporting of No. Query Invalidation Keys
+>>log;
+No. Query Invalidation Keys
+No. Query Invalidation Keys
+No. Query Invalidation Keys
+numQueryInvKeys:
+numQueryInvKeys:
+>>
+>>exit;
+
+End of MXCI Session
+
+>>grant all on t131a to SQL_USER7;
+
+--- SQL operation complete.
+>>insert into t131a values(1, 1, 1, 1, 1, 1);
+
+--- 1 row(s) inserted.
+>>log;
+>>
+>>cqd AUTO_QUERY_RETRY_WARNINGS 'ON';
+
+--- SQL operation complete.
+>>
+>>
+>>set envvar sqlci_cursor '1';
+
+--- SQL operation complete.
+>>declare c3 cursor for
++>select * from t131a for update of c10k;
+
+--- SQL operation complete.
+>>prepare s3 from update t131a set c10k = c10k+22 where current of c3;
+
+--- SQL command prepared.
+>>
+>>-- run a session to revoke UPDATE for this user, then grant it again.
+>>
+>>log;
+>>
+>>revoke UPDATE on t131a from SQL_USER7;
+
+--- SQL operation complete.
+>>
+>>grant UPDATE on t131a to SQL_USER7;
+
+--- SQL operation complete.
+>>
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>begin work;
+
+--- SQL operation complete.
+>>open c3;
+
+--- SQL operation complete.
+>>fetch c3;
+
+UNIQ C10K C1K C100 C10 C1
+----------- ----------- ----------- ----------- ----------- -----------
+
+ 1 1 1 1 1 1
+
+--- 1 row(s) selected.
+>>execute s3;
+
+*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.
+
+*** WARNING[8734] Statement must be recompiled to allow privileges to be re-evaluated.
+
+--- 1 row(s) updated.
+>>commit;
+
+--- SQL operation complete.
+>>select * from t131a;
+
+UNIQ C10K C1K C100 C10 C1
+----------- ----------- ----------- ----------- ----------- -----------
+
+ 1 23 1 1 1 1
+
+--- 1 row(s) selected.
+>>
+>>-- run a session to revoke UPDATE for this user.
+>>
+>>declare c4 cursor for
++>select * from t131a for update of c10k;
+
+--- SQL operation complete.
+>>prepare s4 from update t131a set c10k = c10k+22 where current of c4;
+
+--- SQL command prepared.
+>>
+>>log;
+>>
+>>revoke UPDATE on t131a from SQL_USER7;
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>begin work;
+
+--- SQL operation complete.
+>>open c4;
+
+--- SQL operation complete.
+>>fetch c4;
+
+UNIQ C10K C1K C100 C10 C1
+----------- ----------- ----------- ----------- ----------- -----------
+
+ 1 23 1 1 1 1
+
+--- 1 row(s) selected.
+>>execute s4;
+
+*** ERROR[4481] The user does not have UPDATE privilege on table or view TRAFODION.SCH.T131A.
+
+*** ERROR[8822] The statement was not prepared.
+
+*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.
+
+*** WARNING[8734] Statement must be recompiled to allow privileges to be re-evaluated.
+
+--- 0 row(s) updated.
+>>commit;
+
+--- SQL operation complete.
+>>select * from t131a;
+
+UNIQ C10K C1K C100 C10 C1
+----------- ----------- ----------- ----------- ----------- -----------
+
+ 1 23 1 1 1 1
+
+--- 1 row(s) selected.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>grant all on t131a to SQL_USER7;
+
+--- SQL operation complete.
+>>
+>>sh sqlci -i"TEST131(priv_cancel)" -u"SQL_User7" ;
+>>
+>>-- cancel my own query is allowed with no grant
+>>prepare s1 from
++>values(user());
+
+--- SQL command prepared.
+>>
+>>execute s1;
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER7
+
+--- 1 row(s) selected.
+>>
+>>log;
+>>
+>>sh grep "^QID is " QIDLOG | cut -c 8- | sed "s/^/control query cancel qid /g" | sed "s/$/;/g" > cancel_cmd;
+>>
+>>-- expect error 8031 since the query is not running.
+>>obey cancel_cmd;
+>>control query cancel qid MXID11000006223212317056413926129000000000206U3334000_52_S1;
+
+*** ERROR[8031] Server declined cancel request for query ID MXID11000006223212317056413926129000000000206U3334000_52_S1. The query is not in OPEN or FETCH or EXECUTE state.
+
+--- SQL operation failed with errors.
+>>
+>>-- expect error 8029
+>>control query cancel qid
++>MXID11000023943212197828612249700000000000206U6553500_20_S1;
+
+*** ERROR[8029] Server declined cancel request. The user is not authorized to make this request.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>
+>>sh sqlci -i"TEST131(grant_cancel)";
+>>
+>>grant component privilege QUERY_CANCEL on sql_operations to SQL_USER7;
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>cqd AUTO_QUERY_RETRY_WARNINGS 'ON';
+
+--- SQL operation complete.
+>>
+>>-- expect error 8026 and no AQR warning
+>>control query cancel qid
++>MXID11000023943212197828612249700000000000206U6553500_20_S1;
+
+*** ERROR[8026] Server declined cancel request. The query ID of the targeted query was not found.
+
+--- SQL operation failed with errors.
+>>
+>>prepare s1 from
++>control query cancel qid
++>MXID11000023943212197828612249700000000000206U6553500_20_S1;
+
+--- SQL command prepared.
+>>
+>>-- expect error 8026 and no AQR warning
+>>execute s1;
+
+*** ERROR[8026] Server declined cancel request. The query ID of the targeted query was not found.
+
+--- SQL operation failed with errors.
+>>
+>>sh sleep 2;
+>>-- expect error 8026 and AQR warning
+>>execute s1;
+
+*** ERROR[8026] Server declined cancel request. The query ID of the targeted query was not found.
+
+*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.
+
+*** WARNING[8734] Statement must be recompiled to allow privileges to be re-evaluated.
+
+--- SQL operation failed with errors.
+>>
+>>sh sqlci -i"TEST131(revoke_cancel)";
+>>
+>>revoke component privilege QUERY_CANCEL on sql_operations from SQL_USER7;
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- expect error 8029
+>>control query cancel qid
++>MXID11000023943212197828612249700000000000206U6553500_20_S1;
+
+*** ERROR[8029] Server declined cancel request. The user is not authorized to make this request.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>
+>>sh sqlci -i"TEST131(grant_cancel_role)";
+>>create role role131;
+
+--- SQL operation complete.
+>>grant component privilege QUERY_CANCEL on SQL_OPERATIONS to role131;
+
+--- SQL operation complete.
+>>grant role role131 to sql_user7;
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- expect error 8026
+>>control query cancel qid
++>MXID11000023943212197828612249700000000000206U6553500_20_S1;
+
+*** ERROR[8026] Server declined cancel request. The query ID of the targeted query was not found.
+
+--- SQL operation failed with errors.
+>>
+>>sh sqlci -i"TEST131(revoke_cancel_role)";
+>>revoke role role131 from sql_user7;
+
+--- SQL operation complete.
+>>revoke component privilege QUERY_CANCEL on SQL_OPERATIONS from role131;
+
+--- SQL operation complete.
+>>drop role role131;
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- expect error 8029
+>>control query cancel qid
++>MXID11000023943212197828612249700000000000206U6553500_20_S1;
+
+*** ERROR[8029] Server declined cancel request. The user is not authorized to make this request.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>obey TEST131(clnup);
+>>
+>>set schema $$TEST_SCHEMA$$;
+
+--- SQL operation complete.
+>>
+>>drop table if exists T131c;
+
+--- SQL operation complete.
+>>drop table if exists T131b;
+
+--- SQL operation complete.
+>>drop table if exists T131a;
+
+--- SQL operation complete.
+>>revoke component privilege QUERY_CANCEL on sql_operations from SQL_USER7;
+
+*** ERROR[1018] Grant of role or privilege QUERY_CANCEL on component SQL_OPERATIONS from DB__ROOT to SQL_USER7 not found, revoke request ignored.
+
+--- SQL operation failed with errors.
+>>revoke role role131 from sql_user7;
+
+*** ERROR[1338] Role ROLE131 is not defined in the database.
+
+--- SQL operation failed with errors.
+>>revoke component privilege QUERY_CANCEL on SQL_OPERATIONS from role131;
+
+*** ERROR[1008] Authorization identifier ROLE131 does not exist.
+
+--- SQL operation failed with errors.
+>>drop role role131;
+
+*** ERROR[1338] Role ROLE131 is not defined in the database.
+
+--- SQL operation failed with errors.
+>>
+>>
+>>log;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/e3d65c15/core/sql/regress/core/TEST000
----------------------------------------------------------------------
diff --git a/core/sql/regress/core/TEST000 b/core/sql/regress/core/TEST000
index 73b047b..2839301 100755
--- a/core/sql/regress/core/TEST000
+++ b/core/sql/regress/core/TEST000
@@ -1,4 +1,4 @@
--- Test: TEST001 (Core)
+-- Test: TEST000 (Core)
-- @@@ START COPYRIGHT @@@
--
-- Licensed to the Apache Software Foundation (ASF) under one
@@ -32,3 +32,15 @@ log;
-- not logging it since it may or may not exist
create shared schema trafodion.sch;
+initialize authorization;
+register user sql_user1 as sql_user1;
+register user sql_user2 as sql_user2;
+register user sql_user3 as sql_user3;
+register user sql_user4 as sql_user4;
+register user sql_user5 as sql_user5;
+register user sql_user6 as sql_user6;
+register user sql_user7 as sql_user7;
+register user sql_user8 as sql_user8;
+register user sql_user9 as sql_user9;
+register user sql_user10 as sql_user10;
+
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/e3d65c15/core/sql/regress/core/TEST131
----------------------------------------------------------------------
diff --git a/core/sql/regress/core/TEST131 b/core/sql/regress/core/TEST131
new file mode 100755
index 0000000..75eb331
--- /dev/null
+++ b/core/sql/regress/core/TEST131
@@ -0,0 +1,365 @@
+-- Test: TEST131 (Executor)
+-- @@@ START COPYRIGHT @@@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+--
+-- @@@ END COPYRIGHT @@@
+--
+-- Functionality: Executor support for REVOKE (query invalidation).
+-- Expected files: EXPECTED131
+-- Table created: T131a, T131b, T131c
+--
+--
+
+obey TEST131(clnup);
+
+log LOG131 ;
+
+-- run the test in sqlci sessions which start after authorization
+-- is enabled.
+log;
+sh sqlci -i"TEST131(test_session1)";
+log LOG131 ;
+
+obey TEST131(clnup);
+
+log;
+exit;
+
+?section test_session1
+
+log LOG131 ;
+
+obey TEST131(setup);
+log;
+
+sh sqlci -i"TEST131(dml)" -u"SQL_User7" ;
+
+log LOG131;
+grant all on t131a to SQL_USER7;
+insert into t131a values(1, 1, 1, 1, 1, 1);
+log;
+
+sh sqlci -i"TEST131(update_where_current_of)" -u"SQL_User7" ;
+
+
+log LOG131;
+grant all on t131a to SQL_USER7;
+
+sh sqlci -i"TEST131(priv_cancel)" -u"SQL_User7" ;
+
+?section clnup
+
+set schema $$TEST_SCHEMA$$;
+
+drop table if exists T131c;
+drop table if exists T131b;
+drop table if exists T131a;
+revoke component privilege QUERY_CANCEL on sql_operations from SQL_USER7;
+revoke role role131 from sql_user7;
+revoke component privilege QUERY_CANCEL on SQL_OPERATIONS from role131;
+drop role role131;
+
+?section setup
+
+create table T131a
+ (uniq int not null,
+ c10K int ,
+ c1K int,
+ c100 int,
+ c10 int,
+ c1 int,
+ primary key (uniq)
+ ) no partition;
+
+create table T131b
+ (uniq int not null,
+ c10K int ,
+ c1K int,
+ c100 int,
+ c10 int,
+ c1 int,
+ primary key (uniq)
+ ) no partition;
+
+create table T131c
+ (uniq int not null,
+ c10K int ,
+ c1K int,
+ c100 int,
+ c10 int,
+ c1 int,
+ primary key (uniq)
+ ) no partition;
+
+grant all on t131a to SQL_USER7;
+grant all on t131b to SQL_USER7;
+grant all on t131c to SQL_USER7;
+
+
+?section dml
+
+log LOG131;
+
+cqd AUTO_QUERY_RETRY 'OFF';
+
+cqd CAT_ENABLE_QUERY_INVALIDATION 'ON';
+
+prepare sel_abc from select * from t131a, t131b, t131c;
+
+log; log EXPLAIN131 clear;
+explain sel_abc;
+log;
+
+sh echo "Query_Invalidation_Keys explain output" >> LOG131;
+sh grep "Query_Invalidation_Keys *{[-0-9]" EXPLAIN131 | cut -c 1-28 >> LOG131;
+
+log LOG131;
+
+-- run a session to revoke privs for this user.
+
+log;
+
+sh sqlci -i"TEST131(revoke1)";
+
+log LOG131;
+
+execute sel_abc;
+
+-- test the GET STATISTICS reporting of No. Query Invalidation Keys
+log; log STATS131 clear;
+get statistics for rms all;
+get statistics for rms 0;
+
+select
+ substr(variable_info,
+ position('numQueryInvKeys' in variable_info),
+ position('numQueryInvKeys' in variable_info) +
+ 17 + (position(' ' in
+ substr(variable_info,
+ 17 + position('numQueryInvKeys:' in variable_info))) -
+ position('numQueryInvKeys:' in variable_info)))
+from table(statistics(NULL, 'RMS_INFO=-1'));
+
+log;
+
+sh grep "Query Invalidation Keys *[1-9]" STATS131 | cut -c 1-28 >> LOG131;
+sh grep "^numQueryInvKeys: *[1-9]" STATS131 | cut -c 1-17>> LOG131;
+
+
+log LOG131;
+
+?section update_where_current_of
+log LOG131;
+
+cqd AUTO_QUERY_RETRY_WARNINGS 'ON';
+
+
+set envvar sqlci_cursor '1';
+declare c3 cursor for
+select * from t131a for update of c10k;
+prepare s3 from update t131a set c10k = c10k+22 where current of c3;
+
+-- run a session to revoke UPDATE for this user, then grant it again.
+
+log;
+sh sqlci -i"TEST131(revoke_and_regrant_upd)";
+log LOG131;
+
+begin work;
+open c3;
+fetch c3;
+execute s3;
+commit;
+select * from t131a;
+
+-- run a session to revoke UPDATE for this user.
+
+declare c4 cursor for
+select * from t131a for update of c10k;
+prepare s4 from update t131a set c10k = c10k+22 where current of c4;
+
+log;
+sh sqlci -i"TEST131(revoke_update)";
+log LOG131;
+
+begin work;
+open c4;
+fetch c4;
+execute s4;
+commit;
+select * from t131a;
+
+?section priv_cancel
+log LOG131;
+
+-- cancel my own query is allowed with no grant
+prepare s1 from
+values(user());
+
+execute s1;
+
+log;
+log QIDLOG clear;
+display qid for s1;
+
+log;
+log LOG131;
+
+sh grep "^QID is " QIDLOG | cut -c 8- | sed "s/^/control query cancel qid /g" | sed "s/$/;/g" > cancel_cmd;
+
+-- expect error 8031 since the query is not running.
+obey cancel_cmd;
+
+-- expect error 8029
+control query cancel qid
+MXID11000023943212197828612249700000000000206U6553500_20_S1;
+
+sh sqlci -i"TEST131(grant_cancel)";
+
+cqd AUTO_QUERY_RETRY_WARNINGS 'ON';
+
+-- expect error 8026 and no AQR warning
+control query cancel qid
+MXID11000023943212197828612249700000000000206U6553500_20_S1;
+
+prepare s1 from
+control query cancel qid
+MXID11000023943212197828612249700000000000206U6553500_20_S1;
+
+-- expect error 8026 and no AQR warning
+execute s1;
+
+sh sleep 2;
+-- expect error 8026 and AQR warning
+execute s1;
+
+sh sqlci -i"TEST131(revoke_cancel)";
+
+-- expect error 8029
+control query cancel qid
+MXID11000023943212197828612249700000000000206U6553500_20_S1;
+
+sh sqlci -i"TEST131(grant_cancel_role)";
+
+-- expect error 8026
+control query cancel qid
+MXID11000023943212197828612249700000000000206U6553500_20_S1;
+
+sh sqlci -i"TEST131(revoke_cancel_role)";
+
+-- expect error 8029
+control query cancel qid
+MXID11000023943212197828612249700000000000206U6553500_20_S1;
+
+
+?section revoke1
+
+log LOG131;
+
+revoke all on t131c from SQL_USER7;
+
+-- Test that identical revokes do not make new RMS siks.
+grant all on t131a to SQL_USER3;
+grant all on t131b to SQL_USER3;
+grant all on T131c to SQL_USER3;
+
+revoke all on t131a from SQL_USER3;
+revoke all on t131b from SQL_USER3;
+revoke all on t131c from SQL_USER3;
+
+log; log STATS131 clear;
+get statistics for RMS 0;
+log LOG131;
+sh grep "Query Invalidation Keys *[1-9]" STATS131 >> STATS131_1;
+
+grant all on t131a to SQL_USER3;
+grant all on t131b to SQL_USER3;
+grant all on T131c to SQL_USER3;
+
+revoke all on t131a from SQL_USER3;
+revoke all on t131b from SQL_USER3;
+revoke all on t131c from SQL_USER3;
+
+log; log STATS131 clear;
+get statistics for RMS 0;
+sh grep "Query Invalidation Keys *[1-9]" STATS131 >> STATS131_2;
+
+sh diff --brief STATS131_1 STATS131_2 >> LOG131;
+
+log LOG131;
+
+log;
+
+?section revoke_and_regrant_upd
+
+log LOG131;
+
+revoke UPDATE on t131a from SQL_USER7;
+
+grant UPDATE on t131a to SQL_USER7;
+
+
+?section revoke_update
+
+log LOG131;
+
+revoke UPDATE on t131a from SQL_USER7;
+
+?section revoke_and_regrant_sel
+
+log LOG131;
+
+revoke SELECT on t131a from SQL_USER7;
+
+grant SELECT on t131a to SQL_USER7;
+
+
+?section revoke_select
+
+log LOG131;
+
+revoke SELECT on t131a from SQL_USER7;
+
+?section grant_cancel
+
+log LOG131;
+
+grant component privilege QUERY_CANCEL on sql_operations to SQL_USER7;
+
+?section revoke_cancel
+
+log LOG131;
+
+revoke component privilege QUERY_CANCEL on sql_operations from SQL_USER7;
+
+?section grant_cancel_role
+
+log LOG131;
+create role role131;
+grant component privilege QUERY_CANCEL on SQL_OPERATIONS to role131;
+grant role role131 to sql_user7;
+
+?section revoke_cancel_role
+log LOG131;
+revoke role role131 from sql_user7;
+revoke component privilege QUERY_CANCEL on SQL_OPERATIONS from role131;
+drop role role131;
+
+?section end_of_test
+-- end of test.