はじめに
アクセスコントロールとユーザー管理は、システム内のユーザー数や異なるデータベースエンティティが増えるにつれて、急速に複雑になる可能性のある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リストに追加することはできません。これは、システム上のすべてのセッションが自動的にシステムセッションになることを防ぐためのセキュリティ対策です。
ロールから権限を使用するには？
ユーザーアカウントにロールのメンバーシップを付与したら、どのようにそれらを使用しますか？ロールによってアカウントに付与された権限にアクセスするには、それをアクティブ化する必要があります。
現在アクティブなロールを表示する
新しいロールをアクティブ化する前に、現在のユーザーセッションでどのロールがアクティブになっているかを確認できます。
セッションのアクティブなロールを表示するには、次のように入力します
出力には、現在のセッションでアクティブな1つ以上のロールが表示されます。これらのロールに関連付けられた権限は、実行が許可されているアクションに追加されます。
セッションのロールをアクティブ化する方法
セッション中にアクティブなロールを変更するには、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コマンドはユーザーまたはロールから権限を削除したり、ユーザーからロールのメンバーシップを削除したりできます。
ユーザーアカウントからロールを削除するための基本的な構文は次のようになります
このようなステートメントを実行すると、ユーザーはロールを通じて付与された権限にアクセスできなくなります。
例として、'appuser'@'localhost'ユーザーアカウントからwriteappロールを取り消すには、次のように入力します
ただし、ユーザーが他の方法（直接付与されたか、異なるロールのメンバーシップを通じて付与されたか）で権限を付与されている場合、その権限には引き続きアクセスできます。したがって、もし'appuser'@'localhost'ユーザーが以前に付与したreadappロールのメンバーでもあった場合、彼らはappdbデータベースに対して依然としてSELECT権限を持つことになります。
結論
MySQLデータベースでロールを使用して権限を配布することは、アクセスコントロールシステムの管理オーバーヘッドと複雑さを簡素化するのに役立ちます。同じ責任を持つユーザーが同じ権限を持つことを保証することは、多数の異なる権限を直接付与するよりも、ロールを使用する方がはるかに簡単です。
同様に、ロールを使用すると、権限付与の意図を明確にすることができます。コメントなしでアカウントに大量の権限を付与するのではなく、慎重に選択されたロール名によって、アクセスするさまざまな理由を区別するのに役立ちます。事前に時間をかけてロールを作成し整理することで、データのさまざまな部分へのユーザーアクセスを管理する能力が長期的にはより簡単になります。
よくある質問
権限フラッシュ操作を実行し、サーバーに許可テーブルをリロードさせるには、FLUSH PRIVILEGESステートメントを発行します。
これは、mysqladmin flush-privilegesまたはmysqladmin reloadコマンドを実行することでも行えます。
特定のスコープでユーザーに全権限を割り当てるには、ALLまたはALL PRIVILEGESの省略形を使用できます。
以下の構文は、salesデータベースに関連する、ユーザーが実行できるすべての権限を'salesadmin'@'localhost'ユーザーに付与します。
ユーザーにグローバルな読み取り専用権限を付与するには、スコープコンポーネントのデータベース部分とデータベースオブジェクト部分の両方にワイルドカードを使用できます。
基本的な構文は次のようになります
読み取り専用の付与のスコープを単一のデータベースに限定するには、ドットの左側のワイルドカードをデータベース名に置き換えます
同様に、データベース内の特定のテーブルにのみアクセスを許可するには、以下を使用します
MySQLで新しいルート、またはスーパーユーザーアカウントを作成するには、GRANT ALL PRIVILEGESステートメントを使用して、データベース内のすべてに対する完全なルートアクセスを付与する必要があります。
基本的な構文は次のようになります
アカウントで利用可能な権限を理解するには、SHOW GRANTSコマンドを使用できます。
ユーザーに対して有効になっている権限を確認するには、次のように入力します
アカウントがどのロールのメンバーであるかを知った後、そのロールがユーザーに提供する権限を次のように入力して確認できます
