Bug #75180 Improve privilege granularity of optimize table
Submitted: 11 Dec 2014 13:22
Reporter: Tim Smith Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:5.6.20/5.7 OS:Any
Assigned to: CPU Architecture:Any

[11 Dec 2014 13:22] Tim Smith
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.