Commands can be denied when triggers with DEFINER are used.
Description
Environment
AFFECTED CS IDs
Smart Checklist
Activity
Jira Bot September 9, 2021 at 7:56 PM
To: @Iwo Panowicz
CC: @George Lorch
Hi, I'm jira-bot, Percona's Jira automation tool. I've detected that someone from
Percona has made an edit to the Summary field of an issue that you reported.
I'm not sentient (yet) so I'm not sure whether the person fixed a typo, changed
a few words, or completely rewrote the text. In any case, it is Percona Engineering's
intention to make the Summary and Description of an issue as accurate as possible
so that we're fixing the actual problem you're encountering, and to avoid
misunderstandings about symptoms and causes.
If the current Summary does not accurately reflect the problem you are reporting,
or if you feel the change was otherwise inappropriate in some way, please add a
new comment explaining things and we'll address it as soon as we can.
This message will be added only once per issue, regardless of how many times
the Summary is edited.
message-code:summary-edited
Marcelo Altmann June 30, 2021 at 4:23 PM
This is a regression of "WL#9384: Prepare each DML statement once" -
https://github.com/mysql/mysql-server/commit/67c3c70e4895874d43434f1df556f9f30d781b48
Iwo Panowicz June 30, 2021 at 2:17 PM
Just for the records,
steps from 01 needs to be run as any user (root), steps from 02 as user02 user.
Details
Details
Assignee
Reporter
Labels
Original estimate
Time tracking
Fix versions
Affects versions
Priority
MediumSmart Checklist
Open Smart Checklist
Smart Checklist
Description:
Commands can be denied when triggers with ,,definer'' are used. It's reproducible with 8.0.25 upstream. It's not reproducible with 8.0.20.
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>