1# this test mostly test privilege control (what doesn't work 2# in the embedded server by default). So skip the test in embedded-server mode. 3-- source include/not_embedded.inc 4 5#Don't run this test when thread_pool active 6--source include/not_threadpool.inc 7 8-- source include/testdb_only.inc 9 10set local sql_mode=""; 11set global sql_mode=""; 12 13--disable_warnings 14drop table if exists t1,t2; 15drop view if exists v1,v2; 16drop function if exists f1; 17drop function if exists f2; 18--enable_warnings 19 20--replace_result 'Tables_in_INFORMATION_SCHEMA (T%)' 'Tables_in_information_schema (T%)' 21--sorted_result 22show tables from INFORMATION_SCHEMA like 'T%'; 23create database `inf%`; 24create database mbase; 25use `inf%`; 26show tables; 27 28# 29# Bug#18113 SELECT * FROM information_schema.xxx crashes server 30# Bug#17204 second CALL to procedure crashes Server 31# Crash happened when one selected data from one of INFORMATION_SCHEMA 32# tables and in order to build its contents server had to open view which 33# used stored function and table or view on which one had not global or 34# database-level privileges (e.g. had only table-level or had no 35# privileges at all). 36# 37grant all privileges on `inf%`.* to 'mysqltest_1'@'localhost'; 38grant all privileges on `mbase`.* to 'mysqltest_1'@'localhost'; 39create table t1 (f1 int); 40delimiter |; 41create function func1(curr_int int) returns int 42begin 43 declare ret_val int; 44 select max(f1) from t1 into ret_val; 45 return ret_val; 46end| 47delimiter ;| 48create view v1 as select f1 from t1 where f1 = func1(f1); 49create function func2() returns int return 1; 50 51use mbase; 52delimiter |; 53create procedure p1 () 54begin 55select table_name from information_schema.key_column_usage 56order by table_name; 57end| 58delimiter ;| 59 60create table t1 61(f1 int(10) unsigned not null, 62 f2 varchar(100) not null, 63 primary key (f1), unique key (f2)); 64 65connect (user1,localhost,mysqltest_1,,); 66connection user1; 67--disable_result_log 68select * from information_schema.tables; 69call mbase.p1(); 70call mbase.p1(); 71call mbase.p1(); 72--enable_result_log 73 74connection default; 75use `inf%`; 76drop user mysqltest_1@localhost; 77drop table t1; 78select table_name, table_type, table_comment from information_schema.tables 79where table_schema='inf%' and func2(); 80select table_name, table_type, table_comment from information_schema.tables 81where table_schema='inf%' and func2(); 82drop view v1; 83drop function func1; 84drop function func2; 85 86drop database `inf%`; 87drop procedure mbase.p1; 88drop database mbase; 89disconnect user1; 90 91# 92# Bug#18282 INFORMATION_SCHEMA.TABLES provides inconsistent info about invalid views 93# 94use test; 95create table t1 (i int); 96create function f1 () returns int return (select max(i) from t1); 97create view v1 as select f1(); 98create table t2 (id int); 99create function f2 () returns int return (select max(i) from t2); 100create view v2 as select f2(); 101drop table t2; 102select table_name, table_type, table_comment from information_schema.tables 103where table_schema='test' order by table_name; 104drop table t1; 105select table_name, table_type, table_comment from information_schema.tables 106where table_schema='test' order by table_name; 107drop function f1; 108drop function f2; 109drop view v1, v2; 110 111# 112# Bug#20543 select on information_schema strange warnings, view, different 113# schemas/users 114# 115# 116create database testdb_1; 117create user testdb_1@localhost; 118grant all on testdb_1.* to testdb_1@localhost with grant option; 119 120create user testdb_2@localhost; 121grant all on test.* to testdb_2@localhost with grant option; 122 123connect (testdb_1,localhost,testdb_1,,test); 124use testdb_1; 125create table t1 (f1 char(4)); 126create view v1 as select f1 from t1; 127grant insert on v1 to testdb_2@localhost; 128 129create view v5 as select f1 from t1; 130grant select, show view on v5 to testdb_2@localhost; 131 132--error ER_SPECIFIC_ACCESS_DENIED_ERROR 133create definer=`no_such_user`@`no_such_host` view v6 as select f1 from t1; 134 135connection default; 136use testdb_1; 137create view v6 as select f1 from t1; 138grant select, show view on v6 to testdb_2@localhost; 139 140create table t2 (f1 char(4)); 141create definer=`no_such_user`@`no_such_host` view v7 as select * from t2; 142 143show fields from testdb_1.v6; 144show create view testdb_1.v6; 145 146show create view testdb_1.v7; 147show fields from testdb_1.v7; 148 149connection testdb_1; 150 151create table t3 (f1 char(4), f2 char(4)); 152create view v3 as select f1,f2 from t3; 153grant insert(f1), insert(f2) on v3 to testdb_2@localhost; 154 155connect (testdb_2,localhost,testdb_2,,test); 156create view v2 as select f1 from testdb_1.v1; 157create view v4 as select f1,f2 from testdb_1.v3; 158 159show fields from testdb_1.v5; 160show create view testdb_1.v5; 161 162show fields from testdb_1.v6; 163show create view testdb_1.v6; 164 165connection testdb_1; 166show fields from testdb_1.v7; 167show create view testdb_1.v7; 168 169revoke insert(f1) on v3 from testdb_2@localhost; 170revoke select,show view on v5 from testdb_2@localhost; 171connection default; 172use testdb_1; 173revoke select,show view on v6 from testdb_2@localhost; 174connection testdb_2; 175 176--error ER_TABLEACCESS_DENIED_ERROR 177show fields from testdb_1.v5; 178--error ER_TABLEACCESS_DENIED_ERROR 179show create view testdb_1.v5; 180 181--error ER_TABLEACCESS_DENIED_ERROR 182show fields from testdb_1.v6; 183--error ER_TABLEACCESS_DENIED_ERROR 184show create view testdb_1.v6; 185 186--error ER_TABLEACCESS_DENIED_ERROR 187show fields from testdb_1.v7; 188--error ER_TABLEACCESS_DENIED_ERROR 189show create view testdb_1.v7; 190 191show create view v4; 192#--error ER_VIEW_NO_EXPLAIN 193show fields from v4; 194 195show fields from v2; 196show fields from testdb_1.v1; 197show create view v2; 198--error ER_TABLEACCESS_DENIED_ERROR 199show create view testdb_1.v1; 200 201select table_name from information_schema.columns a 202where a.table_name = 'v2'; 203select view_definition from information_schema.views a 204where a.table_name = 'v2'; 205select view_definition from information_schema.views a 206where a.table_name = 'testdb_1.v1'; 207 208--error ER_VIEW_INVALID 209select * from v2; 210 211connection default; 212use test; 213drop view testdb_1.v1, v2, testdb_1.v3, v4; 214drop database testdb_1; 215connection testdb_1; 216disconnect testdb_1; 217--source include/wait_until_disconnected.inc 218connection testdb_2; 219disconnect testdb_2; 220--source include/wait_until_disconnected.inc 221connection default; 222drop user testdb_1@localhost; 223drop user testdb_2@localhost; 224 225# 226# Bug#22763 Disrepancy between SHOW CREATE VIEW and I_S.VIEWS 227# 228create database testdb_1; 229create table testdb_1.t1 (a int); 230create view testdb_1.v1 as select * from testdb_1.t1; 231 232grant show view on testdb_1.* to mysqltest_1@localhost; 233grant select on testdb_1.v1 to mysqltest_1@localhost; 234 235connect (user1,localhost,mysqltest_1,,test); 236connection user1; 237select table_schema, table_name, view_definition from information_schema.views 238where table_name='v1'; 239show create view testdb_1.v1; 240 241connection default; 242revoke select on testdb_1.v1 from mysqltest_1@localhost; 243connection user1; 244select table_schema, table_name, view_definition from information_schema.views 245where table_name='v1'; 246--error ER_TABLEACCESS_DENIED_ERROR 247show create view testdb_1.v1; 248 249connection default; 250drop user mysqltest_1@localhost; 251drop database testdb_1; 252connection user1; 253disconnect user1; 254--source include/wait_until_disconnected.inc 255connection default; 256 257set global sql_mode=default; 258 259# 260# MDEV-20549 SQL SECURITY DEFINER does not work for INFORMATION_SCHEMA tables 261# 262 263create user foo@localhost; 264grant select on test.* to foo@localhost; 265create procedure rootonly() select 1; 266create sql security definer view v1d as select current_user(),user from information_schema.processlist; 267create sql security invoker view v1i as select current_user(),user from information_schema.processlist; 268create sql security definer view v2d as select table_name from information_schema.tables where table_schema='mysql' and table_name like '%user%'; 269create sql security invoker view v2i as select table_name from information_schema.tables where table_schema='mysql' and table_name like '%user%'; 270create sql security definer view v3d as select schema_name from information_schema.schemata where schema_name like '%mysql%'; 271create sql security invoker view v3i as select schema_name from information_schema.schemata where schema_name like '%mysql%'; 272create sql security definer view v4d as select routine_name from information_schema.routines where routine_schema='test'; 273create sql security invoker view v4i as select routine_name from information_schema.routines where routine_schema='test'; 274create sql security definer view v5d as select view_definition > '' from information_schema.views where table_name='v1d'; 275create sql security invoker view v5i as select view_definition > '' from information_schema.views where table_name='v1d'; 276connect foo,localhost,foo; 277select * from v1d; 278select * from v1i; 279select * from v2d; 280select * from v2i; 281select * from v3d; 282select * from v3i; 283select * from v4d; 284select * from v4i; 285select * from v5d; 286select * from v5i; 287connection default; 288select * from v1d; 289select * from v1i; 290select * from v2d; 291select * from v2i; 292select * from v3d; 293select * from v3i; 294select * from v4d; 295select * from v4i; 296select * from v5d; 297select * from v5i; 298disconnect foo; 299drop view v1d, v1i, v2d, v2i, v3d, v3i, v4d, v4i, v5d, v5i; 300drop user foo@localhost; 301drop procedure rootonly; 302