core/sql/regress/privs1/TEST132

-- ============================================================================
-- 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
--   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
-- ============================================================================

obey TEST132(clean_up);
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
obey TEST132(set_up);
log LOG132 clear;
obey TEST132(test_libraries);
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;
cleanup schema t132sch;
drop schema t132sch_private cascade;

?section set_up
create shared schema t132sch;

-- Prepare library file
sh rm -f ./etest132.dll;
sh sh $$scriptsdir$$/tools/dll-compile.ksh etest132.cpp
  2>&1 | tee LOG132-SECONDARY;
set pattern $$DLL$$ etest132.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
obey TEST132(manage_library);

-- fail: sql_user1 cannot create a library
changeuser sql_user1;
obey TEST132(manage_library);
changeuser db__root;

-- succeed: grant DB__ROOTROLE to sql_user1
grant role DB__ROOTROLE to sql_user1;
changeuser sql_user1;
obey TEST132(manage_library);
changeuser DB__ROOT;

-- fail: just grant the create privilege
grant component privilege CREATE_LIBRARY on sql_operations to sql_user2;
changeuser sql_user2;
obey TEST132(manage_library);
changeuser DB__ROOT;

-- 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;
changeuser sql_user2;
obey TEST132(manage_library);
changeuser DB__ROOT;

-- 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;
get libraries in schema t132sch;
create library t132_l1 file 'etest132.dll';
get libraries in schema t132sch;
drop library t132_l1;
get libraries in schema t132sch;

?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;
--set parserflags 131072;
--cqd DDL_TRANSACTIONS 'ON';

get tables, match '%T132%';

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;

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, match '%T132%';
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
showddl t132t1;
showddl t132t2;

-- DB__ROOT can populate indexes
obey TEST132(populate_index);
obey TEST132(popindex_check_reset);

-- object owner can populate
-- sql_user1 owns t132t2 but not t132t1
--  popindex fails for t132t1 but works for the rest
changeuser sql_user1;
obey TEST132(populate_index);
changeuser db__root;
obey TEST132(popindex_check_reset);

-- if user belongs to DB__ROOTROLE, has DML privileges, so can populate indexes
grant role DB__ROOTROLE to sql_user2;
changeuser sql_user2;
obey TEST132(populate_index);
changeuser db__root;
obey TEST132(popindex_check_reset);
revoke role DB__ROOTROLE from sql_user2;

-- sql_user3 requires both SELECT and INSERT privileges
-- only t132t2 has granted both privileges
grant SELECT on t132t1 to sql_user3;
grant SELECT, INSERT on t132t2 to sql_user3 by sql_user1;
changeuser sql_user3;
obey TEST132(populate_index);
changeuser db__root;
obey TEST132(popindex_check_reset);

-- reset
drop table t132t1 cascade;
drop table t132t2 cascade;
get tables, match 'T132%';

?section populate_index
set schema t132sch;

populate index t132t1_ndx1 on t132t1;;
populate index t132t2_ndx1 on t132t2;

?section popindex_check_reset
set schema t132sch;
set parserflags 1;

select count(*) from table (index_table t132t1_ndx1);
select count(*) from table (index_table t132t2_ndx1);

cleanup 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;

?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
changeuser sql_user1;
obey TEST132(show_objects);

-- sql_user2 get privileges through DB__ROOTROLE role SHOW privilege
-- first illustrate that sql_user2 has no privileges
changeuser sql_user2;
obey TEST132(show_objects);
changeuser db__root;
grant role DB__ROOTROLE to sql_user2;

-- now sql_user2 has privileges with the grant
changeuser sql_user2;
obey TEST132(show_objects);
changeuser db__root;
revoke role DB__ROOTROLE from sql_user2;

-- sql_user3 gets some privileges through SELECT grant
set schema t132sch;
grant SELECT on t132_teams to sql_user3;
changeuser sql_user3;
obey TEST132(show_objects);
changeuser db__root;
set schema t132sch;
revoke select on t132_teams from 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";
changeuser sql_user1;
obey TEST132(show_objects);

changeuser db__root;
set schema t132sch;
drop table t132_teams cascade;
drop table t132_games cascade;
drop sequence t132_team_number_sequence;

?section show_objects

set schema t132sch;
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 SELECT privilege 
--   have the MANAGE_STATISTICS privilege (DB__ROOTROLE has priv)
-- =================================================================

set schema t132sch;
get tables, match '%T132%';

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, match '%T132%';
select count(*) from t132t1;
select count(*) from t132t2;

-- update statistics as DB__ROOT
obey TEST132(update_stats);

-- DB__ROOTROLE is granted MANAGE_STATISTICS privilege by default
-- first show that sql_user2 cannot perform operations
changeuser sql_user2;
obey TEST132(update_stats);
changeuser db__root;

-- now show privileges after being granted DB__ROOTROLE role
grant role DB__ROOTROLE to sql_user2;
changeuser sql_user2;
obey TEST132(update_stats);
changeuser DB__ROOT;
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
changeuser sql_user1;
obey TEST132(show_update_stats);
changeuser DB__ROOT;

-- 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;
changeuser sql_user3;
obey TEST132(show_update_stats);
changeuser db__root;

-- now show privileges after being granted MANAGE_STATISTICS
grant component privilege MANAGE_STATISTICS on sql_operations to sql_user3;
get privileges on component sql_operations for sql_user3;
changeuser sql_user3;
obey TEST132(show_update_stats);
changeuser db__root;
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
changeuser sql_user3;
obey TEST132(show_stats);
changeuser db__root;

-- grant select to allow showstats to work
set schema t132sch;
grant SELECT on t132t1 to sql_user4;
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
showddl t132t1;
changeuser sql_user4;
obey TEST132(show_stats);
changeuser db__root;

-- testcase for trafodion-2188 fix
create schema t132sch_private;
set schema t132sch_private;
CREATE TABLE t132t3
( c1 char(12) not null, c2 char(12) not null, c3 char(12) not null, 
  PRIMARY KEY (C1 ASC))
SALT USING 4 PARTITIONS
ON (C1);

upsert using load into t132t3
select
  x1 || x2 || x3 || x4 || x5,
  x2 || x4 || x1,
  x5 || x3
-- the from clause below creates 100,000 rows, the cross product of
-- 5 copies of { 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' }
  from (values('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j')) T(x1)
transpose 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' as x2
transpose 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' as x3
transpose 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' as x4
transpose 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' as x5;
;
select count(*) from t132t3;

grant component privilege MANAGE_STATISTICS on sql_operations to sql_user3;
get privileges on component sql_operations for sql_user3;
changeuser sql_user3;
obey TEST132(update_stats1);
changeuser db__root;
set schema t132sch_private; 
revoke component privilege MANAGE_STATISTICS on sql_operations from sql_user3;
cleanup schema t132sch_private;
set schema t132sch;

-- 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;

get tables, match 'T132%';

?section show_update_stats
obey TEST132(update_stats);
obey TEST132(show_stats);

?section update_stats
set schema t132sch;

update statistics for table t132t1 on every column;
update statistics for table t132t2 on every column;

?section update_stats1
set schema t132sch_private;
update statistics for table t132t3 create sample random 10 percent;

?section show_stats
set schema t132sch;

showstats for table t132t1 on every column;
showstats for table t132t2 on every column;