I have a mysql user (I'll call) "user5" who only has "grant usage" (i.e. no privileges) in the output of "show grants", but can still do a select on database "app_db" (which is what I want, but I do understand how it has that privilege). There is no anonymous user. How can user5 be using its database with this configuration?

When logged in as user5:

mysql> show grants;
| Grants for [email protected].%                                                                                   |
| GRANT USAGE ON *.* TO 'user5'@'10.14.%' IDENTIFIED BY PASSWORD '*long hash' |

When logged in as root, looking at the User table:

mysql> select User, Host, Select_priv from mysql.user;
| User     | Host          | Select_priv |
| root     | localhost     | Y           |
| root     |     | Y           |
| root     |    | Y           |
| user5    | 10.14.%       | N           |
| (other named non-root users)           |

...yet, as user5, "select * from app_db.users" returns results.

  • 1
    Any entries in mysql.db for the user? Commented Jun 4, 2014 at 20:31
  • @Derek Downey What is mysql.db? Oh, I think you mean the mysql database. Yes, per above, it shows use in mysql.user with no privileges.
    – Paul Lynch
    Commented Jun 4, 2014 at 20:54
  • @DerekDowney I was just thinking the same thing Commented Jun 4, 2014 at 21:01
  • 2
    Hey Paul, please login as root and run FLUSH PRIVILEGES;. After that, find out if user5 can still do things. Commented Jun 4, 2014 at 21:02

2 Answers 2


Most likely, your user5 has database (i.e. schema) privileges, just not server-wide privileges. So look here.

USE app_db;

or check in the data dictionary for the same:


if not anything, then maybe also check

  • After "use app_db", "show grants" returns the same entry as before (just "grant usage"). The two selects above both return the empty set.
    – Paul Lynch
    Commented Jun 4, 2014 at 21:00
  • How about the 2nd query above, the one on SCHEMA_PRIVILEGES view. Does that shed any light on user5? Or mabye run that query without the where clause, i.e.: SELECT * FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES just to see all the DB-level privileges. Commented Jun 4, 2014 at 21:06
  • Yes, that was it. I was just trying to edit my comment. There were SCHEMA_PRIVILEGES were there under a more fully qualified IP (i.e. not 10.14.%, but 10.14.12.%). Confusing, but that explains it. Thanks!
    – Paul Lynch
    Commented Jun 4, 2014 at 21:14

When I see GRANT USAGE for a specific user, that user can connect, get global status variables, and that's it. There is one horrible exception: THE TEST DATABASE !!!

Did you know that there are two entries in the mysql.db tables that allow anonymous logins to authenticate when the database is named test or a database whose first five characters are test_ ?

I wrote about this a long time ago :

My Posted Question : MySQL : Why are there "test" entries in mysql.db?

My Posted Answer : MySQL : Why are there "test" entries in mysql.db?

This is still a chronic problem to this very day.

To check this out, please run

select db,user,host from mysql.db where LEFT(db,4)='test';

If your output is the following:

mysql> select db,user,host from mysql.db where LEFT(db,4)='test';
| db      | user | host |
| test    |      | %    |
| test\_% |      | %    |
2 rows in set (0.10 sec)

and you are putting tables in a test database, then this is the root cause.

  • 1
    FWIW, this is fixed in Percona Server. I.e. we just don't add those rows in our distribution. Commented Jun 4, 2014 at 21:34

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.