6.2.10 ロールの使用

MySQL ロールは、権限の名前付きコレクションです。ユーザーアカウントと同様に、ロールには付与された権限と取り消された権限があります。

ユーザーアカウントには、各ロールに関連付けられた権限をアカウントに付与するロールを付与できます。これにより、アカウントへの一連の権限の割当てが可能になり、必要な権限割当てを概念化して実装するために、個々の権限を付与するかわりに便利です。

次のリストに、MySQL が提供するロール管理機能の概要を示します:

CREATE ROLE および DROP ROLE は、ロールを作成および削除します。
GRANT および REVOKE は、ユーザーアカウントおよびロールから権限を取り消す権限を割り当てます。
SHOW GRANTS には、ユーザーアカウントおよびロールの権限およびロールの割当てが表示されます。
SET DEFAULT ROLE では、デフォルトでアクティブなアカウントロールを指定します。
SET ROLE は、現在のセッション内のアクティブなロールを変更します。
CURRENT_ROLE() 関数は、現在のセッション内のアクティブなロールを表示します。
mandatory_roles および activate_all_roles_on_login システム変数を使用すると、ユーザーがサーバーにログインするときに、必須ロールを定義し、付与されたロールを自動的にアクティブ化できます。

個々のロール操作ステートメント (それらの使用に必要な権限を含む) の詳細は、セクション13.7.1「アカウント管理ステートメント」を参照してください。次の説明では、ロールの使用例を示します。特に指定がないかぎり、ここに示す SQL ステートメントは、root アカウントなどの十分な管理権限を持つ MySQL アカウントを使用して実行する必要があります。

ロールの作成およびロールへの権限の付与

次のシナリオを考えてみます:

アプリケーションは、app_db という名前のデータベースを使用します。
アプリケーションに関連付けられている場合、アプリケーションを作成および保守する開発者、およびアプリケーションと対話するユーザーのアカウントが存在する可能性があります。
開発者には、データベースへの完全なアクセス権が必要です。一部のユーザーには読取りアクセスのみが必要で、その他のユーザーには読取り/書込みアクセスが必要です。

多くのユーザーアカウントに権限を個別に付与しないようにするには、必要な権限セットの名前としてロールを作成します。これにより、適切なロールを付与することで、必要な権限をユーザーアカウントに簡単に付与できます。

ロールを作成するには、CREATE ROLE ステートメントを使用します:

CREATE ROLE 'app_developer', 'app_read', 'app_write';

ロール名はユーザーアカウント名とよく似ており、'user_name'@'host_name'形式のユーザー部分とホスト部分で構成されます。ホスト部分を省略すると、デフォルトで'%'に設定されます。ユーザー部分とホスト部分は、- や % などの特殊文字が含まれていないかぎり、引用符で囲むことはできません。アカウント名とは異なり、ロール名のユーザー部分は空白にできません。追加情報についてはセクション6.2.5「ロール名の指定」を参照してください。

ロールに権限を割り当てるには、ユーザーアカウントに権限を割り当てる場合と同じ構文を使用して GRANT ステートメントを実行します:

GRANT ALL ON app_db.* TO 'app_developer';
GRANT SELECT ON app_db.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';

最初に、1 つの開発者アカウント、読取り専用アクセスを必要とする 2 つのユーザーアカウント、および読取り/書込みアクセスを必要とする 1 つのユーザーアカウントが必要であるとします。 CREATE USER を使用してアカウントを作成します:

CREATE USER 'dev1'@'localhost' IDENTIFIED BY 'dev1pass';
CREATE USER 'read_user1'@'localhost' IDENTIFIED BY 'read_user1pass';
CREATE USER 'read_user2'@'localhost' IDENTIFIED BY 'read_user2pass';
CREATE USER 'rw_user1'@'localhost' IDENTIFIED BY 'rw_user1pass';

各ユーザーアカウントに必要な権限を割り当てるには、前述と同じ形式の GRANT ステートメントを使用できますが、ユーザーごとに個別の権限を列挙する必要があります。かわりに、権限ではなくロールの付与を許可する代替の GRANT 構文を使用します:

GRANT 'app_developer' TO 'dev1'@'localhost';
GRANT 'app_read' TO 'read_user1'@'localhost', 'read_user2'@'localhost';
GRANT 'app_read', 'app_write' TO 'rw_user1'@'localhost';

rw_user1 アカウントの GRANT ステートメントは、読取りおよび書込みロールを付与します。このロールを組み合せて、必要な読取りおよび書込み権限を提供します。

アカウントにロールを付与するための GRANT 構文は、権限を付与するための構文とは異なります: 権限を割り当てる ON 句はありますが、ロールを割り当てる ON 句はありません。構文は異なるため、同じステートメントで権限とロールの割当てを混在させることはできません。 (権限とロールの両方をアカウントに割り当てることはできますが、付与する内容に適した構文を持つ個別の GRANT ステートメントを使用する必要があります。) MySQL 8.0.16 では、匿名ユーザーにロールを付与できません。

作成されたロールはロックされ、パスワードがなく、デフォルトの認証プラグインが割り当てられます。 (これらのロール属性は、後で ALTER USER ステートメントを使用して、グローバル CREATE USER 権限を持つユーザーが変更できます。)

ロック中は、ロールを使用してサーバーに対する認証を行うことはできません。ロックが解除されている場合は、ロールを使用して認証できます。これは、ロールとユーザーの両方が認可識別子であり、それらを区別することはほとんどなく共通であるためです。ユーザーとロールの互換性も参照してください。

必須ロールの定義

mandatory_roles システム変数の値でロールに名前を付けることで、ロールを必須として指定できます。サーバーは必須ロールをすべてのユーザーに付与されたものとして処理するため、アカウントに明示的に付与する必要はありません。

サーバーの起動時に必須ロールを指定するには、サーバーの my.cnf ファイルで mandatory_roles を定義します:

[mysqld]
mandatory_roles='role1,role2@localhost,r3@%.example.com'

実行時に mandatory_roles を設定および永続化するには、次のようなステートメントを使用します:

SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com';

SET PERSIST は、実行中の MySQL インスタンスの値を設定します。また、値が保存され、その後のサーバーの再起動に引き継がれます。後続の再起動に引き継ぐことなく、実行中の MySQL インスタンスの値を変更するには、PERSIST ではなく GLOBAL キーワードを使用します。セクション13.7.6.1「変数代入の SET 構文」を参照してください。

mandatory_roles を設定するには、グローバルシステム変数を設定するために通常必要な SYSTEM_VARIABLES_ADMIN 権限 (または非推奨の SUPER 権限) に加えて、ROLE_ADMIN 権限が必要です。

明示的に付与されたロールと同様に、必須ロールはアクティブ化されるまで有効になりません (ロールのアクティブ化を参照)。ログイン時に、activate_all_roles_on_login システム変数が有効になっている場合は付与されているすべてのロールに対して、それ以外の場合はデフォルトロールとして設定されているロールに対してロールのアクティブ化が行われます。実行時に、SET ROLE によってロールがアクティブ化されます。

mandatory_roles の値で指定されたロールは、REVOKE で取り消すことも、DROP ROLE または DROP USER で削除することもできません。

セッションがデフォルトでシステムセッションにならないようにするには、SYSTEM_USER 権限を持つロールを mandatory_roles システム変数の値にリストできません:

SYSTEM_USER 権限を持つロールが起動時に mandatory_roles に割り当てられた場合、サーバーはエラーログにメッセージを書き込み、終了します。
SYSTEM_USER 権限を持つロールが実行時に mandatory_roles に割り当てられた場合、エラーが発生し、mandatory_roles 値は変更されません。

mandatory_roles で指定されたロールが mysql.user システムテーブルに存在しない場合、そのロールはユーザーに付与されません。サーバーは、ユーザーに対してロールのアクティブ化を試行しても、存在しないロールを必須として処理せず、エラーログに警告を書き込みます。ロールが後で作成されて有効になった場合は、FLUSH PRIVILEGES でサーバーに必須として処理させる必要がある場合があります。

SHOW GRANTS では、セクション13.7.7.21「SHOW GRANTS ステートメント」で説明されているルールに従って必須ロールが表示されます。

ロール権限の確認

アカウントに割り当てられた権限を確認するには、SHOW GRANTS を使用します。例:

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

ただし、「「展開」」なしで付与された各ロールは、そのロールが表す権限に表示されます。ロール権限も表示するには、権限を表示する付与されたロールを指定する USING 句を追加します:

mysql> SHOW GRANTS FOR 'dev1'@'localhost' USING 'app_developer';
+----------------------------------------------------------+
| Grants for dev1@localhost                                |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `dev1`@`localhost`                 |
| GRANT ALL PRIVILEGES ON `app_db`.* TO `dev1`@`localhost` |
| GRANT `app_developer`@`%` TO `dev1`@`localhost`          |
+----------------------------------------------------------+

他のタイプのユーザーを同様に検証します:

mysql> SHOW GRANTS FOR 'read_user1'@'localhost' USING 'app_read';
+--------------------------------------------------------+
| Grants for read_user1@localhost                        |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `read_user1`@`localhost`         |
| GRANT SELECT ON `app_db`.* TO `read_user1`@`localhost` |
| GRANT `app_read`@`%` TO `read_user1`@`localhost`       |
+--------------------------------------------------------+
mysql> SHOW GRANTS FOR 'rw_user1'@'localhost' USING 'app_read', 'app_write';
+------------------------------------------------------------------------------+
| Grants for rw_user1@localhost                                                |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `rw_user1`@`localhost`                                 |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `app_db`.* TO `rw_user1`@`localhost` |
| GRANT `app_read`@`%`,`app_write`@`%` TO `rw_user1`@`localhost`               |
+------------------------------------------------------------------------------+

SHOW GRANTS では、セクション13.7.7.21「SHOW GRANTS ステートメント」で説明されているルールに従って必須ロールが表示されます。

ロールのアクティブ化

ユーザーアカウントに付与されるロールは、アカウントセッション内でアクティブまたは非アクティブにできます。付与されたロールがセッション内でアクティブな場合は、その権限が適用されます。それ以外の場合は適用されません。現在のセッション内でアクティブなロールを判別するには、CURRENT_ROLE() 関数を使用します。

デフォルトでは、アカウントにロールを付与したり、mandatory_roles システム変数値に名前を付けたりしても、アカウントセッション内でロールが自動的にアクティブになることはありません。たとえば、前述の説明では rw_user1 ロールがアクティブ化されていないため、rw_user1 としてサーバーに接続し、CURRENT_ROLE() 関数を起動すると、結果は NONE (アクティブなロールなし) になります:

mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+

ユーザーがサーバーに接続して認証するたびにアクティブにするロールを指定するには、SET DEFAULT ROLE を使用します。以前に作成した各アカウントに割り当てられたすべてのロールにデフォルトを設定するには、次のステートメントを使用します:

SET DEFAULT ROLE ALL TO
  'dev1'@'localhost',
  'read_user1'@'localhost',
  'read_user2'@'localhost',
  'rw_user1'@'localhost';

rw_user1 として接続すると、CURRENT_ROLE() の初期値に新しいデフォルトのロール割当てが反映されます:

mysql> SELECT CURRENT_ROLE();
+--------------------------------+
| CURRENT_ROLE()                 |
+--------------------------------+
| `app_read`@`%`,`app_write`@`%` |
+--------------------------------+

ユーザーがサーバーに接続したときに、明示的に付与されたロールと必須ロールがすべて自動的にアクティブ化されるようにするには、activate_all_roles_on_login システム変数を有効にします。デフォルトでは、自動ロールアクティブ化は無効になっています。

セッション内で、ユーザーは SET ROLE を実行してアクティブなロールのセットを変更できます。たとえば、rw_user1 の場合は次のようになります:

mysql> SET ROLE NONE; SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+
mysql> SET ROLE ALL EXCEPT 'app_write'; SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| `app_read`@`%` |
+----------------+
mysql> SET ROLE DEFAULT; SELECT CURRENT_ROLE();
+--------------------------------+
| CURRENT_ROLE()                 |
+--------------------------------+
| `app_read`@`%`,`app_write`@`%` |
+--------------------------------+

最初の SET ROLE ステートメントは、すべてのロールを非アクティブ化します。次に、rw_user1 を事実上読取り専用にします。 3 つ目はデフォルトのロールをリストアします。

ストアドプログラムおよびビューオブジェクトの有効なユーザーは、DEFINER および SQL SECURITY 属性の影響を受けます。これらの属性は、実行者コンテキストまたは定義者コンテキストのどちらで実行されるかを決定します (セクション25.6「ストアドオブジェクトのアクセス制御」を参照):

起動側コンテキストで実行されるストアドプログラムおよびビューオブジェクトは、現在のセッション内でアクティブなロールで実行されます。
定義者コンテキストで実行されるストアドプログラムおよびビューオブジェクトは、DEFINER 属性で指定されたユーザーのデフォルトロールで実行されます。 activate_all_roles_on_login が有効な場合、このようなオブジェクトは、DEFINER ユーザーに付与されたすべてのロール (必須ロールを含む) で実行されます。ストアドプログラムでは、デフォルトとは異なるロールで実行する必要がある場合、プログラム本体は SET ROLE を実行して必要なロールをアクティブ化する必要があります。

ロールまたはロール権限の取消し

ロールは、アカウントに付与できるのと同様に、アカウントから取り消すことができます:

REVOKE role FROM user;

mandatory_roles システム変数値で指定されたロールは取り消すことができません。

REVOKE をロールに適用して、付与された権限を変更することもできます。これは、ロール自体だけでなく、そのロールを付与されたアカウントにも影響します。すべてのアプリケーションユーザーを一時的に読取り専用にするとします。これを行うには、REVOKE を使用して、app_write ロールから変更権限を取り消します:

REVOKE INSERT, UPDATE, DELETE ON app_db.* FROM 'app_write';

これが発生すると、SHOW GRANTS を使用して表示できるように、権限のないロールのままになります (これは、このステートメントをユーザーだけでなくロールで使用できることを示しています):

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

ロールから権限を取り消すと、変更されたロールが割り当てられているユーザーの権限に影響するため、rw_user1 にはテーブルの変更権限がなくなりました (INSERT、UPDATE および DELETE は存在しません):

mysql> SHOW GRANTS FOR 'rw_user1'@'localhost'
       USING 'app_read', 'app_write';
+----------------------------------------------------------------+
| Grants for rw_user1@localhost                                  |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `rw_user1`@`localhost`                   |
| GRANT SELECT ON `app_db`.* TO `rw_user1`@`localhost`           |
| GRANT `app_read`@`%`,`app_write`@`%` TO `rw_user1`@`localhost` |
+----------------------------------------------------------------+

実際には、rw_user1 の読取り/書込みユーザーが読取り専用ユーザーになりました。これは、app_write ロールが付与されている他のアカウントに対しても発生し、ロールの使用によって個々のアカウントの権限を変更する必要がなくなる方法を示します。

変更権限をロールにリストアするには、変更権限を再度付与します:

GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';

app_write ロールを付与された他のアカウントと同様に、rw_user1 にも変更権限があります。

ロールの削除

ロールを削除するには、DROP ROLE を使用します:

DROP ROLE 'app_read', 'app_write';

ロールを削除すると、そのロールが付与されたすべてのアカウントからロールが取り消されます。

mandatory_roles システム変数値で指定されたロールは削除できません。

ユーザーとロールの互換性

以前に SHOW GRANTS についてヒントされていたように、ユーザーアカウントまたはロールに対する権限が表示されるため、アカウントおよびロールは同じ意味で使用できます。

ロールとユーザーの違いの 1 つは、CREATE ROLE ではデフォルトでロックされる認可識別子が作成されるのに対し、CREATE USER ではデフォルトでロック解除される認可識別子が作成されることです。ただし、適切な権限を持つユーザーは、作成後にロールまたはユーザーをロックまたはロック解除できるため、区別は不変ではありません。

データベース管理者が、特定の認可識別子をロールにする必要があるというプリファレンスを持っている場合は、名前スキームを使用してこの意図を伝達できます。たとえば、ロールにするすべての認可識別子に r_接頭辞を使用でき、それ以外には使用できません。

ロールとユーザーの別の違いは、それらの管理に使用できる権限にあります:

CREATE ROLE および DROP ROLE 権限では、それぞれ CREATE ROLE および DROP ROLE ステートメントのみを使用できます。
CREATE USER 権限を使用すると、ALTER USER, CREATE ROLE, CREATE USER, DROP ROLE, DROP USER, RENAME USER ステートメントおよび REVOKE ALL PRIVILEGES ステートメントを使用できます。

したがって、CREATE ROLE および DROP ROLE 権限は CREATE USER ほど強力ではなく、ロールの作成および削除のみを許可し、より一般的なアカウント操作を実行しないユーザーに付与できます。

ユーザーおよびロールの権限および互換性に関して、ユーザーアカウントをロールのように扱い、そのアカウントを別のユーザーまたはロールに付与できます。その結果、他のユーザーまたはロールにアカウント権限およびロールが付与されます。

次の一連のステートメントは、ユーザーにユーザー、ユーザーにロール、ロールにユーザーまたはロールを付与できることを示しています:

CREATE USER 'u1';
CREATE ROLE 'r1';
GRANT SELECT ON db1.* TO 'u1';
GRANT SELECT ON db2.* TO 'r1';
CREATE USER 'u2';
CREATE ROLE 'r2';
GRANT 'u1', 'r1' TO 'u2';
GRANT 'u1', 'r1' TO 'r2';

いずれの場合も、付与されたオブジェクトに関連付けられた権限が権限受領者オブジェクトに付与されます。これらのステートメントを実行すると、ユーザー (u1) およびロール (r1) から各 u2 および r2 に権限が付与されます:

mysql> SHOW GRANTS FOR 'u2' USING 'u1', 'r1';
+-------------------------------------+
| Grants for u2@%                     |
+-------------------------------------+
| GRANT USAGE ON *.* TO `u2`@`%`      |
| GRANT SELECT ON `db1`.* TO `u2`@`%` |
| GRANT SELECT ON `db2`.* TO `u2`@`%` |
| GRANT `u1`@`%`,`r1`@`%` TO `u2`@`%` |
+-------------------------------------+
mysql> SHOW GRANTS FOR 'r2' USING 'u1', 'r1';
+-------------------------------------+
| Grants for r2@%                     |
+-------------------------------------+
| GRANT USAGE ON *.* TO `r2`@`%`      |
| GRANT SELECT ON `db1`.* TO `r2`@`%` |
| GRANT SELECT ON `db2`.* TO `r2`@`%` |
| GRANT `u1`@`%`,`r1`@`%` TO `r2`@`%` |
+-------------------------------------+

前述の例は単なる説明ですが、ユーザーアカウントとロールの互換性には、次のような実用的なアプリケーションがあります: レガシーアプリケーション開発プロジェクトが MySQL でロールが追加される前に開始されたため、プロジェクトに関連付けられているすべてのユーザーアカウントに権限が直接付与されているとします (ロールの付与によって権限が付与されるのではなく)。これらのアカウントの 1 つは、次のように最初に権限を付与された開発者アカウントです:

CREATE USER 'old_app_dev'@'localhost' IDENTIFIED BY 'old_app_devpass';
GRANT ALL ON old_app.* TO 'old_app_dev'@'localhost';

この開発者がプロジェクトを終了した場合は、権限を別のユーザーに割り当てるか、開発アクティビティが展開されている場合は複数のユーザーに割り当てる必要があります。次に、この問題を処理する方法をいくつか示します:

ロールを使用しない: 元の開発者が使用できないようにアカウントパスワードを変更し、かわりに新しい開発者がそのアカウントを使用するようにします:
```
ALTER USER 'old_app_dev'@'localhost' IDENTIFIED BY 'new_password';
```
ロールの使用: アカウントをロックして、誰もがそのアカウントを使用してサーバーに接続できないようにします:
```
ALTER USER 'old_app_dev'@'localhost' ACCOUNT LOCK;
```
次に、アカウントをロールとして扱います。プロジェクトの新規開発者ごとに、新規アカウントを作成し、元の開発者アカウントを付与します:
```
CREATE USER 'new_app_dev1'@'localhost' IDENTIFIED BY 'new_password';
GRANT 'old_app_dev'@'localhost' TO 'new_app_dev1'@'localhost';
```
その結果、元の開発者アカウント権限が新しいアカウントに割り当てられます。