Description:
Hi all:
Our Innodb Cluster have 3 nodes, single primary mode, when failover, applicaiton use mysql router connected to new primary, Then, Applicaiton report an error, access deny, login in the new primary, the user privileges is different to old master.
How to repeat:
1. current innodb cluster status
(root@localhost) [performance_schema]> select * from replication_group_members;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 07f0e745-96ce-11eb-935c-0050569c7243 | xixin-mysql8-1 | 3306 | ONLINE | PRIMARY | 8.0.22 |
| group_replication_applier | 0abf5d62-96ce-11eb-8ad4-0050569cef35 | xixin-mysql8-2 | 3306 | ONLINE | SECONDARY | 8.0.22 |
| group_replication_applier | a986dcdb-96cd-11eb-af36-0050569ce9a6 | xixin-mysql8-3 | 3306 | ONLINE | SECONDARY | 8.0.22 |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
2. create user and grant privileges ( need grant options ) in xixin-mysql8-1
(root@localhost) [performance_schema]> CREATE USER `testops`@`%` IDENTIFIED WITH mysql_native_password BY '***';
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [performance_schema]> GRANT SELECT, INSERT, UPDATE, DELETE,DROP, Grant Option ON `testops`.* TO `testops`@`%`;
Query OK, 0 rows affected (0.01 sec)
3. check the user testops privileges in xixin-mysql8-1
(root@localhost) [performance_schema]> show grants for `testops`@`%`;
+----------------------------------------------------------------------------------------------+
| Grants for testops@% |
+----------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `testops`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON `testops`.* TO `testops`@`%` WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
4. check the user testops privileges in xixin-mysql8-2
(root@localhost) [performance_schema]> show grants for `testops`@`%`;
+----------------------------------------------------------------------------------------------+
| Grants for testops@% |
+----------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `testops`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON `testops`.* TO `testops`@`%` WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
user testops@'%' cannot replication to secondory when the user grant privileges include "Grant Option"
5. use "WITH GRANT OPTIONS" create user testops1@'%'
(root@localhost) [performance_schema]> CREATE USER `testops1`@`%` IDENTIFIED WITH mysql_native_password BY 'WYtestops888!';
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [performance_schema]> GRANT SELECT, INSERT, UPDATE, DELETE,DROP ON `testops1`.* TO `testops1`@`%` With Grant Option;
Query OK, 0 rows affected (0.01 sec)
6. check the user testops1 privileges in xixin-mysql8-2
(root@localhost) [(none)]> show grants for testops1@'%';
+------------------------------------------------------------------------------------------------+
| Grants for testops1@% |
+------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `testops1`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON `testops1`.* TO `testops1`@`%` WITH GRANT OPTION |
+------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
user testop1s@'%' can replication to secondory when the user grant use optin "With Grant Option"
and I do this test in mysql server 5.7, replicaiton work is normal. slave can replication privileges from master
