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
