データベース管理の世界では、データの整合性とセキュリティを維持するためにユーザー アクセスを制御することが重要です。このブログ投稿では、プロセス、潜在的な落とし穴、デバッグ手順など、MySQL ユーザー権限を設定する実際のシナリオを説明します。

シナリオ

あなたは、複数のデータベースを持つ会社のデータベース管理者であると想像してください:

• オリジナルのデータベース: 製品、顧客、注文、分析
• データベースのコピー: products_copy、customers_copy、orders_copy、analytics_copy

あなたのタスクは、「analyst」という名前のユーザーに次の要件を持つ権限を設定することです:

1. アナリストは、元のデータベースのデータベースとテーブルを表示できる必要があります。
2. アナリストは元のデータベースを編集できないようにする必要があります。
3. アナリストは新しいデータベースを作成できません。
4. アナリストには、「_copy」データベースへのフル アクセス (テーブルの表示、編集、削除、作成) が必要です。

MySQL の GRANT ステートメントと REVOKE ステートメントを使用してこれを実現する方法を詳しく見てみましょう。

ステップ 1: 初期セットアップ

まず、管理者アカウントを使用して MySQL サーバーに接続する必要があります:

mysql -h hostname -P port -u admin -p

「ホスト名」、「ポート」、「管理者」を実際のサーバーの詳細と管理者のユーザー名に置き換えます。

ステップ 2: ユーザーを作成する

ユーザーがまだ存在しない場合は、作成する必要があります:

CREATE USER 'analyst'@'%' IDENTIFIED BY 'password';

「パスワード」を強力で安全なパスワードに置き換えます。

ステップ 3: 必要な権限を付与する

次に、必要な権限を付与しましょう:

-- Grant SELECT on original databases
GRANT SELECT ON products.* TO 'analyst'@'%';
GRANT SELECT ON customers.* TO 'analyst'@'%';
GRANT SELECT ON orders.* TO 'analyst'@'%';
GRANT SELECT ON analytics.* TO 'analyst'@'%';

-- Grant all privileges on copy databases
GRANT ALL PRIVILEGES ON products_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON customers_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON orders_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON analytics_copy.* TO 'analyst'@'%';

-- Grant global privileges
GRANT PROCESS, SHOW DATABASES ON *.* TO 'analyst'@'%';

-- Apply the changes
FLUSH PRIVILEGES;

ステップ 4: 権限を確認する

権限を設定した後、それらを確認することが重要です:

SHOW GRANTS FOR 'analyst'@'%';

デバッグとトラブルシューティング

問題 1: 権限が多すぎる

このシナリオでは、最初に「アナリスト」が多すぎる権限を持っているという問題が発生しました:

mysql> SHOW GRANTS FOR 'analyst'@'%';
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
| Grants for analyst@%                                                                                                                                                                                                                                                                                                              |
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO "analyst"@"%" WITH GRANT OPTION |
| GRANT REPLICATION_APPLIER,ROLE_ADMIN ON *.* TO "analyst"@"%" WITH GRANT OPTION                                                                                                                                                                                                                                                    |
...
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 

解決：

これを修正するために、すべての権限を取り消し、必要な権限のみを付与しました:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'analyst'@'%';
GRANT PROCESS, SHOW DATABASES ON *.* TO 'analyst'@'%';
-- Then re-grant the specific permissions as shown in Step 3

問題 2: コピー データベースに対する権限の欠落

過剰な権限を修正した後、コピー データベースの権限が欠落していることに気付きました:

mysql> SHOW GRANTS FOR 'analyst'@'%';
 ----------------------------------------------------- 
| Grants for analyst@%                                |
 ----------------------------------------------------- 
| GRANT PROCESS, SHOW DATABASES ON *.* TO "analyst"@"%"|
| GRANT SELECT ON "products".* TO "analyst"@"%"       |
| GRANT SELECT ON "customers".* TO "analyst"@"%"      |
| GRANT SELECT ON "orders".* TO "analyst"@"%"         |
| GRANT SELECT ON "analytics".* TO "analyst"@"%"      |
 ----------------------------------------------------- 

解決：

コピー データベースに不足していた権限を追加しました:

GRANT ALL PRIVILEGES ON products_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON customers_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON orders_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON analytics_copy.* TO 'analyst'@'%';
FLUSH PRIVILEGES;

最終結果

これらの変更と修正をすべて適用すると、最終的な付与は次のようになります:

mysql> SHOW GRANTS FOR 'analyst'@'%';
 ----------------------------------------------------- 
| Grants for analyst@%                                |
 ----------------------------------------------------- 
| GRANT PROCESS, SHOW DATABASES ON *.* TO "analyst"@"%"|
| GRANT SELECT ON "products".* TO "analyst"@"%"       |
| GRANT SELECT ON "customers".* TO "analyst"@"%"      |
| GRANT SELECT ON "orders".* TO "analyst"@"%"         |
| GRANT SELECT ON "analytics".* TO "analyst"@"%"      |
| GRANT ALL PRIVILEGES ON "products_copy".* TO "analyst"@"%"|
| GRANT ALL PRIVILEGES ON "customers_copy".* TO "analyst"@"%"|
| GRANT ALL PRIVILEGES ON "orders_copy".* TO "analyst"@"%"|
| GRANT ALL PRIVILEGES ON "analytics_copy".* TO "analyst"@"%"|
 ----------------------------------------------------- 

結論

MySQL ユーザー権限を適切に設定するのは難しい場合がありますが、データベース管理の重要な側面です。 GRANT ステートメントと REVOKE ステートメントを慎重に使用し、結果を常に検証することで、ユーザーにとって安全で機能的な環境を作成できます。

次の重要なポイントを覚えておいてください:

1. 常に最小特権の原則から始めてください。
2. 変更を加えた後に SHOW GRANTS を使用して権限を確認します。
3. グローバル権限に注意してください (ON .)。
4. 変更を加えた後は、忘れずに特権をフラッシュしてください。

これらのガイドラインとこの投稿で概説されている手順に従うことで、MySQL ユーザー権限を効果的に管理するための準備が整います。