Bug #27159 restrict permissions for table maintenance statements
Submitted: 15 Mar 2007 1:02 Modified: 24 Apr 2007 8:31
Reporter: Arjen Lentz Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S4 (Feature request)
Version:any OS:Any (any)
Assigned to: CPU Architecture:Any
Tags: grant, maintenance, Optimize, permissions, Repair

[15 Mar 2007 1:02] Arjen Lentz
Description:
Installations run into situations where users have launched 'REPAIR TABLE' from their read/write users. Docs suggest the permissions needed to run REPAIR TABLE are SELECT and INSERT.
One would like to prevent read/write users from running any table maintenance statements like 'REPAIR TABLE' but there currently seems to be no way to do so, as all regular r/w users would of course require SELECT and INSERT.

How to repeat:
# mysql -u root
mysql> CREATE TABLE test.tbl (i int);
mysql> GRANT SELECT,INSERT ON test.tbl TO user@localhost IDENTIFIED BY 'foo';
mysql> quit

# mysql -u user -pfoo
mysql> REPAIR TABLE test.tbl;

Suggested fix:
Please add additional permission requirements for table maintenance statements, especially for REPAIR TABLE. SUPER would be nice, but another (new!) permission level would also work as long as it's not one typically granted to 'normal' application read/write-capable logons.
[15 Mar 2007 11:06] Sergei Golubchik
related to bug#27145
[15 Mar 2007 11:08] Sergei Golubchik
what's wrong with a user being able to run REPAIR ?
[15 Mar 2007 22:09] Arjen Lentz
Maintenance commands are a DBA issue, they can affect application performance and server operation. They may need to be scheduled. Also, running repair with insufficient parameters may cause a table to be only partially repaired but yet seemingly be correct. A DBA can decide this, a user may not be able to. Also, no backup would automatically be made.
If the DBA wants to have table repairs without their manual intervention, they can set options like myisam-recovery with options they feel are appropriate to the installation (including a backup of the unrepaired table for later analysis or improved recovery).

The traditional blending of dev/user/dba in MySQL is no longer how the MySQL user base operates. In many organisations, these are now separate tasks assigned to people with distinct expertise. The MySQL access system needs to reflect this need, as other RDBMS do.
[16 Mar 2007 5:49] MySQL Verification Team
As long as KILL'ing a REPAIR TABLE can cause the table to become marked as corrupted, we need higher privileges for that REPAIR TABLE.  Otherwise the user can cause a DoS by making the table unavailable to other users....
[16 Mar 2007 7:57] Sergei Golubchik
Any user with SELECT and INSERT privileges can "affect application
performance and server operation". What can REPAIR do that INSERT/SELECT cannot ?
[24 Apr 2007 8:31] Valeriy Kravchuk
REPAIR table re-creates indexes in some cases. CREATE INDEX (mapped to ALTER TABLE) requires ALTER and CREATE table privileges also (http://dev.mysql.com/doc/refman/4.1/en/alter-table.html). So, to be consistent, we should require the same ALTER and CREATE privileges from those who want to run REPAIR.

This is a reasonable feature request.
[24 Apr 2007 9:04] Sergei Golubchik
Nope. REPAIR does not create or drop indexes. It cannot require privileges for actions it doesn't do.
[7 Feb 2008 15:45] Simon Day
Given that repair table can take the use_frm option which has the following warnings in the Mysql documentation (http://dev.mysql.com/doc/refman/5.1/en/repair-table.html)

Use this mode only if you cannot use regular REPAIR modes. The .MYI header contains important table metadata (in particular, current AUTO_INCREMENT value and Delete link) that are lost in REPAIR ... USE_FRM. Don't use USE_FRM if the table is compressed because this information is also stored in the .MYI file.

Do not use USE_FRM if your table was created by a different version of the MySQL server than the one you are currently running. Doing so risks the loss of all rows in the table. It is particularly dangerous to use USE_FRM after the server returns this message:

If a user with select and insert privileges is allowed to do this then they also gain delete/truncate privileges.

At the very least this is a bug and possibly a security problem

Rather than shoehorning permissions for repair into other categories (which at the very least should require select, insert AND delete) a far more senisble option would be to create a privilege specificially for this so that a non root user can be granted this without allowing normal users such a dangerous tool