MySQL道普請便り

第17回MySQLのユーザー管理について[その1]

今回から何回かに分けて、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_privInsert_privが'Y'に設定されます。

その他GRANTステートメントで指定することのできるアカウント単位の属性(SSL接続の強制や1時間あたりのクエリー回数の制限など)がそれぞれカラムとして定義されていますGRANTステートメントで指定可能なアカウント単位の属性の詳細はリファレンスマニュアルを参照してください⁠⁠。

MySQLのユーザー認証の仕組み

MySQLへのログイン試行は、以下のように判定されます。

  1. 接続元ホストの検証
  2. アカウントの検証
  3. パスワードの検証

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は別アカウントのはずなのに認証できてしまう謎」について説明したいと思います。

おすすめ記事

記事・ニュース一覧