Only “grant usage”, but can still select, drop, create?

Posted on

Question :

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.

Answer :

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%';

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.

Leave a Reply

Your email address will not be published. Required fields are marked *