mysqlのユーザ作成で、ホスト名%にlocalhostは含まれない

そんな落とし穴があったんですね・・・


grant文でホスト名指定なしのユーザを作成して

mysql> grant all on test.* to user1 identified by 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for user1;
+------------------------------------------------------------------------------------------------------+
| Grants for user1@%                 |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'user1'@'%'             |
+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

いざ接続！と思ったら蹴られました・・・(´・ω・`)

$ mysql -uuser1 -ppassword
ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)

ホスト部は%になってるからどこからでもOKなのだとばかり思っていたら

localhostは%には含まれないそうなんですね(;´Д｀)

なので改めて、ホスト部をlocalhostと指定したユーザも作成してあげます

mysql> grant all on test.* to 'user1'@'localhost' identified by 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'user1'@'localhost';
+--------------------------------------------------------------------------------------------------------------+
| Grants for user1@localhost                 |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'user1'@'localhost'             |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

これで再度ためしてみると

$ mysql -uuser1 -ppassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: x.x.xx Source distribution

Copyright (c) 20xx, 20xx, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

繋がりました！

めでたしめでたしヾ(*･ω･)ｼ