MySQL 権限制御の詳細分析

今日は日曜日です。朝から少し怠けて、少し遅く起きました。昼は何もすることがなかったので、MySQL の権限制御モジュールをもう一度見直しました。改めて振り返ってみると、まだまだたくさんの細かい点がわかってきました。後で見直すためにここに記録しておきます。
権限の内容については、3月11日の前回の記事でいくつか書きました。今日は、詳細なナレッジポイントを使用して、それらについて1つずつ書きます（この記事で使用したMySQLバージョンは5.7.16です）。これらのナレッジポイントを書く前に、まずMySQLの権限制御の粒度を紹介し、次にクライアントがMySQLでリクエストを開始したときにサーバーが行う検証作業を理解します。まず、権限制御の粒度を見てみましょう。

1. グローバルレベル

グローバル権限は、特定のサーバー内のすべてのデータベースに適用されます。これらの権限は、mysql.user テーブルに保存され、grant all on *.* および revoke all on *.* メソッドを使用して付与または取り消されます。

2. データベースレベル

データベース権限は、テーブルオブジェクトやストアドプロシージャなど、特定のデータベース内のすべてのオブジェクトに適用されます。これらの権限は、mysql.db テーブルに保存されます。データベース権限は、grant all on db_name.* または対応する revoke メソッドを使用して付与および取り消すことができます。

3. 表面レベル

テーブル権限は、特定のテーブル内のすべての列に適用されます。これらの権限は、mysql tables_priv テーブルに格納されます。権限は通常、grant all on db_name.tbl_name および対応する revoke ステートメントを使用して付与または取り消されます。

4. 列レベルの権限

列レベルの権限は、特定のテーブル内の指定された列に適用されます。これらの権限は、mysql.columns_priv テーブルに格納されます。この権限は一般的に使用されないため、次のように付与する方法の例を示します。

まず、yeyztest.test1 テーブルの選択権限を持つユーザーを作成します。

mysql:mysql 19:35:38>>dba_yeyz@'192.168.18.%' の権限を表示します。
+--------------------------------------------------------------------------------+
| [email protected].% への許可 |
+--------------------------------------------------------------------------------+
| 'dba_yeyz'@'192.168.18.%' に *.* の使用権限を付与 |
| 'dba_yeyz'@'192.168.18.%' に `yeyztest`.`test1` に対する SELECT 権限を付与します |
+--------------------------------------------------------------------------------+
セット内の 2 行 (0.00 秒)

次に、test1 のフィールド id を更新すると、結果は次のようになります。

mysql> test1 から * を選択します。
+---------------+
|id|
+---------------+
| 22 |
| 3333333333333 |
+---------------+
セット内の 2 行 (0.00 秒)

mysql> test1 を更新し、id=2 を設定します (id=22 の場合)。
エラー 1142 (42000): テーブル 'test1' に対するユーザー 'dba_yeyz'@'192.168.18.**' への UPDATE コマンドが拒否されました

もちろん、更新することはできません。この時点で、root アカウントを使用して dba_yeyz アカウントに id 列の権限を付与し、その結果を確認します。

mysql:mysql 19:38:38>>dba_yeyz@'192.168.18.%' の権限を表示します。
+--------------------------------------------------------------------------------+
| [email protected].% への許可 |
+--------------------------------------------------------------------------------+
| 'dba_yeyz'@'192.168.18.%' に *.* の使用権限を付与 |
| 'dba_yeyz'@'192.168.18.%' に `yeyztest`.`test1` に対する SELECT 権限を付与します |
| `yeyztest`.`test1` に対する SELECT、UPDATE (id) 権限を 'dba_yeyz'@'192.168.18.%' に付与します |
+--------------------------------------------------------------------------------+
セット内の 3 行 (0.00 秒)

ここでは、フィールドに権限を追加するステートメントに注意する必要があります。

yeyztest.test1 の更新 (id) を XXXXXX に許可する

つまり、権限の後にフィールド名を続けます。

この時点で、columns_priv テーブルをクエリすると、レコードは次のようになります。

mysql:mysql 19:39:46>>columns_privから*を選択します。
+--------------+-----------+-----------+-------------+-------------+-----------------------------+
| ホスト | Db | ユーザー | テーブル名 | 列名 | タイムスタンプ | 列権限 |
+--------------+-----------+-----------+-------------+-------------+-----------------------------+
| 192.168.18.% | yeyztest | dba_yeyz | test1 | id | 0000-00-00 00:00:00 | 更新 |
+--------------+-----------+-----------+-------------+-------------+-----------------------------+
セット内の 1 行 (0.00 秒)

dba_yeyz を使用して更新操作を再度実行すると、結果を確認できます。

mysql> test1 を更新し、id=2 を設定します (id=22 の場合)。
クエリは正常、1 行が影響を受けました (0.00 秒)
一致した行: 1 変更された行: 1 警告: 0

mysql> test1 から * を選択します。
+---------------+
|id|
+---------------+
| 2 |
| 3333333333333 |
+---------------+
セット内の 2 行 (0.00 秒)

id=22 の列が id=2 に正常に変更されました。

5. サブルーチンレベル

ルーチンの作成、ルーチンの変更、実行、および付与権限は、ストアドルーチンに適用されます。これらの権限は、グローバルレベルとデータベースレベルで付与でき、mysql.procs_priv に保存できます。

クライアントがリクエストを開始すると、MySQL サーバーがリクエストを検証するフローチャートは次のようになります。

皆さんのお役に立てればと思い、今日見た詳細の一部を一つずつリストアップします。

1. MySQL バージョン 5.7.16 では、mysql システムライブラリにホストテーブルがありません。権限制御に関連するテーブルは、user、db、table_priv、proc_priv、column_priv の 5 つだけです。

2. mysql.user テーブルの主キーは、ユーザーとホストで構成されています。テーブル構造を見てみましょう。

[email protected]:mysql 19:44:56>>テーブル mysql.user\G の作成を表示
************************** 1. 行 ****************************
    テーブル: ユーザー
テーブルの作成: CREATE TABLE `user` (
 `ホスト` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
 `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT ''
---------------承認フィールド（29）--------------
 `Select_priv` enum('N','Y') 文字セット utf8 NOT NULL デフォルト 'N',
 ......ここでは省略----------------------------セキュリティ分野（4）--------------
 `ssl_type` enum('','ANY','X509','SPECIFIED') 文字セット utf8 NOT NULL DEFAULT '',
 `ssl_cipher` BLOB が NULL ではありません。
 `x509_issuer` BLOB NULLではありません。
 `x509_subject` BLOB NULLではありません。
---------------リソース制御フィールド（4）--------------
 `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
 `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
 `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
 `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
--------------プラグインフィールド（1項目）---------------
 `plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password',
--------------パスワードフィールド（5）--------------
 `authentication_string` テキスト COLLATE utf8_bin、
 `password_expired` enum('N','Y') 文字セット utf8 NOT NULL デフォルト 'N',
 `password_last_changed` タイムスタンプ NULL デフォルト NULL、
 `password_lifetime` smallint(5) unsigned DEFAULT NULL,
 `account_locked` enum('N','Y') 文字セット utf8 NOT NULL デフォルト 'N',
 主キー (`ホスト`,`ユーザー`) ------------- 結合主キー、ホストが先 ------------
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='ユーザーとグローバル権限'
セット内の 1 行 (0.00 秒)

3. tables_priv テーブルには、次の権限、つまりテーブルに対する権限のみがあります。

選択、挿入、更新、削除、ドロップ、作成、変更、許可、参照、インデックス、ビューの作成、ビューの表示、トリガー。

columns_privテーブルの権限は、選択、挿入、更新、参照の4つのみです。

4. 一般的に、ユーザーのパスワードを変更する方法はいくつかあります。

user@host のパスワードを設定します = password('newpassword');
mysql.user を更新し、authentication_string=password('pwd') を設定します。ここで、user='username'、host='hostname' です。
'newpassword' で識別されるユーザー user@host を変更します。
mysqladmin -u ユーザー名 -h ホスト名 -p パスワード "新しいパスワード";

最善の方法は、alter user メソッドを使用することです。実際、新しいバージョン 8.0 では、set password メソッドは使用できなくなったため、alter user メソッドを使用して新しいパスワードを設定することをお勧めします。

さらに、一般的には使用されていない別の方法として、grant メソッドを使用して以前のパスワードを上書きする方法があります。ここでは、単純に実験して効果を確認します。

mysql:mysql 20:01:05>> '111111' で識別される 'dba_yeyz'@'192.168.18.%' に `yeyztest`.`test` の SELECT 権限を許可します。
クエリは正常、影響を受けた行は 0 行、警告は 1 件 (0.00 秒)
mysql:mysql 20:01:29>>mysql.user から user、host、concat(user、'@'、'\'',host、'\'')、authentication_string を選択します。
+------------------+----------------+-----------------------------------+------------------------------------------+
| ユーザー | ホスト | concat(user,'@','\'',host,'\'') | 認証文字列 |
+------------------+----------------+-----------------------------------+------------------------------------------+
| dba_yeyz | 192.168.18.% | dba_yeyz@'192.168.18.%' | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
+------------------+----------------+-----------------------------------+------------------------------------------+
セット内の行数は 11 です (0.00 秒)

mysql:mysql 20:01:31>> '123456' によって識別される 'dba_yeyz'@'192.168.18.%' に `yeyztest`.`test` の SELECT 権限を許可します。
クエリは正常、影響を受けた行は 0 行、警告は 1 件 (0.00 秒)

mysql:mysql 20:01:57>>mysql.user から user、host、concat(user、'@'、'\'',host、'\'')、authentication_string を選択します。
+------------------+----------------+-----------------------------------+------------------------------------------+
| ユーザー | ホスト | concat(user,'@','\'',host,'\'') | 認証文字列 |
+------------------+----------------+-----------------------------------+------------------------------------------+
| dba_yeyz | 192.168.18.% | dba_yeyz@'192.168.18.%' | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------------------+----------------+-----------------------------------+------------------------------------------+
セット内の 1 行 (0.00 秒)

上記のテストからわかるように、grant メソッドを使用して特定のユーザーのパスワードをリセットすると、以前のパスワードが上書きされるため、この操作はオンラインでは注意して使用する必要があります。 grant するたびに既存のアカウントがあるかどうかを確認し、既存のアカウントがないことを確認してから grant してください。

5. 誤って MySQL のルートパスワードを忘れた場合は、MySQL サービスを再起動し、パラメータ --skip-grant-tables を追加して MySQL サービスを起動できます。これにより、権限テーブルでのマッチング作業が直接回避され、MySQL サービスに直接ログインしてルートアカウントのパスワードを変更できます。

6. mysql.user テーブルでアカウントを作成したりパスワードを変更したりするために update または insert を使用する場合は、ステートメントの実行後に権限のフラッシュ操作を使用して権限テーブルを更新する必要があります。そうしないと、操作は有効になりません。

7. mysqladminツールの実行に影響する権限がいくつかあります。

リロード権限: フラッシュ操作に影響します

シャットダウン権限: シャットダウン操作に影響します

プロセス権限: プロセスリスト操作に影響します

super権限: kill操作に影響します

8. 前述のmysql.userテーブルのリソース制御フィールドは

max_questions: 1 時間あたりのリクエストの最大数、max_updates: 1 時間あたりの更新の最大数、max_connections: 1 時間あたりの接続の最大数、max_user_connections: 1 人のユーザーが同時に確立できる接続の最大数。

このパラメータをユーザーに対して設定する場合は、次の SQL を使用して設定できます。

mysql:mysql 20:01:58>> 'dba_yeyz'@'192.168.18.%' に対して `yeyztest`.`test` の SELECT 権限を付与し、max_queries_per_hour を 1000 に設定します。
クエリは正常、影響を受けた行は 0 行、警告は 1 件 (0.00 秒)

mysql:mysql 20:13:13>>mysql.user から user、host、max_questions を選択します (user='dba_yeyz' の場合)。  
+----------+--------------+---------------+
| ユーザー | ホスト | 最大質問数 |
+----------+--------------+---------------+
| dba_yeyz | 192.168.18.% | 1000 |
+----------+--------------+---------------+
セット内の 1 行 (0.00 秒)

ここで、 grant ステートメントでは with オプションが使用されていることに注意してください。 with に続くことができるオプションは 5 つあります。

付与オプション: 承認されたユーザーは、他のユーザーにこれらの権限を付与できます。

max_queries_per_hour count: 1 時間ごとに実行できるクエリの数。

max_updates_per_hour count: カウント更新は 1 時間ごとに実行できます。

max_connections_per_hour count: 1 時間ごとに確立できる接続数をカウントします。

max_user_connections count: 1人のユーザーが同時に確立できる接続数を設定します

9. グローバル変数を設定します。

グローバル default_password_lifetime を 180 に設定します。

グローバル default_password_lifetime を 0 に設定します。

パスワードのライフサイクルは 6 か月に設定でき、その後は無効になります。0 に設定すると、永久に有効になります。

もちろん、ユーザーを作成するときに、パスワード変更サイクルを指定したり、パスワード変更サイクルを無効にしたりすることもできます。

ユーザー 'jeffrey'@'localhost' を作成します。パスワードの有効期限は 90 日です。
ALTER USER 'jeffrey'@'localhost' パスワードの有効期限を 90 日に設定します。

ユーザー 'jeffrey'@'localhost' を作成します。パスワードは無期限です。
ALTER USER 'jeffrey'@'localhost' パスワードの有効期限が切れないようにします。

10. アカウントのパスワードを削除したように見える場合もありますが、アカウントのパスワードでアクセスできる場合があります。この場合は、ユーザーテーブルに空のレコードがあるかどうかを確認する設定を確認する必要があります。

mysql.user から user,host を選択します (user='';)

ユーザーレコードを空に設定している可能性が高く、その場合、すべてのユーザーが直接ログインできます。もしある場合は、セキュリティの目的に反するため、直接削除するのが最善です。

上記はMySQLの権限制御の詳細分析です。MySQLの権限制御の詳細については、123WORDPRESS.COMの他の関連記事に注目してください。

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