MySQLのユーザーアカウント管理と権限管理の詳細な説明

序文

MySQL の権限テーブルは、データベースの起動時にメモリにロードされます。ユーザーが ID 認証に合格すると、メモリ内の対応する権限にアクセスします。これにより、ユーザーはデータベースの権限の範囲内でさまざまな操作を実行できます。

詳しい紹介を見てみましょう。

mysql の権限システムは、おおよそ 5 つのレベルに分かれています。

グローバルレベル

グローバル権限は、特定のサーバー内のすべてのデータベースに適用されます。これらの権限は mysql.user テーブルに保存されます。 GRANT ALL ON *.* および REVOKE ALL ON *.* は、グローバル権限のみを付与および取り消します。

データベースレベル

データベース権限は、特定のデータベース内のすべてのオブジェクトに適用されます。これらの権限は、mysql.db および mysql.host テーブルに保存されます。 GRANT ALL ON db_name.* および REVOKE ALL ON db_name.* は、データベース権限のみを付与および取り消します。

テーブルレベル

テーブル権限は、特定のテーブル内のすべての列に適用されます。これらの権限は、mysql.talbes_priv テーブルに保存されます。 GRANT ALL ON db_name.tbl_name および REVOKE ALL ON db_name.tbl_name は、テーブル権限のみを付与および取り消します。

列レベル

列の権限は、特定のテーブル内の単一の列に適用されます。これらの権限は、mysql.columns_priv テーブルに保存されます。 REVOKE を使用する場合は、付与される列と同じ列を指定する必要があります。

サブルーチンレベル

CREATE ROUTINE、ALTER ROUTINE、EXECUTE、および GRANT 権限は、ストアドルーチンに適用されます。これらの権限は、グローバルレベルまたはデータベースレベルで付与できます。また、CREATE ROUTINE を除き、これらの権限はサブプログラムレベルで付与でき、mysql.procs_priv テーブルに格納されます。

これらの権限は次のシステムテーブルに保存されます。

mysql.ユーザー
phpMySQLデータベース
mysql.ホスト
mysql.テーブル_priv
mysql.列のプライベート

ユーザーが接続すると、mysqld は上記のテーブルを通じてユーザーの権限を確認します。

1. 権限テーブルへのアクセス

権限アクセスの 2 つのプロセスでは、システムは「mysql」データベース (MySQL のインストール時に作成され、データベース名は「mysql」) 内の最も重要な 3 つの権限テーブル、user、host、db を使用します。

これら 3 つのテーブルのうち、最も重要なのは user テーブルであり、次に db テーブルが続きます。host テーブルはほとんどの場合使用されません。

ユーザーの列は、主にユーザー列、権限列、セキュリティ列、リソース制御列の 4 つの部分に分かれています。

最もよく使用される列はユーザー列と権限列であり、権限列は一般権限と管理権限に分かれています。通常の権限は、select_priv、super_priv などのデータベース操作に使用されます。

ユーザーが接続すると、権限テーブルへのアクセスプロセスには次の 2 つのプロセスがあります。

まず、ユーザーテーブルのホスト、ユーザー、パスワードの 3 つのフィールドから、接続された IP、ユーザー名、パスワードがテーブルに存在するかどうかを判断します。存在する場合は、ID 認証に合格し、存在しない場合は接続を拒否します。

認証が成功すると、データベース権限は次の権限テーブルの順序で取得されます: user -> db -> tables_priv -> columns_priv。

これらの権限テーブルでは、権限の範囲は順番に狭まり、グローバル権限がローカル権限をカバーします。上記の最初の段階は簡単に理解できます。次の例では、2 番目の段階について詳しく説明します。

テストを容易にするために、変数sql_modeを変更する必要があります。そうしないと、次のようにエラーが報告されます。

MySQL [(なし)]> *.* に対する select 権限を xxx@localhost に付与します。
エラー 1133 (42000): ユーザー テーブルに一致する行が見つかりません
MySQL [(なし)]> SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
クエリは正常、影響を受けた行は 0 行、警告は 2 件 (0.07 秒)

MySQL [(なし)]> *.* に対する select 権限を xxx@localhost に付与します。
クエリは正常、影響を受けた行は 0 行、警告は 2 件 (0.10 秒)

// sql_mode のデフォルト値は NO_AUTO_CREATE_USER です (パスワードも指定されていない限り、GRANT によって新しいユーザーが自動的に作成されるのを防ぎます)
セッション sql_mode を 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' に設定します。

1. ユーザーを作成する

xxx@localhost、すべてのデータベースのすべてのテーブルに対する選択権限を付与します

まず、ユーザーテーブルに表示される権限ステータスを確認します

MySQL [mysql]> user="xxx" かつ host='localhost' の場合、user から * を選択します \G;
************************** 1. 行 ****************************
ホスト: ローカルホスト
ユーザー: xxx
選択特権: Y
挿入権限: N
更新権限: N
削除権限: N
作成権限: N
ドロップ権限: N
リロード権限: N
シャットダウン権限: N
プロセス権限: N
ファイル権限: N
権限付与: N
参照権限: N
インデックス権限: N
権限変更: N
表示_db_priv: N
スーパー特権: N
作成_tmp_table_priv: N
ロックテーブル権限: N
実行権限: N
Repl_slave_priv: N
Repl_client_priv: N
作成ビュー権限: N
表示権限: N
作成ルーチン権限: N
ルーチン権限の変更: N
ユーザー権限の作成: N
イベント特権: N
トリガー権限: N
テーブルスペース作成権限: N
SSL_タイプ: 
SSL暗号: 
x509_発行者: 
x509_件名: 
最大質問数: 0
最大更新回数: 0
最大接続数: 0
最大ユーザー接続数: 0
プラグイン:mysql_native_password
認証文字列: 
パスワードの有効期限: N
パスワード最終変更日時: 2018-12-03 17:34:49
パスワードの有効期間: NULL
アカウントロック: N

dbテーブルの権限ステータスを再度確認します

MySQL [mysql]> user="xxx" かつ host='localhost' の場合、db から * を選択します \G;
空セット (0.03 秒)

ユーザーテーブルではSelect_priv: Y、その他はすべてNであることがわかります。

DBテーブルにレコードがありません

つまり、すべてのデータベースに対して同じ権限を持つユーザーは、db テーブルに記録する必要はなく、ユーザーテーブルの select_priv を "Y" に変更するだけで済みます。つまり、ユーザーテーブル内の各権限は、すべてのデータベースに対する権限を表します。

2. xxx@localhost の権限を変更して、db1 データベース内のすべてのテーブルに対する権限のみを選択します。

MySQL [mysql]> データベース db1 を作成します。
クエリは正常、1 行が影響を受けました (0.01 秒)

MySQL [mysql]> re^C
MySQL [mysql]> xxx@localhost からの *.* の選択を取り消します。
クエリは正常、影響を受けた行は 0 行、警告は 1 件 (0.06 秒)

MySQL [mysql]> db1.* に対する選択権限を xxx@localhost に付与します。
クエリは正常、影響を受けた行は 0 行、警告は 1 件 (0.09 秒)

MySQL [mysql]> user='xxx'\G から * を選択します。
************************** 1. 行 ****************************
ホスト: ローカルホスト
ユーザー: xxx
選択権限: N
挿入権限: N
更新権限: N
削除権限: N
作成権限: N
ドロップ権限: N
リロード権限: N

MySQL [mysql]> user='xxx'\G の db から * を選択します。
************************** 1. 行 ****************************
ホスト: ローカルホスト
デシベル: db1
ユーザー: xxx
選択特権: Y
挿入権限: N
更新権限: N
削除権限: N
作成権限: N
ドロップ権限: N
権限付与: N
参照権限: N
インデックス権限: N
権限変更: N
作成_tmp_table_priv: N
ロックテーブル権限: N
作成ビュー権限: N
表示権限: N
作成ルーチン権限: N
ルーチン権限の変更: N
実行権限: N
イベント特権: N
トリガー権限: N

このとき、userテーブルのselect_privが「N」となり、dbテーブルにdbをxxxとするレコードが追加されていることがわかります。つまり、特定の権限が一部のデータベースにのみ付与されている場合、ユーザーテーブル内の対応する権限列は「N」のままになり、特定のデータベース権限が db テーブルに書き込まれます。テーブルと列の権限メカニズムは db のものと似ています。

3. tables_priv はテーブルの権限を記録します。

MySQL [db1]> テーブル t1(id int(10),name char(10)) を作成します。
クエリは正常、影響を受けた行は 0 行 (0.83 秒)

MySQL [db1]> db1.t1 に対する選択権限を mmm@localhost に付与します。
クエリは正常、影響を受けた行は 0 行、警告は 2 件 (0.06 秒)

MySQL [mysql]> user='mmm'\G の場合、user から * を選択します。
************************** 1. 行 ****************************
ホスト: ローカルホスト
ユーザー: mmm
選択権限: N
挿入権限: N
更新権限: N
削除権限: N
作成権限: N
ドロップ権限: N
リロード権限: N
...

MySQL [mysql]> user='mmm'\G の db から * を選択します。
空のセット (0.00 秒)


MySQL [mysql]> tables_priv から * を選択し、 user='mmm'\G;
************************** 1. 行 ****************************
ホスト: ローカルホスト
デシベル: db1
ユーザー: mmm
テーブル名: t1
付与者: root@localhost
タイムスタンプ: 0000-00-00 00:00:00
Table_priv: 選択
列の権限: 
セット内の 1 行 (0.00 秒)

エラー： 
クエリが指定されていません

MySQL [mysql]> columns_priv から * を選択し、 user='mmm'\G;
空のセット (0.00 秒)

tables_priv テーブルにレコードが追加されていることがわかりますが、ユーザー db columns_priv テーブルにはレコードがありません。

上記の例から、ユーザーが権限認証を通過して権限付与を行うと、==user -> db -> tables_priv -> columns_priv== の順序で権限付与が行われることがわかります。つまり、最初にグローバル権限テーブル user がチェックされます。user 内の該当権限が「Y」の場合、このユーザーのすべてのデータベースに対する権限は「Y」であり、db、tables_priv、columns_priv はチェックされなくなります。「N」の場合は、db テーブルでこのユーザーに対応する特定のデータベースがチェックされ、db 内の「Y」の権限が取得されます。db 内の該当権限が「N」の場合は、tables_priv と columns_priv 内の権限が順にチェックされます。すべて「N」の場合は、権限を保有していないと判断されます。

2. アカウント管理

付与

Grant は、承認だけでなく、ユーザーの作成にも使用できます。

認証構文:

ライブラリ名.テーブル名に対する権限リストを 'password' で識別されるユーザー@ホストに付与します。

すべてのデータベースに対してすべての権限を実行し、ローカルにのみ接続できる権限を持つユーザー p1 を作成します。

MySQL [mysql]> *.* のすべての権限を p1@localhost に付与します。
クエリは正常、影響を受けた行は 0 行、警告は 2 個 (0.03 秒)

MySQL [mysql]> user='p1'\G のユーザーから * を選択します
************************** 1. 行 ****************************
ホスト: ローカルホスト
ユーザー: p1
選択特権: Y
挿入権限: Y
更新権限: Y
削除権限: Y
作成権限: Y
ドロップ権限: Y
リロード権限: Y
シャットダウン権限: Y
プロセス権限: Y
ファイル権限: Y
権限付与: N
参照権限: Y
インデックス権限: Y
権限変更: Y
Show_db_priv: Y
スーパー特権: Y
作成_tmp_table_priv: Y
ロックテーブル権限: Y
実行権限: Y
Repl_slave_priv: Y
Repl_client_priv: Y
ビュー権限の作成: Y
表示権限: Y
作成ルーチン権限: Y
ルーチン権限の変更: Y
ユーザー権限の作成: Y
イベント特権: Y
トリガー権限: Y
テーブルスペース権限の作成: Y
SSL_タイプ: 
SSL暗号: 
x509_発行者: 
x509_件名: 
最大質問数: 0
最大更新回数: 0
最大接続数: 0
最大ユーザー接続数: 0
プラグイン:mysql_native_password
認証文字列: 
パスワードの有効期限: N
パスワード最終変更日: 2018-12-03 18:11:01
パスワードの有効期間: NULL
アカウントロック: N
セット内の 1 行 (0.00 秒)

grant_priv 権限を除き、ユーザーテーブル内のすべての権限は「Y」です。

p1に許可権限を追加する

MySQL [mysql]> grant オプションを使用して、*.* のすべての権限を p1@localhost に付与します。
クエリは正常、影響を受けた行は 0 行、警告は 1 件 (0.03 秒)

MySQL [mysql]> user='p1'\G のユーザーから * を選択します
************************** 1. 行 ****************************
ホスト: ローカルホスト
ユーザー: p1
選択特権: Y
挿入権限: Y
更新権限: Y
削除権限: Y
作成権限: Y
ドロップ権限: Y
リロード権限: Y
シャットダウン権限: Y
プロセス権限: Y
ファイル権限: Y
権限付与: Y
参照権限: Y
インデックス権限: Y
権限変更: Y
Show_db_priv: Y
スーパー特権: Y
作成_tmp_table_priv: Y
ロックテーブル権限: Y
実行権限: Y
Repl_slave_priv: Y
Repl_client_priv: Y
ビュー権限の作成: Y
表示権限: Y
作成ルーチン権限: Y
ルーチン権限の変更: Y
ユーザー権限の作成: Y
イベント特権: Y
トリガー権限: Y
テーブルスペース権限の作成: Y
SSL_タイプ: 
SSL暗号: 
x509_発行者: 
x509_件名: 
最大質問数: 0
最大更新回数: 0
最大接続数: 0
最大ユーザー接続数: 0
プラグイン:mysql_native_password
認証文字列: 
パスワードの有効期限: N
パスワード最終変更日: 2018-12-03 18:11:01
パスワードの有効期間: NULL
アカウントロック: N
セット内の 1 行 (0.00 秒)

権限を付与するためのパスワードを設定する

MySQL [mysql]> '123' で識別される p1@localhost に、付与オプションを使用して *.* のすべての権限を付与します。
クエリは正常、影響を受けた行は 0 行、警告は 2 件 (0.01 秒)

MySQL [mysql]> user='p1'\G のユーザーから * を選択します
************************** 1. 行 ****************************
ホスト: ローカルホスト
ユーザー: p1
選択特権: Y
挿入権限: Y
更新権限: Y
削除権限: Y
作成権限: Y
ドロップ権限: Y
リロード権限: Y
シャットダウン権限: Y
プロセス権限: Y
ファイル権限: Y
権限付与: Y
参照権限: Y
インデックス権限: Y
権限変更: Y
Show_db_priv: Y
スーパー特権: Y
作成_tmp_table_priv: Y
ロックテーブル権限: Y
実行権限: Y
Repl_slave_priv: Y
Repl_client_priv: Y
ビュー権限の作成: Y
表示権限: Y
作成ルーチン権限: Y
ルーチン権限の変更: Y
ユーザー権限の作成: Y
イベント特権: Y
トリガー権限: Y
テーブルスペース権限の作成: Y
SSL_タイプ: 
SSL暗号: 
x509_発行者: 
x509_件名: 
最大質問数: 0
最大更新回数: 0
最大接続数: 0
最大ユーザー接続数: 0
プラグイン:mysql_native_password
認証文字列: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
パスワードの有効期限: N
パスワード最終変更日時: 2018-12-03 18:14:40
パスワードの有効期間: NULL
アカウントロック: N
セット内の 1 行 (0.00 秒)

バージョン5.7以降のデータベースでは、パスワードフィールドはauthentication_stringに変更されます。

任意の IP アドレスから接続できる新しいユーザー p2 を作成します。このユーザーには、db1 データベース内のすべてのテーブルを選択、更新、挿入、および削除する権限があります。初期パスワードは「123」です。

MySQL [mysql]> '123' で識別される 'p2'@'%' に db1.* の select、insert、update、delete 権限を許可します。
クエリは正常、影響を受けた行は 0 行、警告は 1 件 (0.01 秒)

MySQL [mysql]> user='p2'\G から * を選択します。
************************** 1. 行 ****************************
ホスト： ％
ユーザー: p2
選択権限: N
挿入権限: N
更新権限: N
削除権限: N
作成権限: N
ドロップ権限: N
リロード権限: N
シャットダウン権限: N
...
テーブルスペース作成権限: N
SSL_タイプ: 
SSL暗号: 
x509_発行者: 
x509_件名: 
最大質問数: 0
最大更新回数: 0
最大接続数: 0
最大ユーザー接続数: 0
プラグイン:mysql_native_password
認証文字列: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
パスワードの有効期限: N
パスワード最終変更日: 2018-12-03 18:20:44
パスワードの有効期間: NULL
アカウントロック: N
セット内の 1 行 (0.00 秒)

エラー： 
クエリが指定されていません

MySQL [mysql]> user='p2'\G の db から * を選択します。
************************** 1. 行 ****************************
ホスト： ％
デシベル: db1
ユーザー: p2
選択特権: Y
挿入権限: Y
更新権限: Y
削除権限: Y
作成権限: N
ドロップ権限: N
権限付与: N
参照権限: N
インデックス権限: N
権限変更: N
作成_tmp_table_priv: N
ロックテーブル権限: N
作成ビュー権限: N
表示権限: N
作成ルーチン権限: N
ルーチン権限の変更: N
実行権限: N
イベント特権: N
トリガー権限: N
セット内の 1 行 (0.00 秒)

ユーザーテーブル内の権限はすべて「N」、dbテーブルに追加されたレコード権限はすべて「Y」です。これにより、ユーザーに過剰な権限を与えることなく、適切な権限のみが付与されます。

この例では、IP 制限はすべての IP からの接続を許可するため、「%」に設定されています。MySQL データベースでは、ユーザーテーブルのホストフィールドを通じて制御が行われます。ホストには、次の種類の値を割り当てることができます。

注意: mysql データベースのユーザーテーブルの host の値が "%" または空の場合、すべての外部 IP が接続できますが、ローカルサーバー localhost は含まれません。したがって、ローカルサーバーを含める場合は、localhost に個別に権限を付与する必要があります。

ユーザー p3@% にスーパー、プロセス、およびファイル権限を付与します。

MySQL [mysql]> db1.* のスーパー、プロセス、ファイルの権限を 'p3'@'%' に付与します。
エラー 1221 (HY000): DB GRANT および GLOBAL PRIVILEGES の使用方法が正しくありません


MySQL [mysql]> *.* のスーパー、プロセス、ファイルの権限を 'p3'@'%' に付与します。
クエリは正常、影響を受けた行は 0 行 (0.03 秒)

これらの権限はすべて管理権限であるため、データベースを指定することはできません。「on」の後には *.* を続ける必要があります。そうしないと、上記のようにエラーメッセージが表示されます。

これらの権限は何のためにあるのですか?

processこの権限により、ユーザーはSHOW PROCESSLISTおよびKILLコマンドを実行できます。デフォルトでは、すべてのユーザーが SHOW PROCESSLIST コマンドを実行できますが、照会できるのはこのユーザーのプロセスのみです。

ファイル権限を持つユーザーのみが、select ..into outfile および load data infile... 操作を実行できます。ただし、重大なセキュリティリスクが発生するため、管理者以外のアカウントにファイル、プロセス、およびスーパー権限を付与しないでください。

スーパー権限により、ユーザーは任意のクエリを終了したり、グローバル変数のSETステートメントを変更したり、CHANGE MASTER、PURGE MASTER LOGSを使用したりできます。

もう一つの特別なもの

使用権限

接続（ログイン）権限、ユーザーの作成、および使用権限が自動的に付与されます（デフォルトで付与されます）。

この権限はデータベースへのログインにのみ使用でき、いかなる操作も実行できません。使用権限を取り消すことはできません。つまり、ユーザーを取り消したり、ユーザーを削除したりすることはできません。

アカウント権限の表示

アカウントが作成されたら、次のコマンドを実行して権限を表示できます。

user@host の許可を表示します。
MySQL [mysql]> p2@'%' の権限を表示します。
+-------------------------------------------------------------+
| p2@% の助成金 |
+-------------------------------------------------------------+
| *.* の使用権限を 'p2'@'%' に付与 |
| `db1`.* に対する SELECT、INSERT、UPDATE、DELETE 権限を 'p2'@'%' に付与します |
+-------------------------------------------------------------+
セット内の 2 行 (0.00 秒)

アカウント権限の変更

db1 内のすべてのテーブルに対する選択権限を持つユーザーアカウント p4 を作成します。

MySQL [mysql]> db1.* に対する選択権限を p4@'%' に付与します。
クエリは正常、影響を受けた行は 0 行、警告は 1 件 (0.01 秒)

MySQL [mysql]> p4@'%' の権限を表示します。
+-------------------------------------+
| p4@% の助成金 |
+-------------------------------------+
| 'p4'@'%' に *.* の使用権限を付与 |
| `db1`.* に対する 'p4'@'%' への SELECT 権限を付与 |
+-------------------------------------+
セット内の 2 行 (0.00 秒)

削除権限を追加

MySQL [mysql]> db1.* の削除権限を p4@'%' に付与します。
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

MySQL [mysql]> p4@'%' の権限を表示します。
+---------------------------------------------+
| p4@% の助成金 |
+---------------------------------------------+
| 'p4'@'%' に *.* の使用権限を付与 |
| `db1`.* に対する SELECT、DELETE 権限を 'p4'@'%' に付与 |
+---------------------------------------------+
セット内の 2 行 (0.00 秒)

既存の選択権限と結合する

削除権限を削除する

revoke ステートメントは、付与された権限を取り消すことができます。

MySQL [mysql]> p4@'%' の権限を表示します。
+---------------------------------------------+
| p4@% の助成金 |
+---------------------------------------------+
| 'p4'@'%' に *.* の使用権限を付与 |
| `db1`.* に対する SELECT、DELETE 権限を 'p4'@'%' に付与 |
+---------------------------------------------+
セット内の 2 行 (0.00 秒)

MySQL [mysql]> p4@'%' から db1.* の削除を取り消します。
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

MySQL [mysql]> p4@'%' の権限を表示します。
+-------------------------------------+
| p4@% の助成金 |
+-------------------------------------+
| 'p4'@'%' に *.* の使用権限を付与 |
| `db1`.* に対する 'p4'@'%' への SELECT 権限を付与 |
+-------------------------------------+
セット内の 2 行 (0.00 秒)

使用を取り消すことはできますか？

MySQL [mysql]> p4@'%' から db1.* の選択を取り消します。
クエリは正常、影響を受けた行は 0 行 (0.02 秒)

MySQL [mysql]> p4@'%' の権限を表示します。
+--------------------------------+
| p4@% の助成金 |
+--------------------------------+
| 'p4'@'%' に *.* の使用権限を付与 |
+--------------------------------+
セット内の 1 行 (0.00 秒)

MySQL [mysql]> p4@'%' から db1.* の使用権を取り消します。
エラー 1141 (42000): ホスト '%' 上のユーザー 'p4' にそのような権限は定義されていません

使用権限は取り消すことができません。つまり、ユーザーを取り消しても、そのユーザーを削除することはできません。

アカウントを完全に削除するには、drop userを使用します。

ユーザー p4@'%' を削除します。

アカウントリソースの制限

MySQL アカウントを作成するときに、アカウントリソース制限と呼ばれる別の種類のオプションがあります。この種類のオプションの目的は、各アカウントが実際に持つリソース制限を制限することです。ここでの「リソース」には主に次のものが含まれます。

max_queries_per_hour count: 1 つのアカウントで 1 時間あたりに実行されるクエリの数
max_upodates_per_hour count: 1 つのアカウントに対して 1 時間あたりに実行される更新の数
max_connections_per_hour count : 1 つのアカウントが 1 時間あたりにサーバーに接続する回数
max_user_connections count: 1つのアカウントが同時にサーバーに接続できる回数

知らせ：

ユーザーまたは権限を追加し、mysql> flush privileges; を使用して権限を更新します。

具体的な権限については、公式ウェブサイトのドキュメントを参照してください。

権限の提供

要約する

上記はこの記事の全内容です。この記事の内容が皆さんの勉強や仕事に一定の参考学習価値を持つことを願っています。ご質問があれば、メッセージを残してコミュニケーションしてください。123WORDPRESS.COM を応援していただきありがとうございます。

以下もご興味があるかもしれません: