MySQL Bugs: #21964: login fails on Linux, but correct IP address is in grant table

Bug #21964	login fails on Linux, but correct IP address is in grant table
Submitted:	1 Sep 2006 12:11	Modified:	3 Nov 2006 21:30
Reporter:	d di (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.18	OS:	Linux (Linux (SuSE 9.0))
Assigned to:		CPU Architecture:	Any
Tags:	127.0.0.1, FQDN, linux, localhost, skip-name-resolve, unix

Description:
Can't login locally to a Linux server.

Proper privileges are granted to 127.0.0.1 (MySQL name resolve is turned off),
but I get this error:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Seems MySQL resolves the IP to a hostname even with "skip-name-resolve".

Works on Windows, by the way.

How to repeat:
To reproduce, first
 - install MySQL server on a Linux box and
 - add "skip-name-resolve" to /etc/my.{cnf,ini}.

Then,
 - login as root
 - Run "USE MYSQL; TRUNCATE TABLE user;"
 - Run "GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;"

Lastly,
 - login as root from the local box

An error message pops up:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Name resolve is disabled, so the IP address 127.0.0.1 shouldn't resolve to localhost as seen in the error message.

Suggested fix:
Stop resolving IP addresses when skip-name-resolve is specified? :-)

Thank you for a problem report. Please, describe this step:

- login as root from the local box

in more details. What exact mysql command line was used?

> Please, describe this step in more details:
> - login as root from the local box

Commands used:

root@wks-027:~# ssh server01
Password: ********
root@server01:~# mysql -u root -p
Password: ********
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password:
YES)

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ (namely, http://dev.mysql.com/doc/refman/5.0/en/connection-access.html).

> Thank you for taking the time to write to us, but this is not a bug.

I disagree.

If the bug as reported is not the bug (which I very much feel it is), then you have many more bugs that you'll to fix.  In this case, the bugs are that:

a.) The resolve does NOT happen on Windows.  On Windows, using "127.0.0.1" in the GRANT tables work fine with resolve is turned off, but "localhost" does not.

Here's a transcript proving that fact for you:
===================================
C:\> mysqld-nt --console
[Note] mysqld-nt: ready for connections.

C:\> mysql -u root -p
Enter password:

mysql> SELECT * FROM `user`
Host,User,Password,Select_priv,Insert_priv,Update_priv, <snip>
localhost,root,,True,True,True, <snip>
mysql> bye

C:\> pskill mysqld-nt
C:\> echo skip-name-resolve >> %windir%\my.ini
C:\> mysqld-nt --console
[Note] mysqld-nt: ready for connections.

C:\> mysql -u root -p
Enter password:
ERROR 1130 (HY000): Host '127.0.0.1' is not allowed to connect to this MySQL server
===================================

b.) When connecting via HeidiSQL from the local Unix box, the "localhost" permission set does not work, while "127.0.0.1" does.  Test yourself by fx. installing Wine and HeidiSQL on your Unix MySQL server.

Feel free to fix it either way, but I really feel that the fix outlined in the original description of this bug is the most sane (eg. that "127.0.0.1" is consistently used in place of "localhost" when resolve is off, instead of the current mix of things.)

> Please double-check the documentation at
> http://dev.mysql.com/doc/refman/5.0/en/connection-access.html)

I'm taking a wild guess that you mean this section:
> A Host value may be a hostname or an IP number,
> or 'localhost' to indicate the local host.

?

The guy who wrote that snippet of doc obviously did not know or think about the "skip-name-resolve" option, so there's another thing you should fix :-).

If you're refering to another part of the docs, please include it here for reference.

Please, send the results of:

select host, user from mysql.user;

statements from your Linux machine and from your Windows machine. I'll check if the behaviour you described is really intended then.

Linux server is 5.0.18, Windows server is 5.0.24.

`mysql`.`user` contains one entry.
That entry is: <host>,'root','',<...>
where "<...>" is 26x 'True', 4x blank string and 4x 0 (zero)
and <host> is either '127.0.0.1' or 'localhost'.

On Windows, I always use '127.0.0.1', since it works regardless of "skip-name-resolve" setting. Very nice!

Makes you wonder why the default in a new MySQL installation is root/localhost, not root/127.0.0.1, since the latter clearly works much better. I've just made a habit of replacing 'localhost' with '127.0.0.1' on all new installations, but clearly other people are having trouble with this, judging by comments in the manual page you referred me to, and repeated postings on MySQL mailing lists. People get in trouble when 127.0.0.1 does not resolve to the string "localhost" but to "localhost.localdomain" or "host.domain.com" (FQDN).

On Linux boxes I use two entries, both '127.0.0.1' and 'localhost', since '127.0.0.1' does not work in all cases.

I recently attempted to run HeidiSQL on Wine on top of an X connection tunneled by SSH. In that case, I had to also add the machine's official (not loopback) IP address to the grant tables, otherwise I would be denied access.

This issue is probably related to the protocol used by various software, I seem to get different results when using different protocols. Using '127.0.0.1' in the grant tables and the 'mysql' command line client with --protocol=tcp seems to consistenly yield good results, both on Windows and Linux.

"127.0.0.1" works both with skip-name-resolve on and off, ergo (?) the connecting host's IP address is matched against the grant tables regardless of whether it is also resolved and matched by hostname or not.

Thus the subject of this issue was misleading, it's not skip-name-resolve that's failing, rather it's MySQL that's failing in general to match a connection from 'localhost' to a '127.0.0.1' rule.

Hereby changed the subject ;-).

> Using '127.0.0.1' in
> the grant tables and the 'mysql' command line client with --protocol=tcp seems
> to consistenly yield good results, both on Windows and Linux.
....
> Thus the subject of this issue was misleading, it's not skip-name-resolve that's
> failing, rather it's MySQL that's failing in general to match a connection from
> 'localhost' to a '127.0.0.1' rule.

Yes, it should work so. Read about Invoking MySQL Programs here: http://dev.mysql.com/doc/refman/5.0/en/invoking-programs.html

> Yes, it should work so.

No.  While it works fine over TCP,

MySQL fails the login when using a protocol different from "TCP" and at the same time using "127.0.0.1" as host in the grant tables.

It shouldn't.

It's a bug.

Flicking status from "not a bug" to "open".

> Read about Invoking MySQL Programs here:

Ok, thanks.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

The different treatment of "localhost" and 127.0.0.1 by MySQL client library is intended and well documented behavior. 

The fact that 127.0.0.1 can be used with TCP only is part of that behaviour and is also intended and documented.

> The fact that 127.0.0.1 can be used with TCP only
> is intended and documented.

Seems very unlikely, especially as this is not the case on Windows (only Linux), as far as I can see.

Could you please copy the exact sentence where you think this is documented and paste it into this bug?

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.

> We're sorry, but the bug system is not the appropriate forum
> for asking help on using MySQL products.

Huh?
I'm not asking for help.

You've made an outrageous claim that it is intended behaviour that 127.0.0.1 is not considered the local host when connecting via means other than TCP.

I asked you to ratify that with documentation, which you claim to have, but have yet to present.

> Your problem is not the result of a bug.

Since you have in no way given evidence to support that claim, so far it seems that it is.

What you report as a bug is MySQL client library feature which was implemented this way since the very first version of MySQL.

It is something made by designed so there is no way to be considered a bug.

On Unix systems conncecting to "localhost" means connecting through Unix socket.  

That's the way it is, that's how it always used to be and it is very unlikely that it will ever change.

> What you report as a bug is MySQL client library feature
>
> It is something made by designed so there is no way to be
> considered a bug.

The context is:
 * You are granted access by the grant tables.

The situation happening is:
 * MySQL Server suddenly starts denying you access because you change connection method from TCP/IP to Unix sockets.

That's a feature?
Sounds like a bug to me.

> which was implemented this way since the very first version of MySQL.

Just because people doesn't understand (but manages to work around) the design flaws present since the very first versions of MySQL, that doesn't mean that these flaws shouldn't be fixed...

> That's the way it is, that's how it always used to be and it is very
> unlikely that it will ever change.

Ok, fair enough.

Put this bug in a (new?) "Core bugs and design flaws we'll never fix" category and mark it WONTFIX?

But don't tell me "it's a feature, not a bug" :-).

Feature described in official manual is feature, not a bug.

And note you ask us to reduce number of features you can use to tune connections for different users.

> Feature described in official manual is feature, not a bug.

No-one can read your mind.
(And I'm not going to try and guess what you're talking about :-).)
Please include quotes from the manual, if you think something there is relevant.

> And note you ask us to reduce number of features
> you can use to tune connections for different users.

That's clearly a misunderstanding:

I ask for 127.0.0.1 entries in GRANT tables to work, even when I change connection method from TCP to unix sockets.

In other words, for GRANT table entries to work consistenly over different connection methods.

> Please include quotes from the manual, if you think something there is
> relevant.

----<START QUOTE>----
http://dev.mysql.com/doc/refman/5.0/en/invoking-programs.html
The default hostname is localhost. For client programs on Unix, the hostname localhost is special. It causes the client to connect to the MySQL server through a Unix socket file. This occurs even if a --port or -P option is given to specify a port number. To ensure that the client makes a TCP/IP connection to the local server, use --host or -h to specify a hostname value of 127.0.0.1, or the IP address or name of the local server. You can also specify the connection protocol explicitly, even for localhost, by using the --protocol=tcp option.

http://dev.mysql.com/doc/refman/5.0/en/connection-access.html
If you are able to connect to the server, but your privileges are not what you expect, you probably are being authenticated as some other account. To find out what account the server used to authenticate you, use the CURRENT_USER()  function. 

http://dev.mysql.com/doc/refman/5.0/en/access-denied.html
#localhost is a synonym for your local hostname, and is also the default host to which clients try to connect if you specify no host explicitly.

To avoid this problem on such systems, you can use a --host=127.0.0.1 option to name the server host explicitly. This will make a TCP/IP connection to the local mysqld server. You can also use TCP/IP by specifying a --host option that uses the actual hostname of the local host. In this case, the hostname must be specified in a user table row on the server host, even though you are running the client program on the same host as the server.

----<END QUOTE>----

> I ask for 127.0.0.1 entries in GRANT tables to work, even when I change
> connection method from TCP to unix sockets.

Not. You are asking to prevent possibility to allow users with same username and password use different access depend from how they connect to MySQL server; i.e. handle multiply accounts for one server.

> http://dev.mysql.com/doc/refman/5.0/en/invoking-programs.html
> The default hostname is localhost. For client programs on Unix, the
> hostname localhost is special. It causes the client to connect to the
> MySQL server through a Unix socket file. This occurs even if a --port
> or -P option is given to specify a port number.

In other words:
'Using the string "localhost" with the CLI client triggers use of Unix sockets.'

This is fine, I'm not looking to change this behaviour.
=======================================================

(The server should however authenticate the user connecting over localhost,
 using the grant table entry '127.0.0.1', when "skip-hostname-resolve" is
 turned on!)

> To ensure that the client makes a TCP/IP connection to the local
> server, use --host or -h to specify a hostname value of 127.0.0.1,
> or the IP address or name of the local server. You can also specify
> the connection protocol explicitly, even for localhost, by using the
> --protocol=tcp option.

In other words:
'Avoiding the automatic trigger of Unix sockets can be done with the --protocol=tcp option, or by using an IP address to specify the host.'

This is fine, I'm not looking to change this behaviour.
=======================================================

> http://dev.mysql.com/doc/refman/5.0/en/connection-access.html
> If you are able to connect to the server, but your privileges are not
> what you expect, you probably are being authenticated as some other
> account. To find out what account the server used to authenticate you,
> use the CURRENT_USER()  function.

In other words:
'CURRENT_USER() can be used to find out how the server authenticated you.'

This is fine, I'm not looking to change this behaviour.
=======================================================

(The bug is not that I'm seeing unexpected privileges, but that the
 server authenticates me wrongly:

 I'm connecting from the local host.  "skip-hostname-resolve" is turned
 on, so MySQL should be looking for IP addresses, not hostnames, in the
 grant tables.  In this case, that means looking for 127.0.0.1.

 MySQL doesn't.  Even though the 127.0.0.1 entry is there in the grant
 tables, I'm not being authenticated.  Again, "skip-hostname-resolve" is
 turned ON, so MySQL should be looking for IP addresses, not host names,
 in the GRANT tables.)

> http://dev.mysql.com/doc/refman/5.0/en/access-denied.html
> #localhost is a synonym for your local hostname, and is also the
> default host to which clients try to connect if you specify no host
> explicitly.

This is fine, I'm not looking to change this behaviour.
=======================================================

> To avoid this problem on such systems, you can use a --host=127.0.0.1
> option to name the server host explicitly. This will make a TCP/IP
> connection to the local mysqld server. You can also use TCP/IP by
> specifying a --host option that uses the actual hostname of the local
> host.

Thanks for the workaround.

It's firewalled so that I cannot use TCP/IP at all however, so I must use Unix sockets.

> In this case, the hostname must be specified in a user table row
> on the server host, even though you are running the client program on
> the same host as the server.

I don't have permission to change the grant tables.

> > I ask for 127.0.0.1 entries in GRANT tables to work, even when I
> > change connection method from TCP to unix sockets.
>
> Not.

Huh?

Yes, I am.

Read the original problem description if in doubt:
"Can't login locally to a Linux server.  Proper privileges are granted to 127.0.0.1 (MySQL name resolve is turned off) ..."

> You are asking to prevent possibility to allow users with same
> username and password use different access depend from how they
> connect to MySQL server;

That's the point.
The connection method (unix sockets, TCP/IP, et cetera) should not matter, because it's not part of the GRANT table specification.

If this was a desired "posibility", as you say - I'll note that you have demoted it from "feature" to "possibility" - then it would have been implemented as a field in the GRANT tables.

For example, a "protocol" field where the user could specify "tcp" meaning "authenticate as this user only when the connection happens over the tcp protocol".

It's not implemented this way, and therefore it's just a bug.

> i.e. handle multiply accounts for one server.

I'm sure a server can handle multiple accounts, even if it's fixed to correctly look up 127.0.0.1 when a user connects from the local host.

skip-name-resolve means nothing here. See description of what this option does at http://dev.mysql.com/doc/refman/5.0/en/server-options.html and http://dev.mysql.com/doc/refman/5.0/en/dns.html