List:General Discussion« Previous MessageNext Message »
From:Lou Olsten Date:April 30 2004 2:46am
Subject:Re: Creating Users and Passwords
View as plain text  
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 roby@%.your.domain or roby@stripped.%.

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" <Michael.Stassen@stripped>
To: "Lou Olsten" <lou.olsten@stripped>
Cc: <mysql@stripped>
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 user@host 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
> roby@%, 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 roby@localhost user, who would also
> trump roby@% 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
> roby@%, which won't help if you're actually connecting as roby@localhost
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 roby@%.your.domain or roby@stripped.%.
>
> Michael
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/mysql?unsub=1
>

Thread
Creating Users and PasswordsLou Olsten29 Apr
  • Re: Creating Users and PasswordsDan Nelson29 Apr
    • Re: Creating Users and PasswordsPaul DuBois30 Apr
    • Re: Creating Users and PasswordsMichael Stassen30 Apr
  • Re: Creating Users and PasswordsLou Olsten29 Apr
  • Re: Creating Users and PasswordsMichael Stassen30 Apr
  • Re: Creating Users and PasswordsLou Olsten30 Apr
Re: Creating Users and PasswordsTbird67ForSale29 Apr
Re: Creating Users and PasswordsTbird67ForSale29 Apr