List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:April 30 2004 1:34am
Subject:Re: Creating Users and Passwords
View as plain text  
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

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