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
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