PlanetMysql.ru – информация о СУБД MySQL

I recently granted ALTER access in MySQL so a user could run the ALTER TABLE command . However after I granted the necessary privileges, the user was still not able to perform the tasks needed. Reproducing the issue using a test instance, I granted a test user the required privileges and MySQL reported no errors or warnings when the ALTER TABLE was run:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.1.41-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant alter,create,insert on *.* to 'test'@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> show warnings;
Empty set (0.00 sec)

mysql> show errors;
Empty set (0.00 sec)

mysql>

The reason I granted the addition CREATE and INSERT privileges is that according to the MySQL documentation (http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html), they are required by the ALTER privilege:

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 user was attempting to rename a table with the ALTER TABLE [NAME] RENAME [NAME2] command and seeing the following error:

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

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)

Of course I immediately realized what the issue was. In MySQL, ALTER TABLE [NAME] RENAME [NEW_NAME] is done as a DROP (which requires the DROP privilege and CREATE TABLE (which requires the CREATE privilege). So I granted the user the DROP privilege. This time the user was able to successful rename the table as shown below:

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 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 |
+----------------+
| test2          |
+----------------+
1 row in set (0.00 sec)

The MySQL documentation on the ALTER privilege states that it only need CREATE and INSERT but makes no mention of the requirement for DROP privilege. The RENAME TABLE documentation at http://dev.mysql.com/doc/refman/5.1/en/rename-table.html does mention:

When you execute RENAME, you cannot have any locked tables or active transactions. You must also have the ALTER and DROP privileges on the original table, and the CREATE and INSERT privileges on the new table.

However, that should be explicit in the ALTER TABLE documentation, because if you use ALTER TABLE...RENAME you will not necessarily read the documentation for the RENAME TABLE syntax.

To make the documentation better for everyone, I have filed a MySQL Bug Report on this documentation – Bug 51593.

I like the 5.4 developments, overall. It has useful stuff and is being developed and released a reasonable pace. Good progress. While perusing the MySQL 5.4.4 changelog, one particular change drew my attention, since it’s been (re)appearing since 2006. It’s the removal of the TYPE= keyword which was obsoleted since MySQL 4.1 in favour of the ENGINE= syntax in CREATE/ALTER TABLE.

While on the surface it may seem ok to remove the obsolete keyword, there are quite a few apps out there that use it, and that cannot be changed. So these will now be unable to use MySQL 5.4 or beyond. I filed this as a bug in 2006, MySQL bug#17501. If you’re interested in the “history of reappearance”, take a peek at the comments and their timeline. I just put in a new comment to note the 5.4.4 change.

I suppose that a new developer comes along and reckon that removing this keyword is a good idea. But really, why do we need to remove one keyword from the parser? Because that’s all it is. And removing it really does break apps.
Let’s not. Again. Please! And this time please put a comment in the parser source files, referring to the bug#, so that it doesn’t get recycled at a later date. Please just leave it in.