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>

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 00000000000..c96a1ec2849
--- /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 00000000000..e80baeaf591
--- /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 dd764dad7cf..e07a525f691 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: