| -- ============================================================================ |
| -- 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, DB__ROOTROLE does not have DML privileges |
| 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; |
| cleanup 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; |