MySQL Bugs: #94394: Absence of mysql.user leads to auto-apply of --skip-grant-tables

Bug #94394	Absence of mysql.user leads to auto-apply of --skip-grant-tables
Submitted:	19 Feb 2019 14:10	Modified:	26 Feb 2019 18:47
Reporter:	Ceri Williams	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S2 (Serious)
Version:	8.0.14	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
During the --initiliaze phase of a new server, if a configuration issues causes the process to abort it is possible to start the server normally and it seems to auto-apply skip-grant-tables.

This allows access and could be very easy to miss, which has implications such as allowing remote access.

Sadly, you cannot fix this with mysql_upgrade:

$ docker-compose exec node mysql_upgrade                                                         
Checking if update is needed.
Checking server version.
Error occurred: Query against mysql.user table failed when checking the mysql.session.

How to repeat:
This can be tested easily using the official Docker images.

#1 Create a broken container 

We force the container to break with an unknown variable (binlog_encryption). The example uses Docker in Swarm mode so that secrets are shared from files.

$ cat <<EOF > docker-compose.yml
---
version: '3.4'

services:
  node:
    image: mysql:8.0.13
    command:
      - mysqld
      - --log-bin
      - --server-id=3
      - --binlog_encryption=1
    networks:
      dblan:
        ipv4_address: 10.2.1.4
    ports:
      - 10213:3306
    environment:
      - MYSQL_ROOT_PASSWORD_FILE=/run/secrets/opt_db_root_passwd
    secrets:
      - opt_db_root_passwd
    volumes:
      - node_mysql_data:/var/lib/mysql
    healthcheck:
      test: /usr/bin/mysqladmin ping 2>&1 | fgrep -q "mysqld is alive"
      interval: 30s
      timeout: 10s
      retries: 5

volumes:
  node_mysql_data:
    driver: local

secrets:
  opt_db_root_passwd:
    file: ./secrets/opt_db_root_passwd

networks:
  dblan:
    driver: bridge
    ipam:
      driver: default
      config:
        - subnet: 10.2.1.0/23
EOF

$ docker-compose up -d node

$ docker-compose logs node

Attaching to mysql_node_1
node_1  | Initializing database
node_1  | 2019-02-19T13:25:08.001650Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option a
s it' is deprecated and will be removed in a future release.
node_1  | 2019-02-19T13:25:08.001747Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.13) initializing of server in progress as process 33
node_1  | 2019-02-19T13:25:11.139422Z 0 [ERROR] [MY-000067] [Server] unknown variable 'binlog_encryption=1'.
node_1  | 2019-02-19T13:25:11.139433Z 0 [Warning] [MY-010952] [Server] The privilege system failed to initialize correctly. If you have upgraded your server, make sure you're executing mysq
l_upgrade to correct the issue.   
node_1  | 2019-02-19T13:25:11.139438Z 0 [ERROR] [MY-013236] [Server] Newly created data directory /var/lib/mysql/ is unusable. You can safely remove it.
node_1  | 2019-02-19T13:25:11.139441Z 0 [ERROR] [MY-010119] [Server] Aborting
node_1  | 2019-02-19T13:25:12.819365Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.13)  MySQL Community Server - GPL.

#2 Update MySQL version to fix forced failure

$ sed -i 's/mysql:8.0.13/mysql:8.0.14/' docker-compose.yml
$ docker-compose up -d node

$ docker-compose logs node
Attaching to mysql_node_1                                                                                               
node_1  | mysqld: Table 'mysql.plugin' doesn't exist                                                                    
node_1  | 2019-02-19T13:30:30.019632Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option a
s it' is deprecated and will be removed in a future release.                                                            
node_1  | 2019-02-19T13:30:30.019691Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.14) starting as process 1
node_1  | 2019-02-19T13:30:30.321144Z 0 [ERROR] [MY-010735] [Server] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
node_1  | 2019-02-19T13:30:30.424288Z 0 [Warning] [MY-010015] [Repl] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
node_1  | 2019-02-19T13:30:30.430624Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.            
node_1  | 2019-02-19T13:30:30.552884Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider
choosing a different directory.                                                                                         
node_1  | 2019-02-19T13:30:30.553449Z 0 [Warning] [MY-010441] [Server] Failed to open optimizer cost constant tables    
node_1  | 2019-02-19T13:30:30.553733Z 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the info
rmation to the error-log instead: MY-001146 - Table 'mysql.component' doesn't exist                                     
node_1  | 2019-02-19T13:30:30.553761Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the in
formation to the error-log instead: MY-003543 - The mysql.component table is missing or has an incorrect definition.    
node_1  | 2019-02-19T13:30:30.554599Z 0 [ERROR] [MY-010326] [Server] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
node_1  | 2019-02-19T13:30:30.554661Z 0 [Warning] [MY-010952] [Server] The privilege system failed to initialize correctly. If you have upgraded your server, make sure you're executing mysq
l_upgrade to correct the issue.                                                                                         
node_1  | 2019-02-19T13:30:30.554936Z 0 [Warning] [MY-010357] [Server] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them
node_1  | 2019-02-19T13:30:30.555822Z 0 [ERROR] [MY-010353] [Server] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
node_1  | 2019-02-19T13:30:30.556728Z 0 [Warning] [MY-010405] [Repl] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.
node_1  | 2019-02-19T13:30:30.556774Z 0 [ERROR] [MY-010422] [Repl] Error in checking mysql.slave_master_info repository info type of TABLE.
node_1  | 2019-02-19T13:30:30.556821Z 0 [ERROR] [MY-010415] [Repl] Error creating master info: Error checking repositories.
node_1  | 2019-02-19T13:30:30.556843Z 0 [ERROR] [MY-010426] [Repl] Slave: Failed to initialize the master info structure for channel ''; its record may still be present in 'mysql.slave_mast
er_info' table, consider deleting it.                                                                                   
node_1  | 2019-02-19T13:30:30.556864Z 0 [ERROR] [MY-010529] [Repl] Failed to create or recover replication info repositories.
node_1  | 2019-02-19T13:30:30.557931Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.14'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Co
mmunity Server - GPL.                                                                                                   
node_1  | 2019-02-19T13:30:30.578679Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060

$ mysql -h 10.2.1.4 -u i-am-not-a-user -D mysql -Bse "show tables; select ''; select current_user()" 
innodb_index_stats
innodb_table_stats

skip-grants user@skip-grants host

Suggested fix:
Prevent this from happening :)

Skipping grant tables seems like a bad idea when in almost the entire schema has gone AWOL

Sorry, forgot to note that in the example you need to remove "--binlog_encryption=1" from the compose file too before rerunning, or resolve:

Unable to recover binlog encryption master key, please check if keyring plugin is loaded

Hi Ceri, 

Just a comment: if you are doing --initialize, it means you don't have any data on the system isn't it ? you are just creating the system tables... so if it breaks in the middle, I don't really see a problem of having access to a empty, non stable db.

But I might be wrong...

Thank you for the reasonable feature request. 
Currently --initialize is not an atomic operation. If the server fails mid-flight the results are unpredictable. So not a lot of use IMHO to shuffle initialization sequence in some way as it'll just change the way the server behaves. The good part is that --initialize doesn't open any communication channels and does shut the server down when complete. 
Thus I'd suggest checking the state of --initialize before putting the server online on the resulting directory as a workaround until we have an atomic --initialize process.

Thanks for the feedback.

> if you are doing --initialize, it means you don't have any data on the system isn't it ? you are just creating the system tables... so if it breaks in the middle, I don't really see a problem of having access to a empty, non stable db.

Initialisation is kindly done for you and so not necessarily obvious to the unaware user.

It is possible to make the server a slave and maintain the issue, admittedly yo-u should notice this although the error that you get at first is misleading.

$ mysqldump --all-databases --ignore-table=mysql.user --single-transaction --master-data=1

will produce

ERROR 1794 (HY000) at line 33: Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.

If you run with --master-data=2 you obviously don't get the issue. Something like the following allows this:

$ mysqldump --all-databases --ignore-table=mysql.user --single-transaction --master-data=2 | tee /tmp/dump.sql | mysql -h 10.2.1.4 -B

Some people like to maintain grants locally, so could ignore this table plus the related tables, even if not best practice.

$ pt-heartbeat --user=root --ask-pass --host=10.2.1.2 --update
Enter password:

$ pt-heartbeat --host=10.2.1.4 --check --master-server-id=2
0.00

Also, you could have this as a standalone instance and write data. Once again, you would have needed to ignore the fact that you have not created a dedicated user.

It should be noted that this is a consistent issue with 8.0 and does not occur with 5.7, or at least it not reproducible with the same test; 5.7 will fail and then initialize correctly on the second start.

> Thus I'd suggest checking the state of --initialize before putting the server online on the resulting directory as a workaround until we have an atomic --initialize process.

A less experienced user my not realise the exact issue (or find the cause) and just see the message such as "unknown variable 'binlog_encryption=1'." - they fix that and it starts.

As noted above, 5.7 seems to behave in an expected fashion if you set an unknown variable in the config.

+1 to an atomic --initiliaze though

FTR the use of Docker here is just to make testing easy - the issue exists with a normal install.

Also, from a trivial investigation the issue seems to stem from the change from MyISAM to InnoDB. If you remove the files for the mysql.user table in 5.7 it will abort as it can't lock the user table:

2019-02-20T12:04:18.324067Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
2019-02-20T12:04:18.324092Z 0 [ERROR] Fatal error: Failed to initialize ACL/grant/time zones structures or failed to remove temporary table files.
2019-02-20T12:04:18.324148Z 0 [ERROR] Aborting

test case for MTR

Attachment: bug94394.test (application/octet-stream, text), 795 bytes.

This bug is not about --initialize option, but about the fact that the server can successfully start and anyone can access user tables even if table mysql.user does not exist. See attached test case for MTR. Note it will not pass check-testcases test due to missed system tables. Run MTR with disabled option --check-testcases.

Output of MTR test case:

show tables from mysql like 'user';
Tables_in_mysql (user)
user
drop table mysql.user;
select current_user();
current_user()
root@localhost
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;
insert into test.very_important_table values(1, '1234123412341234', 'Sveta Smirnova', '02', '20', '123');
"Restarting MySQL server"
# restart
"MySQL restarted"
show tables from mysql like 'user';
Tables_in_mysql (user)
select current_user();
current_user()
skip-grants user@skip-grants host
select current_user();
current_user()
skip-grants user@skip-grants host
select * from test.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
drop table test.very_important_table;

I'm updating the synopsis to better reflect the issue and its severity. A perfectly healthy server can be turned into one that you wouldn't want running.

To reiterate Sveta's test-case:

mysql> use mysql
mysql> rename table user to disabled_user;
mysql> restart;
mysql> select current_user();
ERROR 2006 (HY000): MySQL server has gone away

mysql> select current_user();
skip-grants user@skip-grants host

Posted by developer:
 
Fixed in 8.0.16.

Previously, if the grant tables were corrupted, the MySQL server
wrote a message to the error log but continued as if the
--skip-grant-tables option had been specified. This resulted in the
server operating in an unexpected state unless --skip-grant-tables
had in fact been specified. Now, the server stops after writing a
message to the error log unless started with --skip-grant-tables.
(Starting the server with that option enables you to connect to
perform diagnostic operations.)