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 | [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>
6
  • 1
    Are you in the correct database when you run the UPDATE? 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

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.

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. 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 you have read our privacy policy.

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