Bug #71908 create view on view using prepare-execute lead to error
Submitted: 3 Mar 2014 10:52 Modified: 24 Oct 2015 18:06
Reporter: xiaobin lin (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.5+ OS:Any
Assigned to:
Tags: create view, execute, prepare

[3 Mar 2014 10:52] xiaobin lin
Description:
create view on a view seems to require an privilege on a NULL database.

How to repeat:
An test-case bellow.

$ cat t/prepare_exec_create_view.test 
create database test2;
GRANT all privileges ON test2.* TO test@localhost;
connect(con1, localhost, test, , test);

connection con1;
use test2;
create table ta(a int);
prepare stmt from "create view v1 as select a from ta";
execute stmt;
DEALLOCATE PREPARE stmt;

prepare stmt2 from "create view v2 as select a from v1";
execute stmt2;
DEALLOCATE PREPARE stmt2;

connection default;
DROP USER test@localhost;
drop database test2;

$ cat r/prepare_exec_create_view.result 
create database test2;
GRANT all privileges ON *.* TO test@localhost;
use test2;
create table ta(a int);
prepare stmt from "create view v1 as select a from ta";
execute stmt;
DEALLOCATE PREPARE stmt;
prepare stmt2 from "create view v2 as select a from v1";
execute stmt2;
DEALLOCATE PREPARE stmt2;
DROP USER test@localhost;
drop database test2;

Suggested fix:
not look into it yet
[4 Mar 2014 8:45] Umesh Shastry
Hello Xiaobin,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh
[2 Mar 2015 11:31] Frodo Looijaard
Still present in 5.6.23
[2 Mar 2015 11:43] Frodo Looijaard
Work-around: grant select rights on all database objects:
  GRANT SELECT ON *.* TO `user`;
[24 Oct 2015 18:06] Erlend Dahl
Fixed in 5.6.24 under the heading of Bug#20030284