Simple Login Issue

RogerWilco · #1 (**permalink**) 01-04-05, 19:52

I am trying to set up a PHP/MySQL application given to me from a third party. Everything is installed on my server, but when I hit the site with a browser, I get a regular database login error. I'm very new to MySQL administration so this is likely a newbie issue. However, I have a lot of experience administering SQL Server so I'm not completely cluless.

I can reproduce the login error at the command line (actual login is changed for privacy):

Running:

Code:

mysql --user=webuser --password=samplepw

Results in:

Code:

ERROR 1045: Access denied for user: 'webuser@localhost' (Using password: YES)

I do a select * from user and get:

Code:

+-----------+------------+------------------+
| Host      | User       | Password         |
+-----------+------------+------------------+
| localhost | root       | XXXXXXXXXXXXXXXX |
| %         | root       |                  |
| localhost |            |                  |
| %         |            |                  |
| localhost | sa         | XXXXXXXXXXXXXXXX |
| %         | webuser    | XXXXXXXXXXXXXXXX |
+-----------+------------+------------------+

I've set the password using several variations of the same command:

Code:

SET PASSWORD FOR 'webuser'@'%' = PASSWORD('samplepw');
SET PASSWORD FOR 'webuser' = PASSWORD('samplepw');
SET PASSWORD FOR webuser = PASSWORD('samplepw');

These all return the suspicious message:

Code:

Query OK, 0 rows affected (0.00 sec)

Why 0 rows affected? That sounds suspicious. However, I can see the encrypted password change in the user table if I try different or blank passwords. Also, if I try an invalid user name, I get an actual error and not the "0 rows affected" message.

None of the above helps and I can still never log in.

The third party that sold us this software specifically designated versions of PHP/MySQL to use instead of the latest.

MySQL version - 3.23.49
PHP version - 4.3.1
(I'm using a Windows 2K server box with all the latest patches and IIS)

My plan is to stick with their version recomendations for now. And eventually, when everything is running smoothly, upgrade to the latest versions.

matt_p · #2 (**permalink**) 01-05-05, 09:52

Common headache with mysql and its authentication scheme,
it's noted somwehere in the docs.
( http://dev.mysql.com/doc/mysql/en/Co...on_access.html )
Shortly spoken, the row "localhost"/blank in mysql.users matches first,
so "webuser/%" isn't considered anymore by the authentification mechanism or some logic around these terms.
Am to lazy now to discuss in full detail ...

Try a ...

GRANT ALL on <yourdatabase>.* to webuser@'localhost' identified by "samplepw";

(you can use "*" instead of <yourdatabase> to give access to all dbs).
This should insert a new row into the mysql.user table,
withe the combination "webuser/localhost"
after that, it should work.

matt_p · #3 (**permalink**) 01-05-05, 10:38

Did some digging around, and finally found the right location where this is documented:

http://dev.mysql.com/doc/mysql/en/Access_denied.html

and I dare quoting the relevant piece here ...

Quote:

If you can't figure out why you get Access denied, remove from the user table all entries that have Host values containing wildcards (entries that contain `%' or `_'). A very common error is to insert a new entry with Host='%' and User='some_user', thinking that this will allow you to specify localhost to connect from the same machine. The reason that this doesn't work is that the default privileges include an entry with Host='localhost' and User=''. Because that entry has a Host value 'localhost' that is more specific than '%', it is used in preference to the new entry when connecting from localhost! The correct procedure is to insert a second entry with Host='localhost' and User='some_user', or to delete the entry with Host='localhost' and User=''. After deleting the entry, remember to issue a FLUSH PRIVILEGES statement to reload the grant tables.

Hope this helps !

RogerWilco · #4 (**permalink**) 01-05-05, 12:42

Thank you matt_p!! That was the problem!

That is really counterintuitive. Even when you explicitly specify a username MySQL will still log in with an anonymous@localhost account. I see the benefit of giving users different levels of access from different servers but this implementation is just not well thought out.

Anyway, that problem is solved. On to the next... Thanks again

matt_p · #5 (**permalink**) 01-06-05, 03:34

Hello Roger,
you're right - is not intuitive at all. But then, on the other hand, MYSQL is the only one from the RDMSs I know (Mysql, MSSQL, Oracle, DB2) that has any builtin logic at all which distinguishes directly "from where" (which IP-adress) a connect request is issued, if I'm not mistaken.
Anyway, glad that I could help and thx for your feedback ...

Internet Services

Web Development

Digital Marketing

Consumer Tech