mysql - 具有所有权限的用户拒绝 ODBC 连接器访问

Question

我正在尝试“测试”ODBC Connector 5.3，它给了我“用户 [用户名]@localhost 的访问被拒绝(使用密码:YES)”。但是，当我查看用户表时，我看到 GRANT ALL PRIVILEGES ON *.* TO [username]@'%' IDENTIFIED BY PASSWORD 'xxxx' WITH GRANT OPTION。

我觉得所有特权都应该由该行授予。我错过了什么？

Answer 1

这是一个常见错误，因为 MySQL 访问受到客户端主机以及用户名和密码凭据的限制，并且必须专门授予访问权限，如 the MySQL documentation 中详细说明。 。请注意，localhost 是一种特殊情况，通配符无法解决它GRANT --

If you cannot figure out why you get Access denied, remove from the user table all rows that have Host values containing wildcards (rows that contain '%' or '_' characters). A very common error is to insert a new row with Host='%' and User='some_user', thinking that this enables you to specify localhost to connect from the same machine. The reason that this does not work is that the default privileges include a row with Host='localhost' and User=''. Because that row has a Host value 'localhost' that is more specific than '%', it is used in preference to the new row when connecting from localhost! The correct procedure is to insert a second row with Host='localhost' and User='some_user', or to delete the row with Host='localhost' and User=''. After deleting the row, remember to issue a FLUSH PRIVILEGES statement to reload the grant tables. See also Section 6.2.6, “Access Control, Stage 1: Connection Verification”.