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

INSERT .. RETURNING bypasses the absence of SELECT privilege

    XMLWordPrintable

    Details

      Description

      create database db;
      create table db.t (pk int auto_increment primary key, a int default 0);
      create user foo;
      grant insert on db.t to foo;
       
      --connect (con1,localhost,foo,,)
      insert into db.t () values () returning pk, a;
       
      # Cleanup
      --disconnect con1
      --connection default
      drop database db;
      drop user foo;
      

      10.5 6cee9b195

      MariaDB [test]> insert into db.t () values () returning pk, a;
      +----+------+
      | pk | a    |
      +----+------+
      |  1 |    0 |
      +----+------+
      1 row in set (0.020 sec)
      MariaDB [test]> show grants;
      +---------------------------------------+
      | Grants for foo@%                      |
      +---------------------------------------+
      | GRANT USAGE ON *.* TO `foo`@`%`       |
      | GRANT INSERT ON `db`.`t` TO `foo`@`%` |
      +---------------------------------------+
      2 rows in set (0.001 sec)
      

      It's not specified in the KB, but one could reasonably expect that RETURNING requires a SELECT privilege at least on the columns which it returns. It also seems to be the case for PostgreSQL, or at least it's documented this way there:

      Use of the RETURNING clause requires SELECT privilege on all columns mentioned in RETURNING.

      Besides, if it works as it is by design, there is an inconsistency because RETURNING * for the same table throws an error:

      MariaDB [test]> insert into db.t () values () returning *;
      ERROR 1143 (42000): SELECT command denied to user 'foo'@'localhost' for column 'pk' in table 't'
      

      DELETE .. RETURNING expectedly returns an error for both variants:

      MariaDB [test]> show grants;
      +---------------------------------------+
      | Grants for foo@%                      |
      +---------------------------------------+
      | GRANT USAGE ON *.* TO `foo`@`%`       |
      | GRANT DELETE ON `db`.`t` TO `foo`@`%` |
      +---------------------------------------+
      2 rows in set (0.001 sec)
       
      MariaDB [test]> delete from db.t returning pk, a;
      ERROR 1143 (42000): SELECT command denied to user 'foo'@'localhost' for column 'pk' in table 't'
      MariaDB [test]> delete from db.t returning *;
      ERROR 1143 (42000): SELECT command denied to user 'foo'@'localhost' for column 'pk' in table 't'
      

        Attachments

          Activity

            People

            Assignee:
            serg Sergei Golubchik
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated: