Uploaded image for project: 'Percona Server'
  1. Percona Server
  2. PS-5431

Absence of mysql.user leads to auto-apply of --skip-grant-tables

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open
    • Priority: High
    • Resolution: Fixed
    • Affects Version/s: 8.0.13-4
    • Fix Version/s: None
    • Component/s: None

      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.
      

      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/8.0.13/8.0.14/; s/- --binlog_encryption=1/#- --binlog_encryption=1/' 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
      

      You can then connect, even with an invalid user:

      $ 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
      

      Additionally, it is possible to configure the host as a slave with a little coercion.

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

      When you try to CHANGE MASTER you will get the error:

      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.
      

      Restarting the container then allows you to start replication. With the following you can avoid the table causing breakage:

      change replication filter replicate_wild_ignore_table = ('mysql.user');

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  Unassigned
                  Reporter:
                  ceri.williams Ceri Williams
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  3 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: