|
Based on the question posted at stackoverflow by Diogo Melo:
https://stackoverflow.com/questions/45510362/error-1142-42000-select-command-denied-for-with-recursive-statement-on-ma
When a query is executed via CTE, it's not enough to have normal privileges for the query, the user must also have SELECT privileges for the CTE itself, which are impossible to grant.
create database db;
|
use db;
|
create table t1 (i int);
|
|
create user foo@localhost;
|
grant SELECT on db.t1 to foo@localhost;
|
|
--connect (con1,localhost,foo,,)
|
use db;
|
with cte as (select * from t1) select * from cte;
|
|
# Cleanup
|
--disconnect con1
|
|
--connection default
|
drop database db;
|
drop user foo@localhost;
|
CURRENT_TEST: bug.t6
|
mysqltest: At line 10: query 'with cte as (select * from t1) select * from cte' failed: 1142: SELECT command denied to user 'foo'@'localhost' for table 'cte'
|
|