MySQL Bugs: #61865: RENAME USER does not work correctly

Bug #61865	RENAME USER does not work correctly - requires FLUSH PRIVILEGES
Submitted:	14 Jul 2011 12:10	Modified:	9 Apr 2012 1:58
Reporter:	Vojtech Kurka	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S2 (Serious)
Version:	5.5.14, 5.0, 5.1	OS:	Windows (win7 64)
Assigned to:		CPU Architecture:	Any
Tags:	grants netmask connect

Description:
When using RENAME USER command to set user IP/netmask combination, the setting does not work immediately.
However, the mysql.user table is updated properly and FLUSH PRIVILEGES solves the issue. I think it should work immediately, without FLUSH PRIVILEGES.

The server is started: E:\tmp\mysql-5.5.14-winx64\bin>mysqld --skip-name-resolve

How to repeat:
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.5.14    |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE USER 'tu'@'10.2.2.%' IDENTIFIED BY 'efvhi9t6932443ybff923ro';
Query OK, 0 rows affected (0.00 sec)

# now I'm able to connect from 10.2.2.65:
mysql -utu -pefvhi9t6932443ybff923ro -h10.2.2.88 -e 'select version()'
+-----------+
| version() |
+-----------+
| 5.5.14    |
+-----------+

mysql> RENAME USER 'tu'@'10.2.2.%' TO 'tu'@'10.2.2.0/255.255.255.0';
Query OK, 0 rows affected (0.00 sec)

# now I'm NOT able to connect from 10.2.2.65:
mysql -utu -pefvhi9t6932443ybff923ro -h10.2.2.88 -e 'select version()'
ERROR 1130 (HY000): Host '10.2.2.65' is not allowed to connect to this MySQL server

mysql> DROP USER 'tu'@'10.2.2.0/255.255.255.0';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'tu'@'10.2.2.0/255.255.255.0' IDENTIFIED BY 'efvhi9t6932443ybff923ro';
Query OK, 0 rows affected (0.00 sec)

# now it works!
mysql -utu -pefvhi9t6932443ybff923ro -h10.2.2.88 -e 'select version()'
+-----------+
| version() |
+-----------+
| 5.5.14    |
+-----------+

Thank you for the report.

I can not repeat described behavior: changes take effect immediately.

Please send us output of SELECT USER(), CURRENT_USER() after one of successful connections by created user.

mysql> CREATE USER 'tu'@'10.2.2.%' IDENTIFIED BY 'efvhi9t6932443ybff923ro';
Query OK, 0 rows affected (0.05 sec)

$ mysql -utu -pefvhi9t6932443ybff923ro -h10.2.2.88 -e 'select user(); select current_user()'
+--------------+
| user()       |
+--------------+
| tu@10.2.2.65 |
+--------------+
+----------------+
| current_user() |
+----------------+
| tu@10.2.2.%    |
+----------------+

mysql> RENAME USER 'tu'@'10.2.2.%' TO 'tu'@'10.2.2.0/255.255.255.0';
Query OK, 0 rows affected (0.00 sec)

$ mysql -utu -pefvhi9t6932443ybff923ro -h10.2.2.88 -e 'select user(); select current_user()'
ERROR 1130 (HY000): Host '10.2.2.65' is not allowed to connect to this MySQL server

.... and after flushing privileges:

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

]$ mysql -utu -pefvhi9t6932443ybff923ro -h10.2.2.88 -e 'select user(); select current_user()'
+--------------+
| user()       |
+--------------+
| tu@10.2.2.65 |
+--------------+
+---------------------------+
| current_user()            |
+---------------------------+
| tu@10.2.2.0/255.255.255.0 |
+---------------------------+

Thank you for the feedback.

Verified as described.

Test case for MTR:

CREATE USER foo@localhost;
grant usage on *.* to foo@'10.128.23.136';

connect (addconfoo, 10.128.23.136, foo,,test,$MASTER_MYPORT);
connection addconfoo;
select user(), current_user();

connection default;
RENAME USER foo@'10.128.23.136' to foo@'10.128.23.0/255.255.255.0';

connect (addconfoobar, 10.128.23.136, foo,,test,$MASTER_MYPORT);
connection addconfoobar;
select user(), current_user();

This is an *S2* bug that has not been fixed yet after almost half a year. You/admin may REVOKE privileges from a user *and the user still has the privileges*

Incredible irresponsibility in prioritizing here!

please ignore my comment.  Was not meant for this post!

Also see http://bugs.mysql.com/bug.php?id=64675

Noted in 5.6.6 changelog.

Under some conditions, the effect of RENAME USER was not recognized
until FLUSH PRIVILEGES was used (which should not be necessary).