MySQLのアクセス権限システムの基礎を知る

MySQLのユーザ権限はいつもなんとなくやってしまっていたので、一度整理したい。

実技は、MySQLのバージョンは少し古めの 4.0.26 で行います。

ユーザの種類(rootユーザ、一般ユーザ、匿名ユーザ)

 Windowsにユーザがあるように、MySQLにも(Windowsユーザとは別に)ユーザがあります。MySQLのユーザには、rootユーザ、一般ユーザ、匿名ユーザの3種類があり、アクセスするためには、そのどれかを使わなければなりません。

 匿名ユーザとは、ユーザ名を指定しなくていい、つまり誰でもOKということなのです。
 
 また、このユーザはホスト、つまりざっくり言うならログインしようとしているコンピュータによっても区別されます。自分のコンピュータにあるMySQLを使う場合は「localhost」からのログインということになります。つまり同じrootユーザでも自分のコンピュータからアクセスする場合と、他のコンピュータからアクセスする場合と、他のコンピュータからネットワーク越しにアクセスする場合は違うユーザとしてみなされるわけです。


以下のようにすると、rootユーザとしてログインします。

mysql - u root

以下、ログイン例です。

$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.26-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> 

ユーザを管理しているデータベース「mysql

rootでMySQLにログインして、 show databasese; というコマンドを打つと、以下のようなデータベースがあることが分かります。

mysql> show databases;
+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.02 sec)

この「mysql」というデータベースは、MySQLを動作させるためのユーザ名やらパスワードやらを管理しているデータベースです。匿名ユーザのアクセスできない設定になっています。
この「mysql」というデータベースから登録されているホストとユーザを表示してみましょう。

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select Host,User from user;
+-----------+------+
| Host      | User |
+-----------+------+
| hoge_dev  |      |
| hoge_dev  | root |
| localhost |      |
| localhost | root |
+-----------+------+
4 rows in set (0.00 sec)

結果として、「hoge_dev」と「localhost」というふたつのホストに、それぞれ空白とrootのユーザがあることが分かります。
ここで、hoge_devというホスト、空白ユーザ、つまり匿名ユーザの両方を削除してしまえば、残るのは localhost の root だけになります。

mysql> delete from user where User='';
Query OK, 2 rows affected (0.00 sec)

mysql> select Host,User from user;
+-----------+------+
| Host      | User |
+-----------+------+
| hoge_dev  | root |
| localhost | root |
+-----------+------+
2 rows in set (0.00 sec)

mysql> delete from user where Host='hoge_dev';
Query OK, 1 row affected (0.00 sec)

mysql> select Host,User from user;
+-----------+------+
| Host      | User |
+-----------+------+
| localhost | root |
+-----------+------+
1 row in set (0.00 sec)

rootユーザにパスワードを設定する

rootユーザにパスワードを与えてみましょう。

set password for root=password('xxxxxx');

とタイプします。xxxxxxには任意のパスワードが入ります。

mysql> set password for root=password('xxxxxx');
Query OK, 0 rows affected (0.00 sec)

ここで、quit とタイプしていったんMySQLモニタを終了し、改めてログインしてみましょう。パスワード付きでMySQLにログインするには、 -p オプションを追加します。

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.26-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>   

ログインできました。 -p をつけると、 Enter password: と尋ねてきますので、先ほど指定したパスワードを入力しましょう。

権限システムの役割

http://dev.mysql.com/doc/refman/5.1/ja/what-privileges.html

MySQL 権限システムの主な役割は、ホストから接続するユーザの認証と、SELECT、 INSERT、 UPDATE、 DELETE などのデータベースにおける権限があるユーザを関連付けることです。

アノニマス ユーザ (不特定多数のユーザー) を持つこと、そして、管理操作や LOAD DATA INFILE などのMySQL 特有の関数での権限を与えるといった追加の機能性を備えています。

権限システムの機能

http://dev.mysql.com/doc/refman/5.1/ja/privileges.html

MySQL 権限システムでは、すべてのユーザが許可がある操作だけを行います。
ユーザは MySQL サーバに接続すると、 接続元のホスト と 指定するユーザ名 でそのアイデンティティ (ID) を認識します。
MySQL では、ホスト名とユーザ名を使用してユーザを認証します。

MySQL のアクセス制御には、サーバに接続するクライアント プログラムを実行するときに、 2 段階を踏みます。
*段階 1:ユーザに接続する権限があるかどうかサーバがチェックする。
*段階 2:接続できた場合、要求ごとにそれを実行できる権限があるかどうかサーバがチェックする。たとえば、データベースのテーブルからレコードを SELECT したり、データベースのテーブルを DROP しようとすると、ユーザにそのテーブルの SELECT 権限があるかどうか、あるいはデータベースの DROP 権限があるかどうかをサーバがチェックする。

接続中に権限を変更した場合(ユーザ自身または第三者によって)、必ずしもその変更は次のクエリに反映するとは限りません。詳細は、項4.7.7. 「権限の変更が反映するタイミング」 を参照してください。

権限が変更されるタイミングは、こちらをみればわかります。

サーバは、mysql データベース (mysql と名付けられたデータベース) の権限テーブルに権限情報を保管します。
通常、GRANT や REVOKE などのクエリを使用して、権限テーブルの内容を間接的に操作し、アカウントのセットアップや権限のコントロールを行ないます。
項12.5.1. 「アカウント管理ステートメント」 も参照してください。ここでは、権限テーブルの根本的なストラクチャと、サーバがクライアントとのやりとりでどのようにそれを使用するかについて説明します。 のどれかを使わなければなります。

GRANTやREVOKEの構文については、こちらで分かります。

サーバでは、両方の段階でのアクセス制御で、mysql データベースの user、 db、 そして host テーブルを使用します。user と db のフィールドをここで示します。host テーブルは、db テーブルと類似しますが、項4.7.6. 「アクセス制御の段階 2: 接続確認」 で説明するように特別な使い方をします。

アクセス制御の段階はMySQL :: MySQL 5.6 リファレンスマニュアル :: 6.2.5 アクセス制御、ステージ 2: リクエストの確認 で確認できます。

MySQLへの接続ユーザを指定して追加し、アクセスを試してみる

これまで見てきた内容をを利用して、ユーザを作成、MySQLへのアクセスを行ってみましょう。
まずは、「段階1のMySQLへの接続」の部分です。

サーバは以下の2つを利用します。

ホスト名 IP 用途
localhost 10.20.138.76 MySQLサーバがインストールされている
fuku_dev 10.20.138.18 外部からのアクセステスト用

現在の設定を確認です。

localhostのrootはパスワード付きでログインできます。

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.0.26-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

ユーザは、 localhostのrootしかいません。

mysql> select Host,User,Password from user;
+-----------+------+------------------+
| Host      | User | Password         |
+-----------+------+------------------+
| localhost | root | 025cde755a31fa8f |
+-----------+------+------------------+
1 row in set (0.00 sec)

それでは、fuku_dev というホストをもつコンピュータがMySQLに接続できるように設定してみましょう。まず、fuku_dev から現在接続できるのかをテストしてみましょう。

$ hostname
fuku_dev
$ mysql -h 10.20.138.76
ERROR 1130 (00000): Host '10.20.138.18' is not allowed to connect to this MySQL server

だめでした。これは正しい動きです。ユーザを設定していないのですから。

それでは、ユーザ名 admin というユーザをMySQLサーバに加えてみて、アクセステストしてみましょう。ユーザの追加にはGrant構文を利用します。
GRANT ステートメントは、システム管理者が MySQL ユーザ アカウントを作成し、アカウントに権利を与える事を可能にします。
GRANT構文はこちら。

GRANT ALL ON *.* TO admin;

上記のように、ユーザを追加します。この文の意味は、
「admin というユーザに対して、GRANT OPTION 以外の全てのシンプルな権限を設定し、すべてのデータベース・テーブルにアクセスできる」
ということです。それでは、追加してアクセスできるかどうか試してみましょう。

まずは、ユーザの追加です。

mysql> GRANT ALL ON *.* TO admin;
Query OK, 0 rows affected (0.00 sec)

mysql> select Host,User from user;
+-----------+----------+
| Host      | User     |
+-----------+----------+
| %         | admin    |
| localhost | root     |
+-----------+----------+
2 rows in set (0.00 sec)

User admin が追加されました。'%' はすべてのホスト名を意味します。なお、MySQL はユーザ名内でワイルドカードをサポートしません。

権限の変更が反映するタイミングは、GRANTステートメントを使用していますので、変更があった直後に権限テーブルをメモリへリロードされ、すぐ反映します。

それでは、admin というユーザからアクセスできるかどうか、 fuku_dev サーバからやってみましょう。

$ hostname
fuku_dev
$ mysql -h 10.20.138.76 -u admin
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 4.0.26-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

できました。ついでに、どのようなデータベースにアクセスできるのかみましょう。

mysql> show databases;
+----------+
| Database |
+----------+
| mysql    | 
| test     | 
+----------+
2 rows in set (0.00 sec)

今回の権限は、「GRANT OPTION以外のすべてのシンプルな権限」ということで、GRANTは実行できません。

mysql> use mysql
Database changed
mysql> GRANT ALL ON *.* TO test_user;
ERROR 1045 (00000): Access denied for user: 'admin@%' (Using password: NO)

でも、updateやdeleteはできます。

mysql> update user set User='test_user' where User='admin';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select host, user from mysql.user;
+-----------+-----------+
| host      | user      |
+-----------+-----------+
| %         | test_user | 
| localhost | root      | 
+-----------+-----------+
2 rows in set (0.00 sec)

mysql> delete from user where  User='test_user';
Query OK, 1 row affected (0.00 sec)

mysql> select host, user from mysql.user;
+-----------+------+
| host      | user |
+-----------+------+
| localhost | root | 
+-----------+------+
1 row in set (0.00 sec)

MySQLへの接続ユーザをホスト名を指定して追加し、アクセスを試してみる

こんどは、ホスト名(fuku_dev)を指定して、ユーザを作成してみましょう。

GRANT ALL ON *.* TO admin@fuku_dev;

上記のように、ユーザを追加します。この文の意味は、
「ホスト fuku_devadmin のadminというユーザに対して、GRANT OPTION 以外の全てのシンプルな権限を設定し、すべてのデータベース・テーブルにアクセスできる」
ということです。
任意ホストからユーザに供与権を適応させる為に、MySQL は user_name@host_name の形で user 値を指定する事をサポートします。
それでは、追加してアクセスできるかどうか試してみましょう。

mysql> GRANT ALL ON *.* TO admin@fuku_dev;
Query OK, 0 rows affected (0.00 sec)

mysql> select Host,User from user;
+-----------+-------+
| Host      | User  |
+-----------+-------+
| fuku_dev  | admin |
| localhost | root  |
+-----------+-------+
2 rows in set (0.00 sec)

ユーザを追加しましたので、fuku_dev サーバからアクセスしてみましょう。

$ hostname
fuku_dev
$ mysql -h 10.20.138.76 -u admin
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 4.0.26-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

ログインできました。

ちなみに、ホスト名ですが、hostname の返り値が fuku_dev だからホスト名は fuku_dev とは限りません。
fuku_dev サーバに /etc/hosts に以下のような設定が必要です。

$ less /etc/hosts | grep fuku_dev
10.20.138.18 fuku_dev

これで、MySQLサーバは 10.20.138.18 のホスト名は fuku_dev だと認識できますので注意してください。

また、ホスト名の部分は IPアドレスでも指定できます。以下は指定例です。

GRANT ALL ON *.* TO admin@10.20.138.18;

さらに、ネットマスクを利用しても指定できます。

GRANT ALL ON *.* TO admin@'10.20.138.0/255.255.255.0';

上記の例だと、10.20.138.0〜10.20.138.255 のIPを持つサーバから、adminユーザからのアクセスが可能になります。
参考=>MySQL :: MySQL 5.6 リファレンスマニュアル :: 6.2.4 アクセス制御、ステージ 1: 接続の検証

まとめ

これで、基本は分かってきました。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 6.2 MySQL アクセス権限システムをみて、データベース・テーブルごとの権限付与なども学んで行こうと思います。


また、突っ込み大歓迎です><お願いします!