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:13 UTC
[13/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/EXPECTED140
----------------------------------------------------------------------
diff --git a/core/sql/regress/catman1/EXPECTED140 b/core/sql/regress/catman1/EXPECTED140
deleted file mode 100644
index a7ee967..0000000
--- a/core/sql/regress/catman1/EXPECTED140
+++ /dev/null
@@ -1,926 +0,0 @@
->>env;
-----------------------------------
-Current Environment
-----------------------------------
-AUTHENTICATION disabled
-AUTHORIZATION enabled
-CURRENT DIRECTORY /opt/home/rmarton/git_ws/core/sql/regress/rundir/catman1
-LIST_COUNT 4294967295
-LOG FILE LOG140
-MESSAGEFILE /opt/home/rmarton/git_ws/core/sqf/export/bin64d/mxcierr ...
-MESSAGEFILE LANG US English
-MESSAGEFILE VRSN {2015-05-22 10:41 LINUX:G4T3035.HOUSTON.HP.COM/rmarton}
-SQL CATALOG TRAFODION
-SQL SCHEMA SCH
-SQL USER CONNECTED user not connected
-SQL USER DB NAME DB__ROOT
-SQL USER ID 33333
-TERMINAL CHARSET ISO88591
-TRANSACTION ID
-TRANSACTION STATE not in progress
-WARNINGS on
->>obey TEST140(create_db);
->>-- create roles
->>create role priv1;
-
---- SQL operation complete.
->>create role priv2;
-
---- SQL operation complete.
->>create role priv3;
-
---- SQL operation complete.
->>create role priv4;
-
---- SQL operation complete.
->>create role metadata_access;
-
---- SQL operation complete.
->>grant role priv1, priv2, priv3, priv4 to sql_user5;
-
---- SQL operation complete.
->>grant role metadata_access to sql_user1, sql_user2, sql_user3,
-+> sql_user4, sql_user5, sql_user6, sql_user7, sql_user8, sql_user9;
-
---- SQL operation complete.
->>
->>grant select on "_MD_".objects to metadata_access;
-
---- SQL operation complete.
->>grant select on "_PRIVMGR_MD_".object_privileges to metadata_access;
-
---- SQL operation complete.
->>grant select on "_MD_".auths to metadata_access;
-
---- SQL operation complete.
->>
->>create shared schema t140_shared_views;
-
---- SQL operation complete.
->>
->>-- 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;
-
---- SQL operation complete.
->>create library t140_l1 file $$QUOTE$$ $$REGRRUNDIR$$/$$DLL$$ $$QUOTE$$ ;
-
---- SQL operation complete.
->>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 ;
-
---- SQL operation complete.
->>grant execute on function sch.t140_translatePrivsBitmap to "PUBLIC";
-
---- SQL operation complete.
->>
->>-- private schema owned by sql_user1
->>create schema t140_user1_private authorization sql_user1;
-
---- SQL operation complete.
->>set schema t140_user1_private;
-
---- SQL operation complete.
->>obey TEST140(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
-+> )
-+> ;
-
---- SQL operation complete.
->>
->>alter table teams add constraint valid_team_no check (team_number > 0);
-
---- SQL operation complete.
->>
->>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');
-
---- 5 row(s) inserted.
->>
->>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)
-+> ;
-
---- SQL operation complete.
->>create index home_games on games (home_team_number);
-
---- SQL operation complete.
->>
->>alter table games add constraint valid_game_number check (game_number > 0);
-
---- SQL operation complete.
->>
->>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');
-
---- 8 row(s) inserted.
->>
->>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;
-
---- SQL operation complete.
->>grant select on t140_shared_views.home_team_games to sql_user1 with grant option;
-
---- SQL operation complete.
->>
->>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;
-
---- SQL operation complete.
->>
->>alter table players add constraint valid_player_number check(player_number > 0);
-
---- SQL operation complete.
->>
->>alter table players add constraint players_teams
-+> foreign key (player_team_number) references teams (team_number);
-
---- SQL operation complete.
->>
->>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);
-
---- 12 row(s) inserted.
->>
->>get tables in schema t140_user1_private;
-
-Tables in Schema TRAFODION.T140_USER1_PRIVATE
-=============================================
-
-GAMES
-PLAYERS
-TEAMS
-
---- SQL operation complete.
->>
->>
->>-- 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;
->>
->>obey TEST140(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
-+>;
-
---- SQL command prepared.
->>
->>obey TEST140(tests);
->>values (user);
-
-(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
-
-DB__ROOT
-
---- 1 row(s) selected.
->>
->>-- user1 owns everything, start of by doing initial grants
->>sh sqlci -i "TEST140(user1_cmds)" -u sql_user1;
->>values (user);
-
-(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
-
-SQL_USER1
-
---- 1 row(s) selected.
->>set schema t140_user1_private;
-
---- SQL operation complete.
->>
->>grant select on games to sql_user2;
-
---- SQL operation complete.
->>
->>-- grants to handle table requests
->>grant all on teams to sql_user2 with grant option;
-
---- SQL operation complete.
->>grant select, insert on players to sql_user2 with grant option;
-
---- SQL operation complete.
->>
->>-- grants to handle view requests
->>grant select on t140_shared_views.home_team_games to sql_user2;
-
---- SQL operation complete.
->>
->>obey TEST140(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
-+>;
-
---- SQL command prepared.
->>
->>execute get_privs;
-
-OBJECT_NAME TYPE GRANTOR GRANTEE GRANTED_PRIVS GRANTABLE_PRIVS
----------------------------------------------------------------------------------------------------------------------------------------------------------------- ---- ---------- ---------- -------------------- --------------------
-
-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_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-
-TRAFODION.T140_USER1_PRIVATE.PLAYERS BT SQL_USER1 SQL_USER2 SI----- SI-----
-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.
->>
->>exit;
-
-End of MXCI Session
-
->>
->>-- Go see what user2, user3, user4 and user5 can do
->>sh sqlci -i "TEST140(user2_cmds)" -u sql_user2;
->>values (user);
-
-(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
-
-SQL_USER2
-
---- 1 row(s) selected.
->>set schema t140_user1_private;
-
---- SQL operation complete.
->>
->>-- user2 has insert privilege on teams and players
->>insert into teams values (6, 'Mets', 'Harry', '8007218888');
-
---- 1 row(s) inserted.
->>insert into players values (11, 'Barry', 3, '2342342345', 'left field');
-
---- 1 row(s) inserted.
->>
->>-- user2 does not have insert privilege on games
->>insert into games values (5,6,9, current_timestamp, 'Michigan');
-
-*** ERROR[4481] The user does not have INSERT privilege on table or view TRAFODION.T140_USER1_PRIVATE.GAMES.
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>-- user2 can select from all tables
->>select count(*) from teams;
-
-(EXPR)
---------------------
-
- 6
-
---- 1 row(s) selected.
->>select count(*) from games;
-
-(EXPR)
---------------------
-
- 8
-
---- 1 row(s) selected.
->>select count(*) from players;
-
-(EXPR)
---------------------
-
- 13
-
---- 1 row(s) selected.
->>
->>-- user2 can select from home_team_games
->>select team_number, game_number from t140_shared_views.home_team_games;
-
-TEAM_NUMBER GAME_NUMBER
------------ -----------
-
- 1 1
- 1 2
- 1 3
- 1 5
- 2 4
- 2 6
- 3 7
- 4 8
-
---- 8 row(s) selected.
->>
->>-- 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;
-
---- SQL operation complete.
->>select distinct player_name from t140_shared_views.games_by_player order by 1;
-
-PLAYER_NAME
---------------------------------------------------
-
-Barry
-Bob
-Jared
-Joanne
-Julie
-Pete
-Toby
-Tom
-Zachary
-
---- 9 row(s) selected.
->>
->>-- user2 cannot propagate select privilege on games
->>grant select on games to sql_user3;
-
-*** ERROR[1012] No privileges were granted. You lack grant option on the specified privileges.
-
---- SQL operation failed with errors.
->>
->>-- user2 can propagate all privileges on teams
->>grant select, delete on teams to sql_user3 with grant option;
-
---- SQL operation complete.
->>
->>-- user2 can propagate select and insert privilege on players
->>grant select on players to sql_user3;
-
---- SQL operation complete.
->>grant insert on players to sql_user3 with grant option;
-
---- SQL operation complete.
->>
->>obey TEST140(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
-+>;
-
---- SQL command prepared.
->>
->>execute get_privs;
-
-OBJECT_NAME TYPE GRANTOR GRANTEE GRANTED_PRIVS GRANTABLE_PRIVS
----------------------------------------------------------------------------------------------------------------------------------------------------------------- ---- ---------- ---------- -------------------- --------------------
-
-TRAFODION.T140_SHARED_VIEWS.GAMES_BY_PLA VI -2 SQL_USER2 S------ NONE
-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_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-
-TRAFODION.T140_USER1_PRIVATE.PLAYERS BT SQL_USER1 SQL_USER2 SI----- SI-----
-TRAFODION.T140_USER1_PRIVATE.PLAYERS BT SQL_USER2 SQL_USER3 SI----- -I-----
-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-
-TRAFODION.T140_USER1_PRIVATE.TEAMS BT SQL_USER2 SQL_USER3 S-D---- S-D----
-
---- 12 row(s) selected.
->>
->>exit;
-
-End of MXCI Session
-
->>sh sqlci -i "TEST140(user3_cmds)" -u sql_user3;
->>values (user);
-
-(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
-
-SQL_USER3
-
---- 1 row(s) selected.
->>set schema t140_user1_private;
-
---- SQL operation complete.
->>
->>-- user3 cannot select from games
->>select count(*) from games;
-
-*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T140_USER1_PRIVATE.GAMES.
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>-- user3 can select from teams and players
->>select count(*) from teams;
-
-(EXPR)
---------------------
-
- 6
-
---- 1 row(s) selected.
->>select count(*) from players;
-
-(EXPR)
---------------------
-
- 13
-
---- 1 row(s) selected.
->>
->>-- 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;
-
---- SQL operation complete.
->>select * from t140_shared_views.players_on_team;
-
-PLAYER_NAME TEAM_NAME
--------------------------------------------------- --------------------
-
-Pete Cardinals
-Barry Cardinals
-Julie Giants
-Joanne Giants
-Toby Giants
-Pete Giants
-Jared Indians
-Zachary Indians
-Omar Tigers
-Lynne Tigers
-Toby White Socks
-Bob White Socks
-Tom White Socks
-
---- 13 row(s) selected.
->>
->>-- user3 cannot select from view games_by_player or home_team_games;
->>select * from t140_shared_views.games_by_player;
-
-*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T140_SHARED_VIEWS.GAMES_BY_PLAYER.
-
-*** ERROR[8822] The statement was not prepared.
-
->>select team_number, game_number from t140_shared_views.home_team_games;
-
-*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T140_SHARED_VIEWS.HOME_TEAM_GAMES.
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>-- user3 can delete from teams;
->>delete from teams where team_number = 6;
-
---- 1 row(s) deleted.
->>
->>-- user3 cannot insert into teams
->>insert into teams values (6, 'Mets', 'Harry', '8007218888');
-
-*** ERROR[4481] The user does not have INSERT privilege on table or view TRAFODION.T140_USER1_PRIVATE.TEAMS.
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>-- user3 cannot propagate select on table games
->>grant select on games to sql_user4;
-
-*** ERROR[1012] No privileges were granted. You lack grant option on the specified privileges.
-
---- SQL operation failed with errors.
->>
->>-- user3 can propagate select but no insert on table teams
->>grant select, insert on teams to sql_user4;
-
---- SQL operation complete.
->>
->>-- user3 can propagate insert on table players
->>grant insert on players to sql_user4;
-
---- SQL operation complete.
->>
->>execute get_privs;
-
-OBJECT_NAME TYPE GRANTOR GRANTEE GRANTED_PRIVS GRANTABLE_PRIVS
----------------------------------------------------------------------------------------------------------------------------------------------------------------- ---- ---------- ---------- -------------------- --------------------
-
-TRAFODION.T140_SHARED_VIEWS.GAMES_BY_PLA VI -2 SQL_USER2 S------ NONE
-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.PLAYERS_ON_T VI -2 SQL_USER3 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
-TRAFODION.T140_USER1_PRIVATE.PLAYERS BT -2 SQL_USER1 SIDU-R- SIDU-R-
-TRAFODION.T140_USER1_PRIVATE.PLAYERS BT SQL_USER1 SQL_USER2 SI----- SI-----
-TRAFODION.T140_USER1_PRIVATE.PLAYERS BT SQL_USER2 SQL_USER3 SI----- -I-----
-TRAFODION.T140_USER1_PRIVATE.PLAYERS BT SQL_USER3 SQL_USER4 -I----- NONE
-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-
-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.
->>
->>exit;
-
-End of MXCI Session
-
->>sh sqlci -i "TEST140(user4_cmds)" -u sql_user4;
->>values (user);
-
-(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
-
-SQL_USER4
-
---- 1 row(s) selected.
->>set schema t140_user1_private;
-
---- SQL operation complete.
->>
->>-- user4 cannot select from games or players
->>select count(*) from games;
-
-*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T140_USER1_PRIVATE.GAMES.
-
-*** ERROR[8822] The statement was not prepared.
-
->>select count(*) from players;
-
-*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T140_USER1_PRIVATE.PLAYERS.
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>-- user4 can create a view on teams;
->>create view t140_shared_views.team_names as
-+>select team_name from teams;
-
---- SQL operation complete.
->>
->>-- user4 cannot select from other views
->>select * from t140_shared_views.players_on_team;
-
-*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T140_SHARED_VIEWS.PLAYERS_ON_TEAM.
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>-- user4 can select from teams;
->>select count(*) from teams;
-
-(EXPR)
---------------------
-
- 5
-
---- 1 row(s) selected.
->>
->>-- user3 has insert privilege into players but not references for the
->>-- associated RI constraint
->>insert into players values (12, 'Aaron', 4, '3453453456', 'right field');
-
-*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T140_USER1_PRIVATE.PLAYERS.
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>--user4 cannot grant any privileges
->>grant select on teams to sql_user5;
-
-*** ERROR[1012] No privileges were granted. You lack grant option on the specified privileges.
-
---- SQL operation failed with errors.
->>grant insert on players to sql_user5;
-
-*** ERROR[1012] No privileges were granted. You lack grant option on the specified privileges.
-
---- SQL operation failed with errors.
->>
->>execute get_privs;
-
-OBJECT_NAME TYPE GRANTOR GRANTEE GRANTED_PRIVS GRANTABLE_PRIVS
----------------------------------------------------------------------------------------------------------------------------------------------------------------- ---- ---------- ---------- -------------------- --------------------
-
-TRAFODION.T140_SHARED_VIEWS.GAMES_BY_PLA VI -2 SQL_USER2 S------ NONE
-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.PLAYERS_ON_T VI -2 SQL_USER3 S------ 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
-TRAFODION.T140_USER1_PRIVATE.PLAYERS BT -2 SQL_USER1 SIDU-R- SIDU-R-
-TRAFODION.T140_USER1_PRIVATE.PLAYERS BT SQL_USER1 SQL_USER2 SI----- SI-----
-TRAFODION.T140_USER1_PRIVATE.PLAYERS BT SQL_USER2 SQL_USER3 SI----- -I-----
-TRAFODION.T140_USER1_PRIVATE.PLAYERS BT SQL_USER3 SQL_USER4 -I----- NONE
-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-
-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.
->>
->>exit;
-
-End of MXCI Session
-
->>sh sqlci -i "TEST140(user5_cmds)" -u sql_user5;
->>values (user);
-
-(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
-
-SQL_USER5
-
---- 1 row(s) selected.
->>obey TEST140(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
-+>;
-
---- SQL command prepared.
->>
->>set schema t140_user1_private;
-
---- SQL operation complete.
->>
->>-- user 5 has no privs
->>select count(*) from teams;
-
-*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T140_USER1_PRIVATE.TEAMS.
-
-*** ERROR[8822] The statement was not prepared.
-
->>
->>exit;
-
-End of MXCI Session
-
->>
->>obey TEST140(revoke_tests);
->>set schema t140_user1_private;
-
---- SQL operation complete.
->>
->>-- 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;
-
---- SQL operation complete.
->>grant select, insert on games to sql_user4;
-
---- SQL operation complete.
->>
->>-- user2 grants
->>grant select, insert on games to sql_user3 with grant option by sql_user2;
-
---- SQL operation complete.
->>grant select on games to sql_user4 with grant option by sql_user2;
-
---- SQL operation complete.
->>grant insert on games to sql_user4 by sql_user2;
-
---- SQL operation complete.
->>
->>-- user3 grants
->>grant select on games to sql_user4 granted by sql_user3;
-
---- SQL operation complete.
->>grant select on games to sql_user5 with grant option granted by sql_user3;
-
---- SQL operation complete.
->>
->>-- user4 grants
->>grant select on games to sql_user5 with grant option granted by sql_user4;
-
---- SQL operation complete.
->>
->>-- user5 grante
->>grant select on games to sql_user6 by sql_user5;
-
---- SQL operation complete.
->>execute get_privs;
-
-OBJECT_NAME TYPE GRANTOR GRANTEE GRANTED_PRIVS GRANTABLE_PRIVS
----------------------------------------------------------------------------------------------------------------------------------------------------------------- ---- ---------- ---------- -------------------- --------------------
-
-TRAFODION.T140_SHARED_VIEWS.GAMES_BY_PLA VI -2 SQL_USER2 SI----- SI-----
-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.PLAYERS_ON_T VI -2 SQL_USER3 S------ 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-----
-TRAFODION.T140_USER1_PRIVATE.GAMES BT SQL_USER1 SQL_USER4 SI----- NONE
-TRAFODION.T140_USER1_PRIVATE.GAMES BT SQL_USER2 SQL_USER3 SI----- SI-----
-TRAFODION.T140_USER1_PRIVATE.GAMES BT SQL_USER2 SQL_USER4 SI----- S------
-TRAFODION.T140_USER1_PRIVATE.GAMES BT SQL_USER3 SQL_USER4 S------ NONE
-TRAFODION.T140_USER1_PRIVATE.GAMES BT SQL_USER3 SQL_USER5 S------ S------
-TRAFODION.T140_USER1_PRIVATE.GAMES BT SQL_USER4 SQL_USER5 S------ S------
-TRAFODION.T140_USER1_PRIVATE.GAMES BT SQL_USER5 SQL_USER6 S------ NONE
-TRAFODION.T140_USER1_PRIVATE.PLAYERS BT -2 SQL_USER1 SIDU-R- SIDU-R-
-TRAFODION.T140_USER1_PRIVATE.PLAYERS BT SQL_USER1 SQL_USER2 SI----- SI-----
-TRAFODION.T140_USER1_PRIVATE.PLAYERS BT SQL_USER2 SQL_USER3 SI----- -I-----
-TRAFODION.T140_USER1_PRIVATE.PLAYERS BT SQL_USER3 SQL_USER4 -I----- NONE
-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-
-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.
->>
->>-- user6 tries to grant select to user7
->>grant select on games to sql_user7 by sql_user6;
-
-*** ERROR[1012] No privileges were granted. You lack grant option on the specified privileges.
-
---- SQL operation failed with errors.
->>
->>-- user5 tries to grant to user2 (circular)
->>grant select on games to sql_user2 by sql_user5;
-
-*** ERROR[1036] Authorization ID SQL_USER5 cannot grant to authorization ID SQL_USER2 because it could create a circular dependency.
-
---- SQL operation failed with errors.
->>
->>-- 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;
-
-*** ERROR[1037] Revoke failed because of a dependent grant between authorization ID SQL_USER3 and authorization ID SQL_USER4
-
---- SQL operation failed with errors.
->>
->>-- remove user3->user5 grant
->>revoke select on games from sql_user4 by sql_user3;
-
---- SQL operation complete.
->>revoke grant option for select on games from sql_user3 by sql_user2;
-
-*** ERROR[1037] Revoke failed because of a dependent grant between authorization ID SQL_USER3 and authorization ID SQL_USER5
-
---- SQL operation failed with errors.
->>revoke select on games from sql_user5 by sql_user3;
-
---- SQL operation complete.
->>revoke grant option for select on games from sql_user3 by sql_user2;
-
---- SQL operation complete.
->>
->>execute get_privs;
-
-OBJECT_NAME TYPE GRANTOR GRANTEE GRANTED_PRIVS GRANTABLE_PRIVS
----------------------------------------------------------------------------------------------------------------------------------------------------------------- ---- ---------- ---------- -------------------- --------------------
-
-TRAFODION.T140_SHARED_VIEWS.GAMES_BY_PLA VI -2 SQL_USER2 SI----- SI-----
-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.PLAYERS_ON_T VI -2 SQL_USER3 S------ 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-----
-TRAFODION.T140_USER1_PRIVATE.GAMES BT SQL_USER1 SQL_USER4 SI----- NONE
-TRAFODION.T140_USER1_PRIVATE.GAMES BT SQL_USER2 SQL_USER3 SI----- -I-----
-TRAFODION.T140_USER1_PRIVATE.GAMES BT SQL_USER2 SQL_USER4 SI----- S------
-TRAFODION.T140_USER1_PRIVATE.GAMES BT SQL_USER4 SQL_USER5 S------ S------
-TRAFODION.T140_USER1_PRIVATE.GAMES BT SQL_USER5 SQL_USER6 S------ NONE
-TRAFODION.T140_USER1_PRIVATE.PLAYERS BT -2 SQL_USER1 SIDU-R- SIDU-R-
-TRAFODION.T140_USER1_PRIVATE.PLAYERS BT SQL_USER1 SQL_USER2 SI----- SI-----
-TRAFODION.T140_USER1_PRIVATE.PLAYERS BT SQL_USER2 SQL_USER3 SI----- -I-----
-TRAFODION.T140_USER1_PRIVATE.PLAYERS BT SQL_USER3 SQL_USER4 -I----- NONE
-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-
-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.
->>
->>log;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/e3d65c15/core/sql/regress/catman1/EXPECTED141
----------------------------------------------------------------------
diff --git a/core/sql/regress/catman1/EXPECTED141 b/core/sql/regress/catman1/EXPECTED141
deleted file mode 100644
index 838ed26..0000000
Binary files a/core/sql/regress/catman1/EXPECTED141 and /dev/null differ
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/e3d65c15/core/sql/regress/catman1/FILTER132
----------------------------------------------------------------------
diff --git a/core/sql/regress/catman1/FILTER132 b/core/sql/regress/catman1/FILTER132
deleted file mode 100755
index d15a796..0000000
--- a/core/sql/regress/catman1/FILTER132
+++ /dev/null
@@ -1,40 +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 @@@
-
-# Like the pattern-masking of QACOMP, here we filter out such stuff as
-# timestamps and generated identifiers. Called by the runregr script
-# before doing diff.
-
-fil=$1
-if [ "$fil" = "" ]; then
- echo "Usage: $0 filename"
- exit 1
-fi
-
-# filters for showstats output
-#Table ID: 56910649950845593
-#2071939932 8 8 8 SYSKEY
-sed "
-s/Table ID: *[0-9]*/Table ID: <ID removed>/g
-s:^[ ]\{0,9\}[0-9]\{1,10\}:0000000000:
-" $fil
-
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/e3d65c15/core/sql/regress/catman1/TEST129
----------------------------------------------------------------------
diff --git a/core/sql/regress/catman1/TEST129 b/core/sql/regress/catman1/TEST129
deleted file mode 100755
index 70875fb..0000000
--- a/core/sql/regress/catman1/TEST129
+++ /dev/null
@@ -1,442 +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 @@@
---
--- Grant column-level insert and select privilges
--- Check privilege for DML statements in compiler
-
-obey TEST129(clean_up);
-
-sh sqlci -i "TEST129(setup)" -u sql_user4;
-sh sqlci -i "TEST129(step1)" -u sql_user1;
-sh sqlci -i "TEST129(step1)" -u sql_user1;
-sh sqlci -i "TEST129(step2)" -u sql_user2;
-sh sqlci -i "TEST129(step3)" -u sql_user3;
-sh sqlci -i "TEST129(step4)" -u sql_user3;
-sh sqlci -i "TEST129(step5)" -u sql_user4;
-sh sqlci -i "TEST129(step6)" -u sql_user4;
-sh sqlci -i "TEST129(step7)" -u sql_user4;
-sh sqlci -i "TEST129(invalidate)" -u sql_user1;
-
-exit;
-
-?section clean_up
-drop schema us4 cascade;
-
-?section setup
-cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
-cqd CAT_TEST_BOOL 'ON';
-log LOG129 clear;
-log LOG129;
-create schema us4;
-set schema us4;
-
-create table t1 (col1 int not null primary key, col2 int, col3 int) no partition;
-insert into t1 values (1,1,1), (2,2,2), (5,5,5), (7,7,7);
-create view v1(vc1,vc2,vc3) as select * from t1 ;
-
-grant update on t1 to sql_user1 ;
-grant select(col3) on t1 to sql_user1;
-grant select on v1 to sql_user1;
-grant update(vc1) on v1 to sql_user1 ;
-grant insert(vc1) on v1 to sql_user1 ;
-
-grant select on t1 to sql_user2;
-grant update (col3) on t1 to sql_user2 ;
-grant update(col2) on t1 to sql_user2;
-
-grant update(col1) on t1 to sql_user3;
-grant select(col1) on t1 to sql_user3;
-showddl t1;
-
-create table t2( a int not null primary key, b int default null, c int default null) ;
-grant insert on t2 to sql_user1;
-grant select on t2 to sql_user1;
-grant insert(a,b) on t2 to sql_user2 ;
-grant select(a,b) on t2 to sql_user2 ;
-grant insert(a) on t2 to sql_user3;
-grant select(a) on t2 to sql_user3;
-showddl t2;
-
-create table t129_starter (a int not null, primary key(a) NOT DROPPABLE )no partition;
-insert into t129_starter values (1);
-
-create table t129_a
- (uniq int not null,
- c100 int not null,
- c10 int not null,
- c1 int not null,
- filler char(4000) default 'a',
- primary key (uniq)
- NOT DROPPABLE
- ) no partition ;
-
-insert into t129_a (uniq,c100,c10,c1)
- select
- 0 + (100 * x100) + (10 * x10) + (1 * x1),
- 0 + (10 * x10) + (1 * x1),
- 0 + (1 * x1),
- 0
- from t129_starter
- transpose 0,1,2,3,4,5,6,7,8,9 as x100
- transpose 0,1,2,3,4,5,6,7,8,9 as x10
- transpose 0,1,2,3,4,5,6,7,8,9 as x1
- ;
-
-update statistics for table t129_a on uniq ;
-
-grant select on t129_a to sql_user1 ;
-grant select on t129_starter to sql_user1 ;
-
--- Testing creating views based on Column-level select privilege
---grant create_view on schema cat.us4 to sql_user3;
-
-create table t3 (a int not null not droppable, b int, c int, d int, primary key (a));
-create table t4 (e int not null not droppable, f int, g int, h int, primary key (e));
-
-grant select (b,d) on t3 to sql_user3 with grant option;
-grant select (f,g) on t4 to sql_user3;
-
-showddl t3;
-showddl t4;
-
-insert into t3 values (1,2,3,4), (5,6,7,8);
-insert into t4 values (11,22,33,44), (55,66,77,88);
-
-?section step1
-cqd CAT_TEST_BOOL 'ON';
-log LOG129;
--- As user1, should fail
-select * from us4.t1 ;
-delete from us4.t1;
-insert into us4.t1 values (1,1,1);
-update us4.t2 set b = c ;
-update us4.v1 set vc2 = vc1 ;
-insert into us4.v1(vc1,vc2) values (10,10) ;
-
--- As user1, should succeed
-update us4.t1 set col3 = 2 ;
-select * from us4.v1 order by vc1;
-insert into us4.t2 values (5,5,5);
-update us4.v1 set vc1 = vc1 ;
-insert into us4.v1(vc1) values (10) ;
-
-delete all from table(querycache()) ;
-delete all from table(natablecache());
-select * from table(querycacheentries());
-select * from table(natablecache()) ;
-
-log;
-
-?section step2
-cqd CAT_TEST_BOOL 'ON';
-log LOG129;
--- As user2, should fail
-delete from us4.t1 ;
-insert into us4.t1 values (1,1,1);
-select * from us4.v1 ;
-insert into us4.t2 values (1,1,1);
-
--- As user2, should succeed
-update us4.t1 set col3 = 4 ;
-update us4.t1 set col2 = 3 ;
-select * from us4.t1 order by col1;
-insert into us4.t2(a,b) values (1,1);
-log;
-
-?section step3
-cqd CAT_TEST_BOOL 'ON';
-log LOG129;
--- as user3
-insert into us4.t2(a,b) values (2,2) ;
-update us4.t1 set col2 = col1;
-
-
-insert into us4.t2(a) values (3) ;
-update us4.t1 set col1 = col1 + 1;
-
-select col1 from us4.t1 order by 1;
-select col2 from us4.t1 ;
-
-select col1 from us4.t1 where col2 > 100;
-select col1 from us4.t1 where col1 > 100 order by 1;
-
-select count(*), min(col1) from us4.t1 group by col2;
-select count(*), min(col1) from us4.t1 group by col1;
-
-select count(*) from us4.t1 group by col1 having min(col2) > 10;
-select count(*) from us4.t1 group by col1 having min(col1) > 10;
-
-select x from us4.t1 transpose col2 as x;
-select x from us4.t1 transpose col1 as x order by 1;
-
-select col1 from us4.t1 sample random balance
-when col2 = 1 then 100 percent else 0 percent end;
-select col1 from us4.t1 sample random balance
-when col1 = 1 then 100 percent else 0 percent end order by 1;
-
-select col1 from us4.t1 where exists (select 1 from (values (us4.t1.col2)) t2(x) where t2.x = 100);
-select col1 from us4.t1 where exists (select 1 from (values (us4.t1.col1)) t2(x) where t2.x = 100) order by 1;
-
-select * from us4.t1 ;
-
-log;
-
-?section step4
-cqd CAT_TEST_BOOL 'ON';
-log LOG129;
--- Testing create view based on column-level SELECT
--- as user3
-
-set schema cat.us4;
-
--- View on single table (positive):
-
-create view v3bd as select b,d from t3;
-select * from v3bd;
-create view v3b as select b from t3;
-select * from v3b;
-create view v3d as select d from t3;
-select * from v3d;
-create view v3bbbbbb (c1,c2,c3,c4,c5,c6) as select b,b,b,b,b,b from t3;
-select * from v3bbbbbb;
-
--- View on single table (negative):
-
-create view v3ac as select a,c from t3;
-create view v3a as select a from t3;
-create view v3c as select c from t3;
-
-create view v3 as select * from t3;
-
-create view v3ab as select a,b from t3;
-create view v3abcd as select a,b,c,d from t3;
-create view v3bc as select b,c from t3;
-
--- View on two tables (positive):
-
-create view v34bf as select b,f from t3, t4;
-select * from v34bf;
-create view v34bdfg as select b,d,f,g from t3, t4;
-select * from v34bdfg;
-create view v34bdfg2 (c1,c2,c3,c4) as select b,d,f,g from t3, t4;
-select * from v34bdfg2;
-create view v34gb as select g,b from t3, t4;
-select * from v34gb;
-
--- View on two tables (negative):
-
-create view v34 as select * from t3,t4;
-create view v34af as select a,f from t3, t4;
-create view v34bh as select b,h from t3,t4;
-
-
-log;
-
-?section step5
-cqd CAT_TEST_BOOL 'ON';
-log LOG129;
--- Testing revoke for column-level SELECT
--- as user4
-
-set schema us4;
-
-grant select (b,d) on t3 to sql_user3 with grant option;
-grant select (f,g) on t4 to sql_user3;
-showddl t3;
-showddl t4;
-
--- Negative tests: revoking from table
-revoke select (c) on t3 from sql_user3;
-revoke select (b) on t3 from sql_user3;
-
-revoke grant option for select (c) on t3 from sql_user3;
-
-revoke grant option for select (d) on t3 from sql_user3;
-
-revoke grant option for select (b) on t3 from sql_user3;
-revoke grant option for select (b) on t3 from sql_user3 cascade;
-showddl t3;
-
-?section step6
-cqd CAT_TEST_BOOL 'ON';
-set schema us4;
-
--- Testing other variations of column level
-
--- Negative tests
-grant select (colx) on t1 to sql_user5;
-grant select (col1, colx, col2) on t1 to sql_user5;
-grant insert (colx) on t1 to sql_user5 with grant option;
-revoke select (colx) on t1 from sql_user5;
-revoke insert (col1, colx) on t1 from sql_user5 cascade;
-
--- Negative tests: duplicate columns
-grant select on t1 (col1, col1) to sql_user5;
-revoke insert on t1 (col1, col1) from sql_user5;
-revoke grant option for select (col1, col1) on t1 from sql_user5;
-
--- Positive tests
-create table t1 (a int not null not droppable, b int);
-showddl t1;
-grant select (a) on t1 to sql_user5 with grant option;
-showddl t1;
-revoke grant option for select (a) on t1 from sql_user5;
-showddl t1;
-revoke select (a) on t1 from sql_user5 cascade;
-showddl t1;
-
-?section invalidate
-cqd CAT_TEST_BOOL 'ON';
-log LOG129;
--- as user1
-
-cqd query_cache '0' ;
-cqd metadata_cache_size '0' ;
-
-select count(*) from us4.t129_a ;
-
-sh sh runmxci.ksh -i "TEST129(revoke1)" -u sql_user4;
--- MUST DELAY so that QVP process has time to send Security Key invalidations to all SQL Compiler caches
-sh sleep 10;
-
--- checking that cache gets refreshed.
--- should get an error
-prepare s1 from select * from us4.t129_a ;
-
-cqd metadata_cache_size reset ;
-
-select case when current_cache_size > 0 then 1 else 0 end from table(natablecache());
-
---should error but place t129_a in natable cache
-prepare s1 from select * from us4.t129_a where c1 > 10;
-
-select case when current_cache_size > 0 then 1 else 0 end from table(natablecache());
-
-sh sh runmxci.ksh -i "TEST129(grant1)" -u sql_user4;
-
-
--- should succeed
-prepare s1 from select * from us4.t129_a as t1, us4.t129_a as t2;
-
-cqd query_cache reset ;
-cqd query_text_cache 'off' ;
-
-select count(*) from us4.t129_a where c1 = 10 ;
-
-select num_entries from table(querycache());
-
-sh sh runmxci.ksh -i "TEST129(revoke1)" -u sql_user4;
--- MUST DELAY so that QVP process has time to send Security Key invalidations to all SQL Compiler caches
-sh sleep 10;
-
--- should fail
-select count(*) from us4.t129_a where c1 = 10 ;
-
--- cache has 1 entry
-select num_entries from table(querycache());
-
-
-cqd query_text_cache reset ;
-cqd auto_query_retry 'off' ;
-
-sh sh runmxci.ksh -i "TEST129(grant1)" -u sql_user4;
-
-select count(*) from us4.t129_a where c1 = 100 ;
-
-sh sh runmxci.ksh -i "TEST129(revoke1)" -u sql_user4;
--- MUST DELAY so that QVP process has time to send Security Key invalidations to all SQL Compiler caches
-sh sleep 10;
-
--- fails
-select count(*) from us4.t129_a where c1 = 100 ;
-
-sh sh runmxci.ksh -i "TEST129(grant1)" -u sql_user4;
-
-cqd auto_query_retry reset ;
-cqd auto_query_retry_warnings 'on' ;
-
-
-select count(*) from us4.t129_a where c10 = 100 ;
-
-sh sh runmxci.ksh -i "TEST129(grant2)" -u sql_user4;
-
-
-select count(*) from us4.t129_a where c10 = 100 ;
-
-cqd auto_query_retry_warnings reset ;
-prepare s1 from select count(*) from us4.t129_a where c10 < 100 ;
-
-sh sh runmxci.ksh -i "TEST129(revoke1)" -u sql_user4;
--- MUST DELAY so that QVP process has time to send Security Key invalidations to all SQL Compiler caches
-sh sleep 10;
-
--- should error
-execute s1 ;
-
-sh sh runmxci.ksh -i "TEST129(grant1)" -u sql_user4;
-
-set envvar sqlci_cursor '1';
-cqd attempt_esp_parallelism 'off' ;
-cqd nested_joins 'off' ;
-cqd merge_joins 'off' ;
-
-declare c1 cursor for
-select uniq from us4.t129_a, us4.t129_starter
-where filler = 'a' or a = 1 for read uncommitted access ;
-
-open c1 ;
-
-sh sh runmxci.ksh -i "TEST129(grant3)" -u sql_user4;
-
-fetch c1;
-fetch c1;
-fetch c1;
-fetch c1;
-fetch c1;
-fetch c1;
-fetch c1;
-fetch c1;
-
-log ;
-exit ;
-
-?section revoke1
-cqd CAT_TEST_BOOL 'ON';
-log LOG129;
-revoke select on us4.t129_a from sql_user1 ;
-
-?section grant1
-cqd CAT_TEST_BOOL 'ON';
-log LOG129;
-grant select on us4.t129_a to sql_user1 ;
-
-?section grant2
-cqd CAT_TEST_BOOL 'ON';
-log LOG129;
-grant select on us4.t129_a to sql_user2 ;
-
-?section grant3
-cqd CAT_TEST_BOOL 'ON';
-log LOG129;
-grant select on us4.t129_a to sql_user3 ;
-
-
-
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/e3d65c15/core/sql/regress/catman1/TEST131
----------------------------------------------------------------------
diff --git a/core/sql/regress/catman1/TEST131 b/core/sql/regress/catman1/TEST131
deleted file mode 100755
index 3e82299..0000000
--- a/core/sql/regress/catman1/TEST131
+++ /dev/null
@@ -1,365 +0,0 @@
--- 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 T131c;
-drop table T131b;
-drop table 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.
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/e3d65c15/core/sql/regress/catman1/TEST132
----------------------------------------------------------------------
diff --git a/core/sql/regress/catman1/TEST132 b/core/sql/regress/catman1/TEST132
deleted file mode 100755
index 19f2904..0000000
--- a/core/sql/regress/catman1/TEST132
+++ /dev/null
@@ -1,447 +0,0 @@
--- ============================================================================
--- TEST132 - tests utility privilege checking
---
--- @@@ 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:
---
--- Library operations
--- LOAD/UNLOAD & PURGEDATA
--- POPULATE INDEX
--- SHOWDDL & INVOKE
--- UPDATE STATISTICS
---
--- Sections:
--- clean_up - removes database setup
--- set_up - prepares for test
--- test_<type> - runs tests for different commands
--- <operation>_<type> - runs tests for an operation by a user
--- ============================================================================
-
-cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
-obey TEST132(clean_up);
-obey TEST132(set_up);
-log LOG132 clear;
-obey TEST132(test_libraries);
-obey TEST132(test_load_unload_purgedata);
-obey TEST132(test_popindex);
-obey TEST132(test_show);
-obey TEST132(test_stats);
-log;
-obey TEST132(clean_up);
-exit;
-
-?section clean_up
-set schema t132sch;
-drop sequence t132_team_number_sequence;
-drop schema t132sch cascade;
-
-?section set_up
-create shared schema t132sch;
-
--- Prepare library file
-sh rm -f ./etest140.dll;
-sh sh $$scriptsdir$$/tools/dll-compile.ksh etest140.cpp
- 2>&1 | tee LOG132-SECONDARY;
-set pattern $$DLL$$ etest140.dll;
-
-?section test_libraries
--- =================================================================
--- run tests to make sure users that create libraries have correct
--- privileges. To create a library, you must:
--- be DB__ROOT
--- be granted DB__ROOTROLE
--- have the MANAGE_LIBRARY and CREATE/CREATE_LIBARARY privileges
--- =================================================================
-
-set schema t132sch;
-get libraries;
-get privileges on component sql_operations for "PUBLIC";
-
--- succeed: DB__ROOT can create a library
-sh sqlci -i "TEST132(manage_library)";
-
--- fail: sql_user1 cannot create a library
-sh sqlci -i "TEST132(manage_library)" -u sql_user1;
-
--- succeed: grant DB__ROOTROLE to sql_user1
-grant role DB__ROOTROLE to sql_user1;
-sh sqlci -i "TEST132(manage_library)" -u sql_user1;
-
--- fail: just grant the create privilege
-grant component privilege CREATE_LIBRARY on sql_operations to sql_user2;
-sh sqlci -i "TEST132(manage_library)" -u sql_user2;
-
--- succeed: now grant the manage_library privilege
-grant component privilege MANAGE_LIBRARY on sql_operations to sql_user2;
-get privileges on component sql_operation for sql_user2;
-sh sqlci -i "TEST132(manage_library)" -u sql_user2;
-
--- reset
-revoke role DB__ROOTROLE from sql_user1;
-revoke component privilege MANAGE_LIBRARY on sql_operations from sql_user2;
-revoke component privilege CREATE_LIBRARY on sql_operations from sql_user2;
-get privileges on component sql_operations for "PUBLIC";
-
-?section manage_library
-
-set schema t132sch;
-log LOG132;
-get libraries in schema t132sch;
-create library t132_l1 file 'udrtest135.dll';
-get libraries in schema t132sch;
-drop library t132_l1;
-get libraries in schema t132sch;
-
-?section test_load_unload_purgedata
--- =================================================================
--- run tests to make sure users with correct privileges can load
--- and unload data. To load a table, you must:
--- be DB__ROOT
--- be granted DB__ROOTROLE
--- have the MANAGE_LOAD
--- be table owner
--- have SELECT and INSERT (DELETE for TRUNCATE option) privilege
---
--- To unload a table, you must:`
--- be DB__ROOT
--- be granted DB__ROOTROLE
--- have the MANAGE_LOAD
--- be table owner
--- have SELECT privilege
---
--- To purgedata data, you must:
--- be DB__ROOT
--- be table owner
--- have SELECT and DELETE privileges
--- =================================================================
-
-set schema t132sch;
-get tables;
-
-
--- perform a load and unload as DB__ROOT
--- purgedata
--- perform a load and unload after being granted DB__ROOTROLE
--- purgedata
--- perform a load and unload without privileges
--- perform a load and unload as table owner
--- purgedata
--- perform a load and unload with SELECT
--- perform a load and unload with SELECT, INSERT
--- purgedata, grant DELETE and purgedata again
--- perform a load (truncate) and unload with select, insert, delete
--- perform a load and unload with MANAGE_LOAD privilege
-
--- reset
-
-?section test_popindex
--- =================================================================
--- run tests to make sure users that populate indexes have correct
--- privileges. To populate an index, you must:
--- be DB__ROOT
--- be table owner
--- have the SELECT and INSERT privilege
--- =================================================================
-
-set schema t132sch;
-get tables;
-
-create table t132t1 (c1 int not null primary key, c2 int);
-create index t132t1_ndx1 on t132t1 (c2) no populate;
-create table t132t2 (c1 int not null primary key, c2 int)
- attribute by sql_user1;
-create index t132t2_ndx1 on t132t2(c2) no populate;
-create table t132t3 (c1 int not null primary key, c2 int)
- attribute by sql_user1;
-create index t132t3_ndx1 on t132t3(c2) no populate;
-
-insert into t132t1 values (1,1), (2,2), (3,3),(4,4), (5,5),(6,6), (7,7),(8,8);
-insert into t132t2 values (1,1), (2,2), (3,3),(4,4), (5,5),(6,6), (7,7),(8,8);
-insert into t132t3 values (1,1), (2,2), (3,3),(4,4), (5,5),(6,6), (7,7),(8,8);
-
-get tables;
-showddl t132t1;
-showddl t132t2;
-showddl t132t3;
-
--- DB__ROOT can populate indexes
-sh sqlci -i "TEST132(populate_index)";
-obey TEST132(popindex_check_reset);
-
--- object owner can populate
--- sql_user1 owns t132t2 and t132t3 but not t132t1
--- popindex fails for t132t1 but works for the rest
-sh sqlci -i "TEST132(populate_index)" -u sql_user1;
-obey TEST132(popindex_check_reset);
-
--- if user belongs to DB__ROOTROLE, has no privilege
-grant role DB__ROOTROLE to sql_user2;
-sh sqlci -i "TEST132(populate_index)" -u sql_user2;
-
--- if user has SELECT and INSERT privileges
-grant SELECT,INSERT on t132t1 to sql_user3;
-grant SELECT, INSERT on t132t2 to sql_user3 by sql_user1;
-sh sqlci -i "TEST132(populate_index)" -u sql_user3;
-obey TEST132(popindex_check_reset);
-
--- reset
-revoke role DB__ROOTROLE from sql_user2;
-drop table t132t1 cascade;
-drop table t132t2 cascade;
-drop table t132t3 cascade;
-get tables;
-
-?section populate_index
-set schema t132sch;
-log LOG132;
-
-populate index t132t1_ndx1 on t132t1;;
-populate index t132t2_ndx1 on t132t2;
-populate index t132t3_ndx1 on t132t3;
-
-?section popindex_check_reset
-set schema t132sch;
-log LOG132;
-set parserflags 1;
-
-select count(*) from table (index_table t132t1_ndx1);
-select count(*) from table (index_table t132t2_ndx1);
-select count(*) from table (index_table t132t3_ndx1);
-
-drop index t132t1_ndx1;
-create index t132t1_ndx1 on t132t1 (c2) no populate;
-drop index t132t2_ndx1;
-create index t132t2_ndx1 on t132t2 (c2) no populate;
-drop index t132t3_ndx1;
-create index t132t3_ndx1 on t132t3 (c2) no populate;
-
-?section test_show
--- =================================================================
--- run tests to make sure users that perform show commands have correct
--- privileges. To perform show commands, you must:
--- be DB__ROOT
--- be object owner
--- have the SHOW privilege (PUBLIC & DB__ROOTROLE has priv)
--- have SELECT privileges on object
--- =================================================================
-
-set schema t132sch;
-
-create table t132_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 t132_teams add constraint valid_team_no check (team_number > 0);
-
-insert into t132_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 t132_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)
- attribute by sql_user1
- ;
-create index t132_home_games on t132_games (home_team_number);
-
-alter table t132_games add constraint valid_game_number check (game_number > 0);
-
-insert into t132_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 t132_giants_games as
- select game_number, game_time, game_location
- from t132_games
- where home_team_number = 2
- order by 1,2,3;
-select * from t132_giants_games;
-
-create view t132_home_teams_games as
- select t.team_number, g.game_number, g.game_time
- from "T132_TEAMS" t,
- "T132_GAMES" g
- where t.team_number = g.home_team_number
- order by 1, game_number, game_time;
-select team_number, game_number from t132_home_teams_games;
-
-create sequence t132_team_number_sequence;
-
--- revoke show prvilege from PUBLIC
-get privileges on component sql_operations for "PUBLIC";
-revoke component privilege "SHOW" on sql_operations from "PUBLIC";
-get privileges on component sql_operations for "PUBLIC";
-
--- DB__ROOT has all privileges
-obey TEST132(show_objects);
-
--- sql_user1 owns some of the objects but not all
-sh sqlci -i "TEST132(show_objects)" -u sql_user1;
-
--- sql_user2 get privileges through DB__ROOTROLE role SHOW privilege
--- first illustrate that sql_user2 has no privileges
-sh sqlci -i "TEST132(show_objects)" -u sql_user2;
-grant role DB__ROOTROLE to sql_user2;
-
--- now sql_user2 has privileges with the grant
-sh sqlci -i "TEST132(show_objects)" -u sql_user2;
-revoke role DB__ROOTROLE from sql_user2;
-
--- sql_user3 gets some privileges through SELECT grant
-grant SELECT on t132_teams to sql_user3;
-sh sqlci -i "TEST132(show_objects)" -u sql_user3;
-
--- regrant the show privs - everyone has privs
-get privileges on component sql_operations for "PUBLIC";
-grant component privilege "SHOW" on sql_operations to "PUBLIC";
-get privileges on component sql_operations for "PUBLIC";
-sh sqlci -i "TEST132(show_objects)" -u sql_user1;
-sh sqlci -i "TEST132(show_objects)" -u sql_user2;
-sh sqlci -i "TEST132(show_objects)" -u sql_user3;
-
-drop table t132_teams cascade;
-drop table t132_games cascade;
-drop sequence t132_team_number_sequence;
-
-?section show_objects
-
-set schema t132sch;
-log LOG132;
-cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
-
-showddl t132_games;
-showddl t132_teams;
-showddl t132_giants_games;
-showddl t132_home_teams_games;
-showddl sequence t132_team_number_sequence;
-
-invoke t132_games;
-invoke t132_teams;
-invoke t132_giants_games;
-invoke t132_home_teams_games;
-
-?section test_stats
--- =================================================================
--- run tests to make sure users that update statistics have correct
--- privileges. To update stats, you must:
--- be DB__ROOT
--- be table owner
--- have the MANAGE_STATISTICS privilege (DB__ROOTROLE has priv)
--- =================================================================
-
-set schema t132sch;
-get tables;
-
-create table t132t1 (c1 int, c2 int);
-create table t132t2 (c1 int, c2 int) attribute by sql_user1;
-insert into t132t1 values (1,1), (2,2), (3,3),(4,4), (5,5),(6,6), (7,7),(8,8);
-insert into t132t2 values (1,1), (2,2), (3,3),(4,4), (5,5),(6,6), (7,7),(8,8);
-
-get tables;
-select count(*) from t132t1;
-select count(*) from t132t2;
-
--- update statistics as DB__ROOT
-sh sqlci -i "TEST132(update_stats)";
-
--- run as DB__ROOTROLE
--- DB__ROOTROLE is granted MANAGE_STATISTICS privilege by default
--- first show that sql_user2 cannot perform operations
-sh sqlci -i "TEST132(update_stats)" -u sql_user2;
-grant role DB__ROOTROLE to sql_user2;
-
--- now show privileges after being granted DB__ROOTROLE role
-sh sqlci -i "TEST132(update_stats)" -u sql_user2;
-revoke role DB__ROOTROLE from sql_user2;
-
--- run as table owner, sql_user1 owns one table
--- update stats only works for t132t2, showstats works on both tables
-sh sqlci -i "TEST132(show_update_stats)" -u sql_user1;
-
--- revoke SHOW privilege from public for the next set of tests
-get privileges on component sql_operations for "PUBLIC";
-revoke component privilege "SHOW" on sql_operations from "PUBLIC";
-get privileges on component sql_operations for "PUBLIC";
-
--- Run with MANAGE_STATISTICS and no SHOW
--- first illustrate that sql_user3 has no privs
-get privileges on component sql_operations for sql_user3;
-sh sqlci -i "TEST132(show_update_stats)" -u sql_user3;
-grant component privilege MANAGE_STATISTICS on sql_operations to sql_user3;
-
--- now show privileges after being granted MANAGE_STATISTICS
-get privileges on component sql_operations for sql_user3;
-sh sqlci -i "TEST132(show_update_stats)" -u sql_user3;
-revoke component privilege MANAGE_STATISTICS on sql_operations from sql_user3;
-get privileges on component sql_operations for sql_user3;
-
--- test showstats
--- showstats should no longer work
-sh sqlci -i "TEST132(show_stats)" -u sql_user3;
-
--- grant select to allow showstats to work
-grant SELECT on t132t1 to sql_user4;
-showddl t132t1;
-sh sqlci -i "TEST132(show_stats)" -u sql_user4;
-
--- reset
-revoke SELECT on t132t1 from sql_user4;
-grant component privilege "SHOW" on sql_operations to "PUBLIC";
-get privileges on component sql_operations for "PUBLIC";
-
-drop table t132t1;
-drop table t132t2;
-
-?section show_update_stats
-obey TEST132(update_stats);
-obey TEST132(show_stats);
-
-?section update_stats
-set schema t132sch;
-log LOG132;
-
-update statistics for table t132t1 on every column;
-update statistics for table t132t2 on every column;
-
-?section show_stats
-set schema t132sch;
-log LOG132;
-
-showstats for table t132t1 on every column;
-showstats for table t132t2 on every column;