Bug #104168 Wrongly handled definer for triggers when PS used
Submitted: 30 Jun 2021 14:07 Modified: 7 Oct 2021 22:09
Reporter: Iwo P Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S2 (Serious)
Version:8.0.25 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[30 Jun 2021 14:07] Iwo P
Description:
Commands can be denieded when triggers with ,,definer'' are used. 

How to repeat:
01) Create initial schema

CREATE USER 'user01'@'%' IDENTIFIED BY 'user01';
CREATE USER 'user02'@'%' IDENTIFIED BY 'user02';
GRANT ALL PRIVILEGES ON *.* to 'user01'@'%';

CREATE DATABASE  test;
CREATE DATABASE  test01;

use test01;
CREATE TABLE c (id int primary key, a varchar(100));
INSERT INTO c SET id =1, a=now();

use test;
CREATE TABLE tbl01 (id int primary key, a varchar(100));
CREATE TABLE tbl02 (id int primary key, a varchar(100));
GRANT SELECT, UPDATE, INSERT on test.tbl01 TO 'user02'@'%';

INSERT INTO tbl01 SET id = 1, a=now();
CREATE DEFINER='user01'@'%' TRIGGER trg01 AFTER UPDATE ON tbl01 FOR EACH ROW UPDATE tbl02 SET a = (SELECT a FROM test01.c WHERE id =1) WHERE id=NEW.id;

02) Run normal and prepared statement:

mysql> update tbl01 set a = now();
Query OK, 2 rows affected (0,03 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> prepare s from 'update tbl01 set a = now()';
Query OK, 0 rows affected (0,01 sec)
Statement prepared

mysql> execute s;
ERROR 1142 (42000): SELECT command denied to user 'user02'@'localhost' for table 'c'
mysql> 

Suggested fix:
Prepared statement should not fail.

Also, it is not reprodubile with 8.0.20, and when the objects are within the same database.
[30 Jun 2021 14:17] Iwo P
Just for the records,

steps from 01 needs to be run as any user (root), steps from 02 as user02 user.
[30 Jun 2021 14:17] Iwo P
Just for the records,

steps from 01 needs to be run as any user (root), steps from 02 as user02 user.
[30 Jun 2021 14:34] MySQL Verification Team
Hello Iwo P,

Thank you for the report and feedback.

regards,
Umesh
[30 Jun 2021 14:36] MySQL Verification Team
- Lowest version checked 8.0.11 - not affected
[30 Jun 2021 16:22] Marcelo Altmann
This is a regression of "WL#9384: Prepare each DML statement once" - 
https://github.com/mysql/mysql-server/commit/67c3c70e4895874d43434f1df556f9f30d781b48
[24 Sep 2021 7:05] aaa bbb
Can you send a message when this bug would be fixed?
[7 Oct 2021 22:09] Christine Cole
Fixed as of the upcoming MySQL 8.0.28 release, and here's the proposed changelog entry from the documentation team:

When executing prepared statements that activated a trigger created with
the DEFINER clause (or a stored function), invoker privileges were used
for checking table access instead of definer privileges. This, in turn,
could cause privilege checks on tables used by the trigger or stored
function to fail.

Thank you for the bug report.