A HUGE thank you to everyone who helped me with this. Everything is working as expected now. That connection stuff (along with the blank user entry) got me!!!
> Finally, are you really sure you want someone to be able to connect as > (effective) root from *anywhere* on the net? Personally, I restrict that > level of access to localhost only. If you really need to be able to > administer mysql remotely, I'd strongly recommend you make the host part as > specific as you can. Maybe [EMAIL PROTECTED] or [EMAIL PROTECTED] Thanks for the tip and when we move to production, we definitely will have tighter security. Right now I'm just laying the groundwork and educating everyone (scary thought) so we can move to production with as little pain as possible. Thanks again. Lou ----- Original Message ----- From: "Michael Stassen" <[EMAIL PROTECTED]> To: "Lou Olsten" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, April 29, 2004 9:34 PM Subject: Re: Creating Users and Passwords > > Lou Olsten wrote: > > > I thought I had a handle on this, but now I'm all screwed up. > > > > MySQL 4.1.1a-alpha-max-debug-log > > Windows 2000 Server > > > > I'm trying to create a user roby with a password of 'foo' with access to everything. Here's what happens: > > > > - Sign in as root on the local host. > > - GRANT ALL PRIVILEGES ON *.* TO roby@'%' IDENTIFIED BY 'foo' WITH GRANT OPTION; > > That's correct. > > > - On same machine, try to login with: mysql -u roby -pfoo -h localhost > > - Receive: ERROR 1045 (28000): Access denied for user: 'roby'@'localhost' > > (Using password: YES) > > First, note that localhost is the default, so "-h localhost" is unnecessary, > though it shouldn't hurt. > > You need to read <http://dev.mysql.com/doc/mysql/en/Connection_access.html>. > The gist is that [EMAIL PROTECTED] matching is done host first, then user. If > more than one host matches, the most specific wins. So, when conecting as > roby from localhost, the anonymous user ''@localhost is a better match than > [EMAIL PROTECTED], because the host part is more specific. This bites a lot of people. > Most, I think, solve this by deleting the anonymous users. > > mysql -u root -p mysql > mysql> DELETE FROM user WHERE User=''; > mysql> FLUSH PRIVILEGES; > > Another possibility is that you have a [EMAIL PROTECTED] user, who would also > trump [EMAIL PROTECTED] when connecting from localhost. You could check with > > SELECT User, Host FROM user WHERE User='roby'; > > Then either drop that user or make his privileges match. > > > I'm wondering why that doesn't work, but here's where I get REALLY > > confused. I can then sign in with NO PASSWORD and get into the system: > > By default, the anonymous user has no password. You can check. Once you're > in, enter > > SELECT CURRENT_USER(); > > to see who mysql believes you are. > > > - mysql -u roby > > Welcome to the MySQL monitor. Commands end with ; or \g. > > Your MySQL connection id is 44 to server version: 4.1.1a-alpha-max-debug-log > > > > Type 'help;' or '\h' for help. Type '\c' to clear the buffer. > > > > mysql> > > > > Moreoever... there is no entry for roby in the mysql.db database, which I > > thought was supposed to happen when I granted everything. > > No. The db table holds db-specific privileges. Global privileges (*.*) go > in the user table. > > > I then issued: > > > > mysql> SET PASSWORD FOR roby = PASSWORD('foo'); > > Query OK, 0 rows affected (0.00 sec) > > mysql> flush privileges; > > Query OK, 0 rows affected (0.10 sec) > > You didn't specify a host, so this defaults to setting the password for > [EMAIL PROTECTED], which won't help if you're actually connecting as [EMAIL > PROTECTED] or > ''@localhost. > > > But still received: > > > > C:\mysql\bin>mysql -u roby -pfoo > > ERROR 1045 (28000): Access denied for user: 'roby'@'localhost' (Using password: YES) > > > > Any help is appreciated! > > > > Lou > > Finally, are you really sure you want someone to be able to connect as > (effective) root from *anywhere* on the net? Personally, I restrict that > level of access to localhost only. If you really need to be able to > administer mysql remotely, I'd strongly recommend you make the host part as > specific as you can. Maybe [EMAIL PROTECTED] or [EMAIL PROTECTED] > > Michael > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
