はじめに
アクセス制御とユーザー管理は、システム内のユーザー数とさまざまなデータベースエンティティが増加するにつれて、すぐに複雑になる可能性のある 2 つの領域です。さまざまなデータベースオブジェクトに対する多数の異なる権限の管理、同じ責任を持つユーザーが同じレベルのアクセス権を持つようにすること、およびアクセス権の監査と絞り込みは、時間が経つにつれてすべて困難になります。
これを解決するために、MySQL には「ロール」と呼ばれる概念があり、特定の名前の下に権限の束をグループ化して、設定を一括して割り当ておよび変更できるようにします。このガイドでは、MySQL 内でロールがどのように機能するか、およびロールを使用してユーザーのデータアクセスを管理しやすくする方法について説明します。
コマンド
MySQL ロールの管理に関連して説明する主要な SQL コマンドを次に示します。
CREATE ROLE:CREATE ROLEコマンドは、データベースシステム内に新しいロールを定義します。DROP ROLE:DROP ROLEコマンドは、既存のロールを削除する逆の操作を行います。GRANT:GRANTコマンドには、ロールに関連する 2 つの異なる目的があります。ロールに権限を追加することと、ユーザーアカウントをロールのメンバーとして追加することです。REVOKE: ロールのコンテキストでは、REVOKEコマンドはロールから権限を削除し、ユーザーアカウントからロールメンバーシップも削除します。SHOW GRANTS:SHOW GRANTSコマンドは、指定されたユーザーアカウントまたはロールの権限を表示します。SET ROLE:SET ROLEコマンドは、ユーザーアカウントがアクティブに使用しているロールを変更します。これにより、セッションに対してどのアクション許可セットを適用するかを指示できます。SET DEFAULT ROLE:SET DEFAULT ROLEコマンドは、クライアントが特定のユーザーアカウントとしてログインしたときに自動的に適用されるロールを定義します。
必要な権限
このガイドに従うには、次の権限が必要です。
CREATE ROLEGRANT OPTIONCREATE USER（別のユーザーのデフォルトロールを設定するため）ROLE_ADMIN（ロールの動作を変更するシステム変数を設定するため）SYSTEM_VARIABLES_ADMIN（ロールの動作を変更するシステム変数を設定するため）
CREATE ROLE 権限は CREATE USER 権限の下位バージョンであり、ロールの作成と管理を可能にします。CREATE USER 権限をすでに持っているアカウントは、ロールを管理するために必要なすべての機能を自動的に備えています。
GRANT OPTION 権限は、ロールに権限を割り当てるために必要です。ロールに割り当てる権限に対して GRANT OPTION を有効にする必要があります。
ロールとは？
MySQL では、ロールは、権限のコンテナまたはコレクションとして機能するエンティティです。管理者は、ユーザーアカウントに権限を割り当てるのと同じ方法で、ロールに権限を割り当てることができます。次に、ユーザーアカウントをロールのメンバーとして追加して、それらのアカウントがロールに関連付けられた権限にアクセスできるようにすることができます。
基本的に、ロールは、権限管理を容易にするために、さまざまな関連する権限をまとめる方法として機能します。個々の権限を割り当てることによって、各ユーザーが必要とする正確なレベルのアクセス権を持っていることを確認する代わりに、名前付きの権限グループを使用すると、より少なく、理解しやすい割り当てを管理できます。
これにより、アクセスレベルを割り当てる際に明確な利点があります。developer、sysadmin、または financeteam ロールをユーザーに割り当てる方が、数十の権限を個別に管理するよりも簡単です。また、複数のアカウントへのアクセスを一度に調整するのも迅速になります。営業チーム用に新しいデータベースを作成する場合、アクセス権を持つ必要のあるすべてのアカウントを追跡する代わりに、salesteam ロールにアクセス権を付与できます。
ロールの作成
CREATE ROLE 権限を持つアカウントがある場合は、CREATE ROLE コマンドを使用してロールを管理できます。
MySQL のロールの構文とは？
ロール名が有効と見なされるためには、MySQL の特定の形式に従う必要があります。多くの点で、MySQL ユーザーアカウントを定義するために使用される形式を反映していますが、いくつかの重要な違いがあります。
ロールは次の形式に従います。
ユーザーと同様に、ロールには 2 つのコンポーネントがあります。ロール名とクライアントが接続しているホストです。ただし、MySQL がこれらのコンポーネントを解釈する方法は異なります。
ロールの場合、名前の '<role>' 部分を空白にすることはできません。ユーザーにあるような「匿名」ロールの概念はありません。一方、'<host>' 部分を省略することは依然として許可されており、MySQL はホストとして % を使用します。ただし、このコンテキストの % は、ワイルドカードではなく、リテラル文字として解釈されます。
事実上、これは、ロール名が表面上はユーザーアカウント名と同じ形式を共有しているにもかかわらず、ユーザーアカウントのように何らかの種類の評価を受けず、2 つのコンポーネントを持つラベルにすぎないことを意味します。名前が 2 つの部分に分かれている理由は、ユーザーとロールの両方として機能できるユーザーアカウントを作成できるためです。ユーザーとして使用する場合、コンポーネントはユーザー管理の記事で説明されている特別な評価ルールに従い、ロールとして使用する場合、名前はリテラルコンポーネント名を使用して直接照合されるだけです。
これらのルールにより、多くの場合、管理者は '<role>' コンポーネントのみを使用してロールを定義することを選択します。これにより、MySQL は '<host>' コンポーネントのリテラル % 文字を代用し、事実上、名前のその部分を非表示で重要でなくします。名前をユーザーアカウントとロールの両方として使用する予定がない場合は、同じようにすることができます。
ロールを作成するには？
新しいロールを作成するには、CREATE ROLE コマンドを使用します。
基本的な構文は次のようになります。
各ロール名をコンマで区切ることで、複数のロールを同時に作成することもできます。
指定したロールのいずれかがシステムにすでに存在する場合、コマンドはエラーで失敗します。
これを回避し、MySQL に警告のみを発行させるには、ロール名の前に CREATE ROLE コマンドの後に IF NOT EXISTS 句を含めることができます。
前述のように、多くの場合、管理者は簡略化のためにロール名の '<host>' 部分を省略し、暗黙的にリテラル % 文字に設定します。したがって、実際には、多くのロール作成コマンドは次のようになります。
ロールに権限を付与するには？
新しいロールを作成した後、通常、次の優先順位は、権限を付与してロールを意味のあるものにすることです。
ロールに権限を付与する方法は、ユーザーアカウントに権限を付与する方法と同じです。付与する正確な権限を指定し、権限が有効なデータベースとデータベースオブジェクトを提供してスコープを指定し、権限を付与するエンティティ（この場合はロール）を指定します。
たとえば、appdb データベースとそのすべてのオブジェクトに対する readapp という名前のロールに SELECT 権限を付与するには、次のように入力できます。
同様に、writeapp という名前のロールに同じデータベースへの書き込み権限を付与するには、次のように入力します。
ユーザーアカウントを直接操作する場合とまったく同じように、ロールに対して権限を付与および取り消すことができます。したがって、提供するアクセスレベルを調整する必要がある場合は、いつでもロールに関連付けられた権限を変更できます。
ユーザーにロールメンバーシップを付与するには？
ロールに権限を追加したら、ロールにメンバーを追加して、関連する権限を付与できます。
これを行うために、MySQL は、ユーザーとロールに権限を付与するために使用するのと同じ GRANT の別の形式を使用します。ただし、この新しい形式では、ロールをユーザーに追加し、ユーザーアカウントがロールに付与されたすべての権限にアクセスできるようにします。
基本的な構文は次のようになります。
たとえば、'reports'@'localhost' ユーザーがレポートを生成するために appdb データベースからデータを読み取ることができる必要がある場合、readapp ロールをユーザーアカウントに追加して、選択権限を付与できます。
同様に、'appuser'@'localhost' に同じデータベース内のデータを管理する機能を与えるには、そのユーザーを writeapp ロールのメンバーにすることができます。
'appuser'@'localhost' アカウントは、データベースからデータを挿入、更新、および削除する機能を持つようになります。新しい権限が writeapp ロールに追加されると、'appuser'@'localhost' アカウントはそれらの権限をすぐに取得します。
すべてのユーザーに特定のロールを自動的に付与するには？
システム上のすべてのユーザーにアクセスさせたいロールがある場合があります。各アカウントに自動的に付与されるロールは、mandatory_roles 変数を設定することで定義できます。
mandatory_roles 変数を変更するには、ユーザーに ROLE_ADMIN および SYSTEM_VARIABLES_ADMIN 権限が必要です。すべてのユーザーに付与するロールは、次のように入力することで設定できます。
ここでは、システムの各ユーザーに 3 つのロールを自動的に付与します。システム変数を設定する場合、mandatory_roles の値は文字列である必要があるため、ロールリスト全体を単一引用符で囲み、バックティックを使用して個々のロールコンポーネントを引用符で囲みます。
SYSTEM_USER 権限を持つロールを mandatory_roles リストに追加することはできません。これは、システム上のすべてのセッションが自動的にシステムセッションにならないようにするためのセキュリティ対策です。
ロールからの権限を使用するには？
ユーザーアカウントにロールメンバーシップを付与したら、どのように使用しますか？ロールによってアカウントに付与された権限にアクセスするには、アクティブ化する必要があります。
現在のアクティブなロールの表示
新しいロールをアクティブ化する前に、ユーザーセッションに対して現在アクティブになっているロールを確認できます。
セッションのアクティブなロールを表示するには、次のように入力します。
出力には、現在のセッションでアクティブになっている 0 個以上のロールが表示されます。これらのロールに関連付けられた権限は、実行できるアクションに追加されます。
セッションのロールをアクティブ化する方法
セッション中にアクティブにするロールを変更するには、SET ROLE コマンドを使用します。このコマンドは、さまざまな方法で使用できます。
基本的な構文は次のようになります。
これにより、問題のロールがアクティブになります。SET ROLE コマンドで言及されていない以前にアクティブだったロールは、現在非アクティブになることに注意することが重要です。
複数のロールを一度にアクティブ化するには、各ロールをコンマで区切ります。
アカウントに付与されたすべてのロールをアクティブ化するには、特定のロールの代わりに ALL を指定できます。
ALL EXCEPT を使用して、特定の例外を除いてすべてのロールをアクティブ化するように MySQL に指示することもできます。
別のアプションは、NONE を指定してアカウント上のすべてのロールを無効にすることです。
これにより、セッションのすべてのユーザーロールが非アクティブになり、ユーザーアカウントに具体的に割り当てられた権限のみが付与されます。
アカウントに定義されたデフォルトのロールリストに戻るには、DEFAULT キーワードを使用します。
ユーザーアカウントのデフォルトロールを定義する方法
ユーザーとしてログインしたときに自動的にアクティブ化されるロールと、SET ROLE DEFAULT を使用したときに再アクティブ化されるロールは構成可能です。
デフォルトでアクティブ化されるロールを定義するには、SET ROLE コマンドを使用する方法と同様に、SET DEFAULT ROLE コマンドを使用します。
これにより、ログイン時または SET ROLE DEFAULT を使用したときに、自分自身のアカウントに対してアクティブ化されるデフォルトロールが設定されます。
ユーザーが CREATE USER 権限を持っている場合、他のアカウントのデフォルトロールを設定できます。
ここでは、'<user>'@'<host>' アカウントが認証時にすべてのロールを自動的にアクティブ化するように指定します。
この構文は、各ユーザーをコンマで区切ることで、複数のアカウントのデフォルトロールを定義するためにも使用できます。
すべてのユーザーのすべてのロールをデフォルトでアクティブ化する
MySQL サーバー上のすべてのアカウントがデフォルトですべてのロールをアクティブ化するようにする場合は、システム設定を変更してそれを行うことができます。
activate_all_roles_on_login 変数が true に設定されている場合、MySQL はログイン時にアカウントに関連付けられたすべてのロールを自動的にアクティブ化します。これは、SET DEFAULT ROLE で指定された設定よりも優先されます。
この機能を有効にするには、SYSTEM_VARIABLES_ADMIN および ROLE_ADMIN 権限が必要です。次のように入力して機能を有効にします。
これにより、ユーザーアカウントはログイン時にすべてのロールを自動的にアクティブ化するようになります。ただし、SET ROLE DEFAULT を使用すると、アカウントに関連付けられたデフォルトロールのみをアクティブ化できます。
ロールから取得した既存の権限を表示する
アカウントで利用できる権限を理解するには、SHOW GRANTS コマンドを使用できます。
ユーザーに有効になっている権限を確認するには、次のように入力します。
出力には、ユーザーアカウントに直接割り当てられたすべての権限と、ユーザーがメンバーとなっているすべてのロールが表示されます。
アカウントがメンバーとなっているロールを学習した後、次のコマンドを入力して、そのロールがユーザーに提供する権限を確認できます。
たとえば、'reports'@'localhost' ユーザーの権限を、readapp ロールでのメンバーシップによって付与された権限も含めて確認するには、次のように使用できます。
これにより、'reports'@'localhost' ユーザーアカウントに明示的に付与されたすべての権限と、readapp ロールによって追加された権限がすべて表示されます。
ユーザーからロールを取り消す
ユーザーからロールを削除したい場合はどうなるでしょうか？ GRANT コマンドがユーザーまたはロールに新しい権限を追加したり、ユーザーにロールを追加したりできるのと同様に、REVOKE コマンドは、ユーザーまたはロールから権限を削除したり、ユーザーからロールメンバーシップを削除したりできます。
ユーザーアカウントからロールを削除するために使用される基本的な構文は次のとおりです。
このようなステートメントを実行すると、ユーザーはロールを通じて付与された権限にアクセスできなくなります。
例として、writeapp ロールを 'appuser'@'localhost' ユーザーアカウントから取り消すには、次のように入力します。
ただし、ユーザーが他の手段（直接付与された、または別のロールのメンバーシップを通じて付与された）を通じて権限を付与されている場合、その権限には引き続きアクセスできます。したがって、'appuser'@'localhost' ユーザーが以前に付与した readapp ロールのメンバーでもある場合、appdb データベースに対する SELECT 権限は引き続き保持します。
結論
MySQL データベースでロールを使用して権限を配布すると、アクセス制御システムの管理オーバーヘッドと複雑さを簡素化できます。ロールを使用すると、同じ責任を持つユーザーが同じ権限を持つようにすることが、多数の異なる権限を直接付与するよりもはるかに簡単になります。
同様に、ロールを使用すると、権限付与の背後にある意図を明確にすることができます。コメントなしで多数の権限をアカウントに付与するのではなく、慎重に選択されたロール名は、アクセスのさまざまな理由を区別するのに役立ちます。事前に時間をかけてロールを作成および整理することで、さまざまなデータ部分へのユーザーアクセスを管理する能力が長期的にはより簡単になります。
FAQ
サーバーに権限テーブルを再読み込みするように指示する flush-privileges 操作を実行するには、FLUSH PRIVILEGES ステートメントを発行できます。
これは、mysqladmin flush-privileges または mysqladmin reload コマンドを実行することでも実行できます。
特定のスコープでユーザーにすべての権限を割り当てるには、ALL または ALL PRIVILEGES の省略形を使用できます。
次の構文は、ユーザーが 'salesadmin'@'localhost' ユーザーに対して実行可能なすべての権限を sales データベースに関連付けて付与します。
ユーザーにグローバルに読み取り専用権限を付与するには、スコープコンポーネントのデータベースとデータベースオブジェクトの両方の部分にワイルドカードを使用できます。
基本的な構文は次のようになります。
読み取り専用権限のスコープを単一のデータベースに制限するには、ドットの左側のワイルドカードをデータベース名に置き換えます。
同様に、データベース内の特定のテーブルへのアクセスのみを許可するには、次を使用します。
MySQL で新しい root またはスーパーユーザーアカウントを作成するには、GRANT ALL PRIVILEGES ステートメントを使用して、データベース内のすべてに対する完全な root アクセス権を付与する必要があります。
基本的な構文は次のようになります。
アカウントで利用できる権限を理解するには、SHOW GRANTS コマンドを使用できます。
ユーザーに有効になっている権限を確認するには、次のように入力します。
アカウントがメンバーとなっているロールを学習した後、次のコマンドを入力して、そのロールがユーザーに提供する権限を確認できます。
