RHEL7.

Wow, I just tried to create a role without having the underscore in the name. That new role appears to work with CTEs. Can you confirm?

If having an underscore in the name of the role messes up the ability to create CTEs, then why is MariaDB allowing it (the creation of the underscored role)?

You are correct. The CTE must have been cached. When I recreated everything from scratch, the new CTE did not work.

Are there plans to have roles work with CTEs in a future release?

Roles in general work with CTE. There is a general problem with CTE and permissions (regardless roles), described in MDEV-13453, possibly you are hitting it.

Please consider the following example.

MariaDB [(none)]> create database db1;

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database db2;

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create table db1.t1 (i int);

Query OK, 0 rows affected (0.19 sec)

MariaDB [(none)]> create user foo@localhost;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> create role r_foo;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant select on db1.t1 to r_foo;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant r_foo to foo@localhost;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant all on db2.* to foo@localhost;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> use db2

Database changed

MariaDB [db2]> show grants;

+------------------------------------------------------+

| Grants for foo@localhost                             |

+------------------------------------------------------+

| GRANT r_foo TO 'foo'@'localhost'                     |

| GRANT USAGE ON *.* TO 'foo'@'localhost'              |

| GRANT ALL PRIVILEGES ON `db2`.* TO 'foo'@'localhost' |

+------------------------------------------------------+

3 rows in set (0.00 sec)

MariaDB [db2]> with cte as (select * from db1.t1) select * from cte;

ERROR 1142 (42000): SELECT command denied to user 'foo'@'localhost' for table 't1'

MariaDB [db2]> set role r_foo;

Query OK, 0 rows affected (0.00 sec)

MariaDB [db2]> show grants;

+------------------------------------------------------+

| Grants for foo@localhost                             |

+------------------------------------------------------+

| GRANT r_foo TO 'foo'@'localhost'                     |

| GRANT USAGE ON *.* TO 'foo'@'localhost'              |

| GRANT ALL PRIVILEGES ON `db2`.* TO 'foo'@'localhost' |

| GRANT USAGE ON *.* TO 'r_foo'                        |

| GRANT SELECT ON `db1`.`t1` TO 'r_foo'                |

+------------------------------------------------------+

5 rows in set (0.00 sec)

MariaDB [db2]> with cte as (select * from db1.t1) select * from cte;

Empty set (0.00 sec)

But if we try to switch the default database to db1 (which we can do), it will stop working. This is the bug MDEV-13453.

MariaDB [db2]> use db1;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [db1]> with cte as (select * from db1.t1) select * from cte;

ERROR 1142 (42000): SELECT command denied to user 'foo'@'localhost' for table 'cte'

It's unrelated to roles, same happens if you grant access directly to the user

MariaDB [(none)]> grant select on db1.t1 to foo@localhost;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants;

+------------------------------------------------------+

| Grants for foo@localhost                             |

+------------------------------------------------------+

| GRANT r_foo TO 'foo'@'localhost'                     |

| GRANT USAGE ON *.* TO 'foo'@'localhost'              |

| GRANT ALL PRIVILEGES ON `db2`.* TO 'foo'@'localhost' |

| GRANT SELECT ON `db1`.`t1` TO 'foo'@'localhost'      |

+------------------------------------------------------+

4 rows in set (0.00 sec)

MariaDB [(none)]> use db2;

Database changed

MariaDB [db2]> with cte as (select * from db1.t1) select * from cte;

Empty set (0.00 sec)

MariaDB [db2]> use db1;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [db1]> with cte as (select * from db1.t1) select * from cte;

ERROR 1142 (42000): SELECT command denied to user 'foo'@'localhost' for table 'cte'

Does it correlate with what you observe?
If it does, it's a duplicate of MDEV-13453 – and yes, we certainly plan to have it fixed.

I don't change the default database, so I'm not sure if this is a duplicate issue. I create a user without a default database. I create the role which has the select permissions and assign that as the default role for the user. But when user tries to do a CTE, he gets a SELECT command denied to user error.

Drop and recreate user, then give user select on the table directly, CTEs run fine. And I've given the select permission on multiple databases, and haven't heard of any issue running the CTEs. I don't have any roles assigned to the users, since the roles don't appear to work as intended.

(I've had to change up the CTEs, since it seems like the CTE results are cached.)

It would be nice to be able to assign multiple roles to the user and have CTEs work, even if we switch databases. Perhaps that's the issue with MDEV-13453.

Both my issue and issue MDEV-13453 seem to point to some issues with MariaDB's implementation of roles, but perhaps I'm misinterpreting MDEV-13453. Regardless, we won't be using roles until some of these issues get worked out.

> I don't change the default database
It is not about changing default database, it is about which database you are using as default at the moment when you are executing CTE. If you only have SELECT permissions there (be it via a role or directly), it is not enough due to the known bug. The SQL snippet in the initial description does not contradict the theory that you encounter this particular problem, so unless you have a counter-example, it is it.

> Drop and recreate user, then give user select on the table directly, CTEs run fine
Please paste the output from the MySQL client which shows it. It should contain the result of SHOW GRANTS in addition to the result of executing the CTE.

Interesting, you are correct. This is what happens when I just grant select to a particular table:
MariaDB [lahman2016]> WITH new2 as (SELECT playerID, yearID, start from Fielding LIMIT 10) SELECT * FROM new2;
ERROR 1142 (42000): SELECT command denied to user 'oo49'@'localhost' for table 'new2'

MariaDB [mysql]> show grants for oo49;
----------------------------------------------------------------------------------------------------------------------------------------

Couldn't figure out why this never problem never occurred before for us (thank goodness), and realized that this is NOT exactly how we grant permissions to our users. Since we want them to have selects on all the tables, this is how it is granted:

MariaDB [mysql]> show grants for usr112;
------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------

And this user has no problems doing a CTE:

[root@watauga fuqua]# mysql -u usr112 -p lahman2016
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 135
Server version: 10.2.8-MariaDB-log MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [lahman2016]> WITH new3 as (SELECT yearID, teamID, Rank from Teams LIMIT 20) SELECT * FROM new3;
------------------

------------------

....

So, maybe the select on dbname.* implicitly grants select on the CTE tables? Which is probably what MDEV-13682 is pointing to. Sorry for the confusion.

We are on MariaDB 10.2.8. Do you know when this issue will be fixed?

I cannot say for certain when MDEV-13453 will be fixed, but you can track the progress by subscribing to it.