Description:
Optimize table is a costly operation in a database, but an user with insert and select privilege can able to run optimize.
How to repeat:
mysql> create database foo;
Query OK, 1 row affected (0.01 sec)
mysql> use foo;
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.15 sec)
mysql> create user 'appuser'@'localhost' identified by 'appUser';
Query OK, 0 rows affected (0.02 sec)
mysql> grant select,insert on foo.t1 to appuser@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> ^DBye
==========================================================================
root@proxylab:/home/vagrant# mysql -u appuser -p
mysql> show grants;
+-------------------------------------------------------------+
| Grants for appuser@localhost |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO `appuser`@`localhost` |
| GRANT SELECT, INSERT ON `foo`.`t1` TO `appuser`@`localhost` |
+-------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> optimize table foo.t1;
+--------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------+----------+----------+-------------------------------------------------------------------+
| foo.t1 | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| foo.t1 | optimize | status | OK |
+--------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.12 sec)
Suggested fix:
This needs to be restricted and optimize should need alter privilege.
