| Bug #100793 | 'GRANT SELECT, GRANT OPTION ON *.* ' would not write the privileges to binlog | ||
|---|---|---|---|
| Submitted: | 10 Sep 2020 7:46 | Modified: | 12 Oct 2020 13:49 |
| Reporter: | Flabby Flabby | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 8.0.20, 8.0.21 | OS: | CentOS |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | grant option | ||
[10 Sep 2020 8:01]
MySQL Verification Team
Hello Flabby, Thank you for the report and feedback. regards, Umesh
[10 Sep 2020 8:02]
MySQL Verification Team
- 8.0.21
bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.21 MySQL Community Server - GPL
Copyright (c) 2000, 2020, 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> create user 'test_user'@'%' identified by '123';
Query OK, 0 rows affected (0.01 sec)
mysql> grant select, grant option on *.* to 'test_user'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show binlog events;
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| binlog.000001 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| binlog.000001 | 125 | Previous_gtids | 1 | 156 | |
| binlog.000001 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 | 235 | Query | 1 | 473 | CREATE USER 'test_user'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$z(,6sz.#
5 vxH&HWxakTCb/QmjbUsgMZQmL.M/Aab8bKlcE5C3PZVprg9' /* xid=3 */ |
| binlog.000001 | 473 | Anonymous_Gtid | 1 | 550 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 | 550 | Query | 1 | 711 | GRANT GRANT OPTION ON *.* TO 'test_user'@'%' WITH GRANT OPTION /* xid=4 */ |
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)
mysql> show grants for 'test_user'@'%';
+----------------------------------------------------------+
| Grants for test_user@% |
+----------------------------------------------------------+
| GRANT SELECT ON *.* TO `test_user`@`%` WITH GRANT OPTION |
+----------------------------------------------------------+
1 row in set (0.00 sec)
mysql> drop user 'test_user'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> drop user 'test_user'@'%';
ERROR 1396 (HY000): Operation DROP USER failed for 'test_user'@'%'
mysql> create user 'test_user'@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT GRANT OPTION ON *.* TO 'test_user'@'%' WITH GRANT OPTION /* xid=4 */;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'test_user'@'%';
+---------------------------------------------------------+
| Grants for test_user@% |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO `test_user`@`%` WITH GRANT OPTION |
+---------------------------------------------------------+
1 row in set (0.00 sec)
^^ SELECT privileges are lost
[10 Sep 2020 8:02]
MySQL Verification Team
- 5.7.31 not affected bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.31-log MySQL Community Server (GPL) Copyright (c) 2000, 2020, 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> create user 'test_user'@'%' identified by '123'; Query OK, 0 rows affected (0.01 sec) mysql> grant select, grant option on *.* to 'test_user'@'%'; Query OK, 0 rows affected (0.01 sec) mysql> show binlog events; +------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------+ | hod03-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.31-log, Binlog ver: 4 | | hod03-bin.000001 | 123 | Previous_gtids | 1 | 154 | | | hod03-bin.000001 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | hod03-bin.000001 | 219 | Query | 1 | 404 | CREATE USER 'test_user'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' | | hod03-bin.000001 | 404 | Anonymous_Gtid | 1 | 469 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | hod03-bin.000001 | 469 | Query | 1 | 612 | GRANT SELECT ON *.* TO 'test_user'@'%' WITH GRANT OPTION | +------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------+ 6 rows in set (0.00 sec) mysql> show grants for 'test_user'@'%'; +----------------------------------------------------------+ | Grants for test_user@% | +----------------------------------------------------------+ | GRANT SELECT ON *.* TO 'test_user'@'%' WITH GRANT OPTION | +----------------------------------------------------------+ 1 row in set (0.00 sec)
[12 Oct 2020 13:49]
Paul DuBois
Posted by developer: Fixed in 8.0.23. GRANT ... GRANT OPTION ... TO and GRANT ... TO .. WITH GRANT OPTION sometimes were not correctly written to the server logs.
[29 May 2021 5:51]
MySQL Verification Team
Bug #98678 marked as duplicate of this one
Description: The `GRANT SELECT, GRANT OPTION ON *.* to 'user'@'%'` would write the binlog with a record like `GRANT GRANT OPTION ON *.* TO 'test_user'@'%' WITH GRANT OPTION`. The 'SELECT' privilege was missing. How to repeat: mysql> create user 'test_user'@'%' identified by '123'; Query OK, 0 rows affected (0.02 sec) mysql> grant select, grant option on *.* to 'test_user'@'%'; Query OK, 0 rows affected (0.01 sec) mysql> show binlog events in 'mysql-bin.000002'; +------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mysql-bin.000002 | 4 | Format_desc | 81 | 125 | Server ver: 8.0.20-1-testaa-debug, Binlog ver: 4 | | mysql-bin.000002 | 125 | Previous_gtids | 81 | 156 | | | mysql-bin.000002 | 156 | Gtid | 81 | 235 | SET @@SESSION.GTID_NEXT= 'a6039cce-f338-11ea-9285-fa163ebe3932:1' | | mysql-bin.000002 | 235 | Query | 81 | 478 | use `mysql`; CREATE USER 'test_user'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$=)v=GD,?;F19/?<Y&/7o83sBjKt9MUg5KU/hW7Ux5guxMWnHF2FLWw9sVrk1' /* xid=41 */ | | mysql-bin.000002 | 478 | Gtid | 81 | 555 | SET @@SESSION.GTID_NEXT= 'a6039cce-f338-11ea-9285-fa163ebe3932:2' | | mysql-bin.000002 | 555 | Query | 81 | 721 | use `mysql`; GRANT GRANT OPTION ON *.* TO 'test_user'@'%' WITH GRANT OPTION /* xid=42 */ | +------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 6 rows in set (0.00 sec) Suggested fix: It looks like the bug fix of BUG #26191109 changed the action of `Rewriter_grant::rewrite` > BUG #26191109 GRANT GRANT OPTION ON *.* TO user granted GRANT OPTION for static but not dynamic privileges > commit 7e702fba224aa49f499508e5866fe1e979c9af2c ```shell diff --git a/sql/sql_rewrite.cc b/sql/sql_rewrite.cc index 487417f..728c2ad 100644 --- a/sql/sql_rewrite.cc +++ b/sql/sql_rewrite.cc @@ -326,6 +326,8 @@ void mysql_rewrite_grant(THD *thd, String *rlb) rlb->append(STRING_WITH_LEN("PROXY")); else if (lex->all_privileges) rlb->append(STRING_WITH_LEN("ALL PRIVILEGES")); + else if (lex->grant_privilege) + rlb->append(STRING_WITH_LEN("GRANT OPTION")); else { ulong priv; ```