I have an issue with MySQL that looks a bit like this old question.
In our case the problem occurs for a delete command. The user has the privileges to delete from a certain table (lets say test.data) and it's possible to delete records from this table like so:
DELETE FROM test.data WHERE id = 43
This works regardless of the current schema for the user. But if I rewrite this statement to the following:
DELETE td FROM test.data td WHERE td.id = 43
it only works from the test schema. From any other schema I get an error "DELETE command denied to user ...". So it looks like the privilege system gets confused when I try to use table aliases in the delete statement.
I needed the table alias because the actual delete statement involved multiple tables with join conditions. As a work around I rewrote the statement to use a subquery instead of a join, but I would really like to know if I'm missing something important here, or if this is more like a bug in the MySQL privilege system.
