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: 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'@'%'