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

使用するコマンド

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

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

必要な権限

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

優先順位の高い順に、次のアカウントを使用してログインする必要があります。

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

MySQLのユーザーアカウントの構文は何ですか？

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

• ユーザー名
• ユーザーが接続しているホスト

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

'<user>'@'<host>'

上記の例のように、単一引用符を使用して、ユーザーアカウントのユーザーコンポーネントとホストコンポーネントを個別にラップできます。コンポーネントのいずれかに誤って解釈される文字が含まれている場合は、必要な場合があります。一般に、それらを明示的に追加することは常に良い考えです。

したがって、MySQLでは、単に'john'という名前のアカウントを持つのではなく、完全なアカウント名には、'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は次のアカウントを次の順序でソートします。

基本構文

新しいユーザーを作成するための基本構文は比較的簡単です。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ユーザーのパスワードを変更するには？

ほとんどの人にとって、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ユーザーの認証プラグインを変更するには？

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

前の例では、Unixソケット認証を使用するように'mary'@'localhost'というアカウントを構成しました。後でそのアカウントを従来のパスワード認証を使用するように変更する場合は、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 --user=<username> --password <dbname>

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

mysql --user=kamal --password testing

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

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

Unixソケット認証を使用してローカルデータベースにログインする方法

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'