|
*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;
|
|