Bug #117051 The table-level SELECT permission will override the database-level DELETE and UPDATE permissions."

• Submitted: 27 Dec 2024 3:57
• Reporter: tianbiao wang
• Status: Open
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.7.44 8.0.34
• OS: Any
• CPU Architecture: Any
• Tags: privileges

[27 Dec 2024 3:57] tianbiao wang

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>