revision-id: f792bd168cc62d64b4c0edb6251b6b8a1352c98a (mariadb-10.2.31-630-gf792bd1) parent(s): a244be7044534a59199a0f11e856be37ba6f02c8 author: Igor Babaev committer: Igor Babaev timestamp: 2020-12-16 23:37:52 -0800 message: MDEV-20751 Permission Issue With Nested CTEs Due to this bug the server reported bogus messages about lack of SELECT privileges for base tables used in the specifications of CTE tables. It happened only if such a CTE were referred to at least twice. For any non-recursive reference to CTE that is not primary the specification of the CTE is cloned. The function check_table_access() is called for such reference. The function checks privileges of the tables referenced in the specification. As no name resolution was performed for CTE references whose definitions occurred outside the specification before the call of check_table_access() that was supposed to check the access rights of the underlying tables these references were considered as references to base tables rather than references to CTEs. Yet for CTEs as well as for derived tables no privileges are needed and thus cannot be granted. The patch ensures proper name resolution of all references to CTEs before any acl checks. Approved by Oleksandr Byelkin <sanja@mariadb.com> --- mysql-test/r/cte_nonrecursive.result | 34 ++++++++++++++++++++++++++++++ mysql-test/t/cte_nonrecursive.test | 40 ++++++++++++++++++++++++++++++++++++ sql/sql_cte.cc | 18 ++++++++++++++-- 3 files changed, 90 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result index a47fdcd..be3f33f 100644 --- a/mysql-test/r/cte_nonrecursive.result +++ b/mysql-test/r/cte_nonrecursive.result @@ -1725,4 +1725,38 @@ drop table db1.t1; drop database db1; create database test; use test; +# +# MDEV-20751: query using many CTEs with grant_tables enabled +# +connection default; +CREATE DATABASE db; +USE db; +CREATE TABLE t1 (a int) ENGINE=MYISAM; +INSERT INTO t1 VALUES (3), (7), (1); +CREATE USER 'u1'@'localhost'; +GRANT USAGE ON db.* TO 'u1'@'localhost'; +GRANT SELECT ON db.t1 TO 'u1'@'localhost'; +FLUSH PRIVILEGES; +connect u1,'localhost',u1,,; +connection u1; +USE db; +WITH +cte1 AS +(SELECT a FROM t1), +cte2 AS +(SELECT cte1.a FROM t1,cte1 WHERE cte1.a = t1.a), +cte3 AS +(SELECT cte2.a FROM t1,cte1,cte2 WHERE cte1.a = t1.a AND t1.a = cte2.a), +cte4 AS +(SELECT cte2.a FROM t1,cte2 WHERE cte2.a = t1.a) +SELECT * FROM cte4 as r; +a +3 +7 +1 +disconnect u1; +connection default; +DROP USER 'u1'@'localhost'; +DROP DATABASE db; +USE test; # End of 10.2 tests diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test index 0174ddb..76fe080 100644 --- a/mysql-test/t/cte_nonrecursive.test +++ b/mysql-test/t/cte_nonrecursive.test @@ -1230,4 +1230,44 @@ drop database db1; create database test; use test; +--echo # +--echo # MDEV-20751: query using many CTEs with grant_tables enabled +--echo # + +--connection default + +CREATE DATABASE db; +USE db; + +CREATE TABLE t1 (a int) ENGINE=MYISAM; +INSERT INTO t1 VALUES (3), (7), (1); + +CREATE USER 'u1'@'localhost'; +GRANT USAGE ON db.* TO 'u1'@'localhost'; +GRANT SELECT ON db.t1 TO 'u1'@'localhost'; +FLUSH PRIVILEGES; + +--connect (u1,'localhost',u1,,) +--connection u1 +USE db; + +WITH +cte1 AS +(SELECT a FROM t1), +cte2 AS +(SELECT cte1.a FROM t1,cte1 WHERE cte1.a = t1.a), +cte3 AS +(SELECT cte2.a FROM t1,cte1,cte2 WHERE cte1.a = t1.a AND t1.a = cte2.a), +cte4 AS +(SELECT cte2.a FROM t1,cte2 WHERE cte2.a = t1.a) +SELECT * FROM cte4 as r; + +--disconnect u1 +--connection default + +DROP USER 'u1'@'localhost'; +DROP DATABASE db; + +USE test; + --echo # End of 10.2 tests diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index dd764da..e07a525 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -864,8 +864,6 @@ st_select_lex_unit *With_element::clone_parsed_spec(THD *thd, goto err; spec_tables_tail= tbl; } - if (check_table_access(thd, SELECT_ACL, spec_tables, FALSE, UINT_MAX, FALSE)) - goto err; if (spec_tables) { if (with_table->next_global) @@ -891,6 +889,22 @@ st_select_lex_unit *With_element::clone_parsed_spec(THD *thd, with_select)); if (check_dependencies_in_with_clauses(lex->with_clauses_list)) res= NULL; + /* + Resolve references to CTE from the spec_tables list that has not + been resolved yet. + */ + for (TABLE_LIST *tbl= spec_tables; + tbl; + tbl= tbl->next_global) + { + if (!tbl->with) + tbl->with= with_select->find_table_def_in_with_clauses(tbl); + if (tbl == spec_tables_tail) + break; + } + if (check_table_access(thd, SELECT_ACL, spec_tables, FALSE, UINT_MAX, FALSE)) + goto err; + lex->sphead= NULL; // in order not to delete lex->sphead lex_end(lex); err: