mysql create user how create new user mysql
このチュートリアルでは、さまざまな認証メカニズム、パスワード管理、リソース制限オプションなどを使用したMySQL CreateUserコマンドについて説明します。
MySQL CREATE USERコマンドは、新しいユーザーを作成し、データベース/テーブルなどへのきめ細かいアクセスを許可するために使用されます。
複数の人が異なるアクセスレベルを持つMySQLサーバーインスタンスを使用しています。 例えば、 特定のデータベースへの読み取りアクセス権を持つユーザーもいれば、特定のデータベースへの読み取り/書き込みアクセス権を持つユーザーもいます。
CREATE USERコマンドは、MySQL管理者がMySQL Serverインスタンスのユーザーを作成し、GRANTクエリを使用してさまざまな権限を付与するために頻繁に使用されます。
学習内容：
私のSQLCREATEUSERコマンド
CREATE USERコマンドは、MySQLサーバーインスタンスに新しいアカウントを作成または追加するために使用されます。
このコマンドは一般的にアクセス可能であり、MySQL管理者がMySQLServerインスタンスのさまざまなユーザーへのアクセスを管理するために使用します。
CREATE USERコマンドを使用してユーザーを作成するときに、次のように指定できます。
- MySQLへの接続中に使用する必要がある認証。
- リソース制限
- パスワード管理のプロパティ：パスワードの有効期限、パスワードの再利用設定。
- アカウントのロック：新しく作成されたアカウントは、ロックまたはロック解除されます。
アカウントは、MySQLシステムテーブルに作成されます。 ‘mysql.user’
注意： CREATE USERコマンドを使用するには、ユーザーはCREATE_USER特権を持っているか、 MySQL システムスキーマ。
最も単純な形式では、CREATEUSERコマンドの構文は次のとおりです。
CREATE USER (IF NOT EXISTS) '{username}'@'{hostname}' IDENTIFIED BY '{passwordString}';オプションに注意してください 存在しない場合 。これにより、ユーザーがすでに存在する場合、SQLクエリの結果は警告をスローするだけでエラーは発生しません。ここに、 「ユーザー名」 ユーザーが接続する実際のユーザー名を指し、 「ホスト名」 ユーザーが接続するホストを指します。
ご注意ください、 「hostname」フィールドを空白のままにすると、ホストの値は「％」であると見なされ、指定されたユーザー名ですべてのホストが接続できるようになります。
次の構文でユーザーを作成します。
CREATE USER 'userx'@'localhost' IDENTIFIED BY 'password';上記のステートメントを使用して、ユーザー名「userx」とパスワードを「password」とするユーザーを作成しました。ローカルMySQLインスタンスでユーザーを作成しているため、ホスト名はlocalhostです。
mysql.usersテーブルにクエリを実行して、作成したユーザーのエントリを確認してみましょう。
SELECT * from mysql.user以下のような出力が表示されます。
最初の4人のユーザーは、MySQLのインストール中に事前に作成されていますが、最後のエントリは、作成したユーザー、つまり「userx」です。
この時点で、データベースの作成、更新、クエリなどの点でユーザーに権限を付与せずに、ユーザーを作成したばかりであることに注意してください。
MySQLクライアントを使用してこのユーザーでログインしてみましょう。
ターミナルを使用して、以下のコマンドで接続できます。
$ /usr/local/mysql/bin/mysql -u userx -pパスワードの入力を求められたら、「password」としてパスワードを入力します。
Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 33 Server version: 8.0.20 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> 次に、CREATEDATABASEキーワードを使用して新しいデータベースを作成します。
mysql> create database student; ERROR 1044 (42000): Access denied for user 'userx'@'localhost' to database 'student' 上記のように、ユーザー「userx」にはデータベースを作成するためのアクセス権がないことを示すエラーメッセージが生成されます。CREATEUSERコマンドで使用できる他のオプションを見てみましょう。
Authプラグインでユーザーを作成
「認証プラグイン」は認証オプションを指定し、mysql.userテーブルのプラグイン列に保存されます。 MySQLは少数の認証プラグインをサポートしています。
サポートされているプラグインは次のとおりです。
- MySQLネイティブパスワードハッシュ： ネイティブパスワードハッシュは、SHA1を使用してパスワード値をmysql.usersテーブルに格納することに他なりません。このメカニズムは、SHA1パスワードハッシュよりも安全性が低いと考えられています。
- SHA2 256パスワードハッシュ： これは、MySQLで使用されるデフォルトの認証プラグインです。つまり、CREATE_USERコマンドで認証プラグインが指定されていない場合でも、デフォルトでこのプラグインが適用されます。
- LDAPを使用した外部認証： LDAPは通常、MySQL認証を組織のActiveDirectoryにリンクするために使用されます。 例えば、 Google SSO、OAuth、またはMicrosoft OutlookLDAPを使用した認証。これには、MySQL側にもLDAPプラグインをインストールする必要があります。詳細については、参照することができます ここに。
>>参照 ここに サポートされているプラグインの完全なリストについては。
デフォルトの認証プラグインとSHA2認証プラグインを使用してユーザーを作成し、それらに対応するハッシュ値をmysql.usersテーブルに作成してみましょう。まず、デフォルトの認証である（SHA2）のユーザーを作成し、に何が保存されているかを確認します。 mysql.user テーブル。
CREATE USER IF NOT EXISTS 'user-default'@'localhost' IDENTIFIED BY 'P@ssw0rd'mysql.usersテーブルでこのユーザー「user-default」の認証プラグインを見てみましょう。
SELECT host,user,plugin,authentication_string from mysql.user where user='user-default' 
ここauthentication_stringには、パスワード文字列「P @ ssw0rd」のSHA-256値が表示されます。
次に、認証プラグインを使用してユーザーを作成しましょう。 「MySQLネイティブパスワード」を使用して、保存されるプラグインの値を確認します。
CREATE USER IF NOT EXISTS 'user-sha1'@'localhost' IDENTIFIED WITH mysql_native_password BY 'P@ssw0rd' SELECT host,user,plugin,authentication_string from mysql.user where user='user-sha1' 
役割を持つユーザーの作成
MySQLは、新しいユーザーに事前定義されたロールを割り当てることもできます。これらの役割には、一部またはすべてのデータベースへのアクセスが構成されています（カスタマイズすることもできます）。 例えば、 「開発者」という名前のロールを作成して、データベースにすべての権限を割り当てることができます。後で、同じロールを使用して新しいユーザーに割り当てることができます。
例を挙げてこれを見てみましょう。
- 開発者という名前の役割を作成します
CREATE ROLE 'developer'- 「test」という名前のデータベースを作成します
CREATE DATABASE test- 「開発者」の役割についてデータベースをテストするための特権を割り当てます
GRANT ALL ON test.* TO 'developer'- ユーザーを作成し、開発者の役割を割り当てます
CREATE USER IF NOT EXISTS 'user-with-role'@'localhost' DEFAULT ROLE developer;- ユーザーがテストデータベースにテーブルを作成できることを検証します
「user-with-role」という名前のユーザーでログインしてみてください。テストデータベースに新しいテーブルを作成してみます。
$ /usr/local/mysql/bin/mysql -u user-with-role Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 44 Server version: 8.0.20 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> use test; Database changed mysql> create table test_table(name varchar(20)); Query OK, 0 rows affected (0.07 sec) SSL / TLSでユーザーを作成
SSL / TLSオプションを使用してユーザーを作成するには、クライアントとサーバーの両方にSSL証明書をインストールする必要があります。デフォルトでは、SSLはMySQL用に構成されていません。
これらに従ってください ステップ MySQLサーバーインスタンスのSSLを構成します。
SSLが有効になっているユーザーを作成するには、ユーザーの作成時にREQUIRESSLオプションを使用できます。
CREATE USER 'user-with-ssl'@'localhost' REQUIRE SSL;同様に、X509を使用して作成すると、クライアント/ユーザーが接続するために有効なX509証明書を持っていることが期待されます。
>>参照 ここに X509についてもっと理解するために。
X509証明書を持つユーザーを作成するには、次のクエリを使用します。
CREATE USER 'user-with-x509'@'localhost' REQUIRE X509;パスワード管理オプションを使用したユーザーの作成
パスワードオプションは、CREATEUSERコマンドを使用してユーザーを作成するときにパスワードのポリシーを設定するために使用されます。
＃1） ログイン時にパスワードの有効期限が切れます。パスワードは最初の使用後に期限切れになり、ユーザーにパスワードの変更を求めます。
CREATE USER IF NOT EXISTS 'test'@'localhost' IDENTIFIED BY 'Password' PASSWORD EXPIRE;＃二） 一定の間隔を置いてパスワードを期限切れにします。パスワードの有効期限は、構成された間隔で構成できます。 例えば、 90日。
CREATE USER IF NOT EXISTS 'test'@'localhost' IDENTIFIED BY 'Password' PASSWORD EXPIRE INTERVAL 90 DAY;＃3） 設定された再試行後にパスワードがロックされます。多くの場合、誤った再試行を「n」回行った後、ユーザーアカウントを（構成された期間）ロックすることが望まれます。以下のクエリでは、ユーザーアカウントは5回の誤った試行の後、2日間ロックされます。
CREATE USER 'test'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'Password' FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 2; ＃4） 新しいパスワードは、構成済みの以前のパスワードと同じであってはなりません。
以下のクエリでは、ユーザーが設定する新しいパスワードは、過去2つのパスワードと同じであってはなりません。
CREATE USER IF NOT EXISTS 'test'@'localhost' IDENTIFIED BY 'Password' PASSWORD EXPIRE PASSWORD HISTORY 2; リソース制限オプションを使用してユーザーを作成
単一のユーザーからのクエリ/リクエストによってデータベースが圧倒され、MySQLサーバーのパフォーマンスに影響を与える可能性を回避するために、特に実稼働MySQLインスタンスにはリソース制限が必要です。
ネットワークトラブルシューティング面接の質問と回答pdf
以下の3つのオプションのいずれかを使用して、リソース制限を設定できます。
＃1）MAX_QUERIES_PER_HOUR – 特定のユーザーに許可される1時間あたりの取得クエリの数。
CREATE USER 'user-with-resource-limits'@'localhost' WITH MAX_QUERIES_PER_HOUR 5ユーザーが1時間あたり5クエリを超えようとすると、MySQLサーバーは次のようなエラーをスローするとします。
ERROR 1226 (42000): User 'user-with-resource-limits' has exceeded the 'max_questions' resource (current value: 5)＃2）MAX_UPDATES_PER_HOUR –特定のユーザーに1時間あたりに許可される更新クエリの数。
CREATE USER 'user-with-resource-limits'@'localhost' WITH MAX_UPDATES_PER_HOUR 50＃3）MAX_CONNECTIONS_PER_HOUR – アカウントが1時間あたりにサーバーに接続できる回数。
CREATE USER 'user-with-resource-limits'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 50＃4）MAX_USER_CONNECTIONS – 特定のユーザーからMySQLサーバーインスタンスへの同時接続の数。
CREATE USER 'user-with-resource-limits'@'localhost' WITH MAX_USER_CONNECTIONS 50上記のすべてのオプションは、ユーザーの作成時に組み合わせることができることに注意してください。 MAX_QUERIESを10として、MAX_UPDATESを100として指定する場合、単一のCREATE_USERクエリを次のように指定できます。
CREATE USER 'user-with-resource-limits'@'localhost' WITH MAX_UPDATES_PER_HOUR 100 MAX_QUERIES_PER_HOUR 10 MySQLの更新とユーザーの削除
MySQLには、既存のユーザーをそれぞれ変更および削除するための2つの重要なコマンド– ALTERUSERおよびDROPUSERが用意されています。例を使用して、これらの両方を理解しましょう。
ALTER USER
MySQL ALTER USERは、既存のMySQLユーザーアカウントを更新/変更するために使用されます。それはに使用することができます、
- リソース制限を更新する
- パスワードオプションを設定する
- アカウントのロックとロック解除など。
ALTERUSERを使用してロックされたアカウントのロックを解除する例を見てみましょう。
ロック状態のユーザーを作成し、ALTERUSERコマンドでロックを解除します。
CREATE USER 'test'@'localhost'I DENTIFIED BY 'Password' ACCOUNT LOCK;これで、このユーザーでログインすると、アカウントがロックされたというメッセージが表示されます（ユーザーはロックされたアカウントの状態で作成されたため）。
$ /usr/local/mysql/bin/mysql -u test -p Enter password: ERROR 3118 (HY000): Access denied for user 'test'@'localhost'. Account is locked. 以下のクエリを使用して、ALTERコマンドでユーザーのロックを解除します。
ALTER USER 'test'@'localhost' ACCOUNT UNLOCKMySQLクライアントを使用してユーザーでログインします。
$ /usr/local/mysql/bin/mysql -u test -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 50 Server version: 8.0.20 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> ドロップユーザー
MySQL DROP USERコマンドは、1人以上の既存のユーザーとそれに関連するすべての特権および付与を削除します。
削除されるユーザーによって作成または更新されたデータ/テーブルは削除/削除されないことに注意してください。
誰かが存在しないユーザーを削除しようとすると、DROPUSERコマンドはエラーをスローします。
ユーザーを作成し、DROPコマンドを使用して削除しましょう。
CREATE USER 'test'@'localhost'; DROP USER 'test'@'localhost'; よくある質問
Q＃1）MySQLのユーザーとは何ですか？
回答： MySQLのユーザーは、MySQLサーバーインスタンスにログインしてさまざまな操作を実行できるアカウント/エンティティです。ユーザーには、1つ以上のデータベースやテーブルへのロールベースのアクセスまたは詳細なアクセスを割り当てることができます。
Q＃2）MySQLに接続しているさまざまなユーザーを確認するにはどうすればよいですか？
回答： MySQLサーバーインスタンスでアクティブなユーザーセッションを表示するには、次のようにprocesslistコマンドを実行できます。
SHOW processlist;このコマンドの出力には、コマンドの実行時に接続されたアクティブなユーザーのセッションのさまざまな属性が表示されます。
以下の出力例：
Q＃3）MySQLでユーザーを切り替えるにはどうすればよいですか？
回答： MySQLはユーザーセッションの概念に取り組んでいます。特定のユーザーでログインすると、セッションを終了するか、新しいウィンドウで新しいユーザーに対してセッションを開くか、ワークベンチなどのMySQLGUIクライアントで新しい接続として開くことができます。
mysqlクライアントから特定のユーザーでログインするには、以下の構文を使用できます。
mysql -u {userName} -p上記のコマンドを記述した後、Enterキーを押すと、{userName}フィールドで指定されたユーザーのパスワードを入力するように求められます。パスワードが検証されると、MySQLシェル/コマンドプロンプトが表示されます。
別のユーザーでログインするには、 exitコマンドを入力し、別のユーザーで再ログインすることにより、現在のセッションを終了できます。
mysql> exitさまざまなユーザーのMySQLサーバーインスタンスに複数の接続を設定することもできます。コマンドプロンプトの新しいタブ/ウィンドウを開き、同じコマンドを使用して別のユーザーでログインするだけです。
mysql -u {userName2} -pQ＃4）MySQLユーザーを読み取り専用にする方法は？
回答： MySQLは、データベースまたはテーブルへのきめ細かいアクセスを許可するための徹底的なメカニズムを提供します。 SELECT、UPDATE、INSERTなどのさまざまな種類のアクセスを1つまたは複数のデータベースまたはテーブルに提供できます。
ユーザーに読み取り専用アクセスのみを許可するために、データベースへのSELECTアクセスを許可できます（すべてのテーブルに*を使用するか、要件に応じてテーブル名を明示的に指定します）
以下の例を参考にして、これを理解しましょう。
「Password」としてパスワードを持つ「readaccess」という名前のユーザーを作成しています
create user 'readaccess'@'localhost' IDENTIFIED BY 'Password'次に、MySQLでGRANTキーワードを使用して、「test」という名前のデータベースのすべてのテーブルへの読み取りアクセスを許可します。
grant select on test.* to 'readaccess'@'localhost'ここで、アクセスを検証するために、「readaccess」という名前のこのユーザーでログインし、データベース「test」内のテーブルにクエリを実行してみてください。
ただし、テストデータベース内のテーブルのいずれかにデータを挿入しようとすると、このユーザーに読み取りアクセスを明示的に許可したため、アクセス拒否エラーが発生します。
Q＃5）MySQLでユーザーを確認するにはどうすればよいですか？
回答： MySQLには、MySQLサーバーインスタンス用に作成されたすべてのユーザーのリストを含む「mysql.user」という名前のシステムレベルのテーブルがあります。このテーブルに対する権限は、通常、rootユーザーまたはadminユーザー、あるいはMySQLサーバーインスタンスの認証と承認を管理するユーザーに制限されています。
Q＃6）MySQLユーザーの名前とパスワードを見つける方法は？
回答： MySQLユーザーとパスワードのマッピングは、アクセス制限テーブルである「mysql.user」システムテーブルに保存されます。パスワードは、ユーザーの作成時に選択した暗号化モードに応じて、暗号化された形式で保存されます。
ただし、誰かのアカウントがロックされている場合、MySQLはALTER USERと呼ばれる別のコマンドを提供します。このコマンドを使用して、特定のユーザーアカウントのロックを解除できます。
結論
このチュートリアルでは、データベース管理者が一般的に認証とアクセス管理に使用するMySQL CREATEUSERコマンドについて学習しました。
MySQLは、事前定義されたアクセス許可を使用して新しいユーザーを作成できるようにする、多くの強力な認証メカニズムと役割ベースの割り当てを提供します。また、さまざまな認証メカニズム、さまざまなパスワード設定、有効期限オプションなどを使用してユーザーを作成する例も確認しました。