Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-20751

Permission Issue With Nested CTEs

    XMLWordPrintable

    Details

      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

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            caffe1neadd1ct Kevin Andrews
            Votes:
            1 Vote for this issue
            Watchers:
            6 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration