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'@'%'
SELECT
privileges on the tables? Speculation: you can'tJOIN
what you can't read. – Michael - sqlbot Dec 17 '16 at 21:35DELETE
? – Rick James Dec 18 '16 at 4:18