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 [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     | 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.

4
  • 1
    Any entries in mysql.db for the user? 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
    Jun 4, 2014 at 20:54
  • @DerekDowney I was just thinking the same thing 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. Jun 4, 2014 at 21:02

2 Answers 2

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%';
3
  • 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, 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. 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
    Jun 4, 2014 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
  • 1
    FWIW, this is fixed in Percona Server. I.e. we just don't add those rows in our distribution. Jun 4, 2014 at 21:34

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .