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

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

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36413 User without any privileges to a sequence can read from it and modify it via column default

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            ngrewe Niels Grewe created issue -
            ngrewe Niels Grewe made changes -
            Field Original Value New Value
            Description I've ran 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:

            {code:sql}
            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';
            {code}

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

            {code:sql}
            CREATE TABLE seq_bug (id INT);
            CREATE SEQUENCE s1;
            ALTER TABLE seq_bug ALTER COLUMN id SET DEFAULT (NEXT VALUE FOR s1);
            {code}

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

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

            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:

            {code:sql}
            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';
            {code}

            The root user is also capable of executing the {{ALTER TABLE}} without extra privileges.             		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:

            {code:sql}
            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';
            {code}

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

            {code:sql}
            CREATE TABLE seq_bug (id INT);
            CREATE SEQUENCE s1;
            ALTER TABLE seq_bug ALTER COLUMN id SET DEFAULT (NEXT VALUE FOR s1);
            {code}

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

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

            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:

            {code:sql}
            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';
            {code}

            The root user is also capable of executing the {{ALTER TABLE}} without extra privileges.
            elenst Elena Stepanova made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            elenst Elena Stepanova added a comment -

            Thanks for the report and test case.

            elenst Elena Stepanova added a comment - Thanks for the report and test case.
            elenst Elena Stepanova made changes -
            Component/s Authentication and Privilege System [ 13101 ]
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            Affects Version/s 10.3 [ 22126 ]
            Affects Version/s 10.4 [ 22408 ]
            Assignee Oleksandr Byelkin [ sanja ]
            jan-hendrik.diederich@cstx.de Jan-Hendrik Diederich added a comment - - edited

            I just had to give my limited powers user the INSERT right for the sequence table.
            Like:
            GRANT INSERT ON database.test_sequence TO 'user'@'localhost';

            With MariaDB 10.4.14.

            Whatever, since we normally don't have root rights, and often no GRANT rights, we can't GRANT rights, so we still need this bug fixed.

            jan-hendrik.diederich@cstx.de Jan-Hendrik Diederich added a comment - - edited I just had to give my limited powers user the INSERT right for the sequence table. Like: GRANT INSERT ON database.test_sequence TO 'user'@'localhost'; With MariaDB 10.4.14. Whatever, since we normally don't have root rights, and often no GRANT rights, we can't GRANT rights, so we still need this bug fixed.
            rpavlov Roman Pavlov added a comment -

            I can confirm this issue for MariaDB 10.5.6 on FreeBSD 12.1

            rpavlov Roman Pavlov added a comment - I can confirm this issue for MariaDB 10.5.6 on FreeBSD 12.1
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 96751 ] MariaDB v4 [ 144140 ]
            slav.umni Slav Umni added a comment -

            Confirm this for MariaDB 10.9.3

            slav.umni Slav Umni added a comment - Confirm this for MariaDB 10.9.3
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.3 [ 22126 ]
            vladmx Vladimir G. added a comment - - edited

            Issue confirmed on MariaDB 11.2.2. Fresh install on Rocky Linux 9.3 (from MariaDB repo using mariadb_repo_setup script).

            For this ALTER TABLE usage, unless GRANT is given to specific object, it won´t work.

            Here my tests:

            – As user root on the database
            CREATE OR REPLACE USER 'test_admin_user'@'%' IDENTIFIED BY 'foo';
            – I need test_admin_user to fully manage any database with prefix s_
            GRANT ALL PRIVILEGES ON `s_%`.* TO 'test_admin_user'@'%' WITH GRANT OPTION;
            FLUSH PRIVILEGES;

            – Reconnect as user test_admin_user to the database
            CREATE OR REPLACE DATABASE s_testdb;
            USE s_testdb;

            CREATE TABLE seq_bug (id INT);
            CREATE SEQUENCE some_seq;

            – Next line fails with message "SQL Error [1142] [42000]: (conn=19) ALTER command denied to user 'test_admin_user'@'X.X.X.X' for table `s_testdb`.`some_seq`"
            ALTER TABLE seq_bug ALTER COLUMN id SET DEFAULT (NEXT VALUE FOR some_seq);

            GRANT ALL PRIVILEGES ON `s_%`.some_seq TO 'test_admin_user'@'%';
            – Next line continues to fail
            ALTER TABLE seq_bug ALTER COLUMN id SET DEFAULT (NEXT VALUE FOR some_seq);

            GRANT ALL PRIVILEGES ON `s_testdb`.some_seq TO 'test_admin_user'@'%';
            – Next line finally succeeds
            ALTER TABLE seq_bug ALTER COLUMN id SET DEFAULT (NEXT VALUE FOR some_seq);

            vladmx Vladimir G. added a comment - - edited Issue confirmed on MariaDB 11.2.2. Fresh install on Rocky Linux 9.3 (from MariaDB repo using mariadb_repo_setup script). For this ALTER TABLE usage, unless GRANT is given to specific object, it won´t work. Here my tests: – As user root on the database CREATE OR REPLACE USER 'test_admin_user'@'%' IDENTIFIED BY 'foo'; – I need test_admin_user to fully manage any database with prefix s_ GRANT ALL PRIVILEGES ON `s_%`.* TO 'test_admin_user'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES; – Reconnect as user test_admin_user to the database CREATE OR REPLACE DATABASE s_testdb; USE s_testdb; CREATE TABLE seq_bug (id INT); CREATE SEQUENCE some_seq; – Next line fails with message "SQL Error [1142] [42000] : (conn=19) ALTER command denied to user 'test_admin_user'@'X.X.X.X' for table `s_testdb`.`some_seq`" ALTER TABLE seq_bug ALTER COLUMN id SET DEFAULT (NEXT VALUE FOR some_seq); GRANT ALL PRIVILEGES ON `s_%`.some_seq TO 'test_admin_user'@'%'; – Next line continues to fail ALTER TABLE seq_bug ALTER COLUMN id SET DEFAULT (NEXT VALUE FOR some_seq); GRANT ALL PRIVILEGES ON `s_testdb`.some_seq TO 'test_admin_user'@'%'; – Next line finally succeeds ALTER TABLE seq_bug ALTER COLUMN id SET DEFAULT (NEXT VALUE FOR some_seq);
            mal-risma Michael Als added a comment -

            Also confirmed on 10.11. 2.

            mal-risma Michael Als added a comment - Also confirmed on 10.11. 2.
            serg Sergei Golubchik made changes -
            Priority Minor [ 4 ] Critical [ 2 ]
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.4(EOL) [ 22408 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 11.4 [ 29301 ]

            People

              sanja Oleksandr Byelkin
              ngrewe Niels Grewe
              Votes:
              4 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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