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.

PlanetMySQL Voting: Vote UP / Vote DOWN

This Thursday (February 25th, 13:00 UTC - way earlier than usual!), Darren Cassar will present Securich - Security Plugin for MySQL. According to Darren, the author of the plugin, Securich is an incredibly handy and versatile tool for managing user privileges on MySQL through the use of roles. It basically makes granting and revoking rights a piece of cake, not to mention added security it provides through password expiry and password history, the customization level it permits, the fact that it runs on any MySQL 5.0 or later and it's easily deployable on any official MySQL binary, platform independent.
More information here: http://www.securich.com/about.html.

For MySQL University sessions, point your browser to this page. You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the conferencing system we're using for MySQL University sessions. It provides integrated voice streaming, chat, whiteboard, session recording, and more.)

MySQL University is a free educational online program for engineers/developers. MySQL University sessions are open to anyone. All sessions (slides & audio) are recorded; the links will be on the respective MySQL University session pages which are listed on the MySQL University home page.

Here's the tentative list of upcoming sessions:

• March 4: MySQL Column Databases (Robin Schumacher)
• March 11: Improving MySQL Full-Text Search (Kristofer Pettersson)

By the way, did I mention that we need more speakers to fill up the 2010 schedule? If you'd like to be a speaker, have a look at this blog article!

PlanetMySQL Voting: Vote UP / Vote DOWN

Just a small note to advise that Securich reached 0.1.4. Some new tools include: * Added Password complexity * Enhanced `set_password` – Old password is now necessary to replace it by a new one * Enhanced Revoke privileges to accept regexp * Added Block user@hostname on a database [...]
PlanetMySQL Voting: Vote UP / Vote DOWN