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

    XMLWordPrintable

    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

            People

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

              Dates

              Created:
              Updated:
              Resolved: