Bug #15520 | "Optimize table" wants INSERT privs? | ||
---|---|---|---|
Submitted: | 6 Dec 2005 14:26 | Modified: | 3 May 2006 17:56 |
Reporter: | Matthias Urlichs | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
Version: | all | OS: | Any (all) |
Assigned to: | Paul DuBois | CPU Architecture: | Any |
[6 Dec 2005 14:26]
Matthias Urlichs
[6 Dec 2005 14:54]
Valeriy Kravchuk
Thank you for a problem report. Please, provide a complete test case (starting with CREATE TABLE..., with all grants performed). Are you sure that table cat1 really exists?
[6 Dec 2005 15:11]
Matthias Urlichs
Come on, please -- if table "cat1" wouldn't exist, I would get a quite different error message. But since you insist ..: root@mysql> revoke insert on smurf.* from smurf@localhost; smurf@mysql> optimize table cat1; ERROR 1142 (42000): INSERT command denied to user 'smurf'@'localhost' for table 'cat1' root@mysql> grant insert on smurf.* to smurf@localhost; smurf@mysql> optimize table cat1; +------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------+----------+----------+----------+ | smurf.cat1 | optimize | status | OK | +------------+----------+----------+----------+ 1 row in set (2.50 sec) mysql> show create table cat1; Table: cat1 Create Table: CREATE TABLE `cat1` ( `id` int(11) NOT NULL auto_increment, `multi` tinyint(1) NOT NULL default '0', `name` varchar(100) NOT NULL default '', `depcat` int(11) default NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`), KEY `depcat` (`depcat`), CONSTRAINT `cat1_ibfk_1` FOREIGN KEY (`depcat`) REFERENCES `cat2` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
[6 Dec 2005 15:12]
Matthias Urlichs
Oh yes -- whether any other permissions exist on that table does not matter; I tried both with an initial "REVOKE ALL" and a "GRANT ALL".
[6 Dec 2005 15:29]
Valeriy Kravchuk
Thank you for the additional information. I was able to repeat the problem you described on 5.0.15-nt (used just as an example, because you marked version as "all"): C:\Documents and Settings\openxs>mysql -uroot -p -P3307 test Enter password: **** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 to server version: 5.0.15-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE TABLE `cat1` ( -> `id` int(11) NOT NULL auto_increment, -> `multi` tinyint(1) NOT NULL default '0', -> `name` varchar(100) NOT NULL default '', -> `depcat` int(11) default NULL, -> PRIMARY KEY (`id`), -> UNIQUE KEY `name` (`name`), -> KEY `depcat` (`depcat`)); Query OK, 0 rows affected (0.68 sec) mysql> exit; Bye C:\Documents and Settings\openxs>mysql -uuser1 -p -P3307 test Enter password: ***** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 to server version: 5.0.15-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show tables like 'cat%'; +-----------------------+ | Tables_in_test (cat%) | +-----------------------+ | cat1 | +-----------------------+ 1 row in set (0.01 sec) mysql> optimize table cat2; ERROR 1142 (42000): INSERT command denied to user 'user1'@'localhost' for table 'cat2' So, your "Come on, please -- if table "cat1" wouldn't exist, I would get a quite different error message." is not really appropriate... Surely I asked because of some reason. But let's go on: mysql> optimize table cat1; ERROR 1142 (42000): INSERT command denied to user 'user1'@'localhost' for table 'cat1' mysql> exit Bye C:\Documents and Settings\openxs>mysql -uroot -p -P3307 test Enter password: **** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 to server version: 5.0.15-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> grant insert on cat1 to user1; Query OK, 0 rows affected (0.03 sec) mysql> exit Bye C:\Documents and Settings\openxs>mysql -uuser1 -p -P3307 test Enter password: ***** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 to server version: 5.0.15-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> optimize table cat1; +-----------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------+----------+----------+----------+ | test.cat1 | optimize | status | OK | +-----------+----------+----------+----------+ 1 row in set (0.44 sec) So, yes, INSERT is needed for InnoDB tables. But I think it is just a documentation request. This should be documented in http://dev.mysql.com/doc/refman/5.0/en/privileges-provided.html and, surely, in http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html.
[6 Dec 2005 15:37]
Matthias Urlichs
Well, I disagree about the Documentation-Only part -- OPTIMIZE is not transaction-friendly, and potentially disruptive. It should therefore not be available to users with simple select/insert/update/delete privileges.
[3 May 2006 17:56]
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 product(s). Additional info: Also noted in manual: SELECT/INSERT are required for ANALYZE TABLE and REPAIR TABLE as well.