今回から何回かに分けて、MySQLのユーザー認証について説明します。今回はまず、「 接続元ホスト名」 、「 ユーザー」 、「 パスワード」がどこに保管され、どのような順番で評価されるかを見ていきましょう。
今回のデモンストレーション環境はあえて「匿名ユーザー」を有効にしておくために、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
MySQLのユーザー情報の格納先
MySQLにログインするためのアカウント情報は、各OSのアカウント情報とは独立しており、MySQLの内部に保管されています。このアカウント情報はmysqld
のメモリ上に展開されています。またアカウント情報の本体とは別に、mysql
スキーマのuser
テーブルに1ユーザー1レコードとしてスナップショットが永続化されています(後の段落で説明しますので、今のところはmysql.user
=アカウント情報の実体、としておいてください) 。
筆者が今回の環境として用意したMySQLサーバーを起動した直後の状態です(前の段落を参照してください) 。この環境は"centos"というホスト名を持ち、IPアドレスは"172.17.1.67"でした。
$ mysql -uroot
mysql> SELECT user, host FROM mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| | centos |
| root | centos |
| | localhost |
| root | localhost |
+------+-----------+
6 rows in set (0.00 sec)
mysql
スキーマのuser
テーブルにはこの他にもたくさんのカラムがあります。少し長くなりますが、DESCRIBE
ステートメントでカラムの情報を表示してみましょう。
mysql> DESCRIBE mysql.user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | YES | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
43 rows in set (0.00 sec)
Host
カラムとUser
カラムが複合プライマリキーとして定義されています。これはMySQLでは「接続元ホスト」と「ユーザー」でユーザーを一意に識別している、ということです(以降、Host
に該当する部分を「接続元ホスト」( ホスト名、IPアドレス、ネットワークアドレスなどの形を含みます) 、User
に該当する部分を「ユーザー」 、(Host, User)
で識別される部分を「アカウント」と呼びます) 。
「rootユーザーのTCP経由の接続である root@127.0.0.1 アカウントと UNIXソケット経由の接続で主に使われる root@localhost アカウントは別のアカウントか?」という疑問に対する1つの答えがこれになります。この問いに対する答えは 「 理屈の上では Yes」となります。しかし、インストールしたままの今回のデモンストレーション環境では「理屈とは違ってNo」になります。これについてはまた別の回で説明しましょう。
Host
カラムはchar(64)
、User
カラムはchar(16)
(MySQL 5.7ではchar(32)
に拡張されました)ですので、この長さを超える接続元ホスト、ユーザーは登録できません。
Password
カラム(MySQL 5.7ではauthentication_string
カラムに変更され、Password
カラムは削除されました。MySQL 5.6とそれ以前のバージョンでは、authentication_string
カラムは存在しますが利用されていません(NULLまたは空文字列が入っています) )にはパスワードのハッシュ値が格納されます。
*_priv
カラムにはユーザーのグローバル権限(GRANT
ステートメント上で許可する対象を*.*
で指定したもの)が格納されています。GRANT SELECT, INSERT ON *.* TO ..
でアカウントに権限を設定した場合、そのアカウントに対応するSelect_priv
とInsert_priv
が'Y'に設定されます。
その他GRANT
ステートメントで指定することのできるアカウント単位の属性(SSL接続の強制や1時間あたりのクエリー回数の制限など)がそれぞれカラムとして定義されています(GRANT
ステートメントで指定可能なアカウント単位の属性の詳細はリファレンスマニュアル を参照してください) 。
MySQLのユーザー認証の仕組み
MySQLへのログイン試行は、以下のように判定されます。
接続元ホストの検証
アカウントの検証
パスワードの検証
1.で行われる検証の内容は、次のSQLステートメントにたとえることができます(たとえです。厳密な挙動は違います) 。
mysql> SELECT EXISTS (SELECT host FROM mysql.user WHERE host= 'localhost');
+--------------------------------------------------------------+
| EXISTS (SELECT host FROM mysql.user WHERE host= 'localhost') |
+--------------------------------------------------------------+
| 1 |
+--------------------------------------------------------------+
1 row in set (0.03 sec)
mysql> SELECT EXISTS (SELECT host FROM mysql.user WHERE host= '172.17.42.1');
+----------------------------------------------------------------+
| EXISTS (SELECT host FROM mysql.user WHERE host= '192.168.0.1') |
+----------------------------------------------------------------+
| 0 |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
接続元ホストがそもそもmysql.user.Host
に登録されているかどうかが判定されます。接続元ホストが登録されておりユーザーやパスワードが違った場合には ER_ACCESS_DENIED_ERROR(Error: 1045)が返却されますが、接続元ホストが登録されていない場合はユーザーの判定まで進まずER_HOST_NOT_PRIVILEGED(Error: 1130)が返却されます。以下はエラーメッセージの比較です。
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
ERROR 1130 (HY000): Host '172.17.42.1' is not allowed to connect to this MySQL server
2.で行われる検証の内容は次のSQLステートメントにたとえることができます。
mysql> SELECT EXISTS(SELECT user FROM mysql.user WHERE (host, user)= ('127.0.0.1', 'root'));
+-------------------------------------------------------------------------------+
| EXISTS(SELECT user FROM mysql.user WHERE (host, user)= ('127.0.0.1', 'root')) |
+-------------------------------------------------------------------------------+
| 1 |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
接続元ホスト、ユーザーを組にしたアカウントを検証します。ここでアカウントが存在しなかった場合はER_ACCESS_DENIED_ERRORになります。
3.で行われるパスワードの検証方法はプラグインで制御することが可能です。MySQL 4.1とそれ以降ではデフォルトでmysql_native_password
認証プラグインが有効になっており、20バイトのランダムな文字列を利用したチャレンジ・レスポンス認証になっています。それ以外ではMySQL 4.0とそれ以前でデフォルトだったmysql_old_password
認証プラグイン、パスワードのやり取りを平文で行うmysql_clear_password
認証プラグインなどがあります(この平文でパスワードをやり取りするためのプラグインは、OSのPAM認証機構とMySQLの認証機構を連携させる場合に主に利用されます。MySQLで一度受け取ったユーザーとパスワードを利用してPAM認証を利用するため、このようなプラグインが必要とされるのです) 。
アカウント情報と mysql.user テーブルの同期
MySQLはmysqldのメモリ上にアカウント情報を持ち、認証にはその情報が利用されます。mysql.user
テーブルはアカウント情報のスナップショットであり、mysqldの起動時にmysql.user
テーブルをロードしてメモリ上にアカウント情報を展開します。
CREATE USER
ステートメントやGRANT
ステートメント、DROP USER
ステートメント、REVOKE
ステートメントなどは、「 メモリ上のアカウント情報そのものとmysql.user
テーブルを同時に変更」します。これにより基本的にメモリ上のアカウント情報とmysql.user
テーブルの内容は同じものになるケースがほとんどですが、何らかの理由でmysql.user
テーブルを直接編集した場合はこれらに差異が生じることになります。そして、実際にアカウントの認証に利用される情報はメモリ上に展開されたアカウント情報のため、「 アカウントは登録されているように見えるけれども実際にログインはできない」という状況になってしまいます(mysql
スキーマを含めたmysqldump
からデータベースをリストアした場合に、この状況になることがあります) 。
メモリ上のアカウント情報は 「 mysqldの(再)起動」または「FLUSH PRIVILEGES
ステートメント」などでリロードできます。「 アカウントを追加した(つもり)だけれど、SHOW GRANTS
ステートメントが追加したアカウントを認識しない」( ER_NONEXISTING_GRANT(Error: 1141)が返却される)ような場合はFLUSH PRIVILEGES
ステートメントを試してみてください。
mysql> SELECT EXISTS(SELECT user FROM mysql.user WHERE (host, user)= ('127.0.0.1', 'yoku0825'));
+-----------------------------------------------------------------------------------+
| EXISTS(SELECT user FROM mysql.user WHERE (host, user)= ('127.0.0.1', 'yoku0825')) |
+-----------------------------------------------------------------------------------+
| 1 |
+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW GRANTS FOR yoku0825@127.0.0.1;
ERROR 1141 (42000): There is no such grant defined for user 'yoku0825' on host '127.0.0.1'
まとめ
MySQLのアカウント情報はmysqldのメモリ上とmysql.user
テーブルに保管されます。実際に認証に利用されるのはメモリ上のアカウント情報であり、mysql.user
はmysqldの起動時及びFLUSH PRIVILEGES
ステートメントの実行時にアカウント情報を再構築するためのデータストアです。
ログイン試行は「接続元ホスト」「 ユーザー」「 パスワード」の順に評価されます。接続元ホストの検証に失敗した場合のみ異なるエラーが返ります。
今回説明した内容の詳細はMySQL :: MySQL 5.6 リファレンスマニュアル :: 6.2.4 アクセス制御、ステージ 1: 接続の検証 やMySQL :: MySQL Internals Manual :: 14.2 Connection Phase などにも説明があります。
次々回では、「 root@127.0.0.1とroot@localhostは別アカウントのはずなのに認証できてしまう謎」について説明したいと思います。