Description:
Create a user and grant db privileges to this user by the following statements:
CREATE USER 'api_test_test'@'%' IDENTIFIED BY '123456';
GRANT USAGE ON *.* TO 'api_test_test'@'%';
GRANT CREATE ROUTINE, INSERT, LOCK TABLES, REFERENCES, SELECT, DROP, DELETE, INDEX, ALTER ROUTINE, CREATE VIEW, CREATE TEMPORARY TABLES, SHOW VIEW, TRIGGER, EVENT, CREATE, UPDATE, EXECUTE, GRANT OPTION, ALTER ON `test_db`.* TO `api_test_test`@`%`;
This grant statement would return success, but the statement stored in the binary log was wrong, the binary log info:
| mysql-bin.000009 | 983 | Query | 1522321141 | 1201 | use `mysql`; CREATE USER 'api_test_test'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' /* xid=792021 */ |
| mysql-bin.000009 | 1201 | Gtid | 1522321141 | 1278 | SET @@SESSION.GTID_NEXT= 'e4918d73-52c8-11ea-b29f-506b4b4b3472:17531' |
| mysql-bin.000009 | 1278 | Query | 1522321141 | 1418 | use `mysql`; GRANT USAGE ON *.* TO 'api_test_test'@'%' /* xid=792022 */ |
| mysql-bin.000009 | 1418 | Gtid | 1522321141 | 1497 | SET @@SESSION.GTID_NEXT= 'e4918d73-52c8-11ea-b29f-506b4b4b3472:17532' |
| mysql-bin.000009 | 1497 | Query | 1522321141 | 1678 | use `mysql`; GRANT GRANT OPTION ON `test_db`.* TO 'api_test_test'@'%' WITH GRANT OPTION /* xid=792027 */ |
Which could cause the slave nodes(if have) have different privileges:
the master node:
mysql> show grants for api_test_test;
+------------------------------------------------------------+
| Grants for api_test_test@% |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO `api_test_test`@`%` |
| GRANT ALL PRIVILEGES ON `test_db`.* TO `api_test_test`@`%` |
+------------------------------------------------------------+
2 rows in set (0.01 sec)
the slave node:
mysql> show grants for api_test_test;
+---------------------------------------------------------------------+
| Grants for api_test_test@% |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `api_test_test`@`%` |
| GRANT USAGE ON `test_db`.* TO `api_test_test`@`%` WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
How to repeat:
CREATE DATABASE test_db;
CREATE USER 'api_test_test'@'%' IDENTIFIED BY '123456';
GRANT USAGE ON *.* TO 'api_test_test'@'%';
GRANT CREATE ROUTINE, INSERT, LOCK TABLES, REFERENCES, SELECT, DROP, DELETE, INDEX, ALTER ROUTINE, CREATE VIEW, CREATE TEMPORARY TABLES, SHOW VIEW, TRIGGER, EVENT, CREATE, UPDATE, EXECUTE, GRANT OPTION, ALTER ON `test_db`.* TO `api_test_test`@`%`;
