MySQL / 認証と認可

MySQLでユーザーと認証を管理する方法

はじめに
前提条件
MySQLがユーザーアカウントをどのように定義し解釈するかを理解する
ユーザーはどのように作成しますか？
既存のユーザーを表示するには？
既存のMySQLユーザーを変更するには？
MySQLにログインするには？
MySQLユーザーを削除するには？
結論
FAQ

はじめに

ユーザー管理は、MySQL データベースシステムを管理しようとする者にとって最も重要な責任の一つです。環境内のユーザーとサービスを最適に表現するためにユーザーアカウントを作成、変更、削除することは、アクセスをロックダウンし、変更の範囲を制限し、変更に対する監査と説明責任を実装するための基礎を築くのに役立ちます。

このガイドでは、MySQL内でのユーザーアカウントの管理方法について説明します。まず、MySQLにおける有効なユーザーとは何かを議論し、システムに新しいユーザーを追加する方法を示します。次に、各ユーザーの認証を構成する方法と、システムが利用可能なオプションのリストから認証オプションを選択する方法について議論します。さらに、既存ユーザーの変更、作成したアカウントでのログイン方法、不要になったユーザーの削除方法についても説明します。

前提条件

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

使用するコマンド

MySQL内でユーザーを作成、変更、削除するために必要な主要コマンドは次のとおりです。

CREATE USER：新しいユーザーアカウントを作成する
ALTER USER：既存のユーザーアカウントに変更を加える
DROP USER：既存のユーザーアカウントを削除する

必要な権限

上記のコマンドを実行するには、CREATE USER 権限を持つアカウントでMySQLにログインする必要があります。CREATE USER 権限により、ユーザーの作成、変更、削除、名前変更などが可能になります。既存ユーザーに関する情報を表示するには、mysql データベースに対する SELECT 権限も必要です。

優先順位に従って、次の方法でログインしてください。

CREATE USER 権限と mysql データベースに対する SELECT 権限を持つ制限付きアカウント
MySQL内でフル権限を持つrootまたは管理者ユーザー

MySQLがユーザーアカウントをどのように定義し解釈するかを理解する

新しいアカウントを作成する前に、MySQLがユーザーの作成と参照に使用する様々な方法に慣れておくことが役立ちます。また、MySQLの認証アルゴリズムを理解して、接続を認証するためにどのユーザーアカウントが使用されるかを把握しておく必要もあります。

MySQLのユーザーアカウントの構文とは？

MySQLでは、ユーザーアカウントはアットマーク（@）で結合された2つの異なる情報で構成されます。

ユーザー名
ユーザーが接続元のホスト

一般的に、システム上のユーザーアカウントは次のようになります。

'<user>'@'<host>'

上記のように、シングルクォートを使用してユーザーアカウントのユーザーとホストの各コンポーネントを個別に囲むことができます。これらのコンポーネントのいずれかに、そうでなければ誤って解釈される可能性のある文字が含まれている場合、必要となることがあります。一般的に、明示的にするために追加することは常に良いアイデアです。

したがって、単に'john'という名前のアカウントがあるだけでなく、MySQLでは、'john'@'localhost'のように、完全なアカウント名には何らかのホストが必要です。これは、システム上に複数の'john'アカウントが存在する可能性があり、異なるドメインからの接続である限り、MySQLはそれらをそれぞれユニークなアカウントとして認識することを意味します。

これらすべてを考慮しても、ユーザーまたはホストコンポーネントを持たないユーザーアカウントを定義することは可能ですが、注意すべき重要な影響があります。

空の文字列を使用して、ユーザー値なしでユーザーアカウントを定義できます。

''@'<host>'

例えば、''@'localhost'としてユーザーを作成できます。このユーザーは、ローカルコンピューターから接続するあらゆるユーザー名に一致します。

同様に、どのホストからでも一致するユーザーアカウントを持つことができます。ホスト値には空の文字列を使用するのではなく、次のように%ワイルドカードを使用します。

'<user>'@'%'

例えば、'john'@'%'を作成した場合、そのアカウントはあらゆるホストから接続する'john'ユーザーに一致します。

MySQLはユーザーをどのように認証しますか？

MySQLが各認証リクエストを実際にどのように処理するかを理解することは、合理的でありながら誤った仮定に起因する一般的な認証問題を回避するために非常に重要です。これについては、弊社のMySQLにおける認証と認可の概要に関する記事で詳しく説明されています。

接続要求を認証する際、MySQLは内部mysqlデータベースのuserテーブル内のいくつかのフィールドを使用して、接続を許可するかどうかを決定します。MySQLは、接続を認証するために、最大で1つのユーザーアカウントレコードを使用します。これは、接続に一致する可能性のあるアカウントが複数ある場合に、MySQLがどのユーザーアカウントを使用するかを決定する方法を必要とすることを意味します。

MySQLのユーザー認証アルゴリズムは、サーバーの起動時に始まります。起動時、MySQLはmysql.userテーブル全体をメモリにロードします。これは、通常のMySQLコマンドを使用してユーザーアカウントが作成されるたびにも行われます。テーブルをロードする際、エントリを最も高い優先度から低い優先度へとソートします。

MySQLはHost列を主要なソートフィールドとして使用し、より具体的な値を持つ結果を優先します。そのため、リテラル値は最も高い優先度として上位にソートされ、%のようなワイルドカードを使用する値は下位にソートされます。最終的なエントリは、他の文字を含まず%のみを含むものと、完全に空白のホストを持つエントリです。

User列は、同じHost値を持つエントリの二次ソートフィールドとして使用されます。ここでも、より正確な一致が優先されます。User列はワイルドカードを使用できないため、空白のUser値を持つエントリを除いて、すべてのエントリは同等です。これらは下位にソートされます。空白のUser値を持つエントリが選択された場合、ユーザーは「匿名ユーザー」として認証され、通常は権限がないことを意味します。

さて、接続要求が行われるたびに、MySQLはメモリ内のソートされたテーブルを上から下へ順に調べていきます。他のエントリも一致する可能性があるかどうかに関わらず、最初に見つかったエントリを使用してユーザーを認証します。クライアントがそのエントリで定義された方法を使用して認証に失敗した場合、接続は失敗し、他のエントリはチェックされません。

MySQLユーザーアカウント定義にユーザーまたはホストを含めないことの意味は？

MySQLの認証アルゴリズムのため、ユーザーまたはホストコンポーネントなしでユーザーアカウントを作成する際に注意しないと、問題が発生する可能性があります。これは、MySQLがどのレコードを使用して認証するかを決定する方法が直感的でなく、予期せぬ結果を招くことがあるためです。

例えば、ユーザーが空の文字列をユーザー部分としてMySQLに認証した場合、MySQLはセッションの残りの間、そのユーザーを「匿名ユーザー」と見なします。原則として、匿名ユーザーはほとんど権限を持たず、接続後に行えることはごくわずかです。異なるユーザーアカウントで認証しようとしたときに、誤って匿名ユーザーとして認証されることさえあります。

ユーザーアカウントにワイルドカードホストを使用する際の課題は、ホスト値が含まれる他のユーザーアカウントが、ワイルドカードを使用するユーザーアカウントを簡単に隠蔽したり、利用不能にしたりする可能性があることです。

例えば、'emily'@'%'として定義されたユーザーアカウントがある場合、任意のホストから'emily'として認証できると期待するかもしれません。しかし、ユーザーが空白で、ホスト値が'emily'が接続しているホストと一致するユーザーアカウントがある場合、MySQLは代わりにそのアカウントを使用して認証します（上記で説明したように、匿名ユーザーログインにつながります）。

例として、MySQLは次のアカウントを次の順序でソートします。

優先度	MySQLアカウント	コメント
1	`'emily'@'localhost'`と`'emily'@'example.com'`	これらは同じ優先度であり、問題ありません。なぜなら、それらのうち1つだけが接続に一致する可能性があるからです。
2	`''@'localhost'`と`''@'example.com'`	これら2つのエントリも、やはり同じ優先度です。ユーザーコンポーネントはありませんが、リテラルのホストコンポーネントを持っているため、正確なホスト値を持つエントリの最下位に配置されます。
3	`'emily'@'%.example.com'`	このエントリはホストコンポーネントにワイルドカードが含まれているため、正確なホスト値を持つエントリよりも低い優先度が与えられます。
4	`''@'%.example.com'`	このエントリは、ホスト値内にワイルドカードを持つエントリとグループ化されます。ユーザーコンポーネントがないため、このグループの最下位に位置します。
5	`'emily'@'%'`	このエントリはワイルドカードのみで構成されるホスト値を持っています。任意のホストに一致するため、非常に低い優先度です。
7	`''@'%'`	このエントリは、任意のホストから任意のユーザーを匿名ユーザーとして認証するために使用できます。任意の接続に一致するため、極めて低い優先度です。
6	`'emily'@''`	このエントリは完全に空白のホスト値を持っており、ワイルドカードホストのみを含むホストよりもさらに低い優先度です。
8	`''@''`	これは可能な限り低い優先度のユーザーです。ホスト情報を含まないため、ホストソート中に最後に配置されます。また、空白のユーザーを含むため、このグループ内の他のエントリの下に配置されます。ユーザーなしのすべてのエントリと同様に、このエントリで認証された接続は匿名ユーザーとしてサインインされます。

ユーザーはどのように作成しますか？

MySQLがユーザーアカウントをどのように扱うかについて理解できたので、新しいユーザーの作成を開始できます。前提条件で説明されている権限を持つユーザーでログインすることを忘れないでください。

基本構文

新しいユーザーを作成するための基本構文は比較的簡単です。CREATE USERコマンドを使用し、新しいアカウントのユーザーとホストを指定します。

CREATE USER '<user>'@'<host>';

これにより、作成時にユーザーとホスト以外の詳細を設定することなく、基本的なアカウントが作成されます。

パスワード付きユーザーを作成するには？

多くの場合、ユーザーの作成中に認証を設定したいことがあります。これは、CREATE USERステートメントにオプションのIDENTIFIED BY句を追加することで可能です。

CREATE USER '<user>'@'<host>' IDENTIFED BY '<password>';

これにより、これまでと同様に新しいユーザーアカウントが作成され、同時にアカウントのパスワードが割り当てられます。後でパスワードを割り当てる方法や、ユーザーのパスワードを変更する方法については、後述します。

Unixソケット認証でユーザーを作成するには？

パスワード認証はほとんどのユーザーにとって最も一般的な認証方法ですが、唯一の選択肢ではありません。MySQLは、ユーザーアカウントで使用するように構成できる、さまざまな内部および外部認証メカニズムを提供しています。例として、Unixソケット認証を使用して新しいアカウントを構成します。

Unixソケット認証は、LinuxまたはUnixライクな環境で使用でき、オペレーティングシステム上のアカウントが、さらなる認証なしでMySQL内の同じアカウント名にアクセスできるようにします。この構成では、MySQL管理者はオペレーティングシステム上のユーザーアカウントが厳密に管理されていることを認識しています。

したがって、オペレーティングシステムにmaryユーザーが存在する場合、Unixソケット認証が定義された認証メカニズムであれば、MySQL内の'mary'@'localhost'アカウントにログインできます。これを今すぐ設定しましょう。

ソケット認証にはauth_socketプラグインが必要です。まず、次のように入力してプラグインをロードします。

INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';

次に、オペレーティングシステムにあるユーザーアカウントと一致するユーザーアカウントを作成します。この例では、上記で説明したmaryアカウントを使用します。オペレーティングシステム名と一致しない名前を使用した場合、このユーザーを使用して認証することはできません。

ソケット認証でユーザーを作成するには、使用する認証プラグインを指定するためにIDENTIFIED WITH句（以前使用したIDENTIFIED BY句とは異なる）を使用する必要があります。

CREATE USER 'mary'@'localhost' IDENTIFIED WITH auth_socket;

これで、オペレーティングシステムのmaryユーザーから'mary'@'localhost' MySQLユーザーに認証できるようになるはずです。maryとしてログインしている場合、ユーザー名やパスワードを指定せずにデータベースに接続します。

mysql

設定したUnixソケット認証を介して、自動的にサインインされるはずです。

既存のユーザーを表示するには？

次に、既存ユーザーに関する情報を検索する方法を見てみましょう。

既存のMySQLユーザーをすべて表示するには、ユーザーとホストのコンポーネント、および現在使用している認証プラグインを含めて、mysql.userデータベースからこれらのフィールドをSELECTします。

SELECT user,host,plugin FROM mysql.user

+------------------+-----------+-----------------------+
  user             | host      | plugin                |
+------------------+-----------+-----------------------+
  mary             | localhost | auth_socket           |
  mysql.infoschema | localhost | caching_sha2_password |
  mysql.session    | localhost | caching_sha2_password |
  mysql.sys        | localhost | caching_sha2_password |
  root             | localhost | caching_sha2_password |
  useradmin        | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
6 rows in set (0.00 sec)

ここでは、システムに6人のユーザーが定義されており、すべてローカルでのみログインできることがわかります。5つのアカウントはパスワード認証を使用するように設定されています。'mary'@'localhost'アカウントはUnixソケット認証を使用するように設定されています。

SHOW CREATE USERコマンドを使用して、ユーザープロパティに関する追加情報を見つけることができます。その名前とは裏腹に、初期アカウント作成時に使用されたものだけでなく、ユーザーアカウントの現在のすべてのプロパティを表示します。

SHOW CREATE USERコマンドは、アカウント名を引数として受け取ります。

SHOW CREATE USER '<user>'@'<host>'\G

通常、コマンドの最後に通常のコロン（;）の代わりに\Gステートメントターミネーターを付けると、結果がより明確に表示されるため、最適です。

'useradmin'@'localhost'アカウントのプロパティを表示するには、次のように入力します。

SHOW CREATE USER 'useradmin'@'localhost'\G

*************************** 1. row ***************************
CREATE USER for useradmin@localhost: CREATE USER 'useradmin'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$O
                                              Rl7lM;@Gt{roB4EWchqDdYM142Lq7pfzcCNiK4yUxnRBlrAgr0sE3' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
1 row in set (0.00 sec)

既存のMySQLユーザーを変更するには？

ALTER USERコマンドを使用して、MySQL内の既存ユーザーを変更できます。これは、アカウント権限（GRANTおよびREVOKEコマンドで制御される）を除く、アカウントのほとんどのユーザー関連プロパティを変更するために使用できます。

ALTER USERの基本構文は次のとおりです。

ALTER USER <user> <properties_to_change>;

MySQLユーザーのパスワードを変更するには？

ほとんどの人にとって、ALTER USERの最も一般的な使用方法はパスワードの変更です。

例えば、'kamal'@'localhost'のパスワードを変更するには、次のように入力します。

ALTER USER 'kamal'@'localhost' IDENTIFIED BY '<new_password>';

ユーザーに一時的なパスワードを設定し、すぐに変更させる必要がある場合は、パスワードを設定し、同時に有効期限を設定できます。

ALTER USER 'kamal'@'localhost' IDENTIFIED BY '<new_password>' PASSWORD EXPIRE;

CREATE USER権限がなくても、いつでも自分のパスワードを変更できます。自分のユーザー名を自動的に入力するには、USER()関数を使用するのが最も簡単です。

ALTER USER USER() IDENTIFIED BY '<new_password>';

MySQLユーザーの認証プラグインを変更するには？

アカウントの認証に使用されるメカニズムまたはプラグインを変更することもできます。

以前の例では、'mary'@'localhost'というアカウントをUnixソケット認証を使用するように設定しました。後でそのアカウントを従来のパスワード認証に変更したい場合は、再度ALTER USERコマンドを使用できます。

まず、サーバーのデフォルト認証プラグインを特定します。パスワードベースの認証方法であれば、デフォルトの選択肢を再利用するのがおそらく最善です。

SHOW VARIABLES LIKE 'default_authentication_plugin';

この場合、デフォルトの認証プラグインはcaching_sha2_passwordなので、パスワード認証に切り替える際にそれを使用します。

次に、'mary'@'localhost'を新しいパスワードでcaching_sha2_passwordプラグインを使用するように変更します。

ALTER USER 'mary'@'localhost' IDENTIFIED WITH 'caching_sha2_password' BY '<marys_password>';

'mary'@'localhost'ユーザーはUnixソケット認証を使用してログインできなくなりますが、提供されたパスワードを使用してログインできます。

認証を含め、MySQLユーザーアカウントの作成と変更方法について説明しましたが、これらの認証方法を使用して実際にログインする方法についてはまだ説明していません。

mysqlクライアントは、ローカルおよびリモートデータベースに接続するために使用できる強力なコマンドラインクライアントです。これを使い、上記で設定した方法を使用して認証する方法について説明します。

パスワード付きのユーザーアカウントを使用してローカルにホストされたMySQLデータベースにログインする場合、基本構文は次のようになります。

mysql --user=<username> --password <dbname>

したがって、'kamal'@'localhost'ユーザーが、システムがホストされているコンピューターからMySQLにログインし、testingデータベースに接続したい場合、次のように入力できます。

mysql --user=kamal --password testing

mysqlクライアントは'kamal'@'localhost'のパスワードを要求します。正しい認証情報を入力すると、testingデータベースに接続されます。

コマンドラインでデータベースを指定するのはオプションです。指定しない場合、サーバーには接続されますが、特定のデータベースには接続されません。

Unixソケット認証を使用してローカルMySQLサーバーにログインするには、一致するアカウント名でオペレーティングシステムにログインする必要があります。したがって、Unixソケット認証を使用して'mary'@'localhost'に認証したい場合は、まずmaryというユーザー名でコンピューターにログインする必要があります。

正しいオペレーティングシステムアカウントを使用している場合、オプションなしでクライアントを実行するだけでローカルデータベースに直接接続できます。

mysql

これまでと同様に、必要に応じてデータベース名を追記して、特定のデータベースに接続することもできます。

MySQLサーバーがローカルサーバーで実行されていない場合、クライアントが接続を試みるホストを指定する必要があります。--hostオプションを追加することでそれが可能です。

ほとんどの場合、リモートMySQLサーバーにはパスワードで認証するため、コマンドは次のようになります。

mysql --user=<username> --password --host=<host> <dbname>

したがって、'tanya'@'<tanyas_domain>'は、example.comにあるMySQLサーバーに次のように入力して接続できます。

mysql --user='tanya' --password --host='example.com'

MySQLユーザーを削除するには？

もはや目的を果たさないユーザーアカウントを保持しておくことは、セキュリティリスクです。DROP USERコマンドを使用すれば、簡単にアカウントを削除できます。

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

DROP USER '<user>'@'<host>';

したがって、'mary'@'localhost'ユーザーを削除するには、次のように入力します。

DROP USER 'mary'@'localhost';

存在しないユーザーを削除しようとすると、エラーが発生します。

ERROR 1396 (HY000): Operation DROP USER failed for 'mary'@'localhost'

これを避けるには、アカウント名の前にIF EXISTS句を追加します。ユーザーが存在すれば削除され、存在しない場合は警告のみが表示されます。

Query OK, 0 rows affected, 1 warning (0.00 sec)

結論

MySQLのユーザーアカウント管理と認証設定は非常に柔軟です。MySQL内でユーザーを作成、変更、情報を取得する方法を学ぶことで、データベースシステムをより効果的に管理できるようになります。

セキュリティのベストプラクティスでは、各固有のユースケースに対して、その範囲を実行するために必要なアクセスレベルのみを付与したアカウントを作成すべきであるとされています。アカウント作成と認証は、このプロセスの最初の段階です。別のガイドでは、その戦略のもう一方の部分を満たすために権限を付与および取り消すことについて説明します。

FAQ

MySQLでユーザーパスワードを変更するには？

ユーザーのパスワードを変更する最も一般的な方法は、ALTER USERを使用することです。例えば、'kamal'@'localhost'のパスワードは次のように変更できます。

ALTER USER 'kamal'@'localhost' IDENTIFIED BY '<new_password>';

また、次のようにして一時パスワードを設定することもできます。

ALTER USER 'kamal'@'localhost' IDENTIFIED BY '<new_password>' PASSWORD EXPIRE;

MySQLでrootユーザーのアクセスエラーを修正するには？

rootユーザーとしてMySQLデータベースにログインしようとしたときにエラーが発生する原因はいくつか考えられます。MySQLは、ドキュメントで実行できるトラブルシューティングの確認事項について説明しています。

一般的なケースとしては、rootユーザーのパスワードをリセットする必要がある場合や、設定ファイルを編集する必要がある場合などがあります。

MySQLでユーザーを削除するには？

DROP USERコマンドを使用してMySQLでユーザーを削除できます。基本構文は次のとおりです。

DROP USER '<user>'@'<host>';

存在しないユーザーを削除しようとするのを避けるために、アカウント名の前にIF EXISTS句を含めることができます。

MySQLの全ユーザーを表示するには？

MySQLインスタンス内のすべてのユーザーをリストするには、mysql.userデータベースに対してSELECTステートメントを使用できます。hostとpluginも含む場合の構文は次のようになります。

SELECT user,host,plugin FROM mysql.user;

MySQLでユーザーの権限を表示するには？

現在のユーザーの権限を表示するには、次の構文を使用できます。

SHOW GRANTS;

他のユーザーの場合は、次のように使用できます。

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

著者について

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

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

MySQLにおける認可と認証の概要

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

GitHubでこのページを編集

MySQL / 認証と認可

MySQLでユーザーと認証を管理する方法

目次

はじめに

前提条件

使用するコマンド

必要な権限

MySQLがユーザーアカウントをどのように定義し解釈するかを理解する

MySQLのユーザーアカウントの構文とは？

MySQLはユーザーをどのように認証しますか？

MySQLユーザーアカウント定義にユーザーまたはホストを含めないことの意味は？

ユーザーはどのように作成しますか？

基本構文

パスワード付きユーザーを作成するには？

Unixソケット認証でユーザーを作成するには？

既存のユーザーを表示するには？

既存のMySQLユーザーを変更するには？

MySQLユーザーのパスワードを変更するには？

MySQLユーザーの認証プラグインを変更するには？

MySQLユーザーを削除するには？

結論

FAQ

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

Prisma データガイド

MySQL / 認証と認可

MySQLでユーザーと認証を管理する方法

目次

はじめに

前提条件

使用するコマンド

必要な権限

MySQLがユーザーアカウントをどのように定義し解釈するかを理解する

MySQLのユーザーアカウントの構文とは？

MySQLはユーザーをどのように認証しますか？

MySQLユーザーアカウント定義にユーザーまたはホストを含めないことの意味は？

ユーザーはどのように作成しますか？

基本構文

パスワード付きユーザーを作成するには？

Unixソケット認証でユーザーを作成するには？

既存のユーザーを表示するには？

既存のMySQLユーザーを変更するには？

MySQLユーザーのパスワードを変更するには？

MySQLユーザーの認証プラグインを変更するには？

MySQLにログインするには？

パスワードを使ってローカルデータベースにログインするには？

Unixソケット認証を使ってローカルデータベースにログインするには？

パスワードを使ってリモートデータベースにログインするには？

MySQLユーザーを削除するには？

結論

FAQ

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

Prisma データガイド