Home / server / Questions / 1168128
Accepted
tilkov
Asked: 2024-11-25 17:35:57 +0800 CST

MySQL queries executed with the wrong user

  • 772

I am connecting to MySQL with user 'feedbackdev', but the queries are executed with 'feedback', which is another user on the same server.

root@board:~# mysql -u feedbackdev -p feedbackdev
Enter password: 

mysql> select user();
+-----------------------+
| user()                |
+-----------------------+
| feedbackdev@localhost | 
+-----------------------+


mysql> select rating, COUNT(*) AS rating_count from order_feedback_view where feedback_created_at >= '2024-10-26 09:01:29' group by rating;
ERROR 1143 (42000): SELECT command denied to user 'feedback'@'localhost' for column 'source_id' in table 'orders'

Here are the GRANTS for the users

mysql> show grants for 'feedbackdev'@'localhost';
+----------------------------------------------------------------------+
| Grants for feedbackdev@localhost                                     |
+----------------------------------------------------------------------+
| GRANT PROCESS, SUPER ON *.* TO `feedbackdev`@`localhost`             |
| GRANT SET_USER_ID ON *.* TO `feedbackdev`@`localhost`                |
| GRANT ALL PRIVILEGES ON `feedbackdev`.* TO `feedbackdev`@`localhost` |
+----------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> show grants for 'feedback'@'localhost';
+---------------------------------------------------------------------------+
| Grants for feedback@localhost                                             |
+---------------------------------------------------------------------------+
| GRANT PROCESS, SUPER, REPLICATION CLIENT ON *.* TO `feedback`@`localhost` |
| GRANT FLUSH_TABLES ON *.* TO `feedback`@`localhost`                       |
| GRANT ALL PRIVILEGES ON `feedback`.* TO `feedback`@`localhost`            |
+---------------------------------------------------------------------------+
3 rows in set (0.00 sec)

MySQL version 8.0.40

mysql

1 Answers

  1. Best Answer

    Views in MySQL (as well as in other RDBMS) can be used to give a limited access to the data otherwise not available to the user accessing the view. For that, there is a SQL SECURITY clause in the definition, and it can be set to DEFINER and have it set to some user other than the user(s) intended to select from the view. Then, the view query must me carefully constructed so no sensitive information is given out, and then access to the data it returns is controlled by the permissions set on the view itself. (The same way it works with stored procedures and functions, for the similar purpose.)

    This has a caveat when you export your VIEW via mysqldump: it exports the the actual DEFINER value stored in the definition, and upon reload it reinstates it, even if it was loaded as another user or into the database that belongs to another user. That is what happened in your case, and the old definer is not allowed to the data belonging to the new user, so when you try to invoke the view its access is denied.

    There are two ways to work around this issue. First is to not dump the definers with mysqldump, using the --skip-definer option. Definers will be set back during reload of the dump, however, to the user that is reloading it.

    The second way is to redefine the view with SQL SECURITY INVOKER, in which case the query of the view will always be executed with the privileges of the user who is invoking the view itself. This would be cleaner solution for this case, because it seems this view logically not supposed to be used to provide a limited access to otherwise protected information, it shouldn't have been defined with definer security in the first place.

    • 3