mysql_connect - username/pass problem

[Cherphas]

Hello,

I just got MySQL and PHP working. Now I'm trying to create a login/pass page but I'm having problems. When I submit the info to check if the user exist in the database, it gives me this error:

Warning: Access denied for user: 'MyName@localhost' (Using password: YES) in /the_Path/default.php on line 104

I'm not sure if I understand the function of the Username and Password in the script. When I started "winmysqladmin.exe" I setup "MyName" as my UserName and also my Password, but It still doesn't work.

Does anybody know why?

One more thing, the PHP files are on a server and the MySQL database is on my machine. Is this a problem?

Cherphas

[jerdo]

Yes that's a problem. According to the error your script is trying to connect to localhost (the mahcine it's running on) using the username and password that is setup on your machine. You will need to setup a mysql user that is allowed access from the machine your scripts on running, and then set all your scripts to connect to your sql machine.

[theeper]

Did you flush privileges after adding the user?

[Cherphas]

No I didn't, should I? All I did was install MySQL and added the PHP script in my page and hope for the best 🆒 ...of course I knew it would not be that simple so I did a bit of research but could not find a specific answer.

So how do I setup a mysql user that is allowed access from the machine and then point my script to the machine MySQL is running on?🙁

Or should MySQL be installed on the server and then I could work on my machine and copy the files to the server?

[theeper]

Add your user, make sure you specify the proper hostname or IP that your script resides on, then issue a flush privileges.

[zalath]

If you have the server running on your local machine, you should connect to the ip of that machine. Also, remember to grant your username access from the host your script is running on.

[Cherphas]

Man I'm really confused! The server that has the PHP files is on another machine (someting I should have mentioned earlier) and the MySQL is installed on my hard drive. Let me see if I understand now:

In MySQL I setup a new user with the name of our network(or IP) as host and the name of my machine as username and give it a password? mysql_connect would have:

mysql_connect(the network name, my machine name, the pass that I assigned)?

[theeper]

No, like this ...

mysql_connect("IP or hostname of MySQL server","username","password");

[theeper]

When you add the user to the MySQL server, make sure that user has permission to connect to the MySQL server from the machine your scripts run on. A command line to add the user would look something like this.

GRANT SELECT,INSERT on mydatabase.mytable TO username@"hostname where scripts run" IDENTIFIED BY 'password'

[Cherphas]

Hi,

I created a user and grant the privileges, but it still gave me an error. I changed the username and password option in mysql_connect to "root" and it actually checks if the user is in the database.

Does anyone know why it didn't work with my Username and Pass of my machine?

I also had a sessions error, but I did some research and it turns out I had something before "session_star();". It's fixed now.

Cherphas

[Cherphas]

Anybody?!

[theeper]

If you added the username and password properly, then flushed privileges, it should work. Keep in mind this is not your *nix user, but your MySQL user.

[Cherphas]

I have this:

$connection=mysql_connect("MyMachnine","MyName","MyPass") or die('Could not connect to the database server');
$db = mysql_select_db("userinfo", $connection) or die ("Unable to select database."); 

if I put the name of my machine as host, my Username and password I created in MySQL, it works until it hits this piece of code:

$db = mysql_select_db("userinfo", $connection) or die ("Unable to select database."); 

It just gives me "Unable to select database". But when I entered "root" for the Username and Password, it worked no problem.

[theeper]

Are you sure the username has permission the DB in question?

[jerdo]

As theeper said, that means the username you created does not have access to that db. You might want to look into getting phpMyAdmin setup on your system with MySQL, it will make changing these settings quite a bit easier.

[Cherphas]

Well I did as you said:

GRANT SELECT,INSERT on mydatabase.mytable TO username@"hostname where scripts run" IDENTIFIED BY 'password'
then I Flushed privileges;

That is where I entered the information of MY machine(the one that has MySQL). But why am I suppose to give permission to my machine if MySQL is on my machine? I even added the machine that has the PHP script as a user, but it doesn't work.

In the Mysql users database, I can see the username and host that I've entered, but the password is not the same that I have entered. It's all different numbers and letters. Anyway, this must work, because it accepts this line:

$connection=mysql_connect("MyMachine","My_Name","My_Pass") or die('Could not connect to the database server');

But in the next line:

$db = mysql_select_db("userinfo",$connection) or die ("Unable to select database.");

It returns my error message "Unable to select database". But, as I said, when I enter the "root" as username, it works fine. Isn't my machine and "root" the same?

Correct me if I'm wrong, the info in mysql_connect just tells the php script to connect to the MySQL db that is on the machine that I specified right?

I'm sorry for repeating the same stuff, but I want to make sure I explain myself correctly.

[Cherphas]

I changed it to this to see my error:

$db = mysql_select_db("userinfo",$connection) or die("Can't select database, error: " . mysql_error());

and the error is:

Can't select database, error: Access denied for user: 'myUsername@machineWithScript' to database 'userinfo'

And I grant the privileges and flushed them. Is there anything else I might have forgot?!!!😕

Thanks for all you help by the way:p

[theeper]

Okay, the reason you don't have permission on that DB is that you have not given the MySQL user (the user that the script uses) permission to connect to that DB. Your MySQL root user probably has permissions on all DBs.

When you add a new user, you need to tell the MySQL server what username and IP/hostname your scripts will connect from. Also, you need to tell MySQL what DB(s) or table(s) this user has permissions on.

Notice the GRANT line I gave to you.

GRANT SELECT,INSERT on mydatabase.mytable TO username@"hostname where scripts run" IDENTIFIED BY 'password'

This means that you have given SELECT and INSERT permissions ONLY on the 'mytable' table of the database 'mydatabase' to a user called 'username' which is coming from 'hostname where scripts run'. This user would have SELECT and INSERT perms on this table and this table only.

If you want to give the user perms on all tables in the 'mydatabase' DB, modify your GRANT query like this ...

GRANT SELECT,INSERT on mydatabase.* TO username@"hostname where scripts run" IDENTIFIED BY 'password'

And if you want to grant all perms on that DB, modify it like this (not recomended though).

GRANT ALL PRIVILEGES on mydatabase.* TO username@"hostname where scripts run" IDENTIFIED BY 'password'

[Cherphas]

It worked!! Theeper, you are the man! I'll stop bugging you now(not for too long though 🙂). Thank you VERY much for all your help!

Cherphas