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|
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,,"*NO-ONE*";
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,,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 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,,"*NO-ONE*";
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
#
#
# MDEV-23729 INFORMATION_SCHEMA Table info. about user locked due to
# max_password_errors
#
# MDEV-32218 message to notify end-user N-days prior the password get
# expired
#
set @old_max_password_errors=@@max_password_errors;
set global max_password_errors=2;
set timestamp= unix_timestamp('2020-01-02 2:3:4');
create user nice_user;
create user naughty_user identified by 'naughty_user_passwd';
alter user naughty_user password expire interval 10 day;
select * from information_schema.users;
USER	PASSWORD_ERRORS	PASSWORD_EXPIRATION_TIME
'mariadb.sys'@'localhost'	0	0000-00-00 00:00:00
'naughty_user'@'%'	0	2020-01-12 02:03:04
'nice_user'@'%'	0	NULL
'root'@'127.0.0.1'	NULL	NULL
'root'@'::1'	NULL	NULL
'root'@'localhost'	NULL	NULL
'root'@HOSTNAME	0	NULL
alter user nice_user password expire interval 10 day;
select * from information_schema.users;
USER	PASSWORD_ERRORS	PASSWORD_EXPIRATION_TIME
'mariadb.sys'@'localhost'	0	0000-00-00 00:00:00
'naughty_user'@'%'	0	2020-01-12 02:03:04
'nice_user'@'%'	0	2020-01-12 02:03:04
'root'@'127.0.0.1'	NULL	NULL
'root'@'::1'	NULL	NULL
'root'@'localhost'	NULL	NULL
'root'@HOSTNAME	0	NULL
connect(localhost,naughty_user,wrong_passwd,test,MASTER_PORT,MASTER_SOCKET);
connect con1, localhost, naughty_user, wrong_passwd;
ERROR 28000: Access denied for user 'naughty_user'@'localhost' (using password: YES)
select * from information_schema.users;
USER	PASSWORD_ERRORS	PASSWORD_EXPIRATION_TIME
'mariadb.sys'@'localhost'	0	0000-00-00 00:00:00
'naughty_user'@'%'	1	2020-01-12 02:03:04
'nice_user'@'%'	0	2020-01-12 02:03:04
'root'@'127.0.0.1'	NULL	NULL
'root'@'::1'	NULL	NULL
'root'@'localhost'	NULL	NULL
'root'@HOSTNAME	0	NULL
connect(localhost,naughty_user,wrong_passwd,test,MASTER_PORT,MASTER_SOCKET);
connect con1, localhost, naughty_user, wrong_passwd;
ERROR 28000: Access denied for user 'naughty_user'@'localhost' (using password: YES)
select * from information_schema.users;
USER	PASSWORD_ERRORS	PASSWORD_EXPIRATION_TIME
'mariadb.sys'@'localhost'	0	0000-00-00 00:00:00
'naughty_user'@'%'	2	2020-01-12 02:03:04
'nice_user'@'%'	0	2020-01-12 02:03:04
'root'@'127.0.0.1'	NULL	NULL
'root'@'::1'	NULL	NULL
'root'@'localhost'	NULL	NULL
'root'@HOSTNAME	0	NULL
# Show all users that are blocked due to max_password_errors reached.
select user from information_schema.users
where password_errors >= @@global.max_password_errors;
user
'naughty_user'@'%'
set global max_password_errors=3;
connect con1, localhost, naughty_user, naughty_user_passwd;
connection default;
select * from information_schema.users;
USER	PASSWORD_ERRORS	PASSWORD_EXPIRATION_TIME
'mariadb.sys'@'localhost'	0	0000-00-00 00:00:00
'naughty_user'@'%'	0	2020-01-12 02:03:04
'nice_user'@'%'	0	2020-01-12 02:03:04
'root'@'127.0.0.1'	NULL	NULL
'root'@'::1'	NULL	NULL
'root'@'localhost'	NULL	NULL
'root'@HOSTNAME	0	NULL
disconnect con1;
# test FLUSH PRIVILEGES
connect(localhost,naughty_user,wrong_passwd,test,MASTER_PORT,MASTER_SOCKET);
connect con1, localhost, naughty_user, wrong_passwd;
ERROR 28000: Access denied for user 'naughty_user'@'localhost' (using password: YES)
select * from information_schema.users where user like '''naughty%';
USER	PASSWORD_ERRORS	PASSWORD_EXPIRATION_TIME
'naughty_user'@'%'	1	2020-01-12 02:03:04
flush privileges;
select * from information_schema.users where user like '''naughty%';
USER	PASSWORD_ERRORS	PASSWORD_EXPIRATION_TIME
'naughty_user'@'%'	0	2020-01-12 02:03:04
# Test unprivileged output
connect con2, localhost, nice_user;
set timestamp= unix_timestamp('2020-01-02 2:3:4');
set password= password('nice_passwd');
select * from information_schema.users;
USER	PASSWORD_ERRORS	PASSWORD_EXPIRATION_TIME
'nice_user'@'%'	0	2020-01-12 02:03:04
# Delete user while some connection is still alive, then select.
connection default;
drop user nice_user;
connection con2;
select * from information_schema.users;
ERROR 0L000: The current user is invalid
disconnect con2;
connection default;
drop user naughty_user;
set global max_password_errors=@old_max_password_errors;
# more password expiration tests
set global default_password_lifetime= 2;
create user u1@localhost password expire;
create user u2@localhost password expire default;
create user u3@localhost password expire interval 10 day;
create user u4@localhost password expire interval 20 day;
create user u5@localhost password expire never;
set timestamp= unix_timestamp('2020-01-17 2:3:4');
select * from information_schema.users where user like '''u_''%';
USER	PASSWORD_ERRORS	PASSWORD_EXPIRATION_TIME
'u1'@'localhost'	0	0000-00-00 00:00:00
'u2'@'localhost'	0	2020-01-04 02:03:04
'u3'@'localhost'	0	2020-01-12 02:03:04
'u4'@'localhost'	0	2020-01-22 02:03:04
'u5'@'localhost'	0	NULL
set global default_password_lifetime= default;
select * from information_schema.users where user like '''u_''%';
USER	PASSWORD_ERRORS	PASSWORD_EXPIRATION_TIME
'u1'@'localhost'	0	0000-00-00 00:00:00
'u2'@'localhost'	0	NULL
'u3'@'localhost'	0	2020-01-12 02:03:04
'u4'@'localhost'	0	2020-01-22 02:03:04
'u5'@'localhost'	0	NULL
drop user u1@localhost;
drop user u2@localhost;
drop user u3@localhost;
drop user u4@localhost;
drop user u5@localhost;
# End of 10.0 tests