0

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'@'%'
  • What about SELECT privileges on the tables? Speculation: you can't JOIN what you can't read. – Michael - sqlbot Dec 17 '16 at 21:35
  • Are you deleting from all 3 tables with the one DELETE? – Rick James Dec 18 '16 at 4:18
2

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.

0

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.

  • Hello, thank you for taking the time to answer the question. I've updated the original question with the query and error message. – Beshoy Girgis Dec 18 '16 at 3:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.