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

Permission Issue With Nested CTEs

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

          Ascending order - Click to sort in descending order
          caffe1neadd1ct Kevin Andrews created issue -
          alice Alice Sherepa made changes -
          Field Original Value New Value
          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.

          		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:
          {code:sql}
          -- 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)
          {code}
          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.

          alice Alice Sherepa made changes -
          Affects Version/s 10.2 [ 14601 ]
          Affects Version/s 10.3 [ 22126 ]
          Affects Version/s 10.4 [ 22408 ]
          alice Alice Sherepa made changes -
          Fix Version/s 10.2 [ 14601 ]
          Fix Version/s 10.3 [ 22126 ]
          Fix Version/s 10.4 [ 22408 ]
          alice Alice Sherepa made changes -
          Assignee Igor Babaev [ igor ]
          alice Alice Sherepa made changes -
          Status Open [ 1 ] Confirmed [ 10101 ]
          igor Igor Babaev (Inactive) made changes -
          Assignee Igor Babaev [ igor ] Oleksandr Byelkin [ sanja ]
          Status Confirmed [ 10101 ] In Review [ 10002 ]
          sanja Oleksandr Byelkin made changes -
          Assignee Oleksandr Byelkin [ sanja ] Igor Babaev [ igor ]
          Status In Review [ 10002 ] Stalled [ 10000 ]
          igor Igor Babaev (Inactive) made changes -
          Status Stalled [ 10000 ] In Progress [ 3 ]
          igor Igor Babaev (Inactive) made changes -
          Fix Version/s 10.2.37 [ 25112 ]
          Fix Version/s 10.3.28 [ 25111 ]
          Fix Version/s 10.4.18 [ 25110 ]
          Fix Version/s 10.5.9 [ 25109 ]
          Fix Version/s 10.2 [ 14601 ]
          Fix Version/s 10.3 [ 22126 ]
          Fix Version/s 10.4 [ 22408 ]
          Resolution Fixed [ 1 ]
          Status In Progress [ 3 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 100185 ] MariaDB v4 [ 156821 ]

          People

            igor Igor Babaev (Inactive)
            caffe1neadd1ct Kevin Andrews
            Votes:
            1 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.