Sunday, June 05, 2016

MySQL 5.7 ghost users

Several months ago I reported on Default Users in MySQL 5.7. With the addition of the sys schema the server needs an extra user to secure operations. The problem with extra users is that, if you are not aware of their existence and why they are needed, you may easily mismanage them. For example, you may have a cleanup routine that you run when you use a new server, and the routine may have a command like

DELETE FROM mysql.user WHERE user != 'root';

This was good up to MySQL 5.6. Then the sys schema was added, and with it the user mysql.sys, which may cause errors if you try to re-create views in the sys schema.

The latest user sneaking below the radar is mysqlxsys. Like its predecessor, it comes well documented, and if only I had read all the manual I'd had known that this user must exist, or things won't work.

Noun bug 9935

Here is how they did not work for me:

$ make_sandbox 5.7.12 -- -c plugin-load=mysqlx=mysqlx.so --no_show
    The MySQL Sandbox,  version 3.1.05
    (C) 2006-2016 Giuseppe Maxia
loading grants
.. sandbox server started
Your sandbox server was installed in $HOME/sandboxes/msb_5_7_12

Check that the plugin is loaded

$ ~/sandboxes/msb_5_7_12/use -e "show plugins" |grep mysqlx
mysqlx    ACTIVE    DAEMON    mysqlx.so    GPL

Check that the port is open

$ sudo netstat -atn  |grep LISTEN|grep 33060
tcp4       0      0  *.33060                *.*                    LISTEN

Try a connection:

$ mysqlsh -h 127.0.0.1 -umsandbox -pmsandbox test
mysqlx: [Warning] Using a password on the command line interface can be insecure.
Creating an X Session to msandbox@127.0.0.1:33060/test
MySQL Error (1045): Unable to switch context to user mysqlxsys

This was my fault, of course. MySQL-Sandbox uses an aggressive cleanup routine, which was necessary up to MySQL 5.6, and less so in MySQL 5.7, but just for the sake of completeness it's still there. The routine removes all users that are neither root or mysql.sys. In this case the installation failed because I was removing an user that was needed.

I am not alone in this affair. Also the MySQL Docker image does the same thing.

A fix is ready for both these issues. MySQL-Sandbox 3.1.08 is already available.