MySQL ユーザー権限管理の実装

1. MySQL の権限の概要

MySQL には、権限を制御する 4 つのテーブルがあります。user分別為user表，db表，tables_priv表，columns_priv表。現在のバージョンは MySQL 5.7.22 です。

mysql 権限テーブルの検証プロセスは次のとおりです。

1. まず、ユーザー テーブルの 3 つのフィールド (ホスト、ユーザー、パスワード) から、接続された IP、ユーザー名、パスワードが存在するかどうかを判断します。存在する場合、検証は合格です。
2. ID 認証後、ユーザー、db、tables_priv、columns_priv の順序で権限が割り当てられ、検証されます。つまり、まずグローバル権限テーブル user をチェックします。user 内の対応する権限が Y の場合、すべてのデータベースに対するこのユーザーの権限は Y となり、db、tables_priv、columns_priv はチェックされなくなります。N の場合は、db テーブル内のこのユーザーに対応する特定のデータベースをチェックし、db 内の権限 Y を取得します。db が N の場合は、tables_priv 内のこのデータベースに対応する特定のテーブルをチェックし、テーブル内の権限 Y を取得します。以下同様に続きます。

1.1 MySQL 権限レベル

次のように分けられます:
グローバル管理権限: MySQL インスタンス レベル全体に適用されます。データベース レベルの権限: 特定のデータベースまたはすべてのデータベースに適用されます。データベース オブジェクト レベルの権限: 特定のデータベース オブジェクト (テーブル、ビューなど) またはすべてのデータベース オブジェクトに適用されます。

権限は、mysql データベースの user、db、tables_priv、columns_priv、および procs_priv システム テーブルに保存され、MySQL インスタンスの起動後にメモリにロードされます。

mysql にどのユーザーがいるかを確認します。

mysql> mysql.user からユーザー、ホストを選択します。

root 用戶在權限系統表中的數據を見てみましょう。

mysql> mysql を使用します。
mysql> select * from user where user='root' and host='localhost'\G; #すべての権限が Y であるため、すべての権限が使用可能ですmysql> select * from db where user='root' and host='localhost'\G; #そのようなレコードはありませんmysql> select * from tables_priv where user='root' and host='localhost'; #そのようなレコードはありませんmysql> select * from columns_priv where user='root' and host='localhost'; #そのようなレコードはありませんmysql> select * from procs_priv where user='root' and host='localhost'; #そのようなレコードはありません

前述の通り、権限の検証プロセス

root@'localhost' ユーザーの権限を確認します

mysql> root@localhost の権限を表示します。
+---------------------------------------------------------------------+
| root@localhost への権限付与 |
+---------------------------------------------------------------------+
| GRANT オプションを使用して、*.* のすべての権限を 'root'@'localhost' に付与します |
| GRANT オプションを使用して ''@'' のプロキシを 'root'@'localhost' に許可します |
+---------------------------------------------------------------------+
セット内の 2 行 (0.00 秒)

2. MySQLの権限の詳細な説明

All/All Privileges權限代表全局或者全數據庫對象級別的所有權限

Alter 権限を使用するとテーブル構造を変更できますが、Create 権限と Insert 権限も必要になります。テーブルの名前を変更する場合は、元のテーブルを変更および削除し、新しいテーブルを作成して挿入する権限が必要です。

Alter ルーチン権限を使用すると、ストアド プロシージャおよび関数を変更または削除できます。

作成権限を使用すると、新しいデータベースとテーブルを作成できます。

ルーチンの作成権限を使用すると、ストアド プロシージャと関数を作成できます。

表領域の作成権限を使用すると、表領域とログ グループを作成、変更、および削除できます。

一時テーブルの作成権限により、一時テーブルを作成できます。

ユーザーの作成権限により、ユーザーの作成、変更、削除、名前変更が可能になります。

ビューの作成権限は、ビューを作成する権限を表します。

削除権限を使用すると、行データを削除できます。

Drop 権限を使用すると、truncate table コマンドを含む、データベース、テーブル、ビューを削除できます。

イベント権限を使用すると、MySQL イベントを照会、作成、変更、削除できます。

実行権限により、ストアド プロシージャと関数を実行できます。

ファイル権限により、MySQL がアクセス可能なディレクトリ内のディスク ファイルの読み取りと書き込みが可能になります。使用可能なコマンドには、load data infile、select ... into outfile、load file() 関数などがあります。

付与オプション権限は、他のユーザーに付与した権限をこのユーザーが付与または取り消すことを許可するかどうかを示します。この権限は、管理者に権限を再付与するときに必要です。

インデックス権限は、インデックスの作成と削除を許可するかどうかを示します。

挿入権限は、テーブルにデータを挿入できるかどうかを示します。挿入権限は、Analyze Table、Optimize Table、および Repair Table ステートメントを実行する場合にも必要です。

ロック権限を使用すると、選択権限でテーブルをロックして、他のリンクがこのテーブルを読み取ったり書き込んだりできないようにすることができます。

プロセス権限を使用すると、show processlist、mysqladmin processlist、show engine などのコマンドを実行して、MySQL のプロセス情報を表示できます。

参照権限はバージョン 5.7.6 以降で導入され、外部キーの作成を許可するかどうかを示します。

Reload 権限により、flush コマンドの実行が許可され、権限テーブルがシステム メモリに再ロードされます。refresh コマンドは、ログ ファイルを閉じて再度開き、すべてのテーブルを更新することを意味します。

レプリケーションクライアント権限により、show master status、show slave status、show binary logsコマンドを実行できます。

レプリケーションスレーブ権限とは、マスタースレーブレプリケーション関係を確立するために、スレーブホストがこのユーザーを介してマスターに接続できることを意味します。

Select 権限を使用すると、テーブルからデータを表示できます。Select 1+1 や Select PI()+2 など、テーブル データをクエリしない一部の Select ステートメントでは、この権限は必要ありません。また、WHERE 条件を含む更新/削除ステートメントを実行する場合にも、Select 権限が必要です。

データベースの表示権限は、show databases コマンドを実行してすべてのデータベース名を表示できることを意味します。

ビューの表示権限を使用すると、show create view コマンドを実行してビューを作成するステートメントを表示できます。

シャットダウン権限では、データベースインスタンスをシャットダウンできます。実行されるステートメントには、mysqladminshutdownが含まれます。

スーパー権限を使用すると、接続を強制的に閉じる kill コマンド、レプリケーション関係を作成するための change master コマンド、サーバーの作成/変更/削除コマンドなど、一連のデータベース管理コマンドを実行できます。

トリガー権限を使用すると、トリガーを作成、削除、実行、および表示できます。

更新権限により、テーブル内のデータを変更できます。

使用権限は、ユーザー作成後のデフォルトの権限であり、それ自体が接続ログイン権限を表します。

2.1 システム権限テーブル

User表: ユーザーアカウント情報とグローバルレベル (すべてのデータベース) の権限を格納します。これにより、どのホストのどのユーザーがデータベースインスタンスにアクセスできるかが決まります。有全局權限則意味著對所有數據庫都有此權限
Db テーブル:數據庫級別権限を格納します。これにより、どのホストのどのユーザーがこのデータベースにアクセスできるかが決まります。
Tables_priv テーブル:存放表級別的權限。これにより、どのホストのどのユーザーがデータベース内のこのテーブルにアクセスできるかが決まります。
Columns_priv テーブル:存放列級別的權限。これにより、どのホストのどのユーザーがデータベース テーブル内のこのフィールドにアクセスできるかが決まります。
Procs_priv テーブル:存放存儲過程和函數

最も重要なのはユーザーテーブルです

2.1.1 ユーザーとDB権限テーブルの構造

ユーザー権限テーブル構造の特別なフィールド:

• プラグインのauthentication_stringフィールドにはユーザー認証情報が保存されます
• Password_expired が 'Y' に設定されている場合、DBA はこのユーザーのパスワードを期限切れに設定し、期限切れ後にユーザーにパスワードのリセットを要求することができます (パスワードをリセットするには、alter user/set password を実行します)。
• Password_last_changed は、パスワードが最後に変更された時刻を表すタイムスタンプ フィールドです。此數值自動更新。
• Password_lifetime は、password_last_changed 時刻からパスワードの有効期限が切れるまでの日数を表します。
• Account_locked代表此用戶被鎖住，無法使用

MySQL 5.7 より前では、ユーザー テーブルにパスワード フィールドがありました。

2.1.2 Tables_priv および columns_priv 権限テーブル構造

Tables_priv および columns_priv 権限値

2.1.3 procs_priv 権限テーブル構造

• Routine_typeは、ストアドプロシージャか関数かを表す列挙型です。
• タイムスタンプおよび付与者フィールドは現時点では使用されません。

システム権限テーブルフィールド長制限テーブル

認可認証における大文字と小文字の区別

• フィールドuser、password、authencation_string、db、table_nameは大文字と小文字が区別されます
• フィールド host、column_name、routine_name は大文字と小文字を区別しません

2.2 ユーザー権利情報管理

2.2.1 ユーザー権限情報の表示

MYSQLのユーザーを確認する

mysql> mysql.user からユーザー、ホストを選択します。

ユーザーに付与されている権限（rootなど）を表示します。

mysql> root@'localhost' の権限を表示します。

ユーザーのその他の不正な情報を表示する

mysql> show create user root@'localhost'; 

2.2.2 ユーザー構成

MySQLの承認済みユーザーは用戶名和登錄主機名の2つの部分で構成されます。

• ユーザーを表す構文は 'user_name'@'host_name' です。
• 一重引用符はオプションですが、包含特殊字符則是必須的
• 「@'localhost'は匿名ログインユーザーを表します
• Host_name にはホスト名または IPv4/IPv6 アドレスを指定できます。 Localhost はローカルマシンを表し、127.0.0.1 はローカル IPv4 アドレスを表し、::1 はローカル IPv6 アドレスを表します。
• Host_nameフィールドでは%和_マッチング文字を使用できます。たとえば、「%」はすべてのホストを表し、「%.mysql.com」は

mysql.com ドメインのすべてのホスト。「192.168.1.%」は、192.168.1 ネットワーク セグメントのすべてのホストを表します。

2.2.3 ユーザー権限の変更

権限を変更するために Grant、revoke、set password、または rename user を実行すると、MySQL は変更された権限情報を自動的にシステム メモリにロードします。

システム権限テーブルを挿入/更新/削除する場合は、システムメモリと同期するために、refresh privilege コマンドを実行する必要があります。refresh privilege コマンドには、 flush privileges /mysqladmin flush-privileges / mysqladmin reload があります。

テーブルおよび列レベルで権限を変更すると、クライアントが次回操作されたときに新しい権限が有効になります。

データベース レベルの権限を変更すると、クライアントが use database コマンドを実行した後に新しい権限が有効になります。

グローバル レベルの権限を変更する場合は、新しい権限を有効にするために接続を再作成する必要があります。

グローバルレベルの権限を変更する場合は、新しい権限を有効にするために接続を再作成する必要があります（たとえば、パスワードの変更）。

2.2.4 mysqlユーザーを作成する

MySQLの承認済みユーザーを作成するには2つの方法があります

• 執行create user/grant命令（推奨方法）
• 挿入文を通じてMySQLシステム権限テーブルを直接操作する

# finley を作成します。これはユーザーを作成するだけで、権限はありません。mysql> CREATE USER 'finley'@'localhost' IDENTIFIED BY 'some_pass';
# finleyを管理者ユーザーにするmysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'localhost' WITH
グラントオプション;
# ユーザーを作成し、すべてのライブラリとテーブルに対する RELOAD,PROCESS 権限を付与しますmysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost' identified by '123456';
 
# テスト データベースと temp テーブルの id 列に対する選択権限のみを持つユーザー keme を作成します。mysql> grant select(id) on test.temp to keme@'localhost' identified by '123456';

2.2.4 mysql権限の回復

revoke コマンドを使用してユーザーの権限を取り消します。取り消すときは、ユーザーが持っている権限を確認してから取り消します。管理者ユーザーをテストします。

mysql> admin@'localhost' の権限を表示します。
mysql> mysql.user からユーザー、ホストを選択します。
mysql> admin@'localhost' から *.* のプロセスを取り消します。 

2.2.5 MySQLユーザーの削除

drop userコマンドを実行して、MySQL ユーザーを削除します。システム権限テーブルから削除することもできます (推奨されません)。

mysql> ユーザー admin@'localhost' を削除します。

2.2.6 MySQLユーザーリソース制限の設定

グローバル変数 max_user_connections を設定すると、すべてのユーザーが同時に MySQL インスタンスに接続できる接続数を制限できます。ただし、このパラメータを使用して各ユーザーを別々に扱うことはできないため、MySQL ではユーザーごとにリソース制限管理が提供されます。

MAX_QUERIES_PER_HOUR: ユーザーが 1 時間に実行できるクエリの数 (基本的にすべてのステートメントを含む)

MAX_UPDATES_PER_HOUR: ユーザーが 1 時間内に変更を実行できる回数 (データベースまたはテーブルを変更するステートメントのみが含まれます)

MAX_CONNECTIONS_PER_HOUR: ユーザーが1時間にMySQLに接続できる回数

MAX_USER_CONNECTIONS:同一時間連接MySQL實例的數量

バージョン 5.0.3 以降、ユーザー 'user'@'%.example.com' のリソース制限は、それぞれ host1.example.com と host2.example.com からの接続ではなく、example.com ドメイン ホストからユーザー user へのすべての接続を参照します。

2.2.7 mysqlユーザーのパスワードを変更する

ユーザー パスワードを変更する方法は次のとおりです。

mysql> ALTER USER 'jeffrey'@'localhost' を 'mypass' によって識別します。
mysql> 'jeffrey'@'localhost' のパスワードを設定 = PASSWORD('mypass');
mysql> 'mypass' によって識別される 'jeffrey'@'localhost' に *.* の使用権限を付与します。
シェル> mysqladmin -u ユーザー名 -h ホスト名 パスワード "新しいパスワード"

ユーザー作成時にパスワードを指定する

mysql> 'mypass' によって識別される 'jeffrey'@'localhost' ユーザーを作成します。

現在のセッションのユーザー パスワードを変更する方法は次のとおりです。

mysql> ALTER USER USER() は 'mypass' によって識別されます。
mysql> パスワードの設定 = PASSWORD('mypass');

2.2.8 MySQLユーザーパスワードの有効期限ポリシーを設定する

システムパラメータdefault_password_lifetimeをすべてのユーザーアカウントに適用するように設定します

• default_password_lifetime=180 有効期限を180日に設定する
• default_password_lifetime=0 パスワードを期限切れにしないように設定する

各ユーザーにパスワード有効期限ポリシーが設定されている場合は、上記のシステムパラメータが上書きされます。

ALTER USER 'jeffrey'@'localhost' パスワードの有効期限を 90 日に設定します。
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER; パスワードは期限切れにならない ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT; デフォルトの有効期限ポリシー

ユーザーのパスワードを手動で強制的に期限切れにする

ALTER USER 'jeffrey'@'localhost' のパスワードが期限切れになります。

2.2.9 mysql ユーザーロック

アカウントのロック/ロック解除句を指定したcreate user/alter userコマンドを実行して、ユーザーのロック状態を設定します。

ユーザー作成ステートメントのデフォルトのユーザー ステータスはロック解除されています。

# 作成時にユーザー mysql をロックします> create user abc2@localhost identified by 'mysql' account lock;

デフォルトでは、Alter userステートメントはユーザーのロック/ロック解除ステータスを変更しません。

# ロックを解除するユーザーを変更する
mysql> ユーザー abc2@'localhost' アカウントのロックを解除します。

クライアントがロック状態のユーザーを使用して MySQL にログインすると、次のエラー メッセージが表示されます。
ユーザー 'user_name'@'host_name' のアクセスが拒否されました。
アカウントがロックされています。

公式ドキュメント: https://dev.mysql.com/doc/refman/5.7/en/privilege-system.html

これで、MySQL ユーザー権限管理の実装に関するこの記事は終了です。MySQL ユーザー権限管理に関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。