p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   MySQL (http://p2p.wrox.com/forumdisplay.php?f=99)
-   -   Access denied for user (http://p2p.wrox.com/showthread.php?t=14196)

wisconsin June 14th, 2004 02:16 PM

Access denied for user
 
I'm trying to set up a script but any user with a password that I've set up in MySQL (except for root user) is not able to access the databases. If the user does not have a password, it works. Any suggestions about why I'm getting errors such as the following would be welcome:

Warning: Access denied for user: 'poll@localhost' (Using password: YES) in sys:/apache/html/poll/db/include/class_mysql.php on line 32

Warning: MySQL Connection Failed: Access denied for user: 'poll@localhost' (Using password: YES) in sys:/apache/html/poll/db/include/class_mysql.php on line 32
Connection Error

MySQL Error : Connection Error
Error Number: 1045 Access denied for user: 'poll@localhost' (Using password: YES)
Browser : Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.0.3705)
Referer : http://www.mysite.net/poll/
PHP Version : 4.2.4-dev
Server : Apache/1.3.27 (NETWARE) PHP/4.2.4-dev mod_jk/1.2.2-dev
Script Name : /poll/db/index.php

Daniel Walker June 15th, 2004 06:10 AM

Is this a new installation of MySQL? If so, any passwords and permissions you apply to users at runtime will only take effect after giving it a FLUSH PRIVILeges command (or after restarting the server, of course).

wisconsin June 15th, 2004 10:27 AM

It's not really a new installation, but I'm just now starting to use it more. I assume the SQL command is FLUSH PRIVIL to FLUSH PRIVILeges? I had been able to log in with the different users I set up before even with passwords. It's only recently that only the root user can log in. Other accounts I've set up allow the user to access the databases but only if no password exists for that user. I'm just not sure why that's happening and what to do to fix it.

Daniel Walker June 16th, 2004 06:33 AM

Urgle! No, it should be FLUSH PRIVILEGES (don't know what happened to my shift key, there :).

One thing to check: you are assigning the passwords (e.g. "my_secret") with values such as:

password('my_secret')

...are you? The reason I ask, is that the DB never actually stores the password; only its MD5 hash. So if you were to assign a user with a statement like:

INSERT INTO user (host,user,password,select_priv) VALUES ('localhost','bo_selecta','my_secret','Y');

The user won't be able to log in, since they'd have to type in something for their password which generated 'my_secret' AFTER having been put through the MD5 hash. Instead, you'd insert something like:

INSERT INTO user (host,user,password,select_priv) VALUES ('localhost','bo_selecta',password('my_secret'),'Y ');

Then, when you look at the user table in the 'mysql' database, you should see something like:

5jkHG7nH0943gFGYj0jythg

For the password (being the output generated by putting the actual password through the hash).

Just a thought.

wisconsin June 16th, 2004 07:30 AM

I checked the user database and found a password had been created and gone through the MD5 hash. I also noticed (and I'm not sure whether it has any relevance to the login problem) that the root user has two entries, one for localhost and one for host %. Whereas the new users I've created that are unable to log in have one entry for host %. I also see at the bottom of some pages a message that says:
Error
The additional Features for working with linked Tables have been deactivated. To find out why click here.

I also tried setting a new password using the statement below and received an error:
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'user( host, user,
PASSWORD , select_priv ) (I'm using MySQL 4.0.18)

Daniel Walker June 16th, 2004 10:06 AM

Well the problem appears to be a lack of a space between the table name 'user' and the bracket.

As for your users being granted a host entry of "%", this is simply an implementation of the SQL92 '%' wildcard character, which you will become increasingly familiar with as you progress with MySQL (and database managers in general). It means simply "Any host", and so, by implication, sould permit users logging on from "localhost" (i.e. locally, on the same machine) to connect - although, as a further aside, MySQL defaults to rerouting all requests from localhost through its Unix Socket (or the equivalent "pipe" file on Win NT) because these connections are much faster.

FWIW, Debian's Woody and Sarge versions of MySQL both default to "skip-networking", which is to say MySQL will disallow any netweorked connections, restricting access to connections from any host other than "localhost" (and event those requests are, like I say, rerouted through the socket, which is the only reason they can get through). So, if you're running Woody or Sarge and find that yourself, logging on locally as root, say, can log in, but no one else can (no matter what privileges you give them) then that's the reason. I don't think this is the case in this instance, but I thought I'd pass it on as a helpful hint, sould you ever find yourself in that situation.

You could *try* adding a localhost entry (which, by the time I've finished typing all this nonsense, you'll probably have already done, anyway :) and see if they can log on then, but I can't see why that should be the case.

wisconsin June 16th, 2004 12:23 PM

Tried several combinations of moving the space in the statement below and still get the #1064 error.

SQL-query :
INSERT INTO user( host, user,
PASSWORD , select_priv )
VALUES ('localhost', 'poll', password( 'polldb' ) , 'Y')

MySQL said:
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'user( host, user,
PASSWORD , select_priv )
VALUES ( 'localho

I did also try creating a user with localhost as the host instead of the wildcard character. Still didn't work.

I also see in the user table that the root user's MD5 code for the password is different between the localhost and % accounts. Seemed strange to me. Why would that be the case?

Daniel Walker June 17th, 2004 07:04 AM

Quote:

quote:Originally posted by wisconsin
 Tried several combinations of moving the space in the statement below and still get the #1064 error.
Okay, try:

................space here
                |
                V
INSERT INTO user ( host, user,
PASSWORD , select_priv )
VALUES ('localhost', 'poll', password( 'polldb' ) , 'Y')


Quote:

quote:
I also see in the user table that the root user's MD5 code for the password is different between the localhost and % accounts. Seemed strange to me. Why would that be the case?
The passwords must be different, in either case, simple as that: it's the only way that can happen. The definition of a one-way-hash algorithm is that it always generates the same garbled output from the same input, and that two input strings have a vanishingly small chance of 'collision' (taht is to say, they are extraordinarily unlikely to both generate the same output string).

(FWIW, this is why, on the rare occasions someone has tried to hack code on the open source code repositories, its is immediately detected: add one character to the entire Linux kernel, and you get an entirely different checksum as the output. Since the checksum is what you use to verify the authenticity of the code, any changes are immediately apparent.)

wisconsin June 17th, 2004 11:52 AM

That's where I tried putting a space in the statement. I tried it again... with the same result:

MySQL said:
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'user( host, user,
PASSWORD , select_priv )
VALUES ( 'localho

I even put several spaces there and they were apparently removed.

happygv June 17th, 2004 01:05 PM

You haven't used SPACE after TABLENAME "user" and before "(" I believe.

It works perfectly for me.

Code:

INSERT INTO user ( host, user, PASSWORD , select_priv )
VALUES ('localhost', 'poll', password( 'polldb' ) , 'Y');

Cheers!

_________________________
-Vijay G
[8]Strive for Perfection[8]


All times are GMT -4. The time now is 10:03 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.