前々回から何回かに分けて、MySQLのユーザー認証について説明しています。「第17回　MySQLのユーザー管理について［その1⁠］⁠」では、「⁠ホスト名⁠」⁠、「⁠ユーザー⁠」⁠、「⁠パスワード」についてと、 mysql.user テーブルについて説明しました。今回は「root@127.0.0.1とroot@localhostは別アカウントのはずなのに認証できてしまう謎」について説明したいと思います。

第17回から引き続き、今回のデモンストレーション環境は敢えて「匿名アカウント」を有効にしておくために、MySQL 5.6をyumリポジトリーからインストールしたものになっています。各バージョンのyum版, rpm版の構成の違いは 「第10回　yum, rpmインストールにおけるMySQL 5.6とMySQL 5.7の違い」 を参考にしてください。

筆者がCentOS 6.6上で今回の環境を作るために実行したコマンドは以下の通りです。

$ sudo yum install -y http://dev.mysql.com/get/mysql57-community-release-el6-7.noarch.rpm
$ sudo yum install -y --disablerepo=mysql57-community --enablerepo=mysql56-community mysql-community-server
$ sudo service mysqld start

root@127.0.0.1とroot@localhostは別アカウントか

前々回説明した通り、MySQLはアカウントを「接続元ホスト」と「ユーザー」の組を用いて一意に識別するため、「⁠root@127.0.0.1とroot@localhostは別アカウントである」というのが一応の説明です。

TCP経由でローカルホストに接続する場合は「接続元ホスト127.0.0.1」のコネクションとなりroot@127.0.0.1アカウントが利用されるのに対し、UNIXソケット経由で接続する場合は「接続元ホストlocalhost（UNIXソケットに「接続元ホスト」の概念はない⁠）⁠」のコネクションとなりroot@localhostアカウントが利用されます。

上記の説明が原則ではあるのですが、MySQLはデフォルトで「接続元ホストの名前解決」機能が有効になっており、これが事態をややこしくします。

TCP経由で接続する

まずは原則通り動くケースとして、/etc/my.cnfの[mysqld]セクションにskip_name_resolveオプションを追記してMySQLを再起動します。

$ sudo vim /etc/my.cnf
..
[mysqld]
skip_name_resolve
..

$ sudo service mysqld restart

この状態でTCP接続を試してみましょう。MySQLサーバが動いているマシン上で明示的にTCP接続を指定するためには、--protocol=tcpオプションまたは-h127.0.0.1オプションを使用します。

$ mysql -uroot -h127.0.0.1
mysql> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| [email protected] |
+----------------+
1 row in set (0.00 sec)

ちなみにIPv6形式で-h::1と指定した場合、「⁠接続元ホスト」がIPv6形式のものが利用されました。

$ mysql -uroot -h::1
mysql> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| root@::1       |
+----------------+
1 row in set (0.00 sec)

UNIXソケットで接続する

同じくUNIXソケット接続ではこのようになります。ローカルホストへの接続は、-hオプションで明示的にIPアドレス形式を取るか、--protocol=tcpオプションを指定しない限り、優先的にUNIXソケット接続を利用しようとします（Windows系OSの場合はUNIXソケット接続が存在せず、優先的にTCP接続を利用しようとします⁠）⁠。

$ mysql -uroot
mysql> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

root@localhostアカウントを削除する

この状態で、root@localhostアカウントをDROP USERしてみましょう。

mysql> DROP USER root@localhost;
Query OK, 0 rows affected (0.01 sec)

これで、（⁠理屈の上では）「⁠TCP接続を使ったroot@127.0.0.1にはアクセス可能だが、UNIXソケット接続のroot@localhostにはアクセス不可」な状態になりました。

$ mysql -uroot -h127.0.0.1 -sse "SELECT current_user()"
[email protected]

$ mysql -uroot -sse "SELECT current_user()"
@localhost

TCP接続は変わらずroot@127.0.0.1アカウントで認証されていますが、UNIXソケット接続の方は「@localhost」という「ユーザー部分が空欄」の妙なアカウント（⁠「⁠匿名アカウント」と呼びます）で認証されました。認証されてしまっているので少しきまりが悪いですが、少なくともroot@localhostアカウントは利用できなくなったことがわかります。

匿名アカウントは「接続元ホストのみを検証し、ユーザーとパスワードを検証しない」ユーザーです。今回の場合はroot@localhostを DROP したことで、「⁠接続元ホストlocalhostにのみマッチした登録のないアカウント」と認証されたために匿名アカウントでのログインになっています。

root@127.0.0.1アカウントを削除する

逆を試してみましょう。root@localhostを作成しなおして、root@127.0.0.1を削除します。skip_name_resolveは設定したままです。

mysql> SELECT user, host FROM user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
|      | centos    |
| root | centos    |
|      | localhost |
+------+-----------+
5 rows in set (0.00 sec)

mysql> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| [email protected] |
+----------------+
1 row in set (0.00 sec)

mysql> CREATE USER root@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL ON *.* TO root@localhost WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP USER [email protected];
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT user, host FROM user;
+------+-----------+
| user | host      |
+------+-----------+
| root | ::1       |
|      | centos    |
| root | centos    |
|      | localhost |
| root | localhost |
+------+-----------+
5 rows in set (0.00 sec)

再び、TCP経由、UNIXソケット経由それぞれのrootユーザーで接続を試してみます。

$ mysql -uroot -h127.0.0.1 -sse "SELECT current_user()"
ERROR 1130 (HY000): Host '127.0.0.1' is not allowed to connect to this MySQL server

$ mysql -uroot -sse "SELECT current_user()"
root@localhost

今度はわかりやすい形でroot@127.0.0.1アカウントでのログイン試行が失敗しました。接続元ホスト127.0.0.1はmysql.userテーブル（本体はメモリ上のアカウント情報）上に1つも残っていませんので、接続元ホストの検証フェーズでエラーになったER_HOST_NOT_PRIVILEGED（Error: 1130）が返却されています。

MySQLの名前解決機能をオンにする

ここまでのテストでは、「⁠root@127.0.0.1とroot@localhostは確かに別アカウントのように振る舞っている」ことが説明できました。では話をややこしくするために有効にしていたskip_name_resolveをmy.cnfからコメントアウトしてMySQLを再起動してみましょう。

$ sudo vim /etc/my.cnf
..
[mysqld]
#skip_name_resolve
..

$ sudo service mysqld restart

さて、どのような結果になるでしょうか。

$ mysql -uroot -h127.0.0.1 -sse "SELECT current_user()"
root@localhost

$ mysql -uroot -sse "SELECT current_user()"
root@localhost

ここがよくある誤解のもとです。デフォルトであるskip_name_resolveなしの状態では、TCP接続でもUNIXソケット経由でも同じくroot@localhostで認証されてしまいました。これではいかにもroot@127.0.0.1とroot@localhostは同じアカウントのように見えてしまいます。

もう少し詳しく状況を把握するために、root@127.0.0.1を再作成した上で、root@localhostとは別のパスワードを設定してみます。

mysql> CREATE USER [email protected] IDENTIFIED BY 'tcp';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL ON *.* TO [email protected] WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SET PASSWORD FOR root@localhost = PASSWORD('socket');
Query OK, 0 rows affected (0.00 sec)

結果は次の表の通りとなりました。

やはり表示上の問題などではなく、確かにroot@localhostアカウントを利用して接続しています（見かけだけroot@127.0.0.1をroot@localhostと表示しているのであれば、パスワードは"tcp"の方が正しいはずですが、パスワード"tcp"の場合はいずれも認証されず、パスワード"socket"の場合のみが認証されました⁠）⁠。

skip_name_resolveパラメータの有無で結果が変わることから推測できると思いますが、これはMySQL内部で名前解決を利用するかどうかによるものです。skip_name_resolveを設定しない状態（MySQLのデフォルトの状態）では、TCP "127.0.0.1" からの接続はIPアドレスの逆引きにより"localhost"に変換され、"root@127.0.0.1"で接続しようとしたコネクションは"root@localhost"としてMySQLに認識されます。"root@localhost"が存在しない状態で"root@127.0.0.1"でログインを試行した場合、名前解決が有効な状態でもちゃんと"root@127.0.0.1"を探し出して認証されますが、接続元ホスト"localhost"はもともとUNIXソケット接続やWindowsの共有メモリ接続、名前付きパイプ接続のみに予約された特別なホスト名なので、UNIXソケット接続を利用した場合に"localhost"から"127.0.0.1"への名前解決は行われません（このことがややこしさを増長している気がします⁠）⁠。

まとめ

MySQLのアカウント上では"root@localhost"と"root@127.0.0.1"は別のアカウントですが、名前解決が有効な状態では"127.0.0.1"が"localhost"に逆引きされてしまい、あたかも"127.0.0.1" = "localhost"のように振る舞うことがあります。ただしこの状態でも、UNIXソケット接続や共有メモリ接続などは"localhost" ⇒ "127.0.0.1"の方向への変換はされません。名前解決の設定の違いに惑わされないように注意しましょう。

次々回では、今回説明しきれなかった「匿名アカウント」について説明したいと思います。