0

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?

Thilo
  • 17,565
  • 5
  • 68
  • 84
Arnthor
  • 2,563
  • 6
  • 34
  • 54

2 Answers2

6

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.

Thilo
  • 17,565
  • 5
  • 68
  • 84
  • 4
    Do not grant all to every database in the server. Just grant that user permissions for the database/tables they need access too. – grantk May 03 '11 at 21:23
  • Works just fine =) Forgot to create user in the same way (@'localhost') – Arnthor May 03 '11 at 21:43
  • 1
    Can this be done to the user inserted into a particular database using `INSERT INTO ` clause? I mean if a user isn't created using `CREATE USER ` command. – Tan Jan 24 '17 at 17:07
3

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.

isra17
  • 401
  • 3
  • 8