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

Privileges not applied correctly for sequences when altering a column to take a default value from a sequence

    XMLWordPrintable

    Details

      Description

      I've run into a little issue related to how privileges are applied to sequences. It seems that for sequences, general privileges on all tables in a DB are not evaluated correctly.

      The following script creates a seq_bug user and database, and grants the user all privileges on the DB:

      DROP DATABASE IF EXISTS seq_bug;
      DROP USER IF EXISTs 'seq_bug'@'localhost';
      CREATE DATABASE seq_bug;
      CREATE USER 'seq_bug'@'localhost' IDENTIFIED BY 'foo';
      GRANT ALL PRIVILEGES ON seq_bug.* TO 'seq_bug'@'localhost';
      

      Now logging into the server as seq_bug, try to execute this DDL:

      CREATE TABLE seq_bug (id INT);
      CREATE SEQUENCE s1;
      ALTER TABLE seq_bug ALTER COLUMN id SET DEFAULT (NEXT VALUE FOR s1);
      

      The expected result would be that the operation completes correctly, what in fact happens is the following:

      ERROR 1142 (42000) at line 3: ALTER command denied to user 'seq_bug'@'localhost' for table 's1'
      

      I was able to work around the issue by granting the user explicit privileges to use that table, so using the following allows the ALTER TABLE statement to complete successfully:

      DROP DATABASE IF EXISTS seq_bug;
      DROP USER IF EXISTs 'seq_bug'@'localhost';
      CREATE DATABASE seq_bug;
      CREATE USER 'seq_bug'@'localhost' IDENTIFIED BY 'foo';
      GRANT ALL PRIVILEGES ON seq_bug.* TO 'seq_bug'@'localhost';
      GRANT ALL PRIVILEGES ON seq_bug.s1 TO 'seq_bug'@'localhost';
      

      The root user is also capable of executing the ALTER TABLE without extra privileges.

        Attachments

          Activity

            People

            Assignee:
            sanja Oleksandr Byelkin
            Reporter:
            ngrewe Niels Grewe
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated: