MySQL Bugs: #51593: MySQL Documentation Incorrect on 'ALTER' Privilege

Bug #51593	MySQL Documentation Incorrect on 'ALTER' Privilege
Submitted:	1 Mar 2010 1:46	Modified:	26 May 2011 19:42
Reporter:	Singer Wang	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0 and 5.1	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
MySQL Documentation at http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html and http://dev.mysql.com/doc/refman/5.0/en/privileges-provided.html states that:

The ALTER privilege enables use of ALTER TABLE to change the structure of or rename tables. (ALTER TABLE also requires the INSERT and CREATE privileges.) 

The above message is incorrect. Even if a user has ALTER,INSERT,CREATE privileges he is not able to rename the table. It should be:

The ALTER privilege enables use of ALTER TABLE to change the structure of or rename tables. (ALTER TABLE also requires the INSERT, CREATE and DROP privileges.) 

How to repeat:
NOTE THAT THIS FAILS DUE TO THE UESER NOT HAVING DROP PRIVILEGE
mysql>
mysql> show grants;
+----------------------------------------------------------+
| Grants for test@localhost                                |
+----------------------------------------------------------+
| GRANT INSERT, CREATE, ALTER ON *.* TO 'test'@'localhost' |
+----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test1          |
+----------------+
1 row in set (0.00 sec)

mysql> alter table test1 rename test2;
ERROR 1142 (42000): DROP command denied to user 'test'@'localhost' for table 'test1'
mysql> Aborted

THIS WORKS WHEN THE USER HAS THE DROP PRIVILEGE
mysql>
mysql>
mysql> show grants;
+----------------------------------------------------------------+
| Grants for test@localhost                                      |
+----------------------------------------------------------------+
| GRANT INSERT, CREATE, DROP, ALTER ON *.* TO 'test'@'localhost' |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test1          |
+----------------+
1 row in set (0.00 sec)

mysql> alter table test1 rename test2;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test2          |
+----------------+
1 row in set (0.00 sec)

mysql>

Suggested fix:
Update documentation

Thank you for the problem report. Note that http://dev.mysql.com/doc/refman/5.1/en/rename-table.html describes privileges required correctly.

YES, it is correct there: http://dev.mysql.com/doc/refman/5.1/en/rename-table.html and I agree. However the statement:

The ALTER privilege enables use of ALTER TABLE to change the structure of or rename tables. (ALTER TABLE also requires the INSERT and CREATE privileges.)

From http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html and http://dev.mysql.com/doc/refman/5.0/en/privileges-provided.html is NOT correct. This is what I wanted to point out.

Same here (http://dev.mysql.com/doc/refman/5.1/en/alter-table.html):

To use ALTER TABLE, you need ALTER, INSERT, and CREATE privileges for the table. 

Which is not true. You need ALTER, INSERT, CREATE, DROP as well..

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.