Here’s a simple question that I wasn’t able to solve with Google or the MySQL documentation (which is normally excellent). Perhaps it’s mentioned somewhere, but I can’t find it. Here’s the question:
What privileges must a user have to run EXPLAIN for a query?
Is there an
EXPLAIN privilege? No, there’s not.
The answer turns out to be really simple: you need the same privileges that you’d need to execute the query itself. At least, as far as I know, that’s the case. I haven’t been able to find a counter-example, and from what I know of the MySQL query execution process, this makes perfect sense.
If you’re not familiar with how
EXPLAIN works, it’s roughly like the following. The presence of the
EXPLAIN keyword sets a flag in the query plan. The query then executes, but instead of doing the work the query would normally do, it instead writes rows into the
EXPLAIN result set at certain points.
Please correct me if I’m wrong. My memory of this code is pretty vague, because the last time I really looked at it was in the MySQL 5.1 days. Just looking at the latest development tree I can see it’s changed a lot since then, which I expected – in MySQL 5.6 and later, there is a lot more functionality for
EXPLAIN. There’s JSON formatting, optimizer tracing, support for
UPDATE,
INSERT,
DELETE and so on.
This is why it’s logical that
EXPLAIN requires the same privileges as the statement itself would require: because the statement itself is executing, although in a special shortcut mode.
What have I missed? What else should be added to this topic? Let me know in the comments!
