Permission Issue With Nested CTEs

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