Bug #20066 DELETE w/ WHERE clause requires SELECT privilege

• Submitted: 25 May 2006 13:15
• Modified: 29 May 2006 13:07
• Reporter: Carsten Pedersen
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.21-community-nt-log
• OS: Windows (Win XP)
• CPU Architecture: Any

[25 May 2006 13:15] Carsten Pedersen

Description:
When using WHERE in a DELETE statement, the DELETE privilege is not enough; you need the SELECT privilege as well.

How to repeat:
#as root:
drop database if exists deltest;
create database deltest;
use deltest;
create table deltest (i int primary key);
insert into deltest values (1), (2);
create user 'deltest'@'localhost' identified by 'p';
grant delete on deltest.* to "deltest"@"localhost";

#as deltest@localhost:
use deltest;
# this doesn't work; tells us that SELECT is denied:
delete from deltest.deltest where i=1;
# this does work:
delete from deltest;

[29 May 2006 13:39] MySQL Verification Team

Below the explanation why delete..where does a select quoting PeterG:

You are effectively selecting, that is, you can find out what
the values of column 'i' are. For example, suppose you want to
know what Joe's salary is. You say:
DELETE FROM t WHERE name='Joe' AND salary < 10;
/* Look to see how many rows were deleted. */
ROLLBACK;
DELETE FROM t WHERE name='Joe' AND salary < 20;
/* Look to see how many rows were deleted. */
ROLLBACK;
... and so on until you see that one row was deleted.
Therefore it is normal to say that you need SELECT privilege
on the columns that you use in a WHERE clause.