Thursday, February 21, 2019

MySQL 8.0 allows unprivileged user access to its data if table mysql.user does not exists

Yesterday my Percona colleague Ceri Williams found a nasty bug in MySQL 8.0. Its twin for Percona Server reported at PS-5431.

He was installing MySQL Server 8.0 having not supported option in his configuration file. Thus initialization failed, but, surprisingly, the subsequent restart was successful and he was able to create, modify and delete tables in his MySQL installation. In other words: he got full access to the database. But he did not create any user account yet!

This new instance of MySQL Server did not have privilege tables, particularly mysql.user, and silently started with --skip-grant-option.

At the first glance starting just initialized MySQL Server with a non-safe option should not cause any harm, because datadir has no data yet. But this is what the experienced user expects! Newbies may miss the fact that their server is now accessible worldwide and start inserting data.

This is bad by itself, but we experienced more and found that the same behavior may happen if someone removes table mysql.user, then let MySQL Server read its privilege tables. E.g., performs a restart.

To demonstrate this behavior I created a table which holds credit card information and inserted data into it:

mysql> create table test.very_important_table(
    -> id int not null primary key,
    -> credit_card_num char(16),
    -> credit_card_owner varchar(256),
    -> credit_card_expire_month char(2),
    -> credit_card_expire_year char(2),
    -> credit_card_cvv char(3))
    -> engine=innodb;
Query OK, 0 rows affected (3.80 sec)

mysql> insert into test.very_important_table values(1, '1234123412341234', 'Sveta Smirnova', '02', '20', '123');
Query OK, 1 row affected (0.04 sec)

mysql> select current_user();
| current_user() |
| root@localhost |
1 row in set (0.00 sec)

After that, I removed the table mysql.user (which I did not expect I will be allowed to do):

mysql> drop table mysql.user;
Query OK, 0 rows affected (0.08 sec)

Then I restarted the server and logged in as user foo and was able to access data in the table with credit card information:

$ mysql -ufoo test
mysql> select current_user();
| current_user()                    |
| skip-grants user@skip-grants host |
1 row in set (0.00 sec)

mysql> select * from very_important_table;
| id | credit_card_num  | credit_card_owner | credit_card_expire_month | credit_card_expire_year | credit_card_cvv |
|  1 | 1234123412341234 | Sveta Smirnova    | 02                       | 20                      | 123             |
1 row in set (0.00 sec)

A bug report was considered a feature request in the first turn and now still in the discussion on how serious it is. There are a few reasons why you may not take this flaw seriously.

First, there is no harm when someone has access to the empty MySQL instance. Allowing to connect to such an instance may help DBA to solve the issue, for example, by running the mysql_upgrade command. Thought it does not restore mysql.user table anyway:

mysql> show tables from mysql like 'user';
Empty set (0.00 sec)

mysql> \q
$ mysql_upgrade
Checking if update is needed.
Checking server version.
Error occurred: Query against mysql.user table failed when checking the mysql.session.

$ mysql

mysql> show tables from mysql like 'user';
Empty set (0.00 sec)

Second, it is not expected that someone would drop table mysql.user manually. It is also not harmful for SQL injection attack if you limit access to your application user: one has to have a privilege which allows updating mysql database to perform the drop. It is good practice to do not grant such privileges for application users.

Third, while it is possible that mysql tablespace may be corrupted on the file system level, it is very unlikely that it will damage only data, belonging to the privilege tables. There are much higher chances that MySQL Server won't be able to start at all after such a corruption happens.

Fourth, to perform such an attack on the file system level, one needs to have access to the file system and can do more significant harm than the behavior reported.

These are the reasons why this behavior may be not considered bad security flow.

However, for new users of MySQL, it is possible to make such a mistake and silently open access to their database for everyone.


  1. It reminds me of a bug I filed 11 years ago:

    In the comments Kolbe noted that the server should refuse to start if it doesn't see an expected mysql.user table on startup :-)

  2. Hi Sveta,

    Nice and interesting catch ny Ceri.

    One thing is deliberately starting with --skip-grant-option, another is to not have it configured and find the privilege tables are missing. Having said that there is a moment during server start where it checks it's environment but during that phase any access to the server should not be allowed so even if things are missing that should be safe.

    I assume the "fix" is easy: create the table with the required structure (and expected initial content) and run flush privileges?

    mysql_upgrade in the past has tried to catch and fix some issues. I guess the server initialisation process needs a bit more polishing for unexpected cases like this one but that should not be too hard.

    1. > I assume the "fix" is easy: create the table with the required structure (and expected initial content) and run flush privileges?

      Probably yes. Still there is a surprise that --skip-grant-option added implicitly.