Permissions error running EXPLAIN on MySQL VIEW

Question

I have a view defined in a MySQL 5.0 database as:

CREATE OR REPLACE ALGORITHM=MERGE 
DEFINER=db1_user@'%' SQL SECURITY DEFINER 
VIEW db2.data_view AS SELECT * FROM db1.data_table;

This is done to give users of db2 access to this single table in db1, and works for SELECT queries:

[db2_user@db2]> select name from data_view limit 1;
+----------------+
| name           |
+----------------+
| slartibartfast |
+----------------+
1 row in set (0.00 sec)

However, when I try and explain the same query, I get:

[db2_user@db2]> explain select name from data_view limit 1;
ERROR 1345 (HY000): EXPLAIN/SHOW can not be issued; lacking privileges for 
underlying table

I granted the db2 user the SHOW VIEW privilege for the view, but I get the same error for EXPLAIN.

GRANT SHOW VIEW ON `db2`.`data_view` TO 'db2_user'@'%';

What is required to allow users with access to the view to run EXPLAIN on it?

score 4 · Answer 1 · answered Oct 29 '14 at 23:12

4

You are trying to explain the query which is different than show create view. Explaining a query is same as selecting it (talking from privilege point of view), hence a select privilege shall be enough for it

GRANT select, SHOW VIEW ON `db2`.`data_view` TO 'db2_user'@'%';
explain select name from data_view limit 1;

answered Oct 29 '14 at 23:12

Payam

479
2
18

Thank you for the response. Unfortunately it does not resolve the issue -- I see the same error. – tcarobruce Nov 04 '14 at 23:23
I'm afraid there is no particular grant option for "explaining" a query in mysql. So do you get that error even if you grant all the options to the user? – Payam Nov 05 '14 at 23:35

score 1 · Answer 2 · answered Jun 02 '16 at 06:14

1

Note that the behavior here appears to have changed between MySQL 5.0 and 5.5, as described in MySQL bug 64198.

answered Jun 02 '16 at 06:14

Matthew Simon Cavalletto

384
3
4

score 0 · Answer 3 · answered Feb 22 '22 at 22:00

I ran into a failure to run an EXPLAIN on a query and found the reason in the MySQL 5.7 documentation:

"The SELECT privilege is needed for tables or views used with EXPLAIN, including any underlying tables in view definitions. "

The needing SELECT permission on the underlying tables of the views was the problem for me. We wall our data off from our lower level analysts by exposing views that they have SELECT rights for but giving the analysts no rights to the underlying tables. This works well for us but makes it impossible for our analysts to use EXPLAIN to optimize their queries. Maybe some of you are running into the same problem.

Permissions error running EXPLAIN on MySQL VIEW

3 Answers3