granting a user the right to connect to a database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
davidklonski
Forum Contributor
Posts: 128
Joined: Mon Mar 22, 2004 4:55 pm

granting a user the right to connect to a database

Post by davidklonski »

Hello

I have a local mysql server (MySQL 5.0)
I would like to create a new database in the server (called app) and to create a user account that will have the right to connect to that database.

I running the following code in order to set up the account:

Code: Select all

// create the new database:
$connection = mysql_connect("localhost", "root", "", MYSQL_CLIENT_COMPRESS);
mysql_query("CREATE DATABASE IF NOT EXISTS app;", $connection);

// set up the second account:
mysql_query("GRANT LOCK TABLES, SELECT, UPDATE, INSERT ON app.* TO 'user'@'%' identified by 'password';", $connection);
mysql_query("GRANT LOCK TABLES, SELECT, UPDATE, INSERT ON app.* TO 'user'@'localhost' identified by 'password';", $connection);
mysql_query("FLUSH PRIVILEGES;", $connection);
Next I try to perfrom the following code:

Code: Select all

$connection = mysql_connect("localhost", "user", "password", MYSQL_CLIENT_COMPRESS);
But I get the following error:
Client does not support authentication protocol requested by server; consider upgrading MySQL client

Is this the right way to create an account?
Why is the code failing?
I am trying to connect from the localhost to the server.
thanks in advance
davidklonski
Forum Contributor
Posts: 128
Joined: Mon Mar 22, 2004 4:55 pm

Post by davidklonski »

I have set the following as well, as recommended in the manual:

Code: Select all

mysql_query("SET PASSWORD FOR 'user'@'localhost' = OLD_PASSWORD('password');";
Now when I try to connect to the server I get the following error:
Access denied for user: 'user'@'localhost' to database 'app'

help is appreciated
Post Reply