"test" and "test\_%" in information_schema.SCHEMA_PRIVILEGES table

[mikeg0]

I'm using mysql:5.6 (from https://hub.docker.com/_/mysql/) and have noticed orphaned "test" records in the information_schema.SCHEMA_PRIVILEGES. Is this normal? I think it's messing up execute privileges for a user I've created in a database named test_con. Note ''@'%' on "test\_%" doesn't have execute privileges, but I've granted 'cc_user'@'%' on "test_con" execute privileges.

mysql> select get_text_codes(1,1,34,3);
ERROR 1370 (42000): execute command denied to user 'cc_user'@'%' for routine 'test_con.get_test_codes'
mysql> show grants for cc_user;
+-----------------------------------------------------------------------------+
| Grants for cc_user@%                                                        |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'cc_user'@'%' IDENTIFIED BY PASSWORD <secret>         |
| GRANT ALL PRIVILEGES ON `test_con`.* TO 'cc_user'@'%' WITH GRANT OPTION |
+-----------------------------------------------------------------------------+

[tianon]

It would appear that this is indeed normal:

$ docker pull mysql:5.6
5.6: Pulling from library/mysql
Digest: sha256:2e48836690b8416e4890c369aa174fc1f73c125363d94d99cfd08115f4513ec9
Status: Image is up to date for mysql:5.6

$ docker run -dit --name test -e MYSQL_ROOT_PASSWORD=example mysql:5.6
08481698b5baf0258e0d23f305086f04235bda300cf3e3d2584dd76f705de05d

$ docker logs --tail=2 test
2018-08-17 19:46:40 1 [Note] mysqld: ready for connections.
Version: '5.6.41'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server (GPL)

$ docker exec -it test mysql -uroot -pexample information_schema
Warning: Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.41 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT * FROM SCHEMA_PRIVILEGES;
+---------+---------------+--------------+-------------------------+--------------+
| GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE          | IS_GRANTABLE |
+---------+---------------+--------------+-------------------------+--------------+
| ''@'%'  | def           | test         | SELECT                  | NO           |
| ''@'%'  | def           | test         | INSERT                  | NO           |
| ''@'%'  | def           | test         | UPDATE                  | NO           |
| ''@'%'  | def           | test         | DELETE                  | NO           |
| ''@'%'  | def           | test         | CREATE                  | NO           |
| ''@'%'  | def           | test         | DROP                    | NO           |
| ''@'%'  | def           | test         | REFERENCES              | NO           |
| ''@'%'  | def           | test         | INDEX                   | NO           |
| ''@'%'  | def           | test         | ALTER                   | NO           |
| ''@'%'  | def           | test         | CREATE TEMPORARY TABLES | NO           |
| ''@'%'  | def           | test         | LOCK TABLES             | NO           |
| ''@'%'  | def           | test         | CREATE VIEW             | NO           |
| ''@'%'  | def           | test         | SHOW VIEW               | NO           |
| ''@'%'  | def           | test         | CREATE ROUTINE          | NO           |
| ''@'%'  | def           | test         | EVENT                   | NO           |
| ''@'%'  | def           | test         | TRIGGER                 | NO           |
| ''@'%'  | def           | test\_%      | SELECT                  | NO           |
| ''@'%'  | def           | test\_%      | INSERT                  | NO           |
| ''@'%'  | def           | test\_%      | UPDATE                  | NO           |
| ''@'%'  | def           | test\_%      | DELETE                  | NO           |
| ''@'%'  | def           | test\_%      | CREATE                  | NO           |
| ''@'%'  | def           | test\_%      | DROP                    | NO           |
| ''@'%'  | def           | test\_%      | REFERENCES              | NO           |
| ''@'%'  | def           | test\_%      | INDEX                   | NO           |
| ''@'%'  | def           | test\_%      | ALTER                   | NO           |
| ''@'%'  | def           | test\_%      | CREATE TEMPORARY TABLES | NO           |
| ''@'%'  | def           | test\_%      | LOCK TABLES             | NO           |
| ''@'%'  | def           | test\_%      | CREATE VIEW             | NO           |
| ''@'%'  | def           | test\_%      | SHOW VIEW               | NO           |
| ''@'%'  | def           | test\_%      | CREATE ROUTINE          | NO           |
| ''@'%'  | def           | test\_%      | EVENT                   | NO           |
| ''@'%'  | def           | test\_%      | TRIGGER                 | NO           |
+---------+---------------+--------------+-------------------------+--------------+
32 rows in set (0.00 sec)

It could be related to the test database that MySQL creates by default, which we remove:

mysql/5.6/docker-entrypoint.sh

Line 162 in b39f1e5

DROP DATABASE IF EXISTS test ;

[mikeg0]

Just came across this post ...
https://dba.stackexchange.com/questions/13361/mysql-why-are-there-test-entries-in-mysql-db

And I just confirmed running mysql_secure_installation will remove the test databases and orphan records from information_schema ...
https://dev.mysql.com/doc/refman/5.5/en/mysql-secure-installation.html

Would it be possible to add an option to run mysql_secure_installation somewhere in the Dockerfile or docker-entrypoint.sh?

[tianon]

Running mysql_secure_installation directly is definitely not something we can do ATM given that it wants to do more than simply removing the "test" example contents (last I looked, it was also updating configuration, etc). What we should definitely do however, is look at mysql_secure_installation and ensure we're doing all the same things (which will probably resolve this issue).

It looks like https://github.com/mysql/mysql-server/blob/b93c1661d689c8b7decc7563ba15f6ed140a4eb6/client/mysql_secure_installation.cc#L726-L749 is the relevant bit of code, especially that second query:

    execute_query_with_message((const char *) "DELETE FROM mysql.db WHERE "
	                                      "Db='test' OR Db='test\\_%'",
			       (const char *) " - Removing privileges on test "
			                      "database...\n");

I'll have a PR shortly!

[ltangvald]

Note that this won't have any effect for 5.7 and 8.0. All mysql_secure_installation does for those versions is enable the validate_password plugin, since 5.7+ is more secure by default.