第69回　MySQLの権限レベルについて

MySQLではGRANTステートメントを使用して、ユーザに対して権限を付与することができます。MySQLの場合は管理者用の権限や特定のカラムに対してのみのSELECT権限などGRANT文で細かい粒度に分けて設定できます。今回はこのような権限レベルついて紹介いたします。GRANT文でいうON *.*の部分が主な話となります。

権限レベル

権限レベルには主にグローバル権限、データベース権限、テーブル権限やカラム権限が存在します。これらをそれぞれ説明します。

グローバル権限

グローバル権限は管理者用のための権限です。GRANT構文のON *.*と記述します。

GRANT ALL ON *.* TO 'user'@'localhost';

権限情報はmysqlデータベースのuserテーブルに格納されます。

mysql> SELECT user, host, Select_priv FROM mysql.user WHERE user='user';
+----------+-----------+-------------+
| user     | host      | Select_priv |
+----------+-----------+-------------+
| user     | localhost | Y           |
+----------+-----------+-------------+

データベース権限

データベース権限は、指定したデータベース内のすべてのオブジェクトに適用されます。GRANT構文のON dbname.*と記述します。

GRANT ALL ON userdb.* TO 'user'@'localhost';
GRANT SELECT ON userdb.* TO 'user'@'localhost';

権限情報はmysqlデータベースのdbテーブルに格納されます。

mysql> SELECT user, host, Select_priv FROM mysql.db WHERE user='user';
+------+-----------+--------+-------------+
| user | host      | db     | Select_priv |
+------+-----------+--------+-------------+
| user | localhost | userdb | Y           |
+------+-----------+--------+-------------+

また、データベース権限ではワイルドカードを指定することができます。その場合はバッククォートで囲み、ワイルドカード文字「%」を使用します。

GRANT SELECT ON `%`.* TO 'user'@'localhost';
GRANT SELECT ON `shard%`.* TO 'user'@'localhost';

1つ目の例はすべてのデータベースに対してSELECTを許可します。2つ目の例はshardという文字列に前方一致したデータベースに対してSELECTを許可します。

また、例文にてALLと指定していますが、これは対象の権限レベルに属するすべての権限を付与します。よって、グローバル権限でのALLとデータベース権限でのALLは違います。たとえば、SUPERやSHUTDOWNなどは管理者権限ですので、データベース権限のALLでは付与されません。

どの権限がどの権限レベルに属するかは、マニュアル：[13.7.1.4 GRANT 構文]の“⁠MySQLによってサポートされる権限⁠”をご参照ください。MySQL上からは権限情報を管理するテーブルをDESCしてフィールドを確認することで、設定可能な権限を推測することができます。

mysqlデータベースのdbテーブルの例

mysql> DESC mysql.db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(16)      | NO   | PRI |         |       |
| 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       |       |
| 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       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_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       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+

テーブル権限

テーブル権限は、指定したテーブル内のすべてのカラムに適用されます。GRANT構文のON dbname.tablenameと記述します。権限情報はmysqlデータベースのtables_privテーブルに格納されます。

GRANT SELECT ON userdb.usertable TO 'user'@'localhost';

1つのGRANT文に対して、複数のテーブルを指定することやワイルドカードを使用することはできません。複数のテーブルを指定したい場合はテーブル数分のGRANT文を発行する必要があります。

カラム権限

カラム権限は、指定したテーブル内のカラムに対して適用されます。これまでの構文と少し変わりprivtype (columnname) ON dbname.tablenameというような記述になります。権限情報はmysqlデータベースのcolumns_privテーブルに格納されます。

GRANT SELECT (col1), INSERT (col1,col2) ON dbname.tablename TO 'user'@'localhost';

また、ここでは説明していませんが、前述の権限レベルの他にストアドルーチン権限やプロキシユーザー権限があります。詳しくはマニュアル[13.7.1.4 GRANT 構文]をご参照ください。

権限レベルのアクセス制御の順番

MySQLは対象のユーザに操作する権限があるのかを確認するために、以下の順番でアクセス制御を管理します。

グローバル権限
データベース権限
ワイルドカードのデータベース権限
テーブル権限
カラム権限

MySQLがアクセスを許可する流れを見てみます。

たとえば、あるユーザがデータベース権限のSELECT権限を保持しているとします。SELECT権限はすべての権限レベルに属しています。SELECTが発行されると、MySQLは最初にグローバル権限を確認します。ここに設定させていないので次にデータベース権限を確認します。ここで設定されているのを確認すると、そのユーザはテーブルへのSELECTを許可されます。

また、データベース権限はワイルドカードを使用していないほうを優先的に適用します。

GRANT SELECT ON `userdb`.* TO 'user'@'localhost';
GRANT INSERT ON `user%`.* TO 'user'@'localhost';

上記の場合は、userdbデータベースが存在しているとどちらも一致します。しかし、そのユーザのuserdbデータベースに対する権限はSELECT権限のみとなります。

権限付与のタイミング

基本的には運用中はユーザ作成時に一緒に権限を付与することが多いと思います。では、接続中のセッションに対して権限の付与や取消（GRANT文やREVOKE文）を行うとどうなるでしょうか。

グローバル権限・・接続中のセッションには反映されません。再接続されたタイミングで反映されます。
データベース権限・・接続中のセッションがUSE文を使用したタイミングで反映されます。
テーブル権限、カラム権限・・接続中のセッションがリクエストを発行したタイミングで反映されます。

よって、グローバル権限は再接続が必要です。それ以外は即時で反映されます。

たとえば、グローバル権限が付与された接続中のセッションに対して、グローバル権限の取消とデータベース権限の付与をするとします。その場合はデータベース権限は付与されますが、グローバル権限の取消は再接続されるまで行われません。よって、そのセッションが切断されるまでは一時的にグローバル権限とデータベース権限が付与されている状態になります。

まとめ

今回はGRANT文の権限レベルについて紹介しました。この権限レベルについて理解して、ユーザには適切な権限を付与するようにしましょう。今回は触れていませんがユーザに関する情報は第17回　MySQLのユーザー管理について［その1］をご参照ください。