Some notes on mysql-5.5.35 auth mechanism.
ivan@wildfire ~ $ sudo mysql
root@localhost [(none)]: SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
root@localhost [(none)]: SELECT host,user,password FROM mysql.user;
+-----------+------+----------+
| host | user | password |
+-----------+------+----------+
| localhost | root | |
| wildfire | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| wildfire | | |
+-----------+------+----------+
6 rows in set (0.01 sec)
root@localhost [(none)]: CREATE USER a IDENTIFIED BY 'pass';
Query OK, 0 rows affected (0.00 sec)
root@localhost [(none)]: CREATE USER b;
Query OK, 0 rows affected (0.00 sec)
root@localhost [(none)]: CREATE DATABASE db;
Query OK, 1 row affected (0.01 sec)
root@localhost [(none)]: GRANT ALL ON db.* TO b;
Query OK, 0 rows affected (0.03 sec)
root@localhost [(none)]: Bye
ivan@wildfire ~ $ mysql -ua -pfoo
ERROR 1045 (28000): Access denied for user 'a'@'localhost' (using password: YES)
ivan@wildfire ~ $ mysql -ub -D db
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'db'
Why? There is a gotcha. Basically when I login mysqld matches the supplied user@host against rows in mysql.user table:
ivan@wildfire ~ $ sudo mysql -e 'SELECT host,user,password FROM mysql.user ORDER BY host DESC, user DESC'
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| wildfire | root | |
| wildfire | | |
| localhost | root | |
| localhost | | |
| ::1 | root | |
| 127.0.0.1 | root | |
| % | b | |
| % | a | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |
+-----------+------+-------------------------------------------+
So let's say I try to login as 'a'@'localhost' with pw 'pass'. ''@'localhost' matches first (' ' is a wild card for any user) and pw match fails. Thus I get rejected. And when I login as 'b'@'localhost' with no pw I get access but can't use the database because mysqld doesn't think that I'm 'b'@'localhost'.
ivan@wildfire ~ $ mysql -ub
b@localhost [(none)]: SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| @localhost |
+----------------+
1 row in set (0.00 sec)
b@localhost [(none)]: USE db;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'db'
So if I asked to log in as 'b'@'localhost' and got a connection it does not mean that I got in as 'b'@'localhost' (in reality I got in as the first account from mysql.user sorted in due form who matches the supplied credentials). And even if /usr/bin/mysql tells me that I'm 'b'@'localhost (look at the prompt), the mysqld actually thinks I'm ''@'localhost' and refuses me the 'db' because an anonymous user does not have LOCK TABLES privilege to 'db.*'
One way to go here would be to not drop a hostname when I create the user.
ivan@wildfire ~ $ sudo mysql
root@localhost [(none)]: GRANT LOCK TABLES ON db.* TO c@localhost IDENTIFIED BY 'pass';
Query OK, 0 rows affected (0.51 sec)
root@localhost [(none)]: SELECT host,user,password FROM mysql.user ORDER BY host DESC, user DESC;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| wildfire | root | |
| wildfire | | |
| localhost | root | |
| localhost | c | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |
| localhost | | |
| ::1 | root | |
| 127.0.0.1 | root | |
| % | b | |
| % | a | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |
+-----------+------+-------------------------------------------+
9 rows in set (0.00 sec)
root@localhost [(none)]: Bye
ivan@wildfire ~ $ mysql -uc -ppass -Ddb
c@localhost [db]: SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| c@localhost |
+----------------+
1 row in set (0.00 sec)
References:
http://bugs.mysql.com/bug.php?id=36576
http://dev.mysql.com/doc/refman/5.5/en/default-privileges.html
http://dev.mysql.com/doc/refman/5.5/en/connection-access.html
http://bazaar.launchpad.net/~mysql/mysql-server/5.5/view/head:/sql/sql_acl.cc (acl_check_host, find_acl_user, compare_hostname, wild_compare)