Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > MySQL
| Search | Today's Posts | Mark Forums Read
MySQL General discussion about the MySQL database.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the MySQL section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 14th, 2004, 02:16 PM
Registered User
 
Join Date: Jun 2004
Location: , , .
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
 
Old June 15th, 2004, 06:10 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 256
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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).
 
Old June 15th, 2004, 10:27 AM
Registered User
 
Join Date: Jun 2004
Location: , , .
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old June 16th, 2004, 06:33 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 256
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old June 16th, 2004, 07:30 AM
Registered User
 
Join Date: Jun 2004
Location: , , .
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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)
 
Old June 16th, 2004, 10:06 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 256
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old June 16th, 2004, 12:23 PM
Registered User
 
Join Date: Jun 2004
Location: , , .
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?
 
Old June 17th, 2004, 07:04 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 256
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.)
 
Old June 17th, 2004, 11:52 AM
Registered User
 
Join Date: Jun 2004
Location: , , .
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old June 17th, 2004, 01:05 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Strive for Perfection




Similar Threads
Thread Thread Starter Forum Replies Last Post
phpMyAdmin - #1044 - Access denied for user crmpicco PHP Databases 0 October 21st, 2007 09:32 AM
access denied for new user, Chapter 3 kanjigirl BOOK: Beginning PHP5, Apache, and MySQL Web Development ISBN: 978-0-7645-7966-0 5 February 19th, 2006 05:08 PM
ERROR 1045: Access denied for user: OBDC@localhost kara333 BOOK: Beginning PHP, Apache, MySQL Web Development ISBN: 978-0-7645-5744-6 2 December 4th, 2005 07:57 AM
1044: Access denied for user '@localhost ... ajutla PHP Databases 3 April 16th, 2005 03:27 PM
Access Denied For User -Chapter 9 AMP_Engineer BOOK: Beginning PHP, Apache, MySQL Web Development ISBN: 978-0-7645-5744-6 5 September 25th, 2004 02:18 AM





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