revision-id: f2bd42343b5767b8e6c5c957b501acbcc8b8af3a (mariadb-10.2.31-602-gf2bd423) parent(s): 178d32f03b84004af3611aab7c6fb9ce0b1388f4 author: Igor Babaev committer: Igor Babaev timestamp: 2020-12-02 10:08:01 -0800 message: MDEV-24314 Unexpected error message when selecting from view that uses mergeable derived table Do not check privileges for derived tables/CTEs and their fields. --- mysql-test/r/view.result | 35 +++++++++++++++++++++++++++++++++++ mysql-test/t/view.test | 30 ++++++++++++++++++++++++++++++ sql/sql_base.cc | 1 + sql/sql_parse.cc | 3 +++ 4 files changed, 69 insertions(+) diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 994d594..0da11c7 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -6767,5 +6767,40 @@ DROP PROCEDURE sp; DROP VIEW v1; DROP TABLE t1; # +# MDEV-24314: create view with derived table without default database +# +drop database test; +create database db1; +create table db1.t1 (a int); +insert into db1.t1 values (3),(7),(1); +create view db1.v1 as select * from (select * from db1.t1) t; +show create view db1.v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db1`.`v1` AS select `t`.`a` AS `a` from (select `db1`.`t1`.`a` AS `a` from `db1`.`t1`) `t` latin1 latin1_swedish_ci +select * from db1.v1; +a +3 +7 +1 +drop view db1.v1; +prepare stmt from " +create view db1.v1 as select * from (select * from db1.t1) t; +"; +execute stmt; +deallocate prepare stmt; +show create view db1.v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db1`.`v1` AS select `t`.`a` AS `a` from (select `db1`.`t1`.`a` AS `a` from `db1`.`t1`) `t` latin1 latin1_swedish_ci +select * from db1.v1; +a +3 +7 +1 +drop view db1.v1; +drop table db1.t1; +drop database db1; +create database test; +use test; +# # End of 10.2 tests # diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index cd5cc6e..2486887 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -6492,5 +6492,35 @@ DROP VIEW v1; DROP TABLE t1; --echo # +--echo # MDEV-24314: create view with derived table without default database +--echo # + +drop database test; + +create database db1; +create table db1.t1 (a int); +insert into db1.t1 values (3),(7),(1); + +create view db1.v1 as select * from (select * from db1.t1) t; +show create view db1.v1; +select * from db1.v1; +drop view db1.v1; + +prepare stmt from " +create view db1.v1 as select * from (select * from db1.t1) t; +"; +execute stmt; +deallocate prepare stmt; +show create view db1.v1; +select * from db1.v1; +drop view db1.v1; + +drop table db1.t1; +drop database db1; + +create database test; +use test; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 590073b..6222736 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -5795,6 +5795,7 @@ find_field_in_table_ref(THD *thd, TABLE_LIST *table_list, #ifndef NO_EMBEDDED_ACCESS_CHECKS /* Check if there are sufficient access rights to the found field. */ if (check_privileges && + !table_list->is_derived() && check_column_grant_in_table_ref(thd, *actual_table, name, length)) fld= WRONG_GRANT; else diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 0166c45..6e1d36b 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -6639,6 +6639,9 @@ check_access(THD *thd, ulong want_access, const char *db, ulong *save_priv, bool check_single_table_access(THD *thd, ulong privilege, TABLE_LIST *tables, bool no_errors) { + if (tables->is_derived()) + return 0; + Switch_to_definer_security_ctx backup_sctx(thd, tables); const char *db_name;