Saturday, November 21, 2015

Default users in MySQL 5.7

Among the many New features introduced by MySQL 5.7, we can notice a strong trend towards improving the server security by default. Two features stand out in this respect:

  • A password-less root is no longer the default for new installations. Unless you say otherwise, the default installers mysqld --initialize and the deprecated mysql_install_db will generate a random password which the user needs to change.
  • The anonymous accounts are no longer created by default. When you start MySQL, you only get the root user (and a new one: read on).

The above features are a great advance not only for security but also for usability. The anonymous users were a continuous source of mismatched connections, with difficult to explain errors, and confusion for beginners and experts alike. That's why MySQL-Sandbox has removed the anonymous accounts since its first release.

There are, however, two more changes in the privileges tables:

  1. We have a new user, mysql.sys, which is not a usable account, as it comes with a deliberately invalid password. Its role is only to allow the sys objects to have an owner different from root. This novelty has caught me by surprise, and I had to adjust the management of users in MySQL-Sandbox, to prevent removal of this account attributes from various mysql tables: user, db, tables_priv. In fact, since we don't have anonymous accounts anymore, MySQL-Sandbox does not remove rows from db and tables_priv. (The changes were apparently new for the team maintaining MySQL images on Docker, as this user is not available on Docker MySQL containers: the entrypoint file removes all accounts from the user file.)
  2. The test database is removed by default. This is, in my opinion, a mistake. The reason for the vulnerability of the test database was that it was open to use for the anonymous users. But since we don't have anonymous users anymore, deleting the test database is like obeying a superstitious belief.

Anyway, MySQL-Sandbox 3.1.02 comes with a few small bug fixes, among which is the preservation of the mysql.sys user and a few adjustments to the tests to take into account the latest change. The test database is always present in sandboxes, despite the above mentioned irrational removal.

Let me demonstrate the issue. In a brand new installation, we create an anonymous user and the test database:

mysql> create user '';
Query OK, 0 rows affected (0.02 sec)

mysql> select host,user from user;
| host | user |
| %    |      |
| %    | root |
2 rows in set (0.00 sec)

mysql> create schema test;
Query OK, 1 row affected (0.01 sec)

Then we try to access the server

# mysql -user=''
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.9-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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

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

mysql> use test
ERROR 1044 (42000): Access denied for user ''@'%' to database 'test'
mysql> use performance_schema
ERROR 1044 (42000): Access denied for user ''@'%' to database 'performance_schema'
mysql> use sys;
ERROR 1044 (42000): Access denied for user ''@'%' to database 'sys'

mysql> show schemas;
| Database           |
| information_schema |
1 row in set (0.00 sec)

mysql> select table_schema,engine,count(*) from information_schema.tables group by table_schema,engine;
| table_schema       | engine | count(*) |
| information_schema | InnoDB |       10 |
| information_schema | MEMORY |       51 |
2 rows in set (0.00 sec)

mysql> show grants for '';
| Grants for @%                |
| GRANT USAGE ON *.* TO ''@'%' |
1 row in set (0.00 sec)

So, the anonymous user is unable to do harm, as it can't even see the databases. The only effective measure was cleaning up the table mysql.db, which was the one giving access to the test database to the anonymous users.


Todd Farmer said...

Hi Giuseppe! I'm glad you're seeing the investment made in making MySQL Server 5.7 secure by default. I want to comment on the two changes you listed - the mysql.sys user account and the removal of the test database.

When sys was first released, it relied on root@localhost privileges. That's not a good security practice, and sys needs only a subset of privileges which root usually has. It wasn't caught until late in the release cycle, but I think it was a very important and appropriate change to make. It seems the lateness of the change caused problems (such as you note with Docker), and those need to be fixed. An ideal scenario would have identified and fixed the issue much earlier, but it wasn't noticed (AFAIK) until very late in the process.

The removal of the test database is a checkbox-type change, and the basic requirement is that we provide a minimal installation package which limits exposure via unnecessary scripts and database objects. Having a default installation which includes a database named "test" raises immediate questions, and while removal of anonymous accounts may have mitigated the impact of such a database, it's far easier to to avoid creating the test database generally than to force DBAs to explain to management why a related security finding isn't really important.

Somethings are done to make MySQL more secure, some are done to make it easier for DBAs to prove that MySQL is more secure. Removing the test database generally falls in the latter category, especially when considered in the context of the other changes you cite.

Giuseppe Maxia said...

Hi @Todd,
Thanks for your comments.
I understand the reason for the removal of the test database although I don't agree on the action. Since there are many users who are still deploying MySQL 5.5 and 5.6, they may see this change and think (erroneously) that removing the test database would remove the vulnerability. Then they naively create a database named "test_bank" and they get the same vulnerability as before.
The removal of anonymous users from both tables (user and db) is the real security measure that should be emphasized. Removing the test database after closing the door to anonymous user feels like a voodoo move.

This debate is probably exaggerated on both sides. I like what the MySQL is doing. Keep up the good job!
From my side, I want to make sure that users of earlier versions understand the real risks in their servers.

Vote on Planet MySQL