第104回　MySQLのROLE［その2］

第102回ではMySQL 8.0の新機能のROLEについて、ROLEの概要や作成方法を確認していきました。今回は、ROLEのオプションであるmandatory_rolesやactivate_all_roles_on_loginと、運用時にできるようになることを紹介します。

mandatory_roles

mandatory_rolesは文字通り、必須とするROLEのことです。このmandatory_rolesを設定することで、全ユーザーに指定したROLEが付与されます。mandatory_rolesはmy.cnfにて設定するか、SET GLOBALまたはSET PERSISTを用いて設定することができます。複数のROLEを設定する場合はカンマ区切りで指定します。

mysql> SET PERSIST mandatory_roles = 'ro_role';
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'mandatory_roles';
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| mandatory_roles | ro_role |
+-----------------+---------+
1 row in set (0.05 sec)

またmandatory_rolesに設定されているROLEは削除したり、ユーザーから権限を剥奪することはできません。

mysql> revoke ro_role from 'test_user'@'localhost';
ERROR 3628 (HY000): The role `ro_role`@`%` is a mandatory role and can't be revoked or dropped. The restriction can be lifted by excluding the role identifier from the global variable mandatory_roles.

activate_all_roles_on_login

activate_all_roles_on_loginは、ユーザーがログインしたときに付与されているROLEをすべて自動で有効にするオプションです。デフォルトではOFFになっており、有効にする場合はmy.cnfに記述する、またはSET構文を用いて有効にする必要があります。

もし、個別のユーザーにSET DEFAULT ROLE構文を用いてデフォルトのROLEを指定している場合でも、activate_all_roles_on_loginがONの場合は優先的に設定されます。activate_all_roles_on_loginで有効にするROLEの対象は、GRANT構文を用いて付与したROLEとmandatory_rolesで定義されているROLEが対象となります。設定値の確認はSHOW VARIABLES構文を用いて確認することができます。

mysql> show variables like 'activate_all_roles_on_login';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | ON    |
+-----------------------------+-------+
1 row in set (0.01 sec)

ROLEまわりの実行権限

MySQL 8.0.17現在ではROLEに関する権限はCREATE ROLE、DROP ROLE、ROLE_ADMINの3つが存在します。それぞれの権限は以下の通りです。

権限名	内容
CREATE ROLE	CREATE ROLE構文でROLEを作成する権限
DROP ROLE	DROP ROLE構文でROLEを削除する権限
ROLE_ADMIN	GRANT構文によるROLEの付与、取り消しとmandatory_rolesの設定権限

もし、権限がない状態でROLEを作成しようとすると下記のようなエラーになります。

mysql> CREATE ROLE rw_role;
ERROR 1396 (HY000): Operation CREATE ROLE failed for 'rw_role'@'%'

ただし、mysqlデータベースに書き込み権限がある場合は直接role_edgesテーブルを書き込めてしまうので、注意が必要です。もし、ROLE関連の実行権限を付与しない場合は、あわせてmysqlデータベースのROLE関連のテーブルにも読み書きできない状態であることを確認しましょう。

ROLEの運用

ROLEを運用していくうえでできるようになることを確認していきます。

新たなユーザーへのROLEの付与

MySQL 5.7ではMySQLでは新しいユーザーを作成するときはCREATE USER構文でユーザーを作成し、GRANT構文で権限を付与するという運用をしていたと思います。

MySQL 8.0では、CREATE USER構文でDEFAULT ROLEを指定することができるようになりました。そのため、ユーザー作成時に必要なROLEを指定してユーザー作成時にROLEも付与することが可能になりました。

mysql> CREATE USER test_user@'%' IDENTIFIED BY 'test_user' DEFAULT ROLE rw_role;
Query OK, 0 rows affected (0.00 sec)

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

ROLEの継承について

ROLEに別のROLEを付与することで、付与したROLEを継承することができます。これを用いることでスキーマ単位や読み書き単位でROLEを作成し、組み合わせて利用することが可能です。ただし、細かく分けすぎてしまうとROLEそのものの数が多くなってしまい管理が大変になってしまいます。ROLEを導入する際は、どのような運用方法にしていくかを十分に検討する必要があります。

defaultのROLEの確認

接続したユーザーがデフォルトでどのようなROLEが付与されるかは、mysqlデータベースのdefault_rolesで確認することができます。もし、各ユーザーデフォルトで設定されるROLEを確認したい場合は、default_rolesテーブルを見ましょう。

mysql> SELECT * FROM default_roles;
+-----------+-----------+-------------------+-------------------+
| HOST      | USER      | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
+-----------+-----------+-------------------+-------------------+
| %         | test_user | %                 | rw_role           |
| 127.0.0.1 | test_user | %                 | rw_role           |
+-----------+-----------+-------------------+-------------------+
2 rows in set (0.01 sec)

まとめ

第102回、第104回で、MySQL 8.0で新たに加わったROLEの機能説明と運用時にできるようになることを紹介しました。ROLEは権限を管理する上で大変便利である反面、数が多いと管理が大変になってしまう恐れがあります。導入を検討する際は、運用方法を決定して導入していきましょう。