MySQL道普請便り

第133回 Partial Revokesによる権限の一部剥奪について

この記事を読むのに必要な時間:およそ 3 分

「MySQL道普請便り」では第69回 MySQLの権限レベルについて第114回 MySQL 8.0から使えるさまざまな権限についてなどで権限について取り上げてきましたが,MySQL8.0.16でPartial Revokesという面白いシステム変数が追加されました。これは通常全権限を与えるが,一部の権限だけを剥奪するというような部分的な制限設定をし,いわばブロックリストのように管理することができます。現状の設定でも同等の権限を付与することが可能ではありますが,付与するスキーマの対象が多い場合等にこのシステム変数は有用かもしれません。

今回はこのシステム変数による挙動や注意点について確認していきましょう。なお,今回利用するMySQLはバージョン8.0.21,OSはCentOS 7を利用しています。

partial_revokesの設定

partial_revokesはMySQL 8.0.21現在ではデフォルトでOFFになっています。つまり,通常のままではこの機能を利用することができません。この機能を利用するためには,SET GLOBALまたはSET PERSIST構文を使ってONにするか,my.cnfにpartial_revokesを記述する必要があります。

mysql> SHOW VARIABLES like 'partial_revokes';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| partial_revokes | OFF   |
+-----------------+-------+
1 row in set (0.01 sec)

mysql> SET GLOBAL partial_revokes = 1;
Query OK, 0 rows affected (0.04 sec)

これで設定は完了です。ためしに,全データベース対してSELECT権限を持ったユーザーを作成し,mysqlスキーマへの権限を剥奪してみましょう。

mysql> CREATE USER lhfukamachi@localhost IDENTIFIED BY '*****';
Query OK, 0 rows affected (0.05 sec)


ql>GRANT SELECT ON *.* TO lhfukamachi@localhost;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>REVOKE SELECT ON mysql.* FROM lhfukamachi@localhost;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>SHOW GRANTS FOR lhfukamachi@localhost;
+-----------------------------------------------------------+
| Grants for lhfukamachi@localhost                          |
+-----------------------------------------------------------+
| GRANT SELECT ON *.* TO `lhfukamachi`@`localhost`          |
| REVOKE SELECT ON `mysql`.* FROM `lhfukamachi`@`localhost` |
+-----------------------------------------------------------+
2 rows in set (0.00 sec)

REVOKEで権限を剥奪後にSHOW GRANTS構文で確認すると,全権限に対してSELECT権限が付与されており,mysqlスキーマに対してSELECT権限がREVOKEされているのが確認できます。実際に作成したユーザーでmysqlスキーマにアクセスしてみます。

mysql> SELECT current_user();
+-----------------------+
| current_user()        |
+-----------------------+
| lhfukamachi@localhost |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'lhfukamachi'@'localhost' for table 'user'

権限がなくてエラーとなりました。もちろんmysqlスキーマ以外のスキーマに対してはSELECT構文を利用することはできます。

このように,partial_revokesは⁠一部を除外した全権限に対して付与を行う⁠という形で利用することができます。実際にpartial_revokeを利用したrevokeの情報は,JSON型のmysql.user.user_attributesカラムのRestrictionsという項目に入っています。

mysql> SELECT concat(user,'@', host), user_attributes FROM mysql.user WHERE user = 'lhfukamachi' and host = 'localhost';
+------------------------+---------------------------------------------------------------------+
| concat(user,'@', host) | user_attributes                                                     |
+------------------------+---------------------------------------------------------------------+
| lhfukamachi@localhost  | {"Restrictions": [{"Database": "mysql", "Privileges": ["SELECT"]}]} |
+------------------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

partial_revokesの注意点

一度partial_revokesの機能を利用して作成したユーザーがmysql上に残っている場合は,partial_revokesを再びOFFにすることはできません。もしOFFにしようとした場合,エラーとなってしまいます。

mysql> SET GLOBAL partial_revokes = 0;
ERROR 3896 (HY000): At least one partial revoke exists on a database. The system variable '@@partial_revokes' must be set to ON.

OFFにする必要がある場合は,partial_revokesで剥奪された権限を削除,またはユーザーごと削除する等して,partial_revokesを利用しない状態にする必要があります。partial_revolesが利用されているユーザーがいるかどうかは,先ほどのmysql.user.user_attributesカラムのRestrictions属性を確認してください。

また,partial_revokesはスキーマを指定する必要があり,一部テーブルやカラムを除きたい,%等のワイルドカードを利用してスキーマを除外したいといったことには利用できません。CREATE TABLEやFILEといったグローバルレベルの権限についてもpartial_revokesは利用できないので注意が必要です。

レプリケーションを利用する場合についても,マスターがpartial_revokesがONになっていて,スレーブがOFFになっている場合に,エラーとなってレプリケーションが止まってしまうため,各インスタンスのpartial_revokesがONになっている状態を確認してから利用する必要があります。

partial_revokesがマスターがON,スレーブがOFFの状態で利用したときのエラー

mysql> SHOW SLAVE STATUS\G
〈省略〉
Last_SQL_Errno: 1141
Last_SQL_Error: Error 'There is no such grant defined for user 'lhfukamachi' on host 'localhost'' on query. Default database: '
'. Query: 'REVOKE SELECT ON mysql.* FROM lhfukamachi@localhost'
  Replicate_Ignore_Server_Ids:

rootなどの管理者ユーザー以外にもGRANT OPTIONを所持しているユーザーがいる場合についても気をつけましょう。GRANT OPTIONを所持していることによって権限付与ができるのですが,権限を付与するユーザーがpartial_revokesによりREVOKEされているスキーマがある場合はそれを引き継いで権限を付与するため,同じく権限がrevokeされた状態になります。

このあたりの挙動については,MySQL Server BlogのPartial Revokes from Database Objectsでさまざまなパターンについて紹介しているので,ぜひご一読ください。

まとめ

今回はpartial_revokesというシステム変数についてご紹介しました。スキーマの数が少ない場合は利用する機会はないかもしれませんが,スキーマの数が多くて通常の権限付与が面倒な場合や,スキーマが動的に生成される場合などには付与する運用面を考えると有効な手段かもしれません。そのような環境であれば,ぜひ利用を検討してみてください。

著者プロフィール

深町日出海(ふかまちひでみ)

GMOメディア株式会社のデータベースエンジニア。主にOracleとMySQLを担当。得意なプログラム言語はJava。MySQLの好きなところはTABLEやINDEXの識別子に64byteまで使えるところ。

Twitter:@lhfukamachi