Bug #54149 RENAME USER migrates table/procedure level privileges, contrary to documentation
Submitted: 1 Jun 2010 15:10 Modified: 3 Jun 2010 18:46
Reporter: Scott Noyes Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: Paul DuBois CPU Architecture:Any

[1 Jun 2010 15:10] Scott Noyes
Description:
http://dev.mysql.com/doc/refman/5.1/en/rename-user.html says:

RENAME USER does not automatically migrate any database objects that the user created, nor does it migrate any privileges that the user had prior to the renaming. This applies to tables, views, stored routines, triggers, and events.

However, privileges are correctly migrated.

How to repeat:
-- Old user exists
mysql> SHOW GRANTS FOR test@host;
+--------------------------------------------------------------------------------------------------------+
| Grants for test@host                                                                                   |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'host' IDENTIFIED BY PASSWORD '*0380BEA27363E56C37F0BFDA438F429080848051' |
| GRANT SELECT ON `test`.* TO 'test'@'host'                                                              |
| GRANT UPDATE ON `test`.`t1` TO 'test'@'host'                                                           |
| GRANT EXECUTE ON PROCEDURE `test`.`updatehost` TO 'test'@'host'                                        |
+--------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

-- New user doesn't exist
mysql> SHOW GRANTS FOR test@newhost;
ERROR 1141 (42000): There is no such grant defined for user 'test' on host 'newhost'

mysql> RENAME USER test@host TO test@newhost;
Query OK, 0 rows affected (0.00 sec)

-- Old user has vanished
mysql> SHOW GRANTS FOR test@host;
ERROR 1141 (42000): There is no such grant defined for user 'test' on host 'host'

-- New user has all the correct privileges
mysql> SHOW GRANTS FOR test@newhost;
+-----------------------------------------------------------------------------------------------------------+
| Grants for test@newhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'newhost' IDENTIFIED BY PASSWORD '*0380BEA27363E56C37F0BFDA438F429080848051' |
| GRANT SELECT ON `test`.* TO 'test'@'newhost'                                                              |
| GRANT UPDATE ON `test`.`t1` TO 'test'@'newhost'                                                           |
| GRANT EXECUTE ON PROCEDURE `test`.`updatehost` TO 'test'@'newhost'                                        |
+-----------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

Suggested fix:
Correct documentation to show that RENAME USER does migrate object level privileges.
[2 Jun 2010 16:29] MySQL Verification Team
Privilege changes seem to be consistent with the description in http://dev.mysql.com/doc/refman/5.1/en/privilege-changes.html - global privileges remain until the next connection; table privileges change for the next query.
[3 Jun 2010 18:46] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.
[3 Jun 2010 19:14] Paul DuBois
See also Bug#20319.
[3 Jun 2010 19:15] Paul DuBois
Updated description:

RENAME USER causes the privileges held by the old user to be those
held by the new user. However, RENAME USER does not automatically
drop or invalidate any database objects that the old user created.
This applies to databases, tables, views, stored routines, triggers,
and events.