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] | $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 | [email protected]   | $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 | man[email protected]      | $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 | [email protected]     | $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>
Improve this question
6
  • 1
    Are you in the correct database when you run the UPDATE?
    – ypercubeᵀᴹ
    Oct 12, 2017 at 23:30
  • Are any VIEWs or Stored Routines involved?
    – Rick James
    Oct 13, 2017 at 0:41
  • @ypercubeᵀᴹ Yes.
    – Jay
    Oct 13, 2017 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, 2017 at 0:55
  • Which specific version of 5.7? Do you have any authentication plugins?
    – Rick James
    Oct 13, 2017 at 2:36

1 Answer 1

Reset to default
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.

Improve this answer
5
  • I'll, once I get back to my work laptop tomorrow. Thanks!
    – Jay
    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
    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
    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
    Oct 13, 2017 at 14:19
  • Hmmm... SHOW GRANTS FOR ''@'localhost';
    – Rick James
    Oct 13, 2017 at 19:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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