Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2(EOL)
-
None
-
10.2.11
Description
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'
|
Attachments
Issue Links
- causes
-
-
- Closed
-
- is duplicated by
-
-
- Closed
-
- links to
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
Based on the question posted at stackoverflow:
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 grants for the CTE itself, which are impossible to grant. {code:sql} 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; {code} {noformat} 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' {noformat} |
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 grants for the CTE itself, which are impossible to grant. {code:sql} 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; {code} {noformat} 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' {noformat} |
| Description |
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 grants for the CTE itself, which are impossible to grant. {code:sql} 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; {code} {noformat} 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' {noformat} |
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. {code:sql} 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; {code} {noformat} 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' {noformat} |
| Remote Link | This issue links to "StackOverflow (Web Link)" [ 28016 ] |
| Link |
This issue is duplicated by |
| Sprint | 10.2.11 [ 203 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Assignee | Igor Babaev [ igor ] | Oleksandr Byelkin [ sanja ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
OK to push after fixing resulta and adding the comment we discussed.
| Assignee | Oleksandr Byelkin [ sanja ] | Igor Babaev [ igor ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.2.11 [ 22634 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 81913 ] | MariaDB v4 [ 152569 ] |
Seems to work on 10.2.2, so perhaps a regression?