MySQLのアクセス権限を設定｜MySQL・データベース講座

ここでは、MySQLの権限システムを詳しく説明している。設定の仕方は「接続の承認」をみてね。

MySQL特権システム概要

MySQLには、データベース、ホスト、ユーザ、テーブル、フィールド毎に、データベースを操作（閲覧、挿入、更新、削除など）するための様々な権限を設定することができる。細かく設定できるのはうれしいことだけど、これがまた結構めんどくさいんだな。覚悟が必要だよ。

MySQLでは、データベースにアクセスできるユーザ/パスワード/ホスト名をmysqlというデータベースに登録しているから、そのデータベースを設定していくことになる。
　mysqlデータベースに用意されている管理用テーブルは、db、host、user、tables_priv、columns_privの5つで、これにより、どのユーザがどのホストからの何のデータベースに対してアクセスできるか指定できる。

mysqlデータベース内のテーブル一覧

データベース名	説明
db	各データベースについてのアクセス制限
host	ホストによる制限
user	ユーザによりアクセスを制限
tables_priv	テーブルについてのアクセス制限
columns_priv	フィールドについてのアクセス制限

管理テーブルは MySQL インストール時に自動的に作られる。5つのテーブルはmysqlデータベースにあるから、以下のようにして確認してみよう。

mysqlデータベースを選択

use mysql

dbテーブルの表示

show fields from db;
select * from db;

hostテーブルの表示

show fields from host;
select * from host;

userテーブルの表示

show fields from user;
select * from user;

MySQLが提供する権限

MySQLの権限には、テーブルへのデータ挿入・削除や、更新などがある。権限の設定はmysqlデータベースの user、db、host、tables_priv、columns_privで行う。下記のテーブルを参照してほしい。

権限	Column	Context
select	Select_priv	tables
insert	Insert_priv	tables
update	Update_priv	tables
delete	Delete_priv	tables
index	Index_priv	tables
alter	Alter_priv	tables
create	Create_priv	databases, tables/indexes
drop	Drop_priv	databases/ tables
grant	Grant_priv	databases/ tables
reload	Reload_priv	server administration
shutdown	Shutdown_priv	server administration
process	Process_priv	server administration
file	File_priv	file access on server

• select, insert, update, deleteの権限は、存在しているデータベースのテーブルに対して許可される。
• select権限はテーブルから行を取り出すことができる。
• index権限はインデックスの作成と破棄(削除)を許可する。
• alter権限は ALTER TABLE の実行を許可する。
• createとdrop(※1)権限は、新しいデータベースやテーブルの作成、あるいは既に存在するデータベース、テーブルの破棄(削除)を許可する。
• grant権限は、他のユーザーに対して自分の権限を持たせる事を許可する。
• fileの権限を与えると、LOAD DATA INFILE と SELECT ... INTO OUTFILE構文を使用して、サーバーのファイルを読み書きする事ができる。

※mysql データベースに登録されているユーザーにdrop権限を与えると、そのユーザーは MySQL のアクセス権限が格納されているデータベースを破棄できます

管理者操作に関する許可

残りの権限は管理者操作に関する許可だ。この権限により、mysqladminコマンドの実行権が設定される。mysqladminコマンドのどれが、どの権限に対応しているかは次の表の通り。

権限	コマンド
reload	reload, refresh, flush-privileges, flush-hosts, flush-logs, flush-tables
shutdown	shutdown
process	processlist, kill

各コマンドの詳細

• reloadはサーバーに権限の設定を再読込させるように伝える。
• refreshコマンドは全てのテーブルをフラッシュし、ログファイルを開き直す。
• flush-privilegesは reload と同義。
• その他のflush-*コマンドはrefreshの動作とよく似ているが、適用範囲を絞っており、ちょっとした場合に有効だ。例えば、ログファイルだけをフラッシュしたい場合、refreshを行うよりもflush-logsが効果的な方法だ。
• shutdownコマンドは、。
• processlistコマンドはサーバーが実行しているスレッドの情報を表示する。killコマンドはサーバーのスレッドをkillする。自分のスレッドは常に表示、killできるが、他人のスレッドをそうするにはprocess権限が必要だ。

管理テーブルの項目

全ての管理テーブルに共通することだけど、テーブルの各項目を分類すると、適用範囲を指定するスコープフィールドと、許可を定義する権限フィールドがの2種類がある。

スコープフィールド

スコープフィールドは、権限テーブルの登録ごとに、その適用範囲を決める。
例えば、userテーブルのHostとUserに "domain.co.jp"と"staff"が登録されている場合、サーバーへの接続はホストdomain.co.jpからアクセスしてきたstaffを許可する。同様に、dbテーブルのHost、User、Dbに"domain.co.jp", "staff", "report"が登録されていると、ホスト domain.co.jp から来たstaffに対し report データベースへの接続が許可される。

フィールド名	タイプ
Host	CHAR(60)
User	CHAR(16)
Password	CHAR(16)
Db	CHAR(64)

権限フィールド

権限フィールドは有効になった許可を示す。

user/db/host テーブルでは、全ての権限フィールドは ENUM('N','Y') で定義される。この値は 'N' か 'Y' のどちらかで、デフォルト値は 'N' だ。tables_privとcolumns_privテーブルは、テーブルか、テーブルとカラムを対にしたスコープフィールドを含む。
tables_privとcolumns_privテーブルでは、権限フィールドはSETフィールドとして定義される

テーブル名	フィールド名	設定可能な権限
tables_priv	Table_priv	'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter'
tables_priv	Column_priv	'Select', 'Insert', 'Update', 'References'
columns_priv	column_priv	'Select', 'Insert', 'Update', 'References'

権限の詳細は下段の「MySQL が提供する権限」を参照。

管理テーブルの詳細

user/db/hostテーブル

サーバーは mysql データベースの user, db, host ３つの管理テーブルから、アクセス制限を決定する。テーブルのフィールドは以下の通り。

テーブル名	user	db	host
スコープフィールド	Host User Password	Host Db User	Host Db
権限フィールド	Select_priv Insert_priv Update_priv Delete_priv Index_priv Alter_priv Create_priv Drop_priv Grant_priv Reload_priv Shutdown_priv Process_priv File_priv	Select_priv Insert_priv Update_priv Delete_priv Index_priv Alter_priv Create_priv Drop_priv Grant_priv	Select_priv Insert_priv Update_priv Delete_priv Index_priv Alter_priv Create_priv Drop_priv Grant_priv

userテーブルのスコープフィールドは、接続要求を受け入れるか拒否するかを決定する。そして、管理テーブルの中でuserの権限許可が最優先される。たとえば、userテーブルの権限許可が'Y'であれば、DbやHostで'N'となっていても、userで設定した'Y'が優先される。userテーブルには最低限の許可を与えるようにし、DbやHostごとに、それぞれの権限許可を定義する方が無難。

tables_priv/columns_privテーブル

tables_privテーブルとcolumns_privテーブルは、基本的にはdbテーブルが提供している機能の集約だ。リクエスト承認のために、サーバーはこれらuser/db/hostの 3 つのテーブルによって決められた許可を基本とするが、もしテーブルに対する要求であるならば、tables_privとcolumns_privテーブルをさらに調べる。これらのテーブルのフィールドは以下のようになっている。

テーブル名→	tables_priv	columns_priv
スコープフィールド	Host Db User Table_name	Host Db User Table_name Column_name
権限フィールド	Table_priv Column_priv	Type
その他フィールド	Timestamp Grantor	Timestamp

権限の定義に関するノウハウ

権限を許可する際の注意事項

ある権限を欲しがるユーザーだけにその権限を許可するのはよい考えだが、権限を与えるときには、特定の事項を熟知していなければならない。

• grant権限を許可されたユーザーは、他のユーザーの権限を変える事ができます。
• alter権限は、テーブル名の変更を行うことにより権限システムを破るために使用されるかもしれません。
• file権限は、サーバー上にある全ての読み込み可能なファイルをデータベースに取り込むことができ、これはSELECT文でアクセスできます。
• shutdown権限は、他のユーザーに対するサービスを、サーバーを停止することによって、拒否するようにできます。
• process権限は実行されているクエリーをプレーンテキストで見ることに使えます。パスワードの設定、変更のクエリーも含みます。
• mysqlデータベースに対しての権限は、パスワードの変更と他の権限の設定を変更ができます。パスワードは暗号化されて登録されており、読むことはできませんが、パスワードを違うものに変えることができます。

以下は MySQL の権限システムで行うものではない。

• アクセスを拒否するユーザーを特定して設定することはでない。
• データベース内のテーブルの作成、破棄の権限を持つが、データベースそのものを作成、破棄できる、そのようなユーザーを設定できない。

管理者権限の設定ノウハウ

reloadやshutdownなどの管理者権限は、userテーブルにだけ定義したほうがいい。なぜなら、管理者操作はデータベースではなくサーバーへの操作だから、hostやdbテーブルで定義する必要はない。また、こうしておくと、管理者操作の許可は、userテーブルの定義だけを見ればわかるようになる。
file操作の権限はuserテーブルにだけ定義するように。これは管理者操作ではないが、アクセスしているデータベースにかかわらず、サーバー内のファイルを読み書きできる。

便利なツールとしてmysqlaccessスクリプト( Yves Carlier 作)がMySQLの配布に含まれている。 mysqlaccess を --help オプションで起動するとヘルプが表示される。mysqlaccess は user,db and hostテーブルだけしか検査しない。テーブルレベルの権限、カラムレベルの権限は調べない。

アクセスコントロールの段階

MySQLは接続の際、ホスト名とユーザー名の両方をあわせてチェックする。違うホスト間でユーザ名が重複していても､ユーザは別のものとして扱われる。

アクセスコントロール

• サーバーは接続許可があるかどうかをチェック
• 接続許可後、サーバーはそれぞれのリクエストをチェック