revision-id: c5f1c71828ad85f38f47ff5b214711a9b36a366d (mariadb-10.2.31-630-gc5f1c71) parent(s): a244be7044534a59199a0f11e856be37ba6f02c8 author: Igor Babaev committer: Igor Babaev timestamp: 2020-12-17 10:09:16 -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_not_embedded.result | 48 +++++++++++++++++++ mysql-test/t/cte_nonrecursive_not_embedded.test | 58 +++++++++++++++++++++++ sql/sql_cte.cc | 18 ++++++- 3 files changed, 122 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/cte_nonrecursive_not_embedded.result b/mysql-test/r/cte_nonrecursive_not_embedded.result new file mode 100644 index 0000000..c96a1ec --- /dev/null +++ b/mysql-test/r/cte_nonrecursive_not_embedded.result @@ -0,0 +1,48 @@ +# +# 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 TABLE t2 (a int) ENGINE=MYISAM; +INSERT INTO t2 VALUES (2), (8), (4); +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 +WITH +cte1 AS +(SELECT a FROM t2), +cte2 AS +(SELECT cte1.a FROM t2,cte1 WHERE cte1.a = t2.a), +cte3 AS +(SELECT cte2.a FROM t2,cte1,cte2 WHERE cte1.a = t2.a AND t2.a = cte2.a), +cte4 AS +(SELECT cte2.a FROM t2,cte2 WHERE cte2.a = t2.a) +SELECT * FROM cte4 as r; +ERROR 42000: SELECT command denied to user 'u1'@'localhost' for table 't2' +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_not_embedded.test b/mysql-test/t/cte_nonrecursive_not_embedded.test new file mode 100644 index 0000000..e80baea --- /dev/null +++ b/mysql-test/t/cte_nonrecursive_not_embedded.test @@ -0,0 +1,58 @@ +-- source include/not_embedded.inc + +--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 TABLE t2 (a int) ENGINE=MYISAM; +INSERT INTO t2 VALUES (2), (8), (4); + + +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; + +--error ER_TABLEACCESS_DENIED_ERROR +WITH +cte1 AS +(SELECT a FROM t2), +cte2 AS +(SELECT cte1.a FROM t2,cte1 WHERE cte1.a = t2.a), +cte3 AS +(SELECT cte2.a FROM t2,cte1,cte2 WHERE cte1.a = t2.a AND t2.a = cte2.a), +cte4 AS +(SELECT cte2.a FROM t2,cte2 WHERE cte2.a = t2.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: