第102回　MySQLのROLE［その1］

MySQL 8.0の新機能の1つにROLEというものが追加されました。

ROLEの追加によって、同じ権限を持つユーザーの作成や権限の管理がやりやすくなりました。他のRDBMSを利用した経験がある人にとっては馴染みのある機能かも知れません。今回は、MySQL 8.0で追加されたROLEの機能について確認していきます。

なお、実行環境はCentOS7、MySQLは8.0.17を利用しています。

ROLEの説明

MySQLの公式ドキュメントによると、ROLEは名前のついた権限の集合というふうに書かれています。従来のユーザーアカウントのように、この名前付きの権限の集合（以下、ROLE）には権限を与えたり、剥奪したりすることが可能です。たとえば、ユーザーAにロールA'を与えたとします。このA'のロールをユーザーBにも付与した場合、ユーザーAとユーザーBは同じ権限を持っている状態になります。以下の内容を確認しながら実際の動きを見ていきましょう。

ROLEの作成

ROLEは CREATE ROLE <role名> で作成することができます。ROLEの削除をする場合はDROP ROLE <role名>で削除することができます。

mysql> CREATE ROLE ro_role;
Query OK, 0 rows affected (0.07 sec)

作ったROLEはmysql.userテーブルに挿入されています。

mysql> SELECT user,host FROM mysql.user WHERE user = 'ro_role';
+---------+------+
| user    | host |
+---------+------+
| ro_role | %    |
+---------+------+
1 row in set (0.00 sec)

このROLEにはまだ権限がない状態なのでd1データベースに対してSELECT権限を与えてみます。付与されている権限の一覧は、従来の通りSHOW GRANTS構文で確認することができます。

mysql> GRANT SELECT on d1.* to ro_role;
Query OK, 0 rows affected (0.07 sec)

mysql> SHOW GRANTS FOR 'ro_role'@'%';
+-----------------------------------------+
| Grants for ro_role@%                    |
+-----------------------------------------+
| GRANT USAGE ON *.* TO `ro_role`@`%`     |
| GRANT SELECT ON `d1`.* TO `ro_role`@`%` |
+-----------------------------------------+
2 rows in set (0.04 sec)

実際にtest_userユーザーにro_roleを付与してみます。

mysql> GRANT 'ro_role' to test_user@localhost;
Query OK, 0 rows affected (0.05 sec)

mysql> SHOW GRANTS FOR test_user@localhost;
+------------------------------------------------+
| Grants for test_user@localhost                 |
+------------------------------------------------+
| GRANT USAGE ON *.* TO `test_user`@`localhost`  |
| GRANT `ro_role`@`%` TO `test_user`@`localhost` |
+------------------------------------------------+

test_userにro_roleが付与されていることが確認できました。

ここで注意していただきたいことは、CREATE ROLE構文を利用して作成したデータはmysql.userテーブルに挿入されるということです。つまりDROP ROLE構文で削除した場合は、mysql.userテーブルのデータが削除されます。mysql.userテーブルにはROLE以外にもUSERのデータが入っているため、仮にhostをワイルドカード（%）で作成しているユーザーにDROP ROLE構文を実行した場合、削除されてしまいます。

mysql> CREATE USER test_user2@'%';
Query OK, 0 rows affected (0.05 sec)

mysql> DROP ROLE test_user2;
Query OK, 0 rows affected (0.01 sec)

ROLEの動きの確認する

先ほど作った、ROLEを付与したユーザーを使ってログインしてみます。現在設定されているROLEを確認するには、CURRENT_ROLE()関数を利用します。

mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.03 sec)

上記のように、通常は付与したROLEがログイン時から利用できるわけではありません。先ほどのROLEを利用するには、SET ROLE構文を使ってROLEを付与します。複数のROLEを付与する場合はカンマ区切りで指定します。

mysql> SET ROLE ro_role;
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| `ro_role`@`%`  |
+----------------+
1 row in set (0.00 sec)

デフォルトでROLEを付与する

SET DEFAULT ROLE構文を利用することで、デフォルトのROLEを定義することができます。デフォルトのROLEとは、ユーザーがサーバーに接続、認証した際、SET ROLE DEFAUL構文を利用した時に有効となるROLEのことです。

d1データベースに書き込み権限を持ったrw_roleを作成し、先ほどのtest_userにrw_roleを与えて、デフォルトではro_roleを有効にするようにしてみます。まず、rootユーザーにてrw＿roleのROLEを付与し、ro_roleをデフォルトのROLEに設定します。

mysql> GRANT 'rw_role' to test_user@localhost;
Query OK, 0 rows affected (0.07 sec)

mysql> SHOW GRANTS FOR test_user@localhost;
+--------------------------------------------------------------+
| Grants for test_user@localhost                               |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `test_user`@`localhost`                |
| GRANT `ro_role`@`%`,`rw_role`@`%` TO `test_user`@`localhost` |
+--------------------------------------------------------------+
2 rows in set (0.04 sec)

mysql> SET DEFAULT ROLE ro_role TO test_user@localhost;
Query OK, 0 rows affected (0.02 sec)

この状態でtest_userにログインし、デフォルトで設定されているROLEを確認してみます。

テストユーザーでログイン

mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| `ro_role`@`%`  |
+----------------+
1 row in set (0.00 sec)

デフォルトでro_roleだけが付与されていることが確認できました。もし、rw_roleも有効にしたい場合は、SET ROLE構文で有効にすることができます。

mysql> SET ROLE ro_role,rw_role;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CURRENT_ROLE();
+-----------------------------+
| CURRENT_ROLE()              |
+-----------------------------+
| `ro_role`@`%`,`rw_role`@`%` |
+-----------------------------+
1 row in set (0.00 sec)

DEFAULT ROLEを何も設定しない場合はSET DEFAULT ROLE NONE、設定しているROLEを全て有効にする場合はSET DEFAULT ROLE ALLを利用して、デフォルトのROLEを設定することもできます。

SET ROLE構文

SET ROLE構文は、付与されたROLEの中からROLEを有効にする構文になります。SET ROLE の他に、DEFAULT、ALL、ALL EXCEPTの構文があります。

DEFAULTはSET DEFAULT ROLEで指定したものを有効にします。NONEは全てのROLEを無効にします。ALLは付与されているROLE全てを有効にします。ALL EXCEPT とすることで指定したROLE名以外の全てのROLEを有効にすることができます。

まとめ

今回は、MySQLのROLEの説明と作成方法、有効方法を紹介しました。ROLE続きとして次回は mandatory_rolesやactivate_all_roles_on_loginと、実際にどのように運用していくのが良いのかを検討していきたと思います。