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

Executing a query via CTE requires more permissions than the query itself

    XMLWordPrintable

    Details

    • Sprint:
      10.2.11

      Description

      Based on the question posted at stackoverflow by Diogo Melo:
      https://stackoverflow.com/questions/45510362/error-1142-42000-select-command-denied-for-with-recursive-statement-on-ma

      When a query is executed via CTE, it's not enough to have normal privileges for the query, the user must also have SELECT privileges for the CTE itself, which are impossible to grant.

      create database db;
      use db;
      create table t1 (i int);
       
      create user foo@localhost;
      grant SELECT on db.t1 to foo@localhost;
       
      --connect (con1,localhost,foo,,)
      use db;
      with cte as (select * from t1) select * from cte;
       
      # Cleanup
      --disconnect con1
       
      --connection default
      drop database db;
      drop user foo@localhost;
      

      CURRENT_TEST: bug.t6
      mysqltest: At line 10: query 'with cte as (select * from t1) select * from cte' failed: 1142: SELECT command denied to user 'foo'@'localhost' for table 'cte'
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: