MySQL 8.0 のユーザーとロールの管理原則と使用方法の詳細

この記事では、MySQL 8.0 のユーザーとロールの管理について例を挙げて説明します。ご参考までに、詳細は以下の通りです。

MySQL 8.0 では、ユーザー管理におけるロール管理など、多くの新機能が追加されています。

デフォルトのパスワード暗号化方式も sha1 から sha2 に調整され、ユーザーの無効化やユーザーの有効期限に関する 5.7 設定が追加されました。

これにより、ユーザー管理と権限管理が改善され、ユーザーのセキュリティも強化されます。

MySQL 8.0 では、MySQL ライブラリ内のテーブルのファイルがデータ ルート ディレクトリの mysql.ibd にマージされます (MySQL 8.0 Innodb エンジンの再構築)。

同時に、MySQL 8.0 では SET PERSIST を使用してパラメータを動的に変更し、構成ファイル (mysqld-auto.cnf、JSON 文字列形式で保存) に保存できます。

これは DBA にとって朗報です。設定ファイルを保存し忘れたり、再起動後に設定を復元したりする心配がありません。

MySQL 8.0 の公式ドキュメントを参照し、公式の例を使用して新しい管理方法を確認しました。

1. MySQLユーザー管理

1.1.認証プラグインとパスワード暗号化方法の変更 MySQL 8.0 では、以前のバージョンの mysql_native_password の代わりに、caching_sha2_password がデフォルトの認証プラグインになります。

デフォルトのパスワード暗号化方法は sha2 です。

以前のバージョンの認証方法とパスワード暗号化方法を維持する必要がある場合は、構成ファイルで変更する必要があります。動的な変更はまだサポートされていないため、有効にするには再起動が必要です: default_authentication_plugin = mysql_native_password。

8.0 の既存の sha2 パスワードを sha1 モードに変更します。

ALTER USER 'root'@'127.0.0.1' IDENTIFIED BY 'passowrd' パスワードは期限切れにならない;

#暗号化ルールを期限切れにならないように変更する

ALTER USER 'root'@'127.0.0.1' を 'password' によって mysql_native_password で識別します。

#ユーザーのパスワード暗号化方式を以前のバージョンに更新する

FLUSH PRIVILEGES; #権限の更新

1.2 ユーザー認証とパスワードの変更

MySQL 8.0 のユーザー認証は、以前のバージョンとは異なります。古いバージョンの一般的な認証ステートメントは、8.0 ではエラーを報告します。

MySQL バージョン 8.0 より前:

GRANT OPTION を指定して、`passowrd' で識別される `wangwei`@`127.0.0.1` に *.* のすべての権限を許可します。

MySQL 8.0 バージョン:

# アカウントとパスワードを作成します。CREATE USER `wangwei`@`127.0.0.1` IDENTIFIED BY 'passowrd';
# 権限を付与する GRANT ALL ON *.* TO `wangwei`@`127.0.0.1` WITH GRANT OPTION;
# 権限の削除 REVOKE all privileges ON databasename.tablename FROM 'username'@'host';
# パスワードを変更する ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your password';

MySQL 8.0 での有効期限付きユーザーの作成:

  ユーザー `wangwei`@`127.0.0.1` を作成します。識別名は 'wangwei' です。パスワードの有効期限は 90 日です。

  GRANT OPTION を指定して、*.* のすべての権限を `wangwei`@`127.0.0.1` に付与します。

MySQL8.0 のユーザーパスワードの変更:

1.2. パスワード有効期限管理

自動パスワード有効期限ポリシーをグローバルに設定するには、default_password_lifetime システム変数を使用します。デフォルト値は 0 で、自動パスワード有効期限は無効になります。

default_password_lifetime の値が正の整数 N の場合、それは許可されたパスワードの有効期間を示し、パスワードは N 日ごとに変更する必要があります。設定ファイルに追加できます:

1: パスワードが約 6 か月後に期限切れになるグローバル ポリシーを確立するには、サーバーの my.cnf ファイルに次の行を追加してサーバーを起動します。

[mysqld]デフォルトパスワードの有効期限=180

2: パスワードが期限切れにならないようにグローバルポリシーを確立するには、default_password_lifetime を 0 に設定します。

[mysqld]デフォルトパスワードの有効期限=0

このパラメータは動的に設定および保存できます。

SET PERSIST default_password_lifetime = 180;SET PERSIST default_password_lifetime = 0;

パスワードの有効期限、アカウント固有の有効期限設定を持つユーザーの作成と変更の例:

90 日ごとにパスワードの変更を要求する:

ユーザー 'wangwei'@'localhost' を作成します。パスワードの有効期限は 90 日です。

ALTER USER 'wangwei'@'localhost' パスワードの有効期限を 90 日に設定します。

パスワードの有効期限を無効にする:

ユーザー 'wangwei'@'localhost' を作成します。パスワードは無期限です。

ALTER USER 'wangwei'@'localhost' パスワードの有効期限が切れないようにします。

グローバル有効期限ポリシーに従ってください:

ユーザー 'wangwei'@'localhost' を作成します。パスワードはデフォルトで期限切れです。

ALTER USER 'wangwei'@'localhost' PASSWORD EXPIRE DEFAULT; 1.3 MySQLユーザーパスワード再利用ポリシー設定

MySQL では、以前のパスワードの再利用を制限できます。再利用制限は、パスワードの変更回数、経過時間、またはその両方に基づいて設定できます。アカウントのパスワード履歴は、過去に割り当てられたパスワードで構成されます。

MySQL では、この履歴からの新しいパスワードの選択を制限できます。

1. パスワード変更回数に基づいてアカウントを制限すると、指定された回数の最新パスワードから新しいパスワードを選択することはできません。たとえば、パスワード変更の最小回数が 3 に設定されている場合、新しいパスワードは最新の 3 つのパスワードと同じにすることはできません。

2. 時間制限によりアカウントが制限されている場合、指定された日数以内の履歴から新しいパスワードを選択することはできません。たとえば、パスワードの再利用間隔が 60 に設定されている場合、新しいパスワードは過去 60 日間に選択されたパスワードの範囲内であってはなりません。

注意: 空のパスワードはパスワード履歴に記録されず、いつでも再利用できます。

パスワード再利用ポリシーをグローバルに設定するには、password_history および password_reuse_interval システム変数を使用します。サーバーの起動時に変数値を指定するには、サーバーの my.cnf ファイルで定義します。

例：

過去 6 個のパスワードまたは 365 日以上前のパスワードの再利用を禁止するには、サーバーの my.cnf ファイルに次の行を追加します。

[mysqld]パスワード履歴=6パスワード再使用間隔=365

構成を動的に設定して保存するには、次のようなステートメントを使用します。

SET PERSIST パスワード履歴 = 6;

SET PERSIST password_reuse_interval = 365;2. MySQL 8.0 でのロール管理

MySQL ロールは、名前の付いた権限のコレクションです。ユーザー アカウントと同様に、ロールにも権限を付与したり取り消したりできます。

ユーザー アカウントにロールを付与して、各ロールに関連付けられた権限をアカウントに付与することができます。ユーザーにロール権限が付与されている場合、そのユーザーにはそのロールの権限が付与されます。

次のリストは、MySQL が提供するロール管理機能をまとめたものです。

• CREATE ROLE および DROP ROLE によるロールの作成と削除。
• GRANT と REVOKE は、ユーザーとロールに権限を割り当てたり取り消したりします。
• SHOW GRANTS は、ユーザーとロールの権限とロールの割り当てを表示します。
• SET DEFAULT ROLE は、デフォルトでアクティブになるアカウント ロールを指定します。
• SET ROLE は、現在のセッションでアクティブなロールを変更します。
• CURRENT_ROLE() 関数は、現在のセッションでアクティブなロールを表示します。

2.1 ロールを作成し、ユーザーにロール権限を付与する

次のシナリオを検討してください。

• アプリケーションは app_db というデータベースを使用します。
• アプリケーションには、アプリケーションを作成および保守する開発者アカウントと管理者アカウントを関連付けることができます。
• 開発者はデータベースへのフルアクセス権が必要です。一部のユーザーには読み取り権限のみが必要ですが、他のユーザーには読み取り/書き込み権限が必要です。

ロールの権限を明確に区別するには、必要な権限セットの名前を持つロールを作成します。適切なロールを付与することで、ユーザー アカウントに必要な権限を簡単に付与できます。

ロールを作成するには、CREATE ROLE を使用します。

ロール「app_developer」、「app_read」、「app_write」を作成します。

ロール名はユーザー アカウント名と非常によく似ており、ユーザー部分とホスト部分で構成される形式です。ホスト部分を省略すると、デフォルトで % になります。ユーザー部分とホスト部分は、特殊文字が含まれていない限り引用符で囲まなくてもかまいません。アカウント名とは異なり、ロール名のユーザー部分は空にできません。ロールに権限を割り当てるには、ユーザーに権限を割り当てる場合と同じ構文を使用します。

app_db.* に 'app_developer' のすべての権限を付与します。app_db.* に 'app_read' の SELECT 権限を付与します。app_db.* に 'app_write' の INSERT、UPDATE、DELETE 権限を付与します。CREATE ROLE 'app_developer'、'app_read'、'app_write'。

ここで、最初に開発者アカウントが 1 つ、読み取り専用アクセスが必要なユーザーが 2 人、読み取り/書き込みアクセスが必要なユーザーが 1 人必要だと仮定します。

CREATEUSER を使用してユーザーを作成します。

'dev1'@'localhost' というユーザーを作成し、'dev1pass' で識別します。
'read_user1'@'localhost' というユーザーを作成し、'read_user1pass' で識別します。
'read_user2'@'localhost' というユーザーを作成し、'read_user2pass' で識別します。
'rw_user1'@'localhost' というユーザーを作成し、'rw_user1pass' で識別します。

各ユーザーに必要な権限を割り当てるには、上記と同じ形式の GRANT ステートメントを使用できますが、各ユーザーの個別の権限をリストする必要があります。

代わりに、権限ではなくロールを付与できる GRANT の代替構文を使用します。

'app_developer' を 'dev1'@'localhost' に許可します。'app_read' を 'read_user1'@'localhost'、'read_user2'@'localhost' に許可します。
'rw_user1'@'localhost' に 'app_read'、'app_write' 権限を付与します。

ロールに必要な読み取り権限と書き込み権限を組み合わせて、GRANT で rw_user1 ユーザーにロールへの読み取り権限と書き込み権限を付与します。

ロールを承認するための GRANT の構文は、ユーザーを承認するための構文とは異なります。ロール承認とユーザー承認を区別するための ON があり、ユーザー承認には ON があり、ロールの割り当てには ON がありません。

構文が異なるため、同じステートメント内でユーザー権限とロールの割り当てを混在させることはできません。 (ユーザーに権限とロールを割り当てることは許可されていますが、各ステートメントの構文が付与の構文と一致するように、個別の GRANT ステートメントを使用する必要があります。)

2.2 ロール権限を確認する

ユーザーに割り当てられた権限を確認するには、SHOW GRANTS を使用します。例えば：

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

ただし、付与された各ロールは、そのロールが表す権限としては表示されずに表示されます。ロールの権限を表示する場合は、USING を追加して表示します。

mysql> 'app_developer' を使用して 'dev1'@'localhost' の権限を表示します。

他の種類のユーザーの認証についても同様です。

mysql> 'app_read' を使用して 'read_user1'@'localhost' の権限を表示します。

2.3 ロールまたはロール権限の取り消し

ユーザーにロールを付与できるのと同様に、アカウントからロールを取り消すこともできます。

ユーザーからロールを取り消します。

REVOKE はロール権限を変更するために使用できます。これは、ロール自体の権限だけでなく、そのロールに付与されたユーザーの権限にも影響します。すべてのユーザーを一時的に読み取り専用にしたい場合は、REVOKE を使用して app_write ロールから変更権限を取り消します。

app_db.* の INSERT、UPDATE、DELETE を 'app_write' から取り消します。

偶然にも、SHOW GRANTS でわかるように、特定のロールにはまったく権限がありません (このステートメントは、ユーザーが使用できる権限を照会するだけでなく、ロールでも使用できます)。

mysql> 'app_write' の権限を表示します。

ロールから権限を取り消すと、そのロール内のすべてのユーザーの権限に影響するため、rw_user1 にはテーブル変更権限がなくなります (INSERT、UPDATE、および DELETE 権限はなくなります)。

mysql> 'rw_user1'@'localhost' の権限を 'app_read'、'app_write' を使用して表示します。

実際には、rw_user1 読み取り/書き込みユーザーは読み取り専用ユーザーになりました。これは、app_write ロールが付与されている他のユーザーにも発生し、個々のアカウントを変更することなくロールを使用して権限を変更できることを示しています。

ロールの編集権限を復元するには、権限を再度付与するだけです。

app_db.* に対する INSERT、UPDATE、DELETE 権限を 'app_write' に付与します。

これで、app_write ロールが付与された他のアカウントと同様に、rw_user1 に再度変更権限が付与されます。

2.4 ロールの削除

ロールを削除するには、DROP ROLE を使用します。

ロール 'app_read'、'app_write' を削除します。

ロールを削除すると、そのロールを付与したすべてのアカウントからそのロールが取り消されます。

2.5 役割とユーザーの実際の適用

レガシー アプリケーション開発プロジェクトが MySQL にロールが存在する前に開始され、プロジェクトに関連付けられたすべてのユーザーに権限が直接付与されていたとします (ロール権限が付与されたのではなく)。アカウントの 1 つは、次のように最初に権限が付与された開発者ユーザーです。

'old_app_dev'@'localhost' というユーザーを作成し、'old_app_devpass' で識別します。old_app.* のすべての権限を 'old_app_dev'@'localhost' に付与します。

この開発者がプロ​​ジェクトを離れる場合は、他のユーザーに権限を割り当てる必要があるかもしれません。また、プロジェクトにさらに多くの人が関与している場合は、複数のユーザーが必要になるかもしれません。この問題を解決するには、いくつかの方法があります。

• ロールを使用しない場合: 元の開発者が使用できないようにアカウントのパスワードを変更し、新しい開発者にそのアカウントを使用させます。

'old_app_dev'@'localhost' というユーザーを 'new_password' で識別するように変更します。

• ロールの使用: アカウントをロックして、誰もそのアカウントを使用してサーバーに接続できないようにします。

ALTER USER 'old_app_dev'@'localhost' アカウント ロック;

その後、アカウントはロールとして扱われます。新しいプロジェクトを開発する新しい開発者ごとに、新しいアカウントを作成し、元の開発者アカウントに付与します。

'new_password' で識別されるユーザー 'new_app_dev1'@'localhost' を作成します。'old_app_dev'@'localhost' を 'new_app_dev1'@'localhost' に付与します。

その結果、元の開発者アカウントの権限が新しいアカウントに割り当てられます。

MySQL 8.0 のユーザーとロールの管理は、ますます Oracle に似てきています。8.0 には多くの新機能があり、変更点も依然として非常に大きいです。DBA は、学習とテストを継続し、新しいバージョンの MySQL に関する理解を更新し、MySQL データベースをより適切に運用および保守する必要があります。将来的には、MySQL データベースの自律性とインテリジェント データベースは避けられない開発トレンドとなり、DBA に解放と挑戦の両方をもたらすでしょう。

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL クエリ スキル」、「MySQL トランザクション操作スキル」、「MySQL ストアド プロシージャ スキル」、「MySQL データベース ロック関連スキルの概要」、および「MySQL 共通関数の概要」

この記事が皆様のMySQLデータベース設計に役立つことを願っています。