Posted by Baron Schwartz on Jul 28, 2014 9:27:00 AM

What Privileges Does EXPLAIN Require in MySQL?

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 resultset 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!

Pic Cred

Subscribe to Email Updates