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

UPDATE privilege at Database and Table level fail to update with SELECT command denied to user

Details

    Description

      *User is unable to update any records with UPDATE privilege at Database (without SELECT privilege at Database level) along with SELECT , UPDATE privileges at Table level. *

       
      Test case (Reproducible):-
       
      MariaDB [(none)]> show grants for 'test_user'@'localhost';
      +---------------------------------------------------------------------------------------------------------------------+
      | Grants for test_user@localhost                                                                                   |
      +---------------------------------------------------------------------------------------------------------------------+
      | GRANT USAGE ON *.* TO 'test_user'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
      | GRANT UPDATE ON `updtesting`.* TO 'test_user'@'localhost'                                                       |
      | GRANT SELECT, INSERT, UPDATE, DELETE ON `updtesting`.`employee` TO 'test_user'@'localhost'                      |
      +---------------------------------------------------------------------------------------------------------------------+
       
       
       
      [root@<hostname>]# mysql -u test_user -ptest
      ..
      ....
      MariaDB [(none)]> update updtesting.employee set name='emp_02' where id=10;
      ERROR 1143 (42000): SELECT command denied to user 'test_user'@'localhost' for column 'id' in table 'employee'
       
      MariaDB [(none)]> show grants;
      +---------------------------------------------------------------------------------------------------------------------+
      | Grants for test_user@localhost                                                                                   |
      +---------------------------------------------------------------------------------------------------------------------+
      | GRANT USAGE ON *.* TO 'test_user'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
      | GRANT UPDATE ON `updtesting`.* TO 'test_user'@'localhost'                                                       |
      | GRANT SELECT, INSERT, UPDATE, DELETE ON `updtesting`.`employee` TO 'test_user'@'localhost'                      |
      +---------------------------------------------------------------------------------------------------------------------+
      

      Workaround :-

      1. Adding SELECT to the UPDATE database level grant allows the update to the table to work

       
      MariaDB [updtesting]> show grants;
      +---------------------------------------------------------------------------------------------------------------------+
      | Grants for test_user@localhost                                                                                   |
      +---------------------------------------------------------------------------------------------------------------------+
      | GRANT USAGE ON *.* TO 'test_user'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
      | GRANT SELECT, UPDATE ON `updtesting`.* TO 'test_user'@'localhost'                                               |
      | GRANT SELECT, INSERT, UPDATE, DELETE ON `updtesting`.`employee` TO 'test_user'@'localhost'                      |
      +---------------------------------------------------------------------------------------------------------------------+
      3 rows in set (0.00 sec)
       
      MariaDB [updtesting]> update updtesting.employee set name='emp_02' where id=10;
      Query OK, 1 row affected (0.00 sec)
      
      

      2. Removing UPDATE at the database level grant allows the table to be updated.

       
      MariaDB [(none)]> show grants;
      +---------------------------------------------------------------------------------------------------------------------+
      | Grants for test_user@localhost                                                                                   |
      +---------------------------------------------------------------------------------------------------------------------+
      | GRANT USAGE ON *.* TO 'test_user'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
      | GRANT SELECT, INSERT, UPDATE, DELETE ON `test_user`.`employee` TO 'test_user'@'localhost'                      |
      +---------------------------------------------------------------------------------------------------------------------+
      2 rows in set (0.00 sec)
       
      MariaDB [(none)]> update updtesting.employee set name='emp_03' where id=10;
      Query OK, 1 row affected (0.00 sec)
      Rows matched: 1  Changed: 1  Warnings: 0
      
      

      Testcase.

      Preparation:

      create database `updtesting`;
      use `updtesting`;
      CREATE TABLE `employee` (`id` int(10) NOT NULL,`name` varchar(20) DEFAULT NULL,PRIMARY KEY (`id`));
      GRANT USAGE ON *.* TO 'test_user'@'localhost' IDENTIFIED BY 'maria2020';
      GRANT UPDATE ON `updtesting`.* TO 'test_user'@'localhost';                                               
      GRANT SELECT, INSERT, UPDATE, DELETE ON `updtesting`.`employee` TO 'test_user'@'localhost';
      

      relogin with user test_user

      mysql -utest_user -pmaria2020

      Execute:

      update updtesting.employee set name='emp_02' where id=10;
      

      Attachments

        Activity

          alice Alice Sherepa added a comment - - edited

          mtr-friendly:

          CREATE TABLE t1(id int);
          INSERT INTO t1 VALUES(2);
           
          CREATE USER u1@localhost;
          #GRANT UPDATE ON test.t1 TO u1@localhost; --works if only on table level, but not on db level 
          GRANT UPDATE ON test.* TO u1@localhost;
          flush privileges;
           
          connect (u1, localhost, u1, ,);
          connection u1;
          update test.t1 set id=1 where id=2;
           
          #Cleanup
          connection default;
          DROP TABLE t1;
          DROP USER u1@localhost;
          

          query 'update test.t1 set id=1 where id=2' failed: 1143: SELECT command denied to user 'u1'@'localhost' for column 'id' in table 't1'
          

          The same behaviour on Mysql 8.0.19

          alice Alice Sherepa added a comment - - edited mtr-friendly: CREATE TABLE t1(id int ); INSERT INTO t1 VALUES (2);   CREATE USER u1@localhost; # GRANT UPDATE ON test.t1 TO u1@localhost; --works if only on table level, but not on db level GRANT UPDATE ON test.* TO u1@localhost; flush privileges ;   connect (u1, localhost, u1, ,); connection u1; update test.t1 set id=1 where id=2;   #Cleanup connection default ; DROP TABLE t1; DROP USER u1@localhost; query 'update test.t1 set id=1 where id=2' failed: 1143: SELECT command denied to user 'u1'@'localhost' for column 'id' in table 't1' The same behaviour on Mysql 8.0.19

          This last test case isn't a bug, there's a default grant in mysql-test tests giving all users (''@localhost) all privileges on the test db.

          So, with a table level UPDATE grant, u1@localhost gets select privileges from that default grant. With a db level UPDATE grant, the default grant no longer applies and there's no SELECT privilege anymore.

          But the original complain looks still valid, here's an modified test case:

          create database mysqltest1;
          use mysqltest1;
          create table t1(id int);
          insert into t1 values(2);
          create user u1@localhost;
          grant select on mysqltest1.t1 to u1@localhost;
          grant update on mysqltest1.* to u1@localhost;
          flush tables;
          connect (u1, localhost, u1, ,);
          show grants;
          update mysqltest1.t1 set id=1 where id=2;
          connection default;
          drop user u1@localhost;
          drop database mysqltest1;
          

          serg Sergei Golubchik added a comment - This last test case isn't a bug, there's a default grant in mysql-test tests giving all users ( ''@localhost ) all privileges on the test db. So, with a table level UPDATE grant, u1@localhost gets select privileges from that default grant. With a db level UPDATE grant, the default grant no longer applies and there's no SELECT privilege anymore. But the original complain looks still valid, here's an modified test case: create database mysqltest1; use mysqltest1; create table t1(id int ); insert into t1 values (2); create user u1@localhost; grant select on mysqltest1.t1 to u1@localhost; grant update on mysqltest1.* to u1@localhost; flush tables; connect (u1, localhost, u1, ,); show grants; update mysqltest1.t1 set id=1 where id=2; connection default ; drop user u1@localhost; drop database mysqltest1;

          People

            serg Sergei Golubchik
            pramod.mahto@mariadb.com Pramod Mahto
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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