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

ALTER TABLE require ALTER privilege on sequence from DEFAULT value expression

Details

    Description

      --source include/not_embedded.inc
      		 
       
      		 
      create user 'probsolvdbuser_dev'@'localhost';
      		 
      CREATE DATABASE problsolvdbdev;
      		 
      GRANT INSERT, UPDATE, ALTER ON problsolvdbdev.* TO 'probsolvdbuser_dev'@'localhost';
      		 
      USE problsolvdbdev;
      		 
      CREATE SEQUENCE seq_test START WITH 100 INCREMENT BY 10;
      		 
      CREATE TABLE action_1 (id int);
      		 
       
      		 
      connect (conn1,localhost,probsolvdbuser_dev,,problsolvdbdev,);
      		 
       
      		 
      alter table action_1 modify `id` int(11) NOT NULL DEFAULT nextval(`seq_test`);
      		 
       
      		 
      connection default;
      		 
      drop user  'probsolvdbuser_dev'@'localhost';
      		 
      drop databese problsolvdbdev;

      Attachments

        Issue Links

          is blocked by

          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
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36380 User has unauthorized access to a sequence through a view with security invoker

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

          Activity

            Ascending order - Click to sort in descending order
            sanja Oleksandr Byelkin created issue -
            sanja Oleksandr Byelkin made changes -
            Field Original Value New Value
            Description {code}
            --source include/not_embedded.inc

            create user 'probsolvdbuser_dev'@'localhost';
            CREATE DATABASE problsolvdbdev;
            GRANT INSERT, UPDATE, ALTER ON problsolvdbdev.* TO 'probsolvdbuser_dev'@'localhost';
            USE problsolvdbdev;
            CREATE SEQUENCE seq_test START WITH 100 INCREMENT BY 10;
            CREATE TABLE action_1 (id int);

            connect (conn1,localhost,probsolvdbuser_dev,,problsolvdbdev,);

            alter table action_1 modify `id` int(11) NOT NULL DEFAULT nextval(`seq_test`);

            connect default;
            drop user 'probsolvdbuser_dev'@'localhost';
            drop databese problsolvdbdev;
            {code}             		{code}
            --source include/not_embedded.inc

            create user 'probsolvdbuser_dev'@'localhost';
            CREATE DATABASE problsolvdbdev;
            GRANT INSERT, UPDATE, ALTER ON problsolvdbdev.* TO 'probsolvdbuser_dev'@'localhost';
            USE problsolvdbdev;
            CREATE SEQUENCE seq_test START WITH 100 INCREMENT BY 10;
            CREATE TABLE action_1 (id int);

            connect (conn1,localhost,probsolvdbuser_dev,,problsolvdbdev,);

            alter table action_1 modify `id` int(11) NOT NULL DEFAULT nextval(`seq_test`);

            connect default;
            drop user 'probsolvdbuser_dev'@'localhost';
            drop databese problsolvdbdev;
            {code}

            TODO: check CREATE TABLE
            sanja Oleksandr Byelkin made changes -
            Affects Version/s 10.6 [ 24028 ]
            sanja Oleksandr Byelkin made changes -
            Fix Version/s 10.6 [ 24028 ]
            sanja Oleksandr Byelkin made changes -
            Affects Version/s 10.11 [ 27614 ]
            Affects Version/s 11.4 [ 29301 ]
            Affects Version/s 11.8 [ 29921 ]
            sanja Oleksandr Byelkin made changes -
            Affects Version/s 10.5 [ 23123 ]
            sanja Oleksandr Byelkin made changes -
            Fix Version/s 10.5 [ 23123 ]
            sanja Oleksandr Byelkin made changes -
            Description {code}
            --source include/not_embedded.inc

            create user 'probsolvdbuser_dev'@'localhost';
            CREATE DATABASE problsolvdbdev;
            GRANT INSERT, UPDATE, ALTER ON problsolvdbdev.* TO 'probsolvdbuser_dev'@'localhost';
            USE problsolvdbdev;
            CREATE SEQUENCE seq_test START WITH 100 INCREMENT BY 10;
            CREATE TABLE action_1 (id int);

            connect (conn1,localhost,probsolvdbuser_dev,,problsolvdbdev,);

            alter table action_1 modify `id` int(11) NOT NULL DEFAULT nextval(`seq_test`);

            connect default;
            drop user 'probsolvdbuser_dev'@'localhost';
            drop databese problsolvdbdev;
            {code}

            TODO: check CREATE TABLE             		{code}
            --source include/not_embedded.inc

            create user 'probsolvdbuser_dev'@'localhost';
            CREATE DATABASE problsolvdbdev;
            GRANT INSERT, UPDATE, ALTER ON problsolvdbdev.* TO 'probsolvdbuser_dev'@'localhost';
            USE problsolvdbdev;
            CREATE SEQUENCE seq_test START WITH 100 INCREMENT BY 10;
            CREATE TABLE action_1 (id int);

            connect (conn1,localhost,probsolvdbuser_dev,,problsolvdbdev,);

            alter table action_1 modify `id` int(11) NOT NULL DEFAULT nextval(`seq_test`);

            connection default;
            drop user 'probsolvdbuser_dev'@'localhost';
            drop databese problsolvdbdev;
            {code}
            sanja Oleksandr Byelkin added a comment -

            CREATE looks like working, test here:

            --source include/not_embedded.inc
            		 
             
            		 
            create user 'probsolvdbuser_dev'@'localhost';
            		 
            CREATE DATABASE problsolvdbdev;
            		 
            GRANT INSERT, UPDATE, CREATE ON problsolvdbdev.* TO 'probsolvdbuser_dev'@'localhost';
            		 
            USE problsolvdbdev;
            		 
            CREATE SEQUENCE seq_test START WITH 100 INCREMENT BY 10;
            		 
             
            		 
            connect (conn1,localhost,probsolvdbuser_dev,,problsolvdbdev,);
            		 
             
            		 
            create table action_1 ( `id` int(11) NOT NULL DEFAULT nextval(`seq_test`));
            		 
             
            		 
            connection default;
            		 
            drop user  'probsolvdbuser_dev'@'localhost';
            		 
            drop databese problsolvdbdev;

            sanja Oleksandr Byelkin added a comment - CREATE looks like working, test here: --source include/not_embedded.inc   create user 'probsolvdbuser_dev'@'localhost'; CREATE DATABASE problsolvdbdev; GRANT INSERT, UPDATE, CREATE ON problsolvdbdev.* TO 'probsolvdbuser_dev'@'localhost'; USE problsolvdbdev; CREATE SEQUENCE seq_test START WITH 100 INCREMENT BY 10;   connect (conn1,localhost,probsolvdbuser_dev,,problsolvdbdev,);   create table action_1 ( `id` int(11) NOT NULL DEFAULT nextval(`seq_test`));   connection default; drop user 'probsolvdbuser_dev'@'localhost'; drop databese problsolvdbdev;
            sanja Oleksandr Byelkin made changes -
            Assignee Oleksandr Byelkin [ sanja ] Vladislav Vaintroub [ wlad ]
            wlad Vladislav Vaintroub made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            wlad Vladislav Vaintroub added a comment -

            Hi sanja
            could you please review the patch, and please give advise to where it needs to be pushed.
            (10.5/10.6 are currently blocked for most people)

            wlad Vladislav Vaintroub added a comment - Hi sanja could you please review the patch, and please give advise to where it needs to be pushed. (10.5/10.6 are currently blocked for most people)
            wlad Vladislav Vaintroub made changes -
            Assignee Vladislav Vaintroub [ wlad ] Oleksandr Byelkin [ sanja ]
            Status Confirmed [ 10101 ] In Review [ 10002 ]
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            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
              sanja Oleksandr Byelkin
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.