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:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.7, 5.7.18 OS:Any
Assigned to: CPU Architecture:Any
Tags: column, grant

[14 May 2017 17:32] Ceri Williams
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
[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] Umesh Shastry
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)