Bug #77732 REGRESSION: replication fails for insufficient privileges
Submitted: 15 Jul 2015 13:40 Modified: 25 Sep 2015 18:26
Reporter: Giuseppe Maxia (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S1 (Critical)
Version:5.7.8, 5.7.9, 8.0.0 OS:Mac OS X
Assigned to: CPU Architecture:Any
Tags: privileges, replication

[15 Jul 2015 13:40] Giuseppe Maxia
Description:
Until version 5.7.7, we can set up replication as follows:
1) in the master, create a user with only REPLICATION SLAVE privilege.
2) in the slave, run a CHANGE MASTER TO, using the above user as connection info.

When the slave connects, the low-privilege replication user will get the replication info, which includes the SERVER_ID through a 'SHOW GLOBAL VARIABLES' command:

2015-07-15T13:37:20.967723Z    8 Connect    rsandbox@localhost on
2015-07-15T13:37:20.977263Z    8 Query  SELECT UNIX_TIMESTAMP()
2015-07-15T13:37:20.977467Z    8 Query  SHOW GLOBAL VARIABLES LIKE 'SERVER_ID'
[ more commands removed]

However, in MySQL 5.7.8, the connection fails, and replication cannot start:
slave1 [localhost] {msandbox} ((none)) > show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 13253
                Connect_Retry: 60
              Master_Log_File:
          Read_Master_Log_Pos: 4
               Relay_Log_File: gmini-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File:
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 0
              Relay_Log_Space: 154
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1142
                Last_IO_Error: The slave I/O thread stops because a fatal error is encountered when it try to get the value of SERVER_ID variable from master. Error: SELECT command denied to user 'rsandbox'@'localhost' for table 'global_variables'
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 529825d2-2af2-11e5-a288-776696c001c3
             Master_Info_File: /Users/gmax/sandboxes/rsandbox_5_7_8/node1/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp: 150715 15:14:20
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
1 row in set (0.00 sec)

How to repeat:
1) create a user that has only the 'REPLICATION SLAVE'  privilege
2) start replication from a slave, using the above user to connect to the master.
[15 Jul 2015 14:04] Giuseppe Maxia
There is a workaround for this issue.
Adding 
show_compatibility_56=on 
to the master configuration will resume the previous behavior.
[16 Jul 2015 7:18] Umesh Shastry
Hello Giuseppe,

Thank you for the report.
Observed this with 5.7.9 builds.

Thanks,
Umesh
[16 Jul 2015 8:32] Umesh Shastry
test results

Attachment: 77732.results (application/octet-stream, text), 12.69 KiB.

[16 Jul 2015 9:45] Umesh Shastry
// 5.8.0 is also affected

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.8.0: bin/mysql -uroot -S run/slave.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.8.0-m17-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

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
owners.

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

mysql> show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 15001 |
+---------------+-------+
1 row in set (0.00 sec)

mysql> CHANGE MASTER TO
    ->      MASTER_HOST='localhost',
    ->      MASTER_PORT=15000,
    ->      MASTER_USER='repl',
    ->      MASTER_PASSWORD='slavepass',
    ->      MASTER_LOG_FILE='master-bin.000001',
    ->      MASTER_LOG_POS=650;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: localhost
                  Master_User: repl
                  Master_Port: 15000
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 650
               Relay_Log_File: slave-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 650
              Relay_Log_Space: 154
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1142
                Last_IO_Error: The slave I/O thread stops because a fatal error is encountered when it try to get the value of SERVER_ID variable from master. Error: SELECT command denied to user 'repl'@'localhost' for table 'global_variables'
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
                  Master_UUID:
             Master_Info_File: /export/umesh/server/binaries/mysql-advanced-5.8.0/slave/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp: 150716 11:43:13
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
1 row in set (0.00 sec)

//

2015-07-16T09:43:13.733012Z    3 Connect        repl@localhost on  using TCP/IP
2015-07-16T09:43:13.733200Z    3 Query  SELECT UNIX_TIMESTAMP()
2015-07-16T09:43:13.733313Z    3 Query  SHOW GLOBAL VARIABLES LIKE 'SERVER_ID'
2015-07-16T09:43:13.733406Z    3 Quit
2015-07-16T09:43:52.203148Z    2 Quit
[20 Jul 2015 3:45] Jesper wisborg Krogh
Posted by developer:
 
This will also happen in 5.7.6 with show_compatibility_56 = OFF (but is not the default in 5.7.6).

Instead of setting show_compatibility_56 = ON it's also possible to grant the replication user SELECT on performance_schema.global_variables:

   GRANT SELECT ON `performance_schema`.`global_variables` TO ...
[20 Jul 2015 4:36] Giuseppe Maxia
If we need to add a privilege to the replication user, we contradict the manual, which states that the only privilege needed is REPLICATION SLAVE.
https://dev.mysql.com/doc/refman/5.7/en/replication-howto-repuser.html

The best fix would be changing the behavior of the replication user. Instead of "show variables like 'server_id'", it should run "SELECT @@server_id"
[27 Jul 2015 18:10] Giuseppe Maxia
This issue causes also replication to fail when we enable the semi-synch replication plugin.

In this scenario, the replication user issues the following commands:

Time                 Id Command    Argument
2015-07-27T18:04:03.442994Z    6 Quit
2015-07-27T18:04:14.960834Z    7 Connect	rsandbox@localhost on  using TCP/IP
2015-07-27T18:04:14.961001Z    7 Query	SELECT UNIX_TIMESTAMP()
2015-07-27T18:04:14.961157Z    7 Query	SHOW GLOBAL VARIABLES LIKE 'SERVER_ID'
2015-07-27T18:04:14.961838Z    7 Query	SET @master_heartbeat_period= 30000001024
2015-07-27T18:04:14.961988Z    7 Query	SET @master_binlog_checksum= @@global.binlog_checksum
2015-07-27T18:04:14.962086Z    7 Query	SELECT @master_binlog_checksum
2015-07-27T18:04:14.962189Z    7 Query	SELECT @@GLOBAL.GTID_MODE
2015-07-27T18:04:14.962312Z    7 Query	SHOW GLOBAL VARIABLES LIKE 'SERVER_UUID'
2015-07-27T18:04:14.962846Z    7 Query	SET @slave_uuid= 'c1243e74-3488-11e5-b17d-b0a321bc4c49'
2015-07-27T18:04:14.963053Z    7 Query	SHOW VARIABLES LIKE 'rpl_semi_sync_master_enabled'
2015-07-27T18:04:14.963735Z    7 Query	SET @rpl_semi_sync_slave= 1
2015-07-27T18:04:14.963929Z    7 Binlog Dump	Log: 'mysql-bin.000002'  Pos: 399

The command "SHOW VARIABLES LIKE 'rpl_semi_sync_master_enabled'" fails because the user does not have permission to access performance_schema.session_variables.
This scenario happens when I gave the replication user SELECT privilege to performance_schema.global_variables. However, that privilege was not sufficient for semi-sync operations.

Here, in addition to the general issue of requiring a table access privilege for the replication user where none was required before, we also have 'SHOW VARIABLES' instead of 'SHOW GLOBAL VARIABLES'.
[17 Aug 2015 14:41] Simon Mudd
See also: bug#78107
[7 Sep 2015 14:53] Jon Stephens
Documented fix in the MySQL 5.7.9 changelog as follows:

    Replication slaves could fail for having insufficient privileges
    when they had been granted only the REPLICATION SLAVE privilege.

Closed.
[25 Sep 2015 18:23] Chris Fidao
To be clear, it seems that the bug is "fixed" by saying that we must ALSO grant the SELECT privilege to the replication user, correct?

Will this be documented in more places than the changelog?
[25 Sep 2015 18:26] Giuseppe Maxia
I have tested a preview build of MySQL 5.7.9. 
The bug is fixed properly, meaning that the slave user will not need additional grants to run as expected.
[18 Jun 2016 21:26] Omer Barnir
Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0