Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.4.8, 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
None
-
CentOS 7
Arch Linux
Description
Queries with CTEs referencing other CTEs eventually cause permission denied errors on users with SELECT permissions on specific list of tables. Issue is also present when using the newer roles based permissions.
Setups to reproduce:
-- Create Schema
|
CREATE DATABASE cte_permissions_test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; |
|
|
-- Create Tables
|
CREATE TABLE cte_permissions_test.table1 ( |
`id` INT NOT NULL AUTO_INCREMENT, |
`sku` varchar(100) NOT NULL, |
`type` varchar(100) NOT NULL, |
`active` TINYINT(1) NOT NULL DEFAULT 0, |
PRIMARY KEY (id) |
)
|
ENGINE=InnoDB
|
DEFAULT CHARSET=utf8mb4 |
COLLATE=utf8mb4_unicode_ci; |
|
|
CREATE TABLE cte_permissions_test.table2 ( |
`id` INT NOT NULL AUTO_INCREMENT, |
`sku` varchar(100) NOT NULL, |
`type` TINYINT(1) NOT NULL, |
`active` TINYINT(1) NOT NULL DEFAULT 0, |
PRIMARY KEY (id) |
)
|
ENGINE=InnoDB
|
DEFAULT CHARSET=utf8mb4 |
COLLATE=utf8mb4_unicode_ci; |
|
|
|
|
CREATE TABLE cte_permissions_test.table3 ( |
`id` INT NOT NULL AUTO_INCREMENT, |
`sku` varchar(100) NOT NULL, |
`qty` TINYINT(1) NOT NULL, |
PRIMARY KEY (id) |
)
|
ENGINE=InnoDB
|
DEFAULT CHARSET=utf8mb4 |
COLLATE=utf8mb4_unicode_ci; |
|
|
CREATE USER 'cte_restricted_user'@'127.0.0.1' IDENTIFIED BY ''; |
GRANT USAGE ON cte_permissions_test.* TO 'cte_restricted_user'@'127.0.0.1'; |
GRANT SELECT ON cte_permissions_test.table1 TO 'cte_restricted_user'@'127.0.0.1'; |
GRANT SELECT ON cte_permissions_test.table2 TO 'cte_restricted_user'@'127.0.0.1'; |
GRANT SELECT ON cte_permissions_test.table3 TO 'cte_restricted_user'@'127.0.0.1'; |
FLUSH PRIVILEGES; |
|
|
mysql -u cte_restricted_user -h 127.0.0.1
|
USE cte_permissions_test; |
|
|
WITH cte1 AS ( |
SELECT * FROM table1 WHERE `type` IN ('type1', 'type2') AND active = 1 |
), cte2 AS ( |
SELECT table2.* FROM table2 |
INNER JOIN cte1 ON cte1.sku=table2.sku |
WHERE |
table2.`type` IN (1,2) AND |
table2.active = 1
|
), cte3 AS ( |
SELECT table3.sku, cte1.`type`, table3.qty FROM table3 |
INNER JOIN cte1 ON cte1.sku=table3.sku |
INNER JOIN cte2 ON table3.sku = cte2.sku |
WHERE |
table3.qty = 0
|
), cte4 AS ( |
SELECT table3.* FROM table3 |
INNER JOIN cte2 ON cte2.sku=table3.sku |
WHERE table3.sku IN (SELECT sku FROM cte1) |
), cte5 AS ( |
SELECT |
cte2.sku, cte2.`type`, cte2.active, cte3.sku as sku_holdings, cte3.qty |
FROM cte2 |
LEFT OUTER JOIN cte3 ON cte3.sku = cte2.sku |
) SELECT * FROM cte5; |
|
|
|
|
-- Result: ERROR 1142 (42000): SELECT command denied to user 'cte_restricted_user'@'localhost' for table 'cte1'
|
|
|
WITH cte1 AS ( |
SELECT * FROM table1 WHERE `type` IN ('type1', 'type2') AND active = 1 |
) SELECT * FROM cte1; |
|
|
-- Result Empty set (0.000 sec) - query runs succesfully and if results inserted does bring them back
|
|
|
|
|
-- Rewriting the above to use cte1 as a subquery where needed:
|
|
|
WITH cte1 AS ( |
SELECT * FROM table1 WHERE `type` IN ('type1', 'type2') AND active = 1 |
), cte2 AS ( |
SELECT table2.* FROM table2 |
INNER JOIN (SELECT * FROM table1 WHERE `type` IN ('type1', 'type2') AND active = 1) AS cte1 ON cte1.sku=table2.sku |
WHERE |
table2.`type` IN (1,2) AND |
table2.active = 1
|
), cte3 AS ( |
SELECT table3.sku, cte1.`type`, table3.qty FROM table3 |
INNER JOIN (SELECT * FROM table1 WHERE `type` IN ('type1', 'type2') AND active = 1) AS cte1 ON cte1.sku=table3.sku |
INNER JOIN cte2 ON table3.sku = cte2.sku |
WHERE |
table3.qty = 0
|
), cte4 AS ( |
SELECT table3.* FROM table3 |
INNER JOIN cte2 ON cte2.sku=table3.sku |
WHERE table3.sku IN (SELECT sku FROM table1 WHERE `type` IN ('type1', 'type2') AND active = 1) |
), cte5 AS ( |
SELECT |
cte2.sku, cte2.`type`, cte2.active, cte3.sku as sku_holdings, cte3.qty |
FROM cte2 |
LEFT OUTER JOIN cte3 ON cte3.sku = cte2.sku |
) SELECT * FROM cte5; |
|
|
-- Result Empty set (0.001 sec) - query runs succesfully and if results inserted does bring them back
|
|
|
SELECT VERSION(); |
|
|
-- Result
|
-- +----------------+
|
-- | VERSION() |
|
-- +----------------+
|
-- | 10.4.8-MariaDB |
|
-- +----------------+
|
-- 1 row in set (0.000 sec) |
We've started relying on CTEs for anyone writing custom reports as an easy way to pick up and use existing query partials defined for our data sets. Significantly reduces the learning curve and makes reusing SQL code easier for our data team. However there is no way we can give users SELECT privilages on the entire schema, per table is a requirement here.
Happy to help further diagnose.
Attachments
Activity
Not a problem, interesting to see it replicated with just one table... much better example!
Thanks for putting that together, mine was based off a live query which needed a lot of debugging and simplification to get down to what I reported. I'm glad it's replicated your side as well, makes things easier to track down.
Thanks for the report!
I repeated the same behavior on current MariaDB 10.2-10.4: