MySQL Bugs: #68436: user@127.0.0.1 is authorized partly as user@localhost.

Bug #68436	user@127.0.0.1 is authorized partly as user@localhost.
Submitted:	20 Feb 2013 2:11	Modified:	20 Feb 2013 17:54
Reporter:	Sadao Hiratsuka	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.6.10	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	authentication

Description:
user@127.0.0.1 is authorized partly as user@localhost.

How to repeat:
- skip-name-resolve = FALSE
- /etc/hosts has the following entry.
127.0.0.1 localhost

GRANT ALL PRIVILEGES ON db1.* TO user@localhost IDENTIFIED BY 'pass1';
GRANT ALL PRIVILEGES ON db2.* TO user@127.0.0.1 IDENTIFIED BY 'pass2';

CREATE DATABASE db1;
CREATE DATABASE db2;

(a) mysql -u user -h localhost -ppass1 db1 -e '' -> authorized
(b) mysql -u user -h localhost -ppass2 db1 -e '' -> denied
(c) mysql -u user -h localhost -ppass1 db2 -e '' -> denied
(d) mysql -u user -h localhost -ppass2 db2 -e '' -> denied
(e) mysql -u user -h 127.0.0.1 -ppass1 db1 -e '' -> authorized
(f) mysql -u user -h 127.0.0.1 -ppass2 db1 -e '' -> denied
(g) mysql -u user -h 127.0.0.1 -ppass1 db2 -e '' -> authorized
(h) mysql -u user -h 127.0.0.1 -ppass2 db2 -e '' -> denied

Suggested fix:
I think the following behavior is convenient.

(e) -> denied
(f) -> denied
(g) -> denied
(h) -> authorized

If the current behavior is expected and valid (by name-resolve),
I think a detailed description in the reference manual is needed.

Current behavior clearly described at http://dev.mysql.com/doc/refman/5.6/en/access-denied.html:

 localhost is a synonym for your local host name, 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 host name of the local host. In this case, the host name 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.

If this behaver is not a bug, there is another problem in "SHOW GRANTS" statement at case (g) mysql -u user -h 127.0.0.1 -ppass1 db2.

$ mysql -u user -h 127.0.0.1 -ppass1 db2
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| user@localhost |
+----------------+
1 row in set (0.00 sec)

mysql56> show grants;
+-------------------------------------------------------------------------------------------------------------+
| Grants for user@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'localhost' IDENTIFIED BY PASSWORD '*22A99BA288DB55E8E230679259740873101CD636' |
| GRANT ALL PRIVILEGES ON `db1`.* TO 'user'@'localhost'                                                       |
+-------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql56> create table db2.tbl1 ( num serial );
Query OK, 0 rows affected (0.08 sec)

If this is not a bug, "SHOW GRANTS" must return grants at db2.