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]
