MySQL command denied for DELETE with JOIN

Question

I have a DELETE query that joins three tables. I have explicit GRANTS for the DELETE on the three tables, but not the database. I'm getting a ERROR 1142 (42000): DELETE command denied to user error when the query runs. I suspect this is happening because of the joins (even though the user has grants to delete from all three tables). I can delete from any one of the tables without issue but with the join, it fails. Does anyone know if there is a way to work around this, allowing the query to succeed?

Edit: more information

The query that I'm attempting to run is:

DELETE FROM t1, t2, t3
USING d1.table1 t1
LEFT JOIN d1.table2 t2 ON t1.id = t2.t1_id AND t2.t2_start_date = 1481554800
LEFT JOIN d1.table3 t3 ON t3.t2_id = t2.id
WHERE t1.xyz = 18

The error I'm getting:

SQLSTATE[42000]: Syntax error or access violation: 1142 DELETE command denied to user 'username'@'ip_address' for table 'table1'

The grants I have in place are:

GRANT SELECT, INSERT, UPDATE, DELETE ON `d1`.`table1` TO 'username'@'%'
GRANT SELECT, INSERT, UPDATE, DELETE ON `d1`.`table2` TO 'username'@'%'
GRANT SELECT, INSERT, UPDATE, DELETE ON `d1`.`table3` TO 'username'@'%'

Answer 1

I've finally tracked down the cause of the permissions issue I'm running into. It turns out that the problem was actually a bug in MySQL.

We're running our infrastructure on AWS Aurora which runs MySQL version 5.10. To set up for providing more information, I tried to recreate the problem locally in a docker container, using the latest version of MySQL (5.7.16). To my surprise, I couldn't reproduce this issue locally, which led me to believe the problem has to be with the difference in MySQL version.

Going through the MySQL version logs, I noticed that in MySQL version 5.6.27, a bug was fixed:

DELETE could check privileges for the wrong database when table aliases were used. (Bug #20777016)

This sounded promising so I modified the query to not use aliases and sure enough, the permissions issues went away.

Answer 2

Without knowing the exact query you're running, I would double-check your mysql.user table, you might have entries with the same username and different hostnames, like 'username'@'localhost' and 'username'@'%'.

I like to FLUSH after change privileges too but AFAIK this is not mandatory unless a manual INSERT/DELETE occurs on the user table.