Bug #31786 Swapping with RENAME TABLE fails
Submitted: 23 Oct 2007 12:27 Modified: 23 Nov 2007 15:30
Reporter: Bart Alewijnse Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.0.22 OS:Linux (Ubuntu 6.06)
Assigned to: CPU Architecture:Any

[23 Oct 2007 12:27] Bart Alewijnse
Description:
Trying to swap two tables with a three-part RENAME TABLE fails with the following error:

ERROR 1142 (42000): DROP,ALTER command denied to user 'me'@'localhost' for table 'b'

Notably, doing the three renames in separate commands works. 

This worked until recently. It seems to have started failing when the system admin updated the mysql server to 5.0.22-0ubuntu6.06.5.

How to repeat:
create table a (a int);
create table b (b int);
rename table a to temp, b to a, temp to b;

Suggested fix:
Should work, as before:)

I'm not sure what the bug is (it's possible it is a peculiarity of the complex permission system I missed), but I'm guessing mysql should take more consideration in applying implied permissions onto temporary/new tables; I've seen similar complaints about peculiarities with temporary tables.
[23 Oct 2007 15:30] MySQL Verification Team
Thank you for the bug report. Could you please try with latest released
version. Thanks in advance,
[24 Nov 2007 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[5 Mar 2008 7:38] Ben Strata
I can reproduce this on Ubuntu 7.04 with two tables in the RENAME.

mysql> select version();
+--------------------------+
| version()                |
+--------------------------+
| 5.0.38-Ubuntu_0ubuntu1.2 | 
+--------------------------+
1 row in set (0.00 sec)

mysql> create table test1 (id int);
Query OK, 0 rows affected (0.09 sec)

mysql> create table test2 (id int);
Query OK, 0 rows affected (0.21 sec)

mysql> rename table test1 to test3,test2 to test1;
ERROR 1142 (42000): DROP,ALTER command denied to user 'loader'@'localhost' for table 'test2'