User can't access a database
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 ?
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 user@127.0.0.1 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'
如果你还没有这样做，你需要运行flush privileges这样mysql知道发生了一些变化，并为用户重新载入特权表：
FLUSH PRIVILEGES;
This very likely has nothing to do with GRANTs.
A very common reason for having incorrect access rights is because of default users that exist in MySQL. Specially ones with '' for User (anonymous users) and/or Host in mysql.user table. Because of the way MySQL handles authentication and proxy users, and the sorting rules used on mysql.user table entries, one could end up using an unexpected user than the one they used for authentication.
Use SELECT USER(); to find out the connecting user that was used during authentication and SELECT CURRENT_USER(); to find out the effective user whose privileges apply during the current session .
And from http://dev.mysql.com/doc/refman/5.6/en/connection-access.html
It is a common misconception to think that, for a given user name, all rows that explicitly name that user are used first when the server attempts to find a match for the connection. This is not true. If you are able to connect to the server, but your privileges are not what you expect, you probably are being authenticated as some other account.
A mysql.user table similar to following
+-----------+----------+-
| Host | User | ...
+-----------+----------+-
| % | root | ... (root from any host)
| % | jeffrey | ... (jeffrey from any host)
| localhost | root | ... (root from localhost)
| localhost | | ... (any user from localhost)
+-----------+----------+-
becomes,
+-----------+----------+-
| Host | User | ...
+-----------+----------+-
| localhost | root | ...
| localhost | | ...
| % | jeffrey | ...
| % | root | ...
+-----------+----------+-
whenever the server reads the user table into memory, in order to handle multiple matches.
When a client attempts to connect, the server looks through the rows in sorted order and uses the first row that matches the client host name and user name.
Precedence is given as: values (IP address, host name, user name, etc.) > '%' > ''
Most of the time application server/client is running in the same host as the database, causing the host name to be picked up as localhost during authentication.
mysql -u jeffrey uses jeffrey@localhost which gets matched against ''@localhost instead of jeffrey@% .
Executing $MYSQL_HOME/bin/mysql_secure_installation will remove anonymous users, while securing the installation, alleviating this unexpected behaviour.
Also check:
[1] http://bugs.mysql.com/bug.php?id=36576 (check comment before last)
[2] http://bugs.mysql.com/bug.php?id=69570
上一篇: 用户自己的权限和组权限的用户权限
下一篇: 用户无法访问数据库