メニュー
Prismaを探索する

MySQL / 認証と認可

MySQLで`GRANT`と`REVOKE`を使用して権限を管理する方法

共有

はじめに

権限管理は、システムおよびデータベース管理の重要な部分です。どのコンポーネントに誰がどのようなアクセス権を持つべきか、どのような権限を持つべきかを決定し、それらのポリシーを有効にする実装を設計するには、かなりの考察と注意が必要です。

MySQLには、データベースシステム全体にアクセスポリシーを実装できる堅牢な権限割り当てシステムがあります。このガイドでは、MySQLユーザーアカウントへの権限の追加と削除、および要件に合致するアクセスポリシーの実装に、GRANTとREVOKEコマンドをどのように使用するかについて説明します。

前提条件

このガイドに沿って進めるには、適切な権限を持つMySQLサーバー上のアカウントが必要です。

使用するコマンド

このガイドで使用する最も重要なコマンドは、GRANTとREVOKEコマンドです。

  • GRANT: ユーザーアカウントに新しい権限を割り当てるために使用します
  • REVOKE: ユーザーアカウントから既存の権限を削除するために使用します

必要な権限

MySQLユーザーの権限を管理するには、以下の権限が必要です。

  • GRANT OPTION: GRANT OPTION権限を使用すると、付与されている任意の権限を付与または剥奪できます。
  • 他のユーザーに割り当てたい任意の権限
  • mysql.*に対するSELECT: 他のアカウントに対してSHOW GRANTSを実行するために使用します

このガイドに沿って進めるために、完全な管理者権限（GRANT OPTION権限を含む）を持つアカウントを使用していることを前提とします。これは、インストール中に設定される一般的な'root'@'localhost'ユーザー、または他の完全な権限を持つ任意のユーザーである可能性があります。

MySQLの権限はどのように機能しますか？

MySQLでは、権限システムによってユーザーが特定のコマンドを実行できるかどうかが決定されます。

クライアントがアクションを実行しようとするたびに、MySQLはユーザーの権限に関する情報を参照し、許可すべきかどうかを判断します。ユーザーがそのアクションを実行するために必要なすべての権限を付与されている場合、MySQLはステートメントを実行します。必要な権限が1つでも不足している場合、エラーが発生します。

MySQLは、どのユーザーがどのような権限を持っているかに関する情報を、mysqlシステムデータベース内のいくつかの異なるテーブルに格納します。MySQLの認証と認可の紹介記事で説明されているように、MySQLが異なる種類の権限情報をどこに保持しているかを確認します。

  • user: userテーブルは、各ユーザーの静的グローバル権限を定義します。これらの権限はMySQLサーバー全体に適用され、プラグインやコンポーネントの可用性には影響されません。
  • global_grants: global_grantsテーブルは、各ユーザーの動的グローバル権限を定義します。プラグインまたはコンポーネントによって定義された権限は、このテーブルに登録されます。
  • db: dbテーブルは、データベースレベルの権限を定義します。dbテーブルは、userテーブルと同様にユーザーのUserとHostの値に一致しますが、行のデータベーススコープを定義するDbという列も持っています。
  • tables_priv: tables_privテーブルは、dbテーブルがデータベースに対して行うのと同様に、テーブルレベルの権限を定義します。テーブルレベルのスコープを有効にするために、User、Host、Dbに加えて、Table_nameという列が利用可能です。
  • columns_priv: tables_privテーブルよりもさらに一歩進んで、columns_privテーブルは列レベルでのアクセスを決定します。この追加の粒度を追加するために、tables_privテーブル内で利用可能な列に加えて、Column_nameという列が含まれます。
  • procs_priv: procs_privテーブルは、プロシージャと関数の実行に対する権限を定義します。これは、User、Host、Db、Routine_name、およびRoutine_type列を使用して、異なる種類のプロセスに対するユーザーの権限をスコープします。
  • proxies_priv: proxies_privテーブルは、ユーザーのプロキシ権限を定義します。プロキシを使用すると、あるユーザーが別のユーザーとして振る舞い、その権限を継承できます。proxies_privテーブルは、UserとHost列を使用してユーザーを照合し、その後、一致したユーザーが誰として振る舞えるかを定義するためにProxied_hostとProxied_userという別の列を使用します。

MySQLで利用可能な権限とは？

MySQLは、さまざまなシステムスコープに適した多くの権限を定義しています。これらの中には、データベース、テーブル、関数の日常的な使用と管理に役立つものもあれば、レプリケーション、バックアップ、接続管理などの管理タスクのために設計されたものもあります。

静的権限（MySQL自体に組み込まれているコア権限）とそのそれぞれのスコープの包括的なリストは、MySQLドキュメントのGRANTおよびREVOKEに対する許可される静的権限テーブルで見つけることができます。MySQLドキュメントの関連する静的権限の説明セクションでは、各権限が何を許可するか、そして多くの場合、どのようなシナリオで最も役立つかについての詳細な概要が提供されています。

動的権限はもう一つの種類の権限です。動的権限はプラグインまたはコンポーネントで定義され、MySQLに登録されて有効になります。それらは常にグローバルスコープであり、追加の機能や特徴を提供します。MySQLドキュメントのGRANTおよびREVOKEに対する許可される動的権限テーブルには、各動的権限とそのコンテキストがリストされています。それぞれの用途の完全な説明は、関連するMySQLドキュメントの動的権限の説明セクションで見つけることができます。

MySQLサーバーでどの権限が有効で利用可能か、またそれらがどのようなコンテキストで関連するかを知るには、以下のコマンドを使用できます

SHOW PRIVILEGES

これは、ユーザーの責任に最も適した権限を理解するのに役立ちます。

アカウントが持つ権限を確認する方法

MySQLの権限がどのように機能するか、どのような権限が利用可能かを検討したところで、各アカウントにどの権限が付与されているかをどのように把握するのでしょうか？

自分のユーザーに付与された権限は、次のように入力していつでも確認できます。

SHOW GRANTS;
+--------------------------------------------------------------------+
  Grants for exampleuser@localhost                                   |
+--------------------------------------------------------------------+
  GRANT USAGE ON *.* TO `exampleuser`@`localhost`                    |
  GRANT ALL PRIVILEGES ON `exampledb`.* TO `exampleuser`@`localhost` |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)

ここでは、'exampleuser'@'localhost'に2つの権限セットが定義されていることがわかります。最初の項目は、グローバルにUSAGEが付与されていることを示しています（ワイルドカード<database>.<table>スコープ*.*で示されます）。その名前にもかかわらず、この文脈でのUSAGEは実際には「権限が付与されていない」ことを意味します。したがって、デフォルトでは、このユーザーには何の権限も与えられていません。2番目のレコードは、exampledbデータベースに対してALL PRIVILEGES、つまり完全なアクセス権が付与されていることを示しています。

ログインしているユーザーアカウントが内部のmysqlデータベースに対するSELECT権限を持っている場合、他のユーザーアカウントに付与された権限を確認できます。他のアカウントの権限を表示するには、次の形式を使用します。

SHOW GRANTS FOR '<user>'@'<host>';

出力には、指定されたアカウントの権限が表示されます。

`GRANT`コマンドはどのように使用しますか？

`GRANT`コマンドは、アカウントに新しい権限を割り当てるために使用されます。これは、以前は持っていなかったデータベース、オブジェクト、またはアクションへのユーザーアカウントのアクセスを追加する主要な方法です。ユーザーアカウントに追加のアクセスを提供したい場合はいつでも、GRANTコマンドが役立ちます。

基本的な構文

権限を割り当てるためのGRANTコマンドの基本的な構文は、かなり簡単です。次の形式に従います。

GRANT <privileges> ON <database>.<object> TO '<user>'@'<host>';

複数の権限はコンマで区切って指定できます。

データベース、テーブル、列などのターゲット設定

上記の構文の<database>.<object>の部分は、権限が付与されるスコープを指示します。これにより、どのオブジェクトに権限が付与されるか、および新しい権限が記録されるmysqlデータベース内の特定のテーブルが決定されます。

権限をグローバルに付与し、ユーザーアカウントがシステム全体でその権限を使用できるようにするには、スコープコンポーネントのデータベース部分とデータベースオブジェクト部分の両方にワイルドカードを使用します。

たとえば、'sally'@'localhost'にグローバルにSELECT権限を付与するには、次のように入力します。

GRANT SELECT ON *.* TO 'sally'@'localhost';

付与のスコープを単一のデータベースに制限するには、ドットの左側のワイルドカードをデータベース名に置き換えます。

GRANT SELECT ON accounting.* TO 'meredith'@'localhost';

アカウントがデータベース内の単一のテーブルにのみアクセスする必要がある場合は、ドットの右側にテーブル名を指定します。

GRANT UPDATE ON accounting.revenue TO 'frank'@'localhost';

最後に、特定の列に権限を適用する場合は、少し異なる形式に従います。列レベルにスコープを設定する場合、権限名の後に括弧で囲んで、権限を適用すべき列を指定する必要があります。

たとえば、library.loansテーブルのdue_by列の値を更新する権限を付与するには、次のように入力します。

GRANT UPDATE (due_by) ON library.loans TO 'autorenew'@'localhost';

`WITH GRANT OPTION`句の使用

WITH GRANT OPTIONと呼ばれる追加の句を`GRANT`ステートメントに付加することで、ユーザーアカウントが特定のスコープで他のユーザーへの権限付与を管理できるようになります。単にユーザーに権限を付与するだけでなく、そのユーザーが同じスコープで持っている任意の権限を他のユーザーに引き渡す能力も付与することになります。

たとえば、ここでは'librarymanager'@'localhost'アカウントに、SELECT、INSERT、UPDATE、およびDELETEの権限を与えるだけでなく、libraryデータベースにおける自身の権限を他のユーザーに引き渡す能力も付与できます。

GRANT SELECT,INSERT,UPDATE,DELETE ON library.* TO 'librarymanager'@'localhost' WITH GRANT OPTION;

`WITH GRANT OPTION`句が、ステートメント内の特定の権限ではなく、アカウント（'librarymanager'@'localhost'）とスコープ（library.*）に適用されることを理解することが重要です。これは、このステートメントで'librarymanager'@'localhost'アカウントに4つの新しい権限を割り当てたとしても、WITH GRANT OPTIONによって、library.*スコープでの任意の権限を渡すことができるようになることを意味します。このアカウントがこのスコープに対するGRANT OPTIONを持つようになったため、将来的に'librarymanager'@'localhoast'に追加の権限を与えた場合でも、それらの権限を自動的に渡すことができるようになります。

上記で示したように、WITH GRANT OPTION句を使用して、アカウントに追加の権限を与えつつ、その権限を他のユーザーに引き渡すことを許可できますが、多くの場合、これら2つのアクションを次のように分離する方がより明確です。

GRANT SELECT,INSERT,UPDATE,DELETE ON library.* TO 'librarymanager'@'localhost';
GRANT GRANT OPTION ON library.* TO 'librarymanager'@'localhost';

GRANT OPTIONを通常の権限として扱う場合、割り当てる権限のリストに含めることもできます。

GRANT SELECT,INSERT,UPDATE,DELETE,GRANT OPTION ON library.* TO 'librarymanager'@'localhost';

いずれの場合でも、結果として'librarymanager'@'localhost'アカウントは、現在および将来にわたってlibraryデータベースに対して持つ任意の権限を他のユーザーに付与できるようになります。これは、GRANT OPTION権限を不注意に割り当てると、管理者が意図しない追加の権限をユーザーがアカウントに与えることができるため、特に危険です。

ユーザーアカウントに一般的な権限を付与する

権限付与の一般的な仕組みについて説明したところで、さまざまな一般的な権限をユーザーアカウントに割り当てる方法の例をいくつか見ていきましょう。

ユーザーに完全なアクセス権を付与する方法

多くの場合、特定のユーザーにデータベースまたはデータベースコンポーネントに対する完全な所有権を割り当てたいと考えるでしょう。たとえば、salesデータベースには、その中のテーブル、関数、インデックスを管理するために指定された特定のユーザーがいるかもしれません。

特定のスコープでユーザーに完全な権限を割り当てるには、ALLまたはALL PRIVILEGESの短縮形を使用できます。

GRANT ALL PRIVILEGES ON sales.* TO 'salesadmin'@'localhost';

これにより、salesデータベースに対するユーザーが割り当て可能なすべての権限が'salesadmin'@'localhost'ユーザーに付与されますが、いくつかの重要な例外があります。ALL PRIVILEGES権限バンドルには、GRANT OPTIONまたはPROXY権限は含まれていません。これらは個別に割り当てる必要があります。これは、権限管理とユーザー代替権限を渡さずに、完全な権限を簡単に割り当てられるようにするためです。

GRANT OPTIONとPROXYを除くすべての権限をグローバルに割り当てるには、*.*スコープを使用します。

GRANT ALL PRIVILEGES ON *.* TO 'systemadmin'@'localhost';

権限管理を含む完全なアクセス権をユーザーに付与する方法

完全な権限を割り当て、さらにユーザーが自身の任意の権限を渡せるようにするには、ステートメントにGRANT OPTIONを含めます。たとえば、前の例の'salesadmin'@'localhost'アカウントに、salesデータベースへの他のユーザーのアクセスを制御する能力を与えるには、代わりに次のように入力できます。

GRANT ALL PRIVILEGES ON sales.* TO 'salesadmin'@'localhost' WITH GRANT OPTION;

これにより、そのアカウントはsalesデータベースへの完全なアクセス権を持つだけでなく、他のユーザーがデータベースで何をできるかを指示することもできるようになります。

この同じロジックは、*.*コンテキストを使用してグローバルに適用できます。この場合、指定されたアカウントは完全な管理者ユーザーになります。

GRANT ALL PRIVILEGES ON *.* TO 'fulladmin'@'localhost' WITH GRANT OPTION;

ユーザーに読み取り専用アクセスを付与する方法

多くの場合、データベースまたはテーブルレベルでは、情報にアクセスできる必要があるものの、データベースやオブジェクトをいかなる方法でも変更する能力を持つべきではないアカウントが存在します。これには、レポートツールや、多くの非インタラクティブなウェブページのように、データにアクセスできるが変更できない必要があるシナリオが含まれます。

SELECT権限は、ユーザーにデータベースまたはオブジェクトに対する読み取り専用権限を与えるのに十分です。'salesreport'@'localhost'ユーザーにsalesデータベースへの読み取り専用アクセスを与えるには、次のように入力します。

GRANT SELECT ON sales.* TO 'salesreport'@'localhost';

このユーザーは、salesデータベースから必要なデータをクエリして抽出できますが、変更を加えることはできません。

通常どおり、グローバルな同等物は*.*スコープを使用します。

GRANT SELECT ON *.* TO 'globalread'@'localhost';

ユーザーに読み書きアクセスを付与する方法

読み取り専用のユースケースの典型的な対は、読み書きアクセスを必要とするユーザーです。この種のアクセスは、データベースまたはオブジェクト内のデータを管理する必要があるあらゆるプロセスに適しています。たとえば、ウェブサイトのユーザープロファイルを作成または編集するプロセスは、読み取りと書き込み権限の両方を必要とします。

ユーザーに読み書きアクセスを割り当てるには、オブジェクトに対するSELECT、INSERT、UPDATE、およびDELETE権限を付与します。例：

GRANT SELECT,INSERT,UPDATE,DELETE ON website.profiles TO 'profilemanager'@'localhost';

ユーザーに追記専用アクセスを付与する方法

もう一つの一般的なシナリオは、テーブルや他のオブジェクトにデータを追記するだけの権限を持つアカウントを作成することです。こうすることで、プロセスは常にオブジェクトに対して追加的な権限を持ち、既存のエントリを書き換えたり変更したりすることはできません。これは、追記専用のイベントログや、更新が履歴を保持するために新しいレコードとして保存されるシナリオに役立ちます。

データベースオブジェクトに対してアカウントに追記専用権限を許可するには、SELECTとINSERT権限のみを付与します。

GRANT SELECT,INSERT ON website.eventlog TO 'weblogger'@'localhost';

アカウントがレコードの特定のフィールドを選択的に更新できるようにしたい場合は、適切な列に対するUPDATE権限を追加で付与できます。

GRANT SELECT,INSERT ON website.eventlog TO 'weblogger'@'localhost';
GRANT UPDATE (comments) ON website.eventlog TO 'weblogger'@'localhost';

`REVOKE`コマンドはどのように使用しますか？

`GRANT`コマンドを見てきたところで、その対となるREVOKEコマンドを紹介する必要があります。GRANTコマンドが特定のスコープでユーザーに追加の権限を割り当てるのに対し、REVOKEコマンドはアカウントから権限を削除することを可能にします。

基本的な構文

`REVOKE`コマンドはGRANTコマンドとかなり似ています。コマンド名を除けば、権限をアカウントに付与するのではなく、アカウントから剥奪する点が異なります。

基本的な構文は次のとおりです。

REVOKE <privileges> ON <database>.<object> FROM '<user>'@'<host>';

GRANTと同様に、複数の権限をコンマで区切って指定できます。

データベース、テーブル、列などのターゲット設定

権限は特定のスコープ（グローバル、データベース、テーブルなど）に紐付けられているため、REVOKEコマンドは、権限を追加する際と同様に、権限を削除するスコープを指定する必要があります。

グローバルレベルで権限を削除するには、*.*ワイルドカードを使用して任意のデータベースと任意のデータベースオブジェクトに一致させます。

REVOKE SELECT ON *.* FROM 'sally'@'localhost';

特定のデータベースから権限を削除するには、ドットの左側にデータベース名を指定します。

REVOKE SELECT ON accounting.* FROM 'meredith'@'localhost';

そして最後に、データベースオブジェクトから権限を削除するには、データベース名とオブジェクト名をドットで区切って指定します。

REVOKE UPDATE ON accounting.revenue FROM 'frank'@'localhost';

剥奪後もユーザーが他の手段を通じて意図しないアクセス権を持っていないことを確認するために、利用可能な権限をチェックすることをお勧めします。

SHOW GRANTS FOR 'frank'@'localhost';

部分的な剥奪を使用して権限を微調整する

MySQL 8.0.16以降、部分的な剥奪がサポートされています。これは、アカウントに広範囲の権限を与え、その後特定のスコープに対してそれらの権限を選択的に削除できることを意味します。

たとえば、ユーザーの権限、認証の詳細などのシステム情報を格納するために使用されるmysqlデータベースを除く、データベースに対する完全な権限を持つアカウントを設定できます。部分的な剥奪により、完全な権限を付与し、そのデータベースに対して特別な例外を追加することができます。

MySQLで部分的な剥奪を有効にするには、有効化する必要があります。サポートされているバージョン（MySQL 8.0.16以降）で以下を入力することで、永続的に有効にできます。

SET PERSIST partial_revokes = ON;

さて、上記で説明したユーザーアカウントを設定するには、次のように入力できます。

CREATE USER 'normaladmin'@'localhost' IDENTIFIED BY '<password>';
GRANT ALL PRIVILEGES ON *.* TO 'normaladmin'@'localhost';
REVOKE ALL PRIVILEGES ON mysql.* FROM 'normaladmin'@'localhost';
GRANT SELECT ON mysql.* TO 'normaladmin'@'localhost';

ここでは、ユーザーを作成し、MySQLサーバー全体に対する完全な権限を付与しました。その後、mysqlデータベースのコンテキストでそれらの権限を具体的に剥奪します。そして、アカウントが引き続きデータベースから値を読み取れるように、SELECT権限を再付与します。

このアカウントの権限を確認すると、これに似たものが表示されます。

SHOW GRANTS FOR 'normaladmin'@'localhost'\G
*************************** 1. row ***************************
Grants for normaladmin@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `normaladmin`@`localhost`
*************************** 2. row ***************************
Grants for normaladmin@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `normaladmin`@`localhost`
*************************** 3. row ***************************
Grants for normaladmin@localhost: REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql`.* FROM `normaladmin`@`localhost`
3 rows in set (0.00 sec)

1行目は、グローバルに適用された（*.*を使用した）ALL PRIVILEGESの短縮形にカプセル化されたすべての静的権限の拡張リストです。2行目は、再びグローバルに適用されたALL PRIVILEGESの短縮形によってカプセル化されたすべての動的権限を示します。3行目は、mysqlデータベースからSELECTが剥奪されたことを除く、データベースレベルで適用されるすべての権限を示します。

`SUPER`権限とは何ですか？

SUPER権限は、強力で潜在的に危険な多くの異なる能力を持つ特殊な権限です。MySQL 8以降、SUPER権限は、よりきめ細かい制御を可能にするために、より詳細な動的権限を優先して非推奨となりました。

SUPER権限が許可していた機能と、代わりに現在使用できる動的権限について学ぶには、MySQLドキュメントに含まれているこれらのリソースを確認してください。

すでにSUPER権限を使用していない場合は、新しいアカウントにSUPER権限を付与するのではなく、必要な動的権限のサブセットを使用することをMySQLは推奨しています。

結論

このガイドでは、MySQLの権限システムが、ユーザーアカウントがさまざまなスコープでさまざまなリソースに対してどのようなレベルのアクセス権を持つかを制御する方法について説明しました。権限は、グローバルに、データベースレベルで、またはより細かくデータベースオブジェクトレベルでユーザーアカウントに割り当てることができます。

ユーザーアカウントのアクセスレベルを向上させるために新しい権限を追加するGRANTコマンドを紹介しました。また、管理者が権限管理の責任を分担できるように、GRANT OPTIONがどのようにユーザーに自身の権限を渡せるようにするか、そして一般的な権限をユーザーアカウントに割り当てる方法について議論しました。REVOKEコマンドを使用してアカウントに割り当てられた権限を削除する方法、および部分的な剥奪が広範な許可に対する例外を規定する方法を実演しました。

ユーザーアカウントに権限を配布する方法を理解することで、最小権限の原則を使用してアクセス管理システムを設定できます。アカウントにその業務を遂行するために必要な特定の権限のみを付与することにより、不正な行動を防ぎ、セキュリティ問題の影響を最小限に抑え、システム内の異なる部分が互いに影響を与え合わないようにする分離戦略を実装できます。

著者について
Justin Ellingwood

ジャスティン・エリングウッド

ジャスティンは2013年からデータベース、Linux、インフラ、開発ツールについて執筆しています。現在は妻と2匹のウサギと共にベルリンに住んでいます。彼は通常、三人称で書く必要がないため、関係者全員にとって安心です。
GitHubでこのページを編集

Prismaのデータガイド

データベースをより身近なものにするための記事の増え続けるライブラリ。

❤️を込めてPrismaが作成
© . All rights reserved.