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

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            Description {code:sql}
            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;
            {code}

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

            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.
            {color:red}
            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.
            {color}

            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:

            {code:sql}
            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
            {code}
            elenst Elena Stepanova made changes -
            Assignee Sergei Golubchik [ serg ]
            serg Sergei Golubchik made changes -
            Priority Major [ 3 ] Blocker [ 1 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Michael Widenius [ monty ]
            elenst Elena Stepanova made changes -
            Description {code:sql}
            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;
            {code}

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

            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.
            {color:red}
            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.
            {color}

            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:

            {code:sql}
            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
            {code}             		Courtesy of [~wlad]

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

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

            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.
            {color:red}
            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.
            {color}

            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:

            {code:sql}
            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
            {code}
            serg Sergei Golubchik added a comment -

            Also, a table referencing another table is somewhat similar to foreign keys. And for them the privilege is checked on creation, not on every check.

            serg Sergei Golubchik added a comment - Also, a table referencing another table is somewhat similar to foreign keys. And for them the privilege is checked on creation, not on every check.
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            elenst Elena Stepanova added a comment -

            Another argument for the "upon creation" approach is that a user doesn't need to have a privilege on a column to insert a default value into the column, so it would be strange if it required a privilege on the underlying function which generates this value.

            elenst Elena Stepanova added a comment - Another argument for the "upon creation" approach is that a user doesn't need to have a privilege on a column to insert a default value into the column, so it would be strange if it required a privilege on the underlying function which generates this value.
            monty Michael Widenius made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            monty Michael Widenius made changes -
            Status In Progress [ 3 ] In Testing [ 10301 ]
            monty Michael Widenius added a comment -

            Pushed to bb-10.11-monty. This is not critical enough to be pushed to 10.6.

            monty Michael Widenius added a comment - Pushed to bb-10.11-monty. This is not critical enough to be pushed to 10.6.

            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.