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 | manydeposits@example.com | $2a$10$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 | nodeposits@example.com   | $2a$10$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 | manager@example.com      | $2a$10$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 | delegate@example.com     | $2a$10$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>
  • 1
    Are you in the correct database when you run the UPDATE? – ypercubeᵀᴹ Oct 12 '17 at 23:30
  • Are any VIEWs or Stored Routines involved? – Rick James Oct 13 '17 at 0:41
  • @ypercubeᵀᴹ Yes. – Jay Oct 13 '17 at 0:55
  • @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. – Jay Oct 13 '17 at 0:55
  • Which specific version of 5.7? Do you have any authentication plugins? – Rick James Oct 13 '17 at 2:36
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.

  • I'll, once I get back to my work laptop tomorrow. Thanks! – Jay Oct 13 '17 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 Oct 13 '17 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 Oct 13 '17 at 2:30
  • @RickJames updated my question with the query results that you just asked me to post. There is a user ' '. – Jay Oct 13 '17 at 14:19
  • Hmmm... SHOW GRANTS FOR ''@'localhost'; – Rick James Oct 13 '17 at 19:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.