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

User without any privileges to a sequence can read from it and modify it via column default

Details

    Description

      Courtesy of wlad

      create database db;
      		 
      create user u;
      		 
      grant create, insert, select on db.t to u;
      		 
      create sequence db.s;
      		 
      select * from db.s;
      		 
       
      		 
      --connect(con1,localhost,u,,db)
      		 
      --error ER_TABLEACCESS_DENIED_ERROR
      		 
      select nextval(s);
      		 
      --error ER_TABLEACCESS_DENIED_ERROR
      		 
      show create sequence s;
      		 
       
      		 
      create table t (a int not null default(nextval(s)));
      		 
      insert into t values (),();
      		 
      select * from t;
      		 
      select default(a) from t;
      		 
      --disconnect con1
      		 
       
      		 
      --connection default
      		 
      select * from db.s;
      		 
      drop database db;
      		 
      drop user u;

      10.5 85ecb80fa3192035b3beff9578dc254a857175dc

      		 
      connect con1,localhost,u,,db;
      		 
      select nextval(s);
      		 
      ERROR 42000: INSERT command denied to user 'u'@'localhost' for table `db`.`s`
      		 
      show create sequence s;
      		 
      ERROR 42000: SHOW command denied to user 'u'@'localhost' for table `db`.`s`
      		 
      create table t (a int not null default(nextval(s)));
      		 
      insert into t values (),();
      		 
      select * from t;
      		 
      a
      		 
      1
      		 
      2
      		 
      select default(a) from t;
      		 
      default(a)
      		 
      3
      		 
      4
      		 
      disconnect con1;

      So, the user u does not have any access to the sequence db.s, cannot read from it or see its structure.
      However, the user can refer to it in CREATE TABLE, and can read/insert a default value in such a table, thus both reading and modifying the sequence.

      It has been discussed that the privileges should be checked at some point. It can be done
      1) upon table creation/altering, or
      2) upon insert/select.

      Either way is likely to break something for existing users. Probably the first way, checking privileges upon creation, will break less, at least because this is a less frequent operation than INSERT/SELECT, but it's hard to predict for sure.
      Another argument for the first approach is that the existence of the sequence is already checked upon CREATE, one can't create a table referring a sequence in DEFAULT if the sequence doesn't exist or if the referred object is not a sequence, which also means that in the variation of the test case above the unprivileged user gets information about db.s which they shouldn't normally have:

      create user u;
      		 
      grant create, insert, select on db.t to u;
      		 
      create table db.s (x int);
      		 
      connect con1,localhost,u,,db;
      		 
      create table t (a int not null default(nextval(s)));
      		 
      ERROR 42S02: 'db.s' is not a SEQUENCE
      		 
      create table t (a int not null default(nextval(s2)));
      		 
      ERROR 42S02: Table 'db.s2' doesn't exist

      Attachments

        Issue Links

          blocks

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36280 ALTER TABLE require ALTER privilege on sequence from DEFAULT value expression

          • Critical - Crashes, loss of data, severe memory leak.
          • In Review
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-19479 Privileges not applied correctly for sequences when altering a column to take a default value from a sequence

          • Critical - Crashes, loss of data, severe memory leak.
          • Confirmed

          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.
          • Open

          Activity

            Transition Time In Source Status Execution Times
            Michael Widenius made transition -
            Open In Progress
            		7d 3h 6m 1
            Michael Widenius made transition -
            In Progress In Testing
            		1h 1m 1

            People

              monty Michael Widenius
              elenst Elena Stepanova
              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.