| 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: | |
| Category: | MySQL Server: Replication | Severity: | S1 (Critical) |
| Version: | 5.7.8, 5.7.9, 8.0.0 | OS: | MacOS |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | privileges, replication | ||
[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]
MySQL Verification Team
Hello Giuseppe, Thank you for the report. Observed this with 5.7.9 builds. Thanks, Umesh
[16 Jul 2015 8:32]
MySQL Verification Team
test results
Attachment: 77732.results (application/octet-stream, text), 12.69 KiB.
[16 Jul 2015 9:45]
MySQL Verification Team
// 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
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.