MySQL - Why does update query still run with only select privileges on a given table for a given user?

Question 1

I have a user 'jay'@'localhost' and following are the grants for that user.

mysql> show grants for 'jay'@'localhost';
+-------------------------------------------------------------------------------------------+
| Grants for jay@localhost |
+-------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jay'@'localhost' |
| GRANT ALL PRIVILEGES ON `curate`.`searches` TO 'jay'@'localhost' |
| GRANT ALL PRIVILEGES ON `curate`.`conversations` TO 'jay'@'localhost' |
| GRANT ALL PRIVILEGES ON `curate`.`feed_entries` TO 'jay'@'localhost' |
| GRANT ALL PRIVILEGES ON `curate`.`checksum_audit_logs` TO 'jay'@'localhost' |
| GRANT ALL PRIVILEGES ON `curate`.`local_authority_entries` TO 'jay'@'localhost' |
| GRANT ALL PRIVILEGES ON `curate`.`domain_terms_local_authorities` TO 'jay'@'localhost' |
| GRANT ALL PRIVILEGES ON `curate`.`trophies` TO 'jay'@'localhost' |
| GRANT ALL PRIVILEGES ON `curate`.`version_committers` TO 'jay'@'localhost' |
| GRANT ALL PRIVILEGES ON `curate`.`follows` TO 'jay'@'localhost' |
| GRANT ALL PRIVILEGES ON `curate`.`devise_multi_auth_authentications` TO 'jay'@'localhost' |
| GRANT ALL PRIVILEGES ON `curate`.`schema_migrations` TO 'jay'@'localhost' |
| GRANT ALL PRIVILEGES ON `curate`.`orcid_profile_requests` TO 'jay'@'localhost' |
| GRANT ALL PRIVILEGES ON `curate`.`subject_local_authority_entries` TO 'jay'@'localhost' |
| GRANT ALL PRIVILEGES ON `curate`.`change_manager_changes` TO 'jay'@'localhost' |
| GRANT ALL PRIVILEGES ON `curate`.`receipts` TO 'jay'@'localhost' |
| GRANT ALL PRIVILEGES ON `curate`.`proxy_deposit_rights` TO 'jay'@'localhost' |
| GRANT ALL PRIVILEGES ON `curate`.`bookmarks` TO 'jay'@'localhost' |
| GRANT ALL PRIVILEGES ON `curate`.`local_authorities` TO 'jay'@'localhost' |
| GRANT ALL PRIVILEGES ON `curate`.`domain_terms` TO 'jay'@'localhost' |
| GRANT ALL PRIVILEGES ON `curate`.`notifications` TO 'jay'@'localhost' |
| GRANT ALL PRIVILEGES ON `curate`.`single_use_links` TO 'jay'@'localhost' |
| GRANT SELECT ON `curate`.`users` TO 'jay'@'localhost' |
| GRANT ALL PRIVILEGES ON `curate`.`help_requests` TO 'jay'@'localhost' |
+-------------------------------------------------------------------------------------------+
24 rows in set (0.00 sec)

As you can see, 'jay'@'localhost' has SELECT privileges only on the table 'curate'.'users'. But the user could still run the UPDATE query on that table.

mysql> select user();
+---------------+
| user() |
+---------------+
| jay@localhost |
+---------------+
1 row in set (0.00 sec)
mysql> select * from users;
+----+--------------------------+--------------------------------------------------------------+----------------------+------------------------+---------------------+---------------+---------------------+---------------------+--------------------+-----------------+---------------------+---------------------+-------+-----------------+----------------+-------------------+------+---------+------------+------------+-------+--------+---------+---------+-------------+-----------+------------------+---------------------+------------------+-------------------+------------+--------------------+--------------------------------------+-----------------+----------+------+------------+-----------+----------+--------------+---------------------+
| id | email | encrypted_password | reset_password_token | reset_password_sent_at | remember_created_at | sign_in_count | current_sign_in_at | last_sign_in_at | current_sign_in_ip | last_sign_in_ip | created_at | updated_at | guest | facebook_handle | twitter_handle | googleplus_handle | name | address | admin_area | department | title | office | chat_id | website | affiliation | telephone | avatar_file_name | avatar_content_type | avatar_file_size | avatar_updated_at | group_list | groups_last_update | user_does_not_require_profile_update | repository_id | provider | uid | first_name | last_name | ucstatus | ucdepartment | waived_welcome_page |
+----+--------------------------+--------------------------------------------------------------+----------------------+------------------------+---------------------+---------------+---------------------+---------------------+--------------------+-----------------+---------------------+---------------------+-------+-----------------+----------------+-------------------+------+---------+------------+------------+-------+--------+---------+---------+-------------+-----------+------------------+---------------------+------------------+-------------------+------------+--------------------+--------------------------------------+-----------------+----------+------+------------+-----------+----------+--------------+---------------------+
| 1 | [email protected] | 2ドルa10ドル$xqNke5vKUvKP8rIjtUSeD.l2L1nZpc8rx.L6mV2K2lr8XNmPpfGdy | NULL | NULL | NULL | 2 | 2017年10月12日 22:20:17 | 2017年10月12日 21:45:52 | 127.0.0.1 | 127.0.0.1 | 2017年10月12日 21:36:25 | 2017年10月12日 22:23:21 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | sufia:rb68xk02w | NULL | NULL | Many | Deposit | NULL | NULL | 1 |
| 2 | [email protected] | 2ドルa10ドル$wTZvQjTyHqSAD9B7LYxsc.9e8W9TMZ2tVv834p6bk0mTs8E/73lOm | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | 2017年10月12日 21:36:28 | 2017年10月12日 21:36:29 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | sufia:rb68xk04f | NULL | NULL | No | Deposits | NULL | NULL | NULL |
| 3 | [email protected] | 2ドルa10ドル$j5If0Yv0bMguADFfOgRZYetE76r8rL76lSuHbyXB1j3t77bgTQa42 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | 2017年10月12日 21:36:29 | 2017年10月12日 21:36:30 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | sufia:rb68xk060 | NULL | NULL | Repository | Manager | NULL | NULL | NULL |
| 4 | [email protected] | 2ドルa10ドル$odYs2pjBrKJIu6aVA/5SsuiyBJw27oyMQacroxG7RB1r4JHLdR5gW | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | 2017年10月12日 21:36:31 | 2017年10月12日 21:36:32 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | sufia:rb68xk08j | NULL | NULL | Student | Delegate | NULL | NULL | NULL |
+----+--------------------------+--------------------------------------------------------------+----------------------+------------------------+---------------------+---------------+---------------------+---------------------+--------------------+-----------------+---------------------+---------------------+-------+-----------------+----------------+-------------------+------+---------+------------+------------+-------+--------+---------+---------+-------------+-----------+------------------+---------------------+------------------+-------------------+------------+--------------------+--------------------------------------+-----------------+----------+------+------------+-----------+----------+--------------+---------------------+
4 rows in set (0.00 sec)
mysql> UPDATE `users` SET `last_name` = 'Deposits', `updated_at` = '2017-10-12 22:23:21' WHERE `users`.`id` = 1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>

What am I missing here?

Edit 1:

Result from @Rick James's answer.
mysql> SELECT Host, User, Db, Table_name FROM mysql.tables_priv
 -> WHERE table_priv LIKE '%Update%';
+-----------+------+--------+-----------------------------------+
| Host | User | Db | Table_name |
+-----------+------+--------+-----------------------------------+
| localhost | jay | curate | version_committers |
| localhost | jay | curate | trophies |
| localhost | jay | curate | subject_local_authority_entries |
| localhost | jay | curate | single_use_links |
| localhost | jay | curate | searches |
| localhost | jay | curate | schema_migrations |
| localhost | jay | curate | receipts |
| localhost | jay | curate | proxy_deposit_rights |
| localhost | jay | curate | orcid_profile_requests |
| localhost | jay | curate | notifications |
| localhost | jay | curate | local_authority_entries |
| localhost | jay | curate | local_authorities |
| localhost | jay | curate | help_requests |
| localhost | jay | curate | follows |
| localhost | jay | curate | feed_entries |
| localhost | jay | curate | domain_terms_local_authorities |
| localhost | jay | curate | domain_terms |
| localhost | jay | curate | devise_multi_auth_authentications |
| localhost | jay | curate | conversations |
| localhost | jay | curate | checksum_audit_logs |
| localhost | jay | curate | change_manager_changes |
| localhost | jay | curate | bookmarks |
+-----------+------+--------+-----------------------------------+
22 rows in set (0.02 sec)
mysql> SELECT Host, User, Db FROM mysql.db WHERE Update_priv = 'Y';
+-----------+------+--------+
| Host | User | Db |
+-----------+------+--------+
| localhost | | curate |
+-----------+------+--------+
1 row in set (0.00 sec)
mysql> SELECT Host, User FROM mysql.db WHERE Update_priv = 'Y';
+-----------+------+
| Host | User |
+-----------+------+
| localhost | |
+-----------+------+
1 row in set (0.00 sec)
mysql> SELECT * FROM mysql.user WHERE User = 'jay';
+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked |
+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| localhost | jay | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *DD431E425EB586BD1C8173743C46FD14F76C03ED | N | 2017年10月12日 17:23:39 | NULL | N |
+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
1 row in set (0.03 sec)
mysql> SELECT * FROM mysql.db WHERE User = 'jay' AND Db = 'curate';
Empty set (0.01 sec)
mysql> SELECT * FROM mysql.user WHERE Host = 'localhost';
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| localhost | jay | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *DD431E425EB586BD1C8173743C46FD14F76C03ED | N | 2017年10月12日 17:23:39 | NULL | N |
| localhost | mysql.session | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2017年10月12日 11:39:23 | NULL | Y |
| localhost | mysql.sys | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2017年10月12日 11:39:23 | NULL | Y |
| localhost | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *EC64FACB5BA6F06DCFB981AF3C1AC6963393B8BE | N | 2017年10月12日 11:41:57 | NULL | N |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM mysql.db WHERE Host = 'localhost' AND Db = 'curate';
+-----------+--------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+-----------+--------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| localhost | curate | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
+-----------+--------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM mysql.tables_priv WHERE table_name = 'users';
+-----------+--------+------+------------+----------------+---------------------+------------+-------------+
| Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv |
+-----------+--------+------+------------+----------------+---------------------+------------+-------------+
| localhost | curate | jay | users | root@localhost | 0000-00-00 00:00:00 | Select | |
+-----------+--------+------+------------+----------------+---------------------+------------+-------------+
1 row in set (0.00 sec)
mysql>

Question 2

Are you in the correct database when you run the UPDATE?

Question 3

Are any VIEWs or Stored Routines involved?

Question 4

@ypercubeTM Yes.

Question 5

@RickJames I believe not. Basically, it's a Ruby on Rails application with MySQL as DB. Rake task creates the database and all the tables required for the application. Let me know how/what to look for. Thanks to you both for prompt replies.

Question 6

Which specific version of 5.7? Do you have any authentication plugins?

Question 7

Peruse these to see if he is getting in some other way:

SELECT Host, User, Db, Table_name FROM mysql.tables_priv
 WHERE table_priv LIKE '%Update%';
SELECT Host, User, Db FROM mysql.db WHERE Update_priv = 'Y';
SELECT Host, User FROM mysql.db WHERE Update_priv = 'Y';

Or...

SELECT * FROM mysql.user WHERE User = 'jay';
SELECT * FROM mysql.db WHERE User = 'jay' AND Db = 'curate';
SELECT * FROM mysql.user WHERE Host = 'localhost';
SELECT * FROM mysql.db WHERE Host = 'localhost' AND Db = 'curate';
SELECT * FROM mysql.tables_priv WHERE table_name = 'users';

Wildcards (eg, '%') may take precedence.

Question 8

I'll, once I get back to my work laptop tomorrow. Thanks!

Question 9

@RickJames SHOW GRANTS FOR ... should capture everything from all the permissions tables, shouldn't it? Even an updatable view with SQL SECURITY DEFINER shouldn't allow itself to be written to by an invoker without the invoker having privileges against the view.

Question 10

I think that is the potential flaw. And, I suggest it is deliberate for a Stored Routine after, say, jay's request has been checked.

Question 11

@RickJames updated my question with the query results that you just asked me to post. There is a user ' '.

Question 12

Hmmm... SHOW GRANTS FOR ''@'localhost';

Rick James Rick James 80.7k5 gold badges52 silver badges119 bronze badges · Answer 1 · 2017-10-13 00:52:42Z

0

Peruse these to see if he is getting in some other way:

SELECT Host, User, Db, Table_name FROM mysql.tables_priv
 WHERE table_priv LIKE '%Update%';
SELECT Host, User, Db FROM mysql.db WHERE Update_priv = 'Y';
SELECT Host, User FROM mysql.db WHERE Update_priv = 'Y';

Or...

SELECT * FROM mysql.user WHERE User = 'jay';
SELECT * FROM mysql.db WHERE User = 'jay' AND Db = 'curate';
SELECT * FROM mysql.user WHERE Host = 'localhost';
SELECT * FROM mysql.db WHERE Host = 'localhost' AND Db = 'curate';
SELECT * FROM mysql.tables_priv WHERE table_name = 'users';

Wildcards (eg, '%') may take precedence.

Share

Improve this answer

answered Oct 13, 2017 at 0:52

Rick James's user avatar

Rick James Rick James

80.7k5 gold badges52 silver badges119 bronze badges

5

I'll, once I get back to my work laptop tomorrow. Thanks!

Jay
– Jay

2017年10月13日 01:06:44 +00:00
Commented Oct 13, 2017 at 1:06
@RickJames SHOW GRANTS FOR ... should capture everything from all the permissions tables, shouldn't it? Even an updatable view with SQL SECURITY DEFINER shouldn't allow itself to be written to by an invoker without the invoker having privileges against the view.

Michael - sqlbot
– Michael - sqlbot

2017年10月13日 02:12:31 +00:00
Commented Oct 13, 2017 at 2:12
I think that is the potential flaw. And, I suggest it is deliberate for a Stored Routine after, say, jay's request has been checked.

Rick James
– Rick James

2017年10月13日 02:30:32 +00:00
Commented Oct 13, 2017 at 2:30
@RickJames updated my question with the query results that you just asked me to post. There is a user ' '.

Jay
– Jay

2017年10月13日 14:19:41 +00:00
Commented Oct 13, 2017 at 14:19
Hmmm... SHOW GRANTS FOR ''@'localhost';

Rick James
– Rick James

2017年10月13日 19:01:06 +00:00
Commented Oct 13, 2017 at 19:01

Add a comment |

Stack Exchange Network

MySQL - Why does update query still run with only select privileges on a given table for a given user?

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Hot Network Questions

MySQL - Why does update query still run with only select privileges on a given table for a given user?

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Related

Hot Network Questions