Description:
In MySQL versions 5.7.44 and 8.0.34, when I grant the user u2 permission to insert, delete, and update all objects in a database, and then grant select permission on table t within the database, it results in the user u2 having insufficient permission to delete from table t.
How to repeat:
mysql> create database x;
mysql> use x;
mysql> create table t(a int);
mysql> insert into t values(1);
mysql> insert into t values(2);
mysql> create user u2@'%' identified by '123ABCabc';
mysql> grant insert,delete,update on x.* to u2@'%';
mysql> grant select on x.t to u2@'%';
mysql> flush privileges;
mysql> exit
mysql -uu2 -p'123ABCabc'
mysql> SHOW GRANTS FOR 'u2'@'%';
+---------------------------------------------------+
| Grants for u2@% |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO 'u2'@'%' |
| GRANT INSERT, UPDATE, DELETE ON `x`.* TO 'u2'@'%' |
| GRANT SELECT ON `x`.`t` TO 'u2'@'%' |
+---------------------------------------------------+
3 rows in set (0.00 sec)
mysql> delete from t where a=1;
ERROR 1143 (42000): SELECT command denied to user 'u2'@'localhost' for column 'a' in table 't'
mysql> select * from t where a=1;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql>
