Thursday, January 29, 2015

MySQL: ERROR 1345 (HY000): EXPLAIN/SHOW can not be issued; lacking privileges for underlying table

While performing select on view one of db user got below error

ERROR 1345 (HY000): EXPLAIN/SHOW can not be issued; lacking privileges for underlying table

Tough user was having select access on required database.  I was surprise with error.  After trying for some time, I got answer. This error occurs in versions after MySQL 5.5.16.  After MySQL version upgraded to MySQL 5.5.19, occurrences of this error started.  I got to know, it’s an intended change of behavior.
Simply having the SELECT privilege on the view/tables should not be enough to see the information returned by EXPLAIN SELECT - e.g. the view's structure.

Now users also require the “SHOW VIEW” privilege on the view in order to do EXPLAIN SELECT on View.