Merge remote-tracking branch 'origin/10.2' into bb-10.2-ext

1 files changed, 58 insertions, 0 deletions

diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result
index d0e42cf4042..96884e848ba 100644
--- a/mysql-test/r/cte_nonrecursive.result
+++ b/mysql-test/r/cte_nonrecursive.result
@@ -1147,3 +1147,61 @@ SELECT * FROM cte_test;
 a
 1
 DROP VIEW cte_test;
+#
+# MDEV-13453: privileges checking for CTE
+#
+create database db;
+use db;
+create table t1 (i int);
+insert into t1
+values (3), (7), (1), (4), (2), (3), (1);
+create table t2 (a int, b int);
+insert into t2
+values (3,10), (7,11), (1,17), (4,15), (2,11), (3,10), (1,15);
+create user foo@localhost;
+grant SELECT on db.t1 to foo@localhost;
+grant SELECT(a) on db.t2 to foo@localhost;
+connect  con1,localhost,foo,,;
+use db;
+with cte as (select * from t1 where i < 4)
+select * from cte;
+i
+3
+1
+2
+3
+1
+with cte as (select * from t1 where i < 4 group by i)
+select * from cte;
+i
+1
+2
+3
+with cte as (select * from t1 where i < 4)
+select * from cte cte1 where i < 2 union select * from cte cte2 where i > 2;
+i
+1
+3
+with cte as (select * from t1 where i < 4 group by i)
+select * from cte cte1 where i < 2 union select * from cte cte2 where i > 2;
+i
+1
+3
+with cte as (select b from t2 where a < 4)
+select * from cte cte1 where b < 15 union select * from cte cte2 where b > 15;
+ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'b' in table 't2'
+with cte as (select a from t2 where a < 4)
+select * from cte cte1 where a < 2 union select * from cte cte2 where a > 2;
+a
+1
+3
+connection default;
+revoke SELECT on db.t1 from foo@localhost;
+connection con1;
+with cte as (select * from t1 where i < 4)
+select * from cte;
+ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
+disconnect con1;
+connection default;
+drop database db;
+drop user foo@localhost;