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.
2 comments:
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';
This helped, thank you!
Post a Comment