MySQL - Access denied for user

问题:

So, I create a new user

CREATE USER servname_shb IDENTIFIED BY 'password';

Grant him all the privileges:

GRANT ALL ON *.* TO servname_shb;

No errors so far. Then I try to connect to database:

$dbhost = "localhost";
$dbname = "servname_shbusers";
$dbuser = "servname_shb";
$dbpass = "password";

$c = mysql_connect($dbhost,$dbuser,$dbpass) or die("Error:".mysql_error());
mysql_select_db($dbname) or die ("Error connecting to databse:".mysql_error());

And get an error:

Access denied for user 'servname_shb'@'localhost' (using password: YES) in <path>

I tried FLUSH PRIVILEGES, dropping and recreating user - no effect. What am I doing wrong?

回答1:

The error messages gives you a hint already. Use this grant statement:

GRANT ALL ON *.* TO 'servname_shb'@'localhost';

Or better, as @grantk points out in the comments, refine the access scope to only what is actually needed.

回答2:

You must use 'servname_shb'@'localhost' as the username. Your request become:

CREATE USER 'servname_shb'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON *.* TO 'servname_shb'@'localhost';

Note: 'servname_shb'@'localhost' only allow connection from localhost. You can use 'servname_shb'@'%' to allow connection from everywhere.