Bug #74840 MySQL fails to respect table specific access rights for the ANY user (''@...)

• Submitted: 13 Nov 2014 16:27
• Modified: 30 Oct 2017 12:09
• Reporter: Tobias Kux
• Status: Not a Bug
• Category: MySQL Server: Security: Privileges
• Severity: S3 (Non-critical)
• Version: 5.5.40, 5.6.16, 5.6.21
• OS: Any
• CPU Architecture: Any

[13 Nov 2014 16:27] Tobias Kux

Description:
I'm trying to allow all users to SELECT from a table `tbl_test` that's part of the database `db_test`. However those access rights do not seem to work.
Query: GRANT SELECT ON `db_test`.`tbl_test` TO ''@'localhost';
(note that ''@'%' still doesn't work).
Query that failed: SELECT * FROM `db_test`.`tbl_test`
Error: 1142 (42000): SELECT command denied to user 'testuser'@'localhost' for table 'tbl_test'
Granting these permission to a specific user worked as expected.

Granting all users to SELECT from the whole database `db_test` worked.
Query: GRANT SELECT, REFERENCES ON `db_test`.* TO ''@'localhost';
Interestingly granting these permissions only worked after a 'FLUSH PRIVILEGES' while revoking them worked immediately.

According to http://dev.mysql.com/doc/refman/5.6/en/account-names.html the empty user should match any user thus after executing the first GRANT, any user should be able to SELECT from `db_test`.`tbl_test`.

Restarting the server did not solve this problem.

How to repeat:
- Create a database which no one has access to or use an existing database
- Create a new table or use an existing one
- Create a new user or use an existing one
- Make sure the user does not have any access rights for the database or any table
- Grant the user the SELECT permission on the table (not the entire database).
- Try to SELECT from that table with that user

Suggested fix:
Also match the empty user with any user for entries from `mysql`.`tables_priv`

[30 Oct 2017 12:09] MySQL Verification Team

Hi!

For security reasons, MySQL does not accept wildcards in the user name. You can create a privilege for the anonymous user, which is ``@`%`, but then you have to login as anonymous, not as user with a definite name. On Unix, user name is automatically read from the system.