mirror of
https://github.com/MariaDB/server.git
synced 2025-01-16 03:52:35 +01:00
387 lines
14 KiB
Text
387 lines
14 KiB
Text
set local sql_mode="";
|
|
set global sql_mode="";
|
|
show tables from INFORMATION_SCHEMA like 'T%';
|
|
Tables_in_information_schema (T%)
|
|
TABLES
|
|
TABLESPACES
|
|
TABLE_CONSTRAINTS
|
|
TABLE_PRIVILEGES
|
|
TABLE_STATISTICS
|
|
TRIGGERS
|
|
create database `inf%`;
|
|
create database mbase;
|
|
use `inf%`;
|
|
show tables;
|
|
Tables_in_inf%
|
|
#
|
|
# Bug#18113 SELECT * FROM information_schema.xxx crashes server
|
|
# Bug#17204 second CALL to procedure crashes Server
|
|
#
|
|
grant all privileges on `inf%`.* to 'mysqltest_1'@'localhost';
|
|
grant all privileges on `mbase`.* to 'mysqltest_1'@'localhost';
|
|
create table t1 (f1 int);
|
|
create function func1(curr_int int) returns int
|
|
begin
|
|
declare ret_val int;
|
|
select max(f1) from t1 into ret_val;
|
|
return ret_val;
|
|
end|
|
|
Warnings:
|
|
Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
|
|
create view v1 as select f1 from t1 where f1 = func1(f1);
|
|
create function func2() returns int return 1;
|
|
use mbase;
|
|
create procedure p1 ()
|
|
begin
|
|
select table_name from information_schema.key_column_usage
|
|
order by table_name;
|
|
end|
|
|
create table t1
|
|
(f1 int(10) unsigned not null,
|
|
f2 varchar(100) not null,
|
|
primary key (f1), unique key (f2));
|
|
connect user1,localhost,mysqltest_1,,;
|
|
connection user1;
|
|
select * from information_schema.tables;
|
|
call mbase.p1();
|
|
call mbase.p1();
|
|
call mbase.p1();
|
|
connection default;
|
|
use `inf%`;
|
|
drop user mysqltest_1@localhost;
|
|
drop table t1;
|
|
select table_name, table_type, table_comment from information_schema.tables
|
|
where table_schema='inf%' and func2();
|
|
table_name table_type table_comment
|
|
v1 VIEW VIEW
|
|
select table_name, table_type, table_comment from information_schema.tables
|
|
where table_schema='inf%' and func2();
|
|
table_name table_type table_comment
|
|
v1 VIEW VIEW
|
|
drop view v1;
|
|
drop function func1;
|
|
drop function func2;
|
|
drop database `inf%`;
|
|
drop procedure mbase.p1;
|
|
drop database mbase;
|
|
disconnect user1;
|
|
#
|
|
# Bug#18282 INFORMATION_SCHEMA.TABLES provides inconsistent info about invalid views
|
|
#
|
|
use test;
|
|
create table t1 (i int);
|
|
create function f1 () returns int return (select max(i) from t1);
|
|
create view v1 as select f1();
|
|
create table t2 (id int);
|
|
create function f2 () returns int return (select max(i) from t2);
|
|
create view v2 as select f2();
|
|
drop table t2;
|
|
select table_name, table_type, table_comment from information_schema.tables
|
|
where table_schema='test' order by table_name;
|
|
table_name table_type table_comment
|
|
t1 BASE TABLE
|
|
v1 VIEW VIEW
|
|
v2 VIEW VIEW
|
|
drop table t1;
|
|
select table_name, table_type, table_comment from information_schema.tables
|
|
where table_schema='test' order by table_name;
|
|
table_name table_type table_comment
|
|
v1 VIEW VIEW
|
|
v2 VIEW VIEW
|
|
drop function f1;
|
|
drop function f2;
|
|
drop view v1, v2;
|
|
#
|
|
# Bug#20543 select on information_schema strange warnings, view, different
|
|
# schemas/users
|
|
#
|
|
create database testdb_1;
|
|
create user testdb_1@localhost;
|
|
grant all on testdb_1.* to testdb_1@localhost with grant option;
|
|
create user testdb_2@localhost;
|
|
grant all on test.* to testdb_2@localhost with grant option;
|
|
connect testdb_1,localhost,testdb_1,,test;
|
|
use testdb_1;
|
|
create table t1 (f1 char(4));
|
|
create view v1 as select f1 from t1;
|
|
grant insert on v1 to testdb_2@localhost;
|
|
create view v5 as select f1 from t1;
|
|
grant select, show view on v5 to testdb_2@localhost;
|
|
create definer=`no_such_user`@`no_such_host` view v6 as select f1 from t1;
|
|
ERROR 42000: Access denied; you need (at least one of) the SUPER, SET USER privilege(s) for this operation
|
|
connection default;
|
|
use testdb_1;
|
|
create view v6 as select f1 from t1;
|
|
grant select, show view on v6 to testdb_2@localhost;
|
|
create table t2 (f1 char(4));
|
|
create definer=`no_such_user`@`no_such_host` view v7 as select * from t2;
|
|
Warnings:
|
|
Note 1449 The user specified as a definer ('no_such_user'@'no_such_host') does not exist
|
|
show fields from testdb_1.v6;
|
|
Field Type Null Key Default Extra
|
|
f1 char(4) YES NULL
|
|
show create view testdb_1.v6;
|
|
View Create View character_set_client collation_connection
|
|
v6 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v6` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci
|
|
show create view testdb_1.v7;
|
|
View Create View character_set_client collation_connection
|
|
v7 CREATE ALGORITHM=UNDEFINED DEFINER=`no_such_user`@`no_such_host` SQL SECURITY DEFINER VIEW `v7` AS select `testdb_1`.`t2`.`f1` AS `f1` from `t2` latin1 latin1_swedish_ci
|
|
Warnings:
|
|
Note 1449 The user specified as a definer ('no_such_user'@'no_such_host') does not exist
|
|
show fields from testdb_1.v7;
|
|
Field Type Null Key Default Extra
|
|
f1 char(4) YES NULL
|
|
Warnings:
|
|
Note 1449 The user specified as a definer ('no_such_user'@'no_such_host') does not exist
|
|
connection testdb_1;
|
|
create table t3 (f1 char(4), f2 char(4));
|
|
create view v3 as select f1,f2 from t3;
|
|
grant insert(f1), insert(f2) on v3 to testdb_2@localhost;
|
|
connect testdb_2,localhost,testdb_2,,test;
|
|
create view v2 as select f1 from testdb_1.v1;
|
|
create view v4 as select f1,f2 from testdb_1.v3;
|
|
show fields from testdb_1.v5;
|
|
Field Type Null Key Default Extra
|
|
f1 char(4) YES NULL
|
|
show create view testdb_1.v5;
|
|
View Create View character_set_client collation_connection
|
|
v5 CREATE ALGORITHM=UNDEFINED DEFINER=`testdb_1`@`localhost` SQL SECURITY DEFINER VIEW `testdb_1`.`v5` AS select `testdb_1`.`t1`.`f1` AS `f1` from `testdb_1`.`t1` latin1 latin1_swedish_ci
|
|
show fields from testdb_1.v6;
|
|
Field Type Null Key Default Extra
|
|
f1 char(4) YES NULL
|
|
show create view testdb_1.v6;
|
|
View Create View character_set_client collation_connection
|
|
v6 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `testdb_1`.`v6` AS select `testdb_1`.`t1`.`f1` AS `f1` from `testdb_1`.`t1` latin1 latin1_swedish_ci
|
|
connection testdb_1;
|
|
show fields from testdb_1.v7;
|
|
Field Type Null Key Default Extra
|
|
f1 char(4) YES NULL
|
|
Warnings:
|
|
Note 1449 The user specified as a definer ('no_such_user'@'no_such_host') does not exist
|
|
show create view testdb_1.v7;
|
|
View Create View character_set_client collation_connection
|
|
v7 CREATE ALGORITHM=UNDEFINED DEFINER=`no_such_user`@`no_such_host` SQL SECURITY DEFINER VIEW `v7` AS select `testdb_1`.`t2`.`f1` AS `f1` from `t2` latin1 latin1_swedish_ci
|
|
Warnings:
|
|
Note 1449 The user specified as a definer ('no_such_user'@'no_such_host') does not exist
|
|
revoke insert(f1) on v3 from testdb_2@localhost;
|
|
revoke select,show view on v5 from testdb_2@localhost;
|
|
connection default;
|
|
use testdb_1;
|
|
revoke select,show view on v6 from testdb_2@localhost;
|
|
connection testdb_2;
|
|
show fields from testdb_1.v5;
|
|
ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table `testdb_1`.`v5`
|
|
show create view testdb_1.v5;
|
|
ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table `testdb_1`.`v5`
|
|
show fields from testdb_1.v6;
|
|
ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table `testdb_1`.`v6`
|
|
show create view testdb_1.v6;
|
|
ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table `testdb_1`.`v6`
|
|
show fields from testdb_1.v7;
|
|
ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table `testdb_1`.`v7`
|
|
show create view testdb_1.v7;
|
|
ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table `testdb_1`.`v7`
|
|
show create view v4;
|
|
View Create View character_set_client collation_connection
|
|
v4 CREATE ALGORITHM=UNDEFINED DEFINER=`testdb_2`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS select `v3`.`f1` AS `f1`,`v3`.`f2` AS `f2` from `testdb_1`.`v3` latin1 latin1_swedish_ci
|
|
show fields from v4;
|
|
Field Type Null Key Default Extra
|
|
f1 char(4) YES NULL
|
|
f2 char(4) YES NULL
|
|
show fields from v2;
|
|
Field Type Null Key Default Extra
|
|
f1 char(4) YES NULL
|
|
show fields from testdb_1.v1;
|
|
Field Type Null Key Default Extra
|
|
f1 char(4) YES NULL
|
|
show create view v2;
|
|
View Create View character_set_client collation_connection
|
|
v2 CREATE ALGORITHM=UNDEFINED DEFINER=`testdb_2`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `v1`.`f1` AS `f1` from `testdb_1`.`v1` latin1 latin1_swedish_ci
|
|
show create view testdb_1.v1;
|
|
ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table `testdb_1`.`v1`
|
|
select table_name from information_schema.columns a
|
|
where a.table_name = 'v2';
|
|
table_name
|
|
v2
|
|
select view_definition from information_schema.views a
|
|
where a.table_name = 'v2';
|
|
view_definition
|
|
select `v1`.`f1` AS `f1` from `testdb_1`.`v1`
|
|
select view_definition from information_schema.views a
|
|
where a.table_name = 'testdb_1.v1';
|
|
view_definition
|
|
select * from v2;
|
|
ERROR HY000: View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
|
|
connection default;
|
|
use test;
|
|
drop view testdb_1.v1, v2, testdb_1.v3, v4;
|
|
drop database testdb_1;
|
|
connection testdb_1;
|
|
disconnect testdb_1;
|
|
connection testdb_2;
|
|
disconnect testdb_2;
|
|
connection default;
|
|
drop user testdb_1@localhost;
|
|
drop user testdb_2@localhost;
|
|
#
|
|
# Bug#22763 Disrepancy between SHOW CREATE VIEW and I_S.VIEWS
|
|
#
|
|
create database testdb_1;
|
|
create table testdb_1.t1 (a int);
|
|
create view testdb_1.v1 as select * from testdb_1.t1;
|
|
grant show view on testdb_1.* to mysqltest_1@localhost;
|
|
grant select on testdb_1.v1 to mysqltest_1@localhost;
|
|
connect user1,localhost,mysqltest_1,,test;
|
|
connection user1;
|
|
select table_schema, table_name, view_definition from information_schema.views
|
|
where table_name='v1';
|
|
table_schema table_name view_definition
|
|
testdb_1 v1 select `testdb_1`.`t1`.`a` AS `a` from `testdb_1`.`t1`
|
|
show create view testdb_1.v1;
|
|
View Create View character_set_client collation_connection
|
|
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `testdb_1`.`v1` AS select `testdb_1`.`t1`.`a` AS `a` from `testdb_1`.`t1` latin1 latin1_swedish_ci
|
|
connection default;
|
|
revoke select on testdb_1.v1 from mysqltest_1@localhost;
|
|
connection user1;
|
|
select table_schema, table_name, view_definition from information_schema.views
|
|
where table_name='v1';
|
|
table_schema table_name view_definition
|
|
testdb_1 v1
|
|
show create view testdb_1.v1;
|
|
ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table `testdb_1`.`v1`
|
|
connection default;
|
|
drop user mysqltest_1@localhost;
|
|
drop database testdb_1;
|
|
connection user1;
|
|
disconnect user1;
|
|
connection default;
|
|
set global sql_mode=default;
|
|
#
|
|
# MDEV-20549 SQL SECURITY DEFINER does not work for INFORMATION_SCHEMA tables
|
|
#
|
|
create user foo@localhost;
|
|
grant select on test.* to foo@localhost;
|
|
create procedure rootonly() select 1;
|
|
create sql security definer view v1d as select current_user(),user from information_schema.processlist where command!='daemon';
|
|
create sql security invoker view v1i as select current_user(),user from information_schema.processlist where command!='daemon';
|
|
create sql security definer view v2d as select table_name from information_schema.tables where table_schema='mysql' and table_name like '%user%';
|
|
create sql security invoker view v2i as select table_name from information_schema.tables where table_schema='mysql' and table_name like '%user%';
|
|
create sql security definer view v3d as select schema_name from information_schema.schemata where schema_name like '%mysql%';
|
|
create sql security invoker view v3i as select schema_name from information_schema.schemata where schema_name like '%mysql%';
|
|
create sql security definer view v4d as select routine_name from information_schema.routines where routine_schema='test';
|
|
create sql security invoker view v4i as select routine_name from information_schema.routines where routine_schema='test';
|
|
create sql security definer view v5d as select view_definition > '' from information_schema.views where table_name='v1d';
|
|
create sql security invoker view v5i as select view_definition > '' from information_schema.views where table_name='v1d';
|
|
connect foo,localhost,foo;
|
|
select * from v1d;
|
|
current_user() user
|
|
root@localhost root
|
|
root@localhost root
|
|
select * from v1i;
|
|
current_user() user
|
|
foo@localhost foo
|
|
select * from v2d;
|
|
table_name
|
|
user
|
|
select * from v2i;
|
|
table_name
|
|
select * from v3d;
|
|
schema_name
|
|
mysql
|
|
select * from v3i;
|
|
schema_name
|
|
select * from v4d;
|
|
routine_name
|
|
rootonly
|
|
select * from v4i;
|
|
routine_name
|
|
select * from v5d;
|
|
view_definition > ''
|
|
1
|
|
select * from v5i;
|
|
view_definition > ''
|
|
0
|
|
connection default;
|
|
select * from v1d;
|
|
current_user() user
|
|
root@localhost foo
|
|
root@localhost root
|
|
select * from v1i;
|
|
current_user() user
|
|
root@localhost foo
|
|
root@localhost root
|
|
select * from v2d;
|
|
table_name
|
|
user
|
|
select * from v2i;
|
|
table_name
|
|
user
|
|
select * from v3d;
|
|
schema_name
|
|
mysql
|
|
select * from v3i;
|
|
schema_name
|
|
mysql
|
|
select * from v4d;
|
|
routine_name
|
|
rootonly
|
|
select * from v4i;
|
|
routine_name
|
|
rootonly
|
|
select * from v5d;
|
|
view_definition > ''
|
|
1
|
|
select * from v5i;
|
|
view_definition > ''
|
|
1
|
|
disconnect foo;
|
|
drop view v1d, v1i, v2d, v2i, v3d, v3i, v4d, v4i, v5d, v5i;
|
|
drop user foo@localhost;
|
|
drop procedure rootonly;
|
|
#
|
|
# End of 10.2 tests
|
|
#
|
|
#
|
|
# MDEV-32500 Information schema leaks table names and structure to unauthorized users
|
|
#
|
|
create database db;
|
|
create table db.t1 (x int, key(x)) engine=InnoDB;
|
|
create table db.t2 (a int, b int, c int, unique(b), check(c>b), foreign key(c) references db.t1(x)) engine=InnoDB;
|
|
create table db.t3 (d int, e int, f int, unique(e), check(f>e), foreign key(f) references db.t1(x),
|
|
foreign key(e) references db.t2(b),
|
|
foreign key(d) references db.t3(f)
|
|
) engine=InnoDB;
|
|
create user u@localhost;
|
|
grant select (a) on db.t2 to u@localhost;
|
|
grant update (d) on db.t3 to u@localhost;
|
|
connect con1,localhost,u,,db;
|
|
select table_name, column_name from information_schema.columns where table_name like 't_';
|
|
table_name column_name
|
|
t2 a
|
|
t3 d
|
|
select table_name, column_name from information_schema.key_column_usage where table_name like 't_';
|
|
table_name column_name
|
|
select table_name, unique_constraint_name, referenced_table_name from information_schema.referential_constraints where table_name like 't_';
|
|
table_name unique_constraint_name referenced_table_name
|
|
t3 x NULL
|
|
t3 b NULL
|
|
t3 f t3
|
|
select table_name, constraint_name, constraint_type from information_schema.table_constraints where table_name like 't_';
|
|
table_name constraint_name constraint_type
|
|
t3 e UNIQUE
|
|
t3 CONSTRAINT_1 CHECK
|
|
t3 t3_ibfk_1 FOREIGN KEY
|
|
t3 t3_ibfk_2 FOREIGN KEY
|
|
t3 t3_ibfk_3 FOREIGN KEY
|
|
show index in t2;
|
|
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
|
|
show index in t3;
|
|
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
|
|
t3 1 d 1 d A 0 NULL NULL YES BTREE NO
|
|
disconnect con1;
|
|
connection default;
|
|
drop user u@localhost;
|
|
drop database db;
|
|
#
|
|
# End of 10.4 tests
|
|
#
|