Bug #104238 Grants in prepare with 2 triggers on the same table
Submitted: 7 Jul 2021 15:16 Modified: 8 Jul 2021 6:23
Reporter: lalit Choudhary Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:8.0.24 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[7 Jul 2021 15:16] lalit Choudhary
Description:
Starting from 8.0.22 executing prepare with below case failed due to privileges issue.

Example:
 mysql 8.0.24

create database db1;
use db1;
CREATE TABLE `table1` (`id` int unsigned NOT NULL AUTO_INCREMENT, `test` varchar(40) DEFAULT NULL, PRIMARY KEY (`id`) );
CREATE TABLE `table2` (`ID` int);
CREATE USER 'user1'@'127.0.0.1' IDENTIFIED BY 'msandbox';
CREATE USER 'user2'@'127.0.0.1' IDENTIFIED BY 'msandbox';
GRANT SELECT, INSERT, UPDATE ON `table1` TO 'user1'@'127.0.0.1';
GRANT INSERT, UPDATE ON `table2` TO `user2`@`127.0.0.1`;
GRANT SELECT ON `db1`.`table2` TO 'user2'@'127.0.0.1';
GRANT TRIGGER ON `table2` TO 'user1'@'127.0.0.1';
CREATE DEFINER=`user1`@`127.0.0.1` TRIGGER `TestTrigger` AFTER UPDATE ON `table2` FOR EACH ROW INSERT INTO table1 (`test`) VALUES ('dsadsa') ON DUPLICATE KEY UPDATE test='dsadsadsadsa';
CREATE DEFINER=`user1`@`127.0.0.1` TRIGGER `TestTrigger2` AFTER UPDATE ON `table2` FOR EACH ROW INSERT INTO table1 (`test`) VALUES ('value');

login as user2:

use db1
show tables;
INSERT INTO table2 (`ID`) VALUES (1);
INSERT INTO table2 (`ID`) VALUES (2);
INSERT INTO table2 (`ID`) VALUES (3);
INSERT INTO table2 (`ID`) VALUES (4);
INSERT INTO table2 (`ID`) VALUES (5);
UPDATE table2 SET ID=5 where id=4;

PREPARE ST1 FROM "UPDATE table2 SET ID=?";
SET @IDD = 5;
EXECUTE ST1 USING @IDD;

mysql [127.0.0.1] {user2} (db1) > EXECUTE ST1 USING @IDD;
ERROR 1142 (42000): SELECT command denied to user 'user2'@'localhost' for table 'table1'
mysql [127.0.0.1] {user2} (db1) > select version();
+-----------+
| version() |
+-----------+
| 8.0.24    |
+-----------+
1 row in set (0.00 sec)

The same test work fine without error in MySQL 8.0.21 version.

mysql [127.0.0.1] {user2} (db1) > PREPARE ST1 FROM "UPDATE table2 SET ID=?";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql [127.0.0.1] {user2} (db1) > SET @IDD = 5;
Query OK, 0 rows affected (0.00 sec)

mysql [127.0.0.1] {user2} (db1) > EXECUTE ST1 USING @IDD;
Query OK, 3 rows affected (0.02 sec)
Rows matched: 5  Changed: 3  Warnings: 0

mysql [127.0.0.1] {user2} (db1) > select version();
+-----------+
| version() |
+-----------+
| 8.0.21    |
+-----------+
1 row in set (0.00 sec)

How to repeat:
above
[7 Jul 2021 15:17] lalit Choudhary
checking MySQL 8.0.22 release notes: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-22.html

I see only the following change for privileges if it's related. 

A SELECT ... FOR SHARE statement now only requires the SELECT privilege. Previously, the SELECT privilege was required with at least one of the DELETE, LOCK TABLES, or UPDATE privileges. (Bug #31096384, Bug #99101)
[8 Jul 2021 6:23] MySQL Verification Team
Hello Lalit,

Thank you for the report and test case.
Imho this is duplicate of Bug #104168, please see Bug #104168

regards,
Umesh