| Bug #86314 | Grants applied differently on the master to the slave | ||
|---|---|---|---|
| Submitted: | 14 May 2017 17:32 | Modified: | 25 Jun 2018 22:38 |
| Reporter: | Ceri Williams | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.7, 5.7.18 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | column, grant | ||
[15 May 2017 9:00]
Ceri Williams
Reproduced in 5.6.28 (version available for testing):
master [localhost] {root} ((none)) > create user test_grant@'%' identified with mysql_native_password;
Query OK, 0 rows affected (0.00 sec)
master [localhost] {root} ((none)) > grant select, select(user) on mysql.user to test_grant@'%';
Query OK, 0 rows affected (0.00 sec)
master [localhost] {root} ((none)) > show grants for test_grant@'%';
+-------------------------------------------------------------------+
| Grants for test_grant@% |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test_grant'@'%' |
| GRANT SELECT, SELECT (user) ON `mysql`.`user` TO 'test_grant'@'%' |
+-------------------------------------------------------------------+
2 rows in set (0.00 sec)
master [localhost] {root} ((none)) > show binlog events in 'mysql-bin.000002' from 255 limit 1\G
*************************** 1. row ***************************
Log_name: mysql-bin.000002
Pos: 255
Event_type: Query
Server_id: 1
End_log_pos: 400
Info: grant select, select(user) on mysql.user to test_grant@'%'
1 row in set (0.00 sec)
master [localhost] {root} ((none)) > select @@version;
+------------+
| @@version |
+------------+
| 5.6.28-log |
+------------+
1 row in set (0.00 sec)
slave1 [localhost] {msandbox} ((none)) > show grants for test_grant@'%';
+-------------------------------------------------------------------+
| Grants for test_grant@% |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test_grant'@'%' |
| GRANT SELECT, SELECT (user) ON `mysql`.`user` TO 'test_grant'@'%' |
+-------------------------------------------------------------------+
2 rows in set (0.00 sec)
[15 May 2017 9:40]
Ceri Williams
Previous comment should have read "Not reproduce in"
[28 Jun 2017 12:13]
MySQL Verification Team
Hello Ceri Williams, Thank you for the report. Observed this with 5.7.18 build(and 5.5.56/5.6.36 replays grants as expected). Thanks, Umesh
[25 Jun 2018 22:38]
Ceri Williams
I have noticed a similar issue that can occur relating to DNS.
- Create 2 basic configs /tmp/mysql/my.cnf and /tmp/mysql/my-slave.cnf
- disable skip-host-cache and skip-name-resolve
- enable binlogs
- set server-id
- set port
- Create 2 nodes using the mysql/mysql-server:57 docker image
- Create a user that can connect via TCP/IP to MySQL
- Configure replication
- The connected IP for the account creating a grant resolves via DNS
# docker run --name mysql-57 --network host --dns 192.168.99.1 --dns-search lan --hostname worker -v /tmp/mysql/my.cnf:/etc/my.cnf -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql/mysql-server:5.7
# docker run --name mysql-57-slave --network host --dns 192.168.99.1 --dns-search lan --hostname worker -v /tmp/mysql/my-slave.cnf:/etc/my.cnf -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql/mysql-server:5.7
# docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
498e719ec898 mysql/mysql-server:5.7 "/entrypoint.sh mysq…" 8 seconds ago Up 7 seconds (health: starting) mysql-57-slave
6d4fcb355160 mysql/mysql-server:5.7 "/entrypoint.sh mysq…" 19 minutes ago Up 19 minutes (healthy) mysql-57
mysql> select current_user();
+-------------------+
| current_user() |
+-------------------+
| ceri@192.168.99.% |
+-------------------+
1 row in set (0.00 sec)
mysql> create user test_repl@'192.168.99.%';
Query OK, 0 rows affected (0.01 sec)
mysql> grant select on mysql.db to test_repl@'192.168.99.%';
Query OK, 0 rows affected (0.01 sec)
mysql> select @@server_id, p.* from mysql.tables_priv p where user = "test_repl"\G
*************************** 1. row ***************************
@@server_id: 1
Host: 192.168.99.%
Db: mysql
User: test_repl
Table_name: db
Grantor: ceri@laptop
Timestamp: 0000-00-00 00:00:00
Table_priv: Select
Column_priv:
1 row in set (0.00 sec)
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 2 | | 3307 | 1 | 20c109f4-78c4-11e8-9fa3-80fa5b24bd2e |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)
mysql> select @@server_id, p.* from mysql.tables_priv p where user = "test_repl"\G
*************************** 1. row ***************************
@@server_id: 2
Host: 192.168.99.%
Db: mysql
User: test_repl
Table_name: db
Grantor: ceri@192.168.99.%
Timestamp: 0000-00-00 00:00:00
Table_priv: Select
Column_priv:
1 row in set (0.00 sec)
Description: Whilst the grant is indeed superfluous in this case as it is accompanied by a full SELECT privilege, this causes all slaves to have a different set of grants to the master: grant select, select(user) on mysql.user to test_grant@'%'; On 5.5 this is not the case, 5.6 untested so far How to repeat: master [localhost] {root} ((none)) > create user test_grant@'%' identified with mysql_native_password; Query OK, 0 rows affected (0.02 sec) master [localhost] {root} ((none)) > grant select, select(user) on mysql.user to test_grant@'%'; Query OK, 0 rows affected (0.01 sec) master [localhost] {root} ((none)) > show grants for test_grant@'%'; +-------------------------------------------------------------------+ | Grants for test_grant@% | +-------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test_grant'@'%' | | GRANT SELECT, SELECT (user) ON `mysql`.`user` TO 'test_grant'@'%' | +-------------------------------------------------------------------+ 2 rows in set (0.00 sec) slave1 [localhost] {msandbox} ((none)) > show grants for test_grant@'%'; +----------------------------------------------------+ | Grants for test_grant@% | +----------------------------------------------------+ | GRANT USAGE ON *.* TO 'test_grant'@'%' | | GRANT SELECT ON `mysql`.`user` TO 'test_grant'@'%' | +----------------------------------------------------+ 2 rows in set (0.00 sec) Entry from binary log: #170514 18:23:53 server id 1 end_log_pos 560 CRC32 0x9c56fd42 Query thread_id=5 exec_time=0 error_code=0 SET TIMESTAMP=1494782633/*!*/; GRANT SELECT ON `mysql`.`user` TO 'test_grant'@'%' Suggested fix: Ensure that the master applies the grant in the same way as it is written to the binlog