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)
• Status: Closed
• Category: MySQL Server: Security: Privileges
• Severity: S3 (Non-critical)
• Version: 5.5+
• OS: Any
• CPU Architecture: Any
• 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] MySQL Verification Team

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