-- 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)
|
Bug
Major
