Description:
Please improve privilege granularity of optimize table.
The manual http://dev.mysql.com/doc/refman/5.6/en/optimize-table.html says "This statement requires SELECT and INSERT privileges for the table." However, the SELECT and INSERT privileges are the basic operations of a table and are usually granted to a user. Therefore, a user can issue "optimize table" too easily and cannot be prevented by DBA via the privileges control mechanism in MySQL. It causes unnecessary performance issue for a single MySQL instance and also leads to huge replication lag if there is a MySQL slave. The phenomenon mentioned above is very annoying for a DBA.
How to repeat:
1. Give a user has SELECT and INSERT privileges to a table.
2. The user with the privileges above can issue optimize table to the table if the user has SELECT and INSERT on the table.
Suggested fix:
Make "optimize table" as a new privilege to prevent a user with SELECT and INSERT privileges to optimize a table. Therefore, the DBA can safely grants a user SELECT and INSERT privileges but not grants the optimize privilege to prevent the user optimize a table but the user can still select and insert the table.