5

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 user5@10.14.%                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| 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     | 127.0.0.1     | Y           |
| root     | 10.14.12.8    | 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? – Derek Downey Jun 4 '14 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 Jun 4 '14 at 20:54
  • @DerekDowney I was just thinking the same thing – RolandoMySQLDBA Jun 4 '14 at 21:01
  • 2
    Hey Paul, please login as root and run FLUSH PRIVILEGES;. After that, find out if user5 can still do things. – RolandoMySQLDBA Jun 4 '14 at 21:02
5

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

USE app_db;
SHOW GRANTS FOR user5;

or check in the data dictionary for the same:

SELECT *
FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
WHERE GRANTEE LIKE '%user5%';

if not anything, then maybe also check

SELECT *
FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
WHERE GRANTEE LIKE '%user5%';
  • 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 Jun 4 '14 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. – Joshua Huber Jun 4 '14 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 Jun 4 '14 at 21:14
3

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. – Bill Karwin Jun 4 '14 at 21:34

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.