Partial Revokesとは
Partial RevokesはMySQL8.0.16から追加された機能です。
グローバルに、つまり全てのスキーマに対して権限を付与してから、一部のスキーマから付与した権限を取り消すことができます。
今回は最新バージョンであるMySQL8.0.19で実際にこの機能を試してみます。
MySQL :: MySQL 8.0 Reference Manual :: 6.2.12 Privilege Restriction Using Partial Revokes
まずは、Partial Revokesがどのようなときに役立つのか見ていきましょう。
なお、本記事で、登場するsakilaとworldデータベースは以下からダウンロードできます。
sakila : https://downloads.mysql.com/docs/sakila-db.zip
world : https://downloads.mysql.com/docs/world.sql.zip
使用例
ユーザー(user@localhost)に対して、次のような権限を付与するとします。
– ただし、mysqlスキーマへのSELECT権限は与えない
スキーマ構成
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | d1 | | d2 | | d3 | | d4 | | d5 | | d6 | | d7 | | d8 | | d9 | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ |
Partial Revokes機能を使用しない場合
これまで(MySQL8.0.16以前)はGRANTを使用して、mysql以外のスキーマに対して１つ１つ明示的に権限を付与する必要がありました。
|
1 2 3 4 5 6 7 8 |
mysql> GRANT SELECT ON d1.* TO user@localhost; mysql> GRANT SELECT ON d2.* TO user@localhost; mysql> GRANT SELECT ON d3.* TO user@localhost; ... mysql> GRANT SELECT ON d8.* TO user@localhost; mysql> GRANT SELECT ON d9.* TO user@localhost; mysql> GRANT SELECT ON performance_schema.* TO user@localhost; mysql> GRANT SELECT ON sys.* TO user@localhost; |
Partial Revokes機能を使用する場合
全てのスキーマ*.*に対してSELECT権限を付与した後、mysqlスキーマへの権限を制限します。
|
1 2 |
mysql> GRANT SELECT ON *.* TO user@localhost; mysql> REVOKE SELECT ON mysql.* FROM user@localhost; |
これでuser@localhostにmysqlスキーマを除いた全てのスキーマに対してSELECT権限を付与することができました。
このように、Partial Revokes機能を使用すると2行で済みます。
特定のスキーマを除いて、多数のスキーマに権限を与えたいといった場合は、Partial Revokes機能を使用すると便利です。
一方、権限を付与するスキーマの数が少ない場合は、今まで通りGRANTで明示的にスキーマ１つ１つに対して権限を付与していったほうがいいでしょう。
両者の比較はこちらのマニュアルでも説明されています。
Partial Revokes機能の有効化
Partial Revokes機能はpartial_revokes変数で設定します。
デフォルトではOFF(注1)となっています。
設定ファイルの[mysqld]セクションにpartial_revokes = ONを追加して、再起動します。
動的に変更することも可能です。
|
1 |
mysql> SET PERSIST partial_revokes = ON; |
注1:一度機能を有効化して、Partial Revokes機能を使用したユーザーが存在していると、次回のサーバー起動時には明示的に有効化していなくても、デフォルトで有効化されます。詳しくは後述の[Partial Revokes機能の無効化]で説明します。
Partial Revokes機能の使用方法
新たに作成したユーザーにPartial Revokesを使用してみます。
まず、ユーザーを作成し、グローバルレベル*.*でSELECTとINSERT権限を付与します。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> CREATE USER user1@localhost IDENTIFIED BY 'Password1!'; Query OK, 0 rows affected (0.01 sec) mysql> GRANT SELECT, INSERT ON *.* TO user1@localhost; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS FOR user1@localhost; +----------------------------------------------------+ | Grants for user1@localhost | +----------------------------------------------------+ | GRANT SELECT, INSERT ON *.* TO `user1`@`localhost` | +----------------------------------------------------+ 1 row in set (0.00 sec) |
次に、worldスキーマのみINSERT権限を制限(Partial Revokes)します。
|
1 2 |
mysql> REVOKE INSERT ON world.* FROM user1@localhost; Query OK, 0 rows affected (0.01 sec) |
user1@localhostの権限を確認すると制限されている状態を表すREVOKEが追加されています。
|
1 2 3 4 5 6 7 8 |
mysql> SHOW GRANTS FOR user1@localhost; +-----------------------------------------------------+ | Grants for user1@localhost | +-----------------------------------------------------+ | GRANT SELECT, INSERT ON *.* TO `user1`@`localhost` | | REVOKE INSERT ON `world`.* FROM `user1`@`localhost` | +-----------------------------------------------------+ 2 rows in set (0.00 sec) |
user1@localhostでログインをして、権限が制限されているかを確認します。
|
1 |
$ mysql -u user1 -p |
worldスキーマのテーブルに対してSELECTはできますが、INSERTの権限が制限されていることが確認できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> SELECT * FROM world.city LIMIT 5; +----+----------------+-------------+---------------+------------+ | ID | Name | CountryCode | District | Population | +----+----------------+-------------+---------------+------------+ | 1 | Kabul | AFG | Kabol | 1780000 | | 2 | Qandahar | AFG | Qandahar | 237500 | | 3 | Herat | AFG | Herat | 186800 | | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 | | 5 | Amsterdam | NLD | Noord-Holland | 731200 | +----+----------------+-------------+---------------+------------+ 5 rows in set (0.05 sec) mysql> INSERT INTO world.city(id , Name, CountryCode) VALUES (4082 ,"test" ,"AFG"); ERROR 1142 (42000): INSERT command denied to user 'user1'@'localhost' for table 'city' |
また、world以外のスキーマに対してはINSERTが実行できます。
|
1 2 |
mysql> INSERT INTO sakila.rental(rental_date, inventory_id, customer_id, staff_id) VALUES(NOW(), 10, 3, 1); Query OK, 1 row affected (0.00 sec) |
Partial Revokesを使用しているユーザーの確認方法
Partial Revokesによって権限が制限されているユーザーはmysql.userテーブルのUser_attributesカラムにRestrictions属性があります。
以下のコマンドでPartial Revokesが使用されているユーザーを確認できます。
|
1 2 3 4 5 6 7 |
mysql> SELECT User, Host, User_attributes->>'$.Restrictions' FROM mysql.user WHERE User_attributes->>'$.Restrictions' <> ''; +-------+-----------+---------------------------------------------------+ | User | Host | User_attributes->>'$.Restrictions' | +-------+-----------+---------------------------------------------------+ | user1 | localhost | [{"Database": "world", "Privileges": ["INSERT"]}] | +-------+-----------+---------------------------------------------------+ 1 row in set (0.00 sec) |
Partial Revokesによる制限の取り消し
Partial Revokesを取り消す方法はいくつかあります。
今回はuser1に以下のように権限を制限した状態で、制限の取り消し方法を紹介します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> GRANT SELECT, INSERT ON *.* TO user1@localhost; mysql> REVOKE INSERT ON world.* FROM user1@localhost; mysql> REVOKE INSERT ON sakila.* FROM user1@localhost; mysql> SHOW GRANTS FOR user1@localhost; +------------------------------------------------------+ | Grants for user1@localhost | +------------------------------------------------------+ | GRANT SELECT, INSERT ON *.* TO `user1`@`localhost` | | REVOKE INSERT ON `sakila`.* FROM `user1`@`localhost` | | REVOKE INSERT ON `world`.* FROM `user1`@`localhost` | +------------------------------------------------------+ 3 rows in set (0.00 sec) |
グローバルに権限を付与する
user1に対して再度グローバルに権限を付与することで、制限を取り消すことができます。
|
1 2 3 4 5 6 7 8 9 10 |
mysql> GRANT SELECT, INSERT ON *.* TO user1@localhost; Query OK, 0 rows affected (0.01 sec) mysql> SHOW GRANTS FOR user1@localhost; +----------------------------------------------------+ | Grants for user1@localhost | +----------------------------------------------------+ | GRANT SELECT, INSERT ON *.* TO `user1`@`localhost` | +----------------------------------------------------+ 1 row in set (0.00 sec) |
スキーマに権限を付与する
制限されているスキーマに対して権限を付与することで、特定のスキーマに対する制限を取り消すことができます。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> GRANT INSERT ON world.* TO user1@localhost; Query OK, 0 rows affected (0.01 sec) mysql> SHOW GRANTS FOR user1@localhost; +------------------------------------------------------+ | Grants for user1@localhost | +------------------------------------------------------+ | GRANT SELECT, INSERT ON *.* TO `user1`@`localhost` | | REVOKE INSERT ON `sakila`.* FROM `user1`@`localhost` | +------------------------------------------------------+ 2 rows in set (0.00 sec) |
グローバルに権限を取り消す
付与されている権限をグローバルでREVOKEすることにより、権限を取り消すことができます。
このときスキーマレベルでの制限もなくなります。
|
1 2 3 4 5 6 7 8 9 10 |
mysql> REVOKE SELECT,INSERT ON *.* FROM user1@localhost; Query OK, 0 rows affected (0.01 sec) mysql> SHOW GRANTS FOR user1@localhost; +-------------------------------------------+ | Grants for user1@localhost | +-------------------------------------------+ | GRANT USAGE ON *.* TO `user1`@`localhost` | +-------------------------------------------+ 1 row in set (0.00 sec) |
Partial Revokesの制限
スキーマのワイルドカードの使用
Partial Revokesではワイルドカードでスキーマを指定することはできないとマニュアルに書いてあります。
Partial revokes must name the schema literally. Schema names that contain the % or _ SQL wildcard characters (for example, myschema%) are not permitted.
引用元 ： MySQL :: MySQL 8.0 Reference Manual :: 6.2.12 Privilege Restriction Using Partial Revokes
実際に試してみます。
まず、user1にワイルドカードでwo%スキーマに対する権限を制限してみます。
|
1 2 3 4 5 |
mysql> GRANT SELECT, INSERT ON *.* TO user1@localhost; Query OK, 0 rows affected (0.00 sec) mysql> REVOKE SELECT, INSERT ON `wo%`.* FROM user1@localhost; Query OK, 0 rows affected (0.00 sec) |
user1@localhostでログインして確認します。
worldスキーマのテーブルに対してSELECTとINSERTが実行できます。
wo%スキーマに対する制限がされていない、つまりワイルドカードが有効になっていないことがわかります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
$ mysql -u user1 -p mysql> SHOW GRANTS; +-----------------------------------------------------------+ | Grants for user1@localhost | +-----------------------------------------------------------+ | GRANT SELECT, INSERT ON *.* TO `user1`@`localhost` | | REVOKE SELECT, INSERT ON `wo%`.* FROM `user1`@`localhost` | +-----------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM world.city LIMIT 5; +----+----------------+-------------+---------------+------------+ | ID | Name | CountryCode | District | Population | +----+----------------+-------------+---------------+------------+ | 1 | Kabul | AFG | Kabol | 1780000 | | 2 | Qandahar | AFG | Qandahar | 237500 | | 3 | Herat | AFG | Herat | 186800 | | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 | | 5 | Amsterdam | NLD | Noord-Holland | 731200 | +----+----------------+-------------+---------------+------------+ 5 rows in set (0.02 sec) mysql> INSERT INTO world.city(id , Name, CountryCode) VALUES (4085 ,"test" ,"AFG"); Query OK, 1 row affected (0.01 sec) |
なおワイルドカードを使用したユーザー名(`test`@`%` など)は有効なままとなっています。
さらに、現在の最新版8.0.19ではPartial Revokes機能を有効化すると、これまで可能であったGRANT文でのワイルドカードによるスキーマの指定も無効となります。
こちらで報告されています。
どのような動作になるのか確認してみます。
test%(testa,testb,testc)スキーマに対してSELECT権限を持ったuser2@localhostが存在しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
mysql> SHOW GRANTS; +----------------------------------------------------------+ | Grants for user2@localhost | +----------------------------------------------------------+ | GRANT USAGE ON *.* TO `user2`@`localhost` | | GRANT SELECT, INSERT ON `test%`.* TO `user2`@`localhost` | +----------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | testa | | testb | | testc | +--------------------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM testa.t1 UNION ALL SELECT * FROM testb.t1 UNION ALL SELECT * FROM testc.t1; +------+ | id | +------+ | 1 | | 1 | | 1 | +------+ 3 rows in set (0.00 sec) |
Partial Revokesを設定ファイルで有効化してから再起動してuser2@localhostでログインします。
|
1 2 3 4 5 |
$ vi /etc/my.cnf partial_revokes = ON $ systemctl restart mysqld $ mysql -u user2 -p |
SHOW GRANTS;で確認できる権限は変更されていませんが、test%に対する権限がなくなっていること、つまりワイルドカードによるスキーマの指定が無効となっていることがわかります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> SHOW GRANTS; +----------------------------------------------------------+ | Grants for user2@localhost | +----------------------------------------------------------+ | GRANT USAGE ON *.* TO `user2`@`localhost` | | GRANT SELECT, INSERT ON `test%`.* TO `user2`@`localhost` | +----------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM testa.t1 UNION ALL SELECT * FROM testb.t1 UNION ALL SELECT * FROM testc.t1; ERROR 1142 (42000): SELECT command denied to user 'user2'@'localhost' for table 't1' |
ワイルドカードでスキーマを指定している場合は、Partial Revokes機能を有効化する際には注意してください。
Partial Revokes機能の無効化
Partial Revokesを無効化するには、Partial Revokes機能が使われていないことが条件となります。
権限が制限されているユーザーがいる場合は、動的に変更しようとするとエラーとなります。
|
1 2 |
mysql> SET PERSIST partial_revokes = OFF; ERROR 3896 (HY000): At least one partial revoke exists on a database. The system variable '@@partial_revokes' must be set to ON. |
Partial Revokes機能を無効化したい場合は、前項の[Partial Revokesによる制限の取り消し]で説明した方法で制限を取り消すか、Partial Revokesが使用されているユーザーを削除してください。
また、権限が制限されているユーザーがいる状態では明示的にpartial_revokes=ONとしていなくても自動で有効化され起動します。
このとき、エラーログには以下の内容が出力されます。
マニュアルでも以下のように説明されています。
For attempts to disable partial_revokes at startup, the server logs an error message and enables partial_revokes.
引用元 ： MySQL :: MySQL 8.0 Reference Manual :: 6.2.12 Privilege Restriction Using Partial Revokes
また、設定ファイルで明示的にpartial_revokes=OFFと設定した場合も、同様に自動でPartial Revokes機能を有効化して起動します。
自動で有効化された際の挙動について
前項で説明した通り、Partial Revokes機能を使用したユーザーがいる状態で明示的にPartial Revokes機能を有効化していない場合は、サーバーは自動でPartial Revokes機能を有効化して起動します。
このときpartial_revokesの値を確認するとOFFと表示されてしまいます。
|
1 2 3 4 5 6 7 |
mysql> SHOW GLOBAL variables like 'partial_revokes'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | partial_revokes | OFF | +-----------------+-------+ 1 row in set (0.01 sec) |
しかし、実際にはサーバーは機能が有効化された状態として動作するようなので注意が必要です。
user1には制限された権限が付与されていて、実際にその制限が機能していることが確認できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> SHOW GRANTS; +----------------------------------------------------------------------+ | Grants for user1@localhost | +----------------------------------------------------------------------+ | GRANT SELECT, INSERT ON *.* TO `user1`@`localhost` WITH GRANT OPTION | | REVOKE SELECT, INSERT ON `world`.* FROM `user1`@`localhost` | +----------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM world.city LIMIT 5; ERROR 1142 (42000): SELECT command denied to user 'user1'@'localhost' for table 'city' mysql> INSERT INTO world.city(id , Name, CountryCode) VALUES (4082 ,"test" ,"AFG"); ERROR 1142 (42000): INSERT command denied to user 'user1'@'localhost' for table 'city' |
権限のレベル
GRANTで付与が可能なレベルは権限によって異なりましたが、Partial Revokesはスキーマレベルでしか行えません。
特定のテーブルやカラムの権限を制限することはできません。
|
1 2 3 |
mysql> GRANT SELECT, INSERT ON *.* TO user1@localhost; mysql> REVOKE INSERT ON world.city FROM user1@localhost; ERROR 1147 (42000): There is no such grant defined for user 'user1' on host 'localhost' on table 'city' |
ただし、制限されたスキーマの特定のテーブル対して権限を付与することは可能です。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> REVOKE SELECT, INSERT ON world.* FROM user1@localhost; mysql> GRANT SELECT, INSERT ON world.city TO user1@localhost; mysql> SHOW GRANTS FOR user1@localhost; +---------------------------------------------------------------+ | Grants for user1@localhost | +---------------------------------------------------------------+ | GRANT SELECT, INSERT ON *.* TO `user1`@`localhost` | | REVOKE SELECT, INSERT ON `world`.* FROM `user1`@`localhost` | | GRANT SELECT, INSERT ON `world`.`city` TO `user1`@`localhost` | +---------------------------------------------------------------+ 3 rows in set (0.00 sec) |
user1でログインするとworldスキーマのテーブルは権限をもつcityテーブルのみ確認できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
$ mysql -u user1 -p mysql> SHOW TABLES FROM world; +-----------------+ | Tables_in_world | +-----------------+ | city | +-----------------+ 1 row in set (0.01 sec) mysql> SELECT * FROM city LIMIT 1; +----+-------+-------------+----------+------------+ | ID | Name | CountryCode | District | Population | +----+-------+-------------+----------+------------+ | 1 | Kabul | AFG | Kabol | 1780000 | +----+-------+-------------+----------+------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM country LIMIT 1; ERROR 1142 (42000): SELECT command denied to user 'user1'@'localhost' for table 'country' |
まとめ
これまでも特定のスキーマを除いて権限を付与することは、GRANTを駆使すればできていたことですが、スキーマ数によっては大変な手間がかかっていました。Partial Revokes機能を待ち望んでいた人もいたのではないでしょうか。
一方で、ワイルドカードが使用できなかったり、スキーマレベル以外では使用できなかったりとやや物足りない面もあります。
[使用例]でも紹介した通り、Partial Revokes機能は長短ありますので、使用する環境に合わせて利用を検討してはいかがでしょうか。
