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.

1 comment:

Abhishek Gupta said...

Correct.
But if the underlying table lies in another database then the user need SELECT on that table too.

CREATE TABLE db2.table2 (id int not null, name varchar(20), primary key (id));
CREATE VIEW db1.view1 AS SELECT * FROM db2.table2;
GRANT SELECT ON db1.view1 TO 'user2'@'localhost';

-- These additional grant will be needed to prevent the 1345 error
GRANT SHOW VIEW ON db1.view1 TO 'user2'@'localhost';
GRANT SELECT ON db2.table2 TO 'user2'@'localhost';