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

使用するコマンド

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

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

必要な権限

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

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

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

MySQLで利用可能な権限は何ですか？

MySQLは、さまざまなシステムスコープに適した多くの権限を定義しています。これらの権限の一部は、データベース、テーブル、および関数の日常的な使用と管理に役立ちますが、他の権限は、レプリケーション、バックアップ、および接続管理などの管理タスク向けに設計されています。

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

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

MySQLサーバーで有効になっており、利用可能な権限、およびそれらが関連するコンテキストを確認するには、次のコマンドを使用できます。

SHOW PRIVILEGES

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

基本構文

権限を割り当てる`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ステートメントに追加して、特定のスコープで他のユーザーの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;

ユーザーに読み取り専用アクセスを付与するにはどうすればよいですか？

多くの場合、データベースまたはテーブルレベルで、情報にアクセスできる必要があるが、データベースまたはオブジェクトを何らかの方法で変更する機能を持つべきではないアカウントがあります。これらには、レポートツールや、多くの非対話型Webページのように、データにアクセス可能にする必要があり、変更可能にする必要がないシナリオが含まれる場合があります。

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

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

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

通常どおり、グローバル相当は`*.*`スコープを使用します。

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

ユーザーに読み取りおよび書き込みアクセスを付与するにはどうすればよいですか？

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

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

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

ユーザーに追記専用アクセスを付与するにはどうすればよいですか？

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

アカウントにデータベースオブジェクトへの追記専用権限を許可するには、`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 <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)

最初の行は、グローバルに適用された（*.* を使用）ALL PRIVILEGES 省略形にカプセル化されたすべての*静的*特権の拡張リストです。2 行目は、再びグローバルに適用された ALL PRIVILEGES 省略形によってカプセル化されたすべての*動的*特権を示しています。3 行目は、mysql データベースから取り消された SELECT を除き、データベースレベルで適用されるすべての特権を示しています。