User can't access a database

Question

In my PHP script, I'm accessing two databases db1 and db2. I have a user myuser@localhost that can access db1 but can't access db2.

When selecting from mysql.user table, there is one record and the host for that user is a wildcard %, there isn't a localhost host.

SELECT user, host FROM mysql.user WHERE user = 'myuser'; give me:

+------------+------+
| user       | host |
+------------+------+
| myuser     | %    |
+------------+------+
1 row in set (0.00 sec)

Looking at GRANTS for that user, I see same permissions for db1 as for db2

SHOW GRANTS FOR 'myuser'@'%';

+-----------------------------------------------------------------------------------------------------------+
| Grants for myuser@%                                                                                   |
+-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'myuser'@'%' IDENTIFIED BY PASSWORD '*7733323232...' |
| GRANT ALL PRIVILEGES ON `db1`.* TO 'myuser'@'%'                                                |
| GRANT ALL PRIVILEGES ON `db2`.* TO 'myuser'@'%'                                              |
+-----------------------------------------------------------------------------------------------------------+

In my PHP script I can access db1 however I get an error: INSERT command denied to user 'myuser'@'localhost' for table 'HISTORY'.

It says user is myuser@localhost and people suggested adding permission for myuser@localhost however, why does this user have access to db1 and not to db2?

Answer 1

localhost does not match % in MySQL. It seems like it should, but in fact it doesn't. You'd have to separately grant privileges to user@localhost, both for the USAGE privilege, and for the privileges on each database.

Or you can connect as [email protected] which does match %. Using the IP address for localhost seems like it should work identically to localhost, but it doesn't. You need to have two lines in the mysql.user table (and also in the mysql.db table in your case) to enable both.

To demonstrate the difference between localhost and 127.0.0.1:

Connecting as mysql -h localhost uses the UNIX socket interface, and bypasses TCP/IP. This can be slightly better for performance, but it has the effect on grant matching described above.

You can force a local TCP/IP connection by connecting as mysql -h 127.0.0.1. Then it will pick up the grants you have made to myuser@%.

So to get the same user, password, and privileges for both the socket interface and the TCP/IP interface, you'd need to run all of the following statements:

GRANT USAGE ON *.* TO 'myuser'@'%' IDENTIFIED BY PASSWORD '*7733323232...'
GRANT USAGE ON *.* TO 'myuser'@'localhost' IDENTIFIED BY PASSWORD '*7733323232...'
GRANT ALL PRIVILEGES ON `db1`.* TO 'myuser'@'%'
GRANT ALL PRIVILEGES ON `db1`.* TO 'myuser'@'localhost'
GRANT ALL PRIVILEGES ON `db2`.* TO 'myuser'@'%'
GRANT ALL PRIVILEGES ON `db2`.* TO 'myuser'@'localhost'

Answer 2

If you haven't done that already, you need to run flush privileges so that mysql knows there was a change and reloads the privileges table for users:

FLUSH PRIVILEGES;