Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.2.6, 10.2.7, 10.2.8
-
RHEL7.
Description
When I grant a select on a table directly to the user, the user is able to use CTEs successfully.
However, when I grant that same select to a role, and then grant the role to the user, the user CANNOT run CTEs successfully.
This user (eg4) has been granted select on the Managers table through the mqm_read role:
GRANT SELECT ON `lahman2016`.`Managers` TO 'mqm_read'; |
But when the user tries to use a CTE, she gets a SELECT command denied error:
MariaDB [lahman2016]> WITH stt as (SELECT playerID, rank FROM Managers LIMIT 10) SELECT * FROM stt; |
ERROR 1142 (42000): SELECT command denied to user 'eg49'@'localhost' for table 'stt' |
|
|
Granting SELECT on the table directly to user, the user can do the CTE: |
|
|
MariaDB [lahman2016]> grant select on Managers to 'eg49'@'%'; |
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [lahman2016]> WITH stt as (SELECT playerID, rank FROM Managers LIMIT 10) SELECT * FROM stt; |
+-----------+------+ |
| playerID | rank |
|
+-----------+------+ |
| wrighha01 | 3 |
|
| woodji01 | 2 |
|
...
|
This seems to defeat the whole purpose of roles.
Attachments
Issue Links
- duplicates
-
-
- Closed
-
Activity
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.
|
Create structures |
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) |
|
Create permissions |
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) |
|
Connect as foo@localhost; note that we are using db2 as the default database |
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) |
|
The role is not set yet, so there is no access to t1 |
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' |
|
Set the role. Now the query works just fine |
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.
|
CTE requires more permissions than just SELECT |
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
|
Connect as a superuser to another permission |
MariaDB [(none)]> grant select on db1.t1 to foo@localhost; |
Query OK, 0 rows affected (0.00 sec) |
|
Connect as foo@localhost, don't set the role this time |
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) |
|
You can still use the same CTE if default database is db2 |
MariaDB [(none)]> use db2; |
Database changed |
MariaDB [db2]> with cte as (select * from db1.t1) select * from cte; |
Empty set (0.00 sec) |
|
...but still can't use it if the default database is db1 |
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;
----------------------------------------------------------------------------------------------------------------------------------------
| Grants for oo49@% |
| GRANT USAGE ON . TO 'oo49'@'%' IDENTIFIED BY PASSWORD '*password' WITH MAX_STATEMENT_TIME 600.000000 |
| GRANT SELECT ON `lahman2016`.`Fielding` TO '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;
------------------------------------------------------------------------------------------------------------------------------------------
| Grants for usr112@% |
------------------------------------------------------------------------------------------------------------------------------------------
| GRANT USAGE ON . TO 'usr112'@'%' IDENTIFIED BY PASSWORD '*passwd' WITH MAX_STATEMENT_TIME 600.000000 |
| GRANT SELECT ON `lahman2016`.* TO '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;
------------------
| yearID | teamID | Rank |
------------------
| 1871 | BS1 | 3 |
| 1871 | CH1 | 2 |
| 1871 | CL1 | 8 |
| 1871 | FW1 | 7 |
| 1871 | NY2 | 5 |
| 1871 | PH1 | 1 |
| 1871 | RC1 | 9 |
| 1871 | TRO | 6 |
....
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.
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)?