MySQL のユーザー管理とセキュリティ権限を学ぶ | 実践的な実験

はじめに

この実験では、MariaDB のユーザー管理と権限について学びます。これは、データベースシステムをセキュアにするための重要なスキルです。ユーザーアカウントの作成と管理、適切な権限の割り当て、およびパーミッションの表示方法を学びます。実践的な演習を通じて、これらの重要なデータベースセキュリティ操作に関する実際の経験を得て、MariaDB における基本的なセキュリティ対策の実装方法を理解します。この知識は、適切なアクセス制御とデータ保護を確保するための、すべてのデータベース管理者にとって不可欠です。

ユーザーアカウントの作成と管理

まず、root として MySQL に接続します。

sudo mysql -u root

ユーザーの作成

異なるアクセスパターンのユーザーを作成しましょう。

-- ローカルホストからのみ接続できるユーザーを作成
CREATE USER 'hr_user'@'localhost' IDENTIFIED BY 'hr123';

-- 任意のホストから接続できるユーザーを作成
CREATE USER'reports_user'@'%' IDENTIFIED BY'reports123';

-- パスワードなしのユーザーを作成（本番環境では推奨されません）
CREATE USER 'intern'@'localhost';

@'localhost' は接続をローカルマシンのみに制限し、@'%' は任意のホストからの接続を許可します。パスワードなしでユーザーを作成することは、本番環境では推奨されません。

作成したユーザーを確認するには：

SELECT User, Host FROM mysql.user;

期待される出力は、システム内のすべてのユーザーを表示します。

+--------------+-----------+
| User         | Host      |
+--------------+-----------+
| reports_user | %         |
| hr_user      | localhost |
| intern       | localhost |
| mariadb.sys  | localhost |
| mysql        | localhost |
| root         | localhost |
+--------------+-----------+

既存のユーザーの管理

既存のユーザーを変更するには：

-- ユーザーのパスワードを変更
SET PASSWORD FOR 'intern'@'localhost' = PASSWORD('intern123');

-- 次のログイン時にユーザーにパスワードの変更を求める
ALTER USER 'hr_user'@'localhost' PASSWORD EXPIRE;

PASSWORD EXPIRE オプションは、ユーザーに次のログイン時にパスワードを変更するように強制します。一時的なパスワードに便利です。

ユーザーを削除するには：

-- ユーザーを削除
DROP USER'reports_user'@'%';

ユーザーを削除した後、ユーザーリストが更新されていることを確認します。

+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| hr_user     | localhost |
| intern      | localhost |
| mariadb.sys | localhost |
| mysql       | localhost |
| root        | localhost |
+-------------+-----------+

権限の付与

これで、ユーザーが何をできるかを制御するために、さまざまな種類の権限を割り当てる方法を学びましょう。

基本的な権限管理

HR ユーザーにいくつかの基本的な権限を付与しましょう。

-- company_db.employees テーブルに対する SELECT と INSERT の権限を付与
GRANT SELECT, INSERT ON company_db.employees TO 'hr_user'@'localhost';

-- 権限を確認
SHOW GRANTS FOR 'hr_user'@'localhost';

GRANT コマンドは、特定のデータベースオブジェクトに対する特定の許可（SELECT、INSERT）をユーザーに与えます。SHOW GRANTS コマンドは、現在の権限を表示します。

期待される出力：

+----------------------------------------------------------------------------------------------------------------+
| Grants for hr_user@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `hr_user`@`localhost` IDENTIFIED BY PASSWORD '*27DB7FC6D35D1F5E6A4C37473D9018DD46DC2944' |
| GRANT SELECT, INSERT ON `company_db`.`employees` TO `hr_user`@`localhost`                                      |
+----------------------------------------------------------------------------------------------------------------+

異なる権限レベル

MariaDB は、さまざまな粒度の権限をサポートしています。

-- グローバル権限（すべてのデータベース）
GRANT CREATE USER ON *.* TO 'intern'@'localhost';

-- データベースレベルの権限
GRANT ALL PRIVILEGES ON company_db.* TO 'hr_user'@'localhost';

-- テーブルレベルの権限
GRANT SELECT, UPDATE ON company_db.employees TO 'intern'@'localhost';

-- 列レベルの権限
GRANT SELECT (name, department), UPDATE (department)
ON company_db.employees TO 'intern'@'localhost';

*.* 構文はすべてのデータベースとテーブルを指し、database.* は特定のデータベース内のすべてのテーブルを指します。列レベルの権限により、細かいアクセス制御が可能になります。

権限の取り消し

権限を削除するには：

-- 特定の権限を取り消す
REVOKE INSERT ON company_db.employees FROM 'hr_user'@'localhost';

-- すべての権限を取り消す
REVOKE ALL PRIVILEGES ON company_db.employees FROM 'intern'@'localhost';

権限を取り消した後は、常に SHOW GRANTS を使用して権限の変更を確認してください。

MariaDB [(none)]> SHOW GRANTS FOR 'hr_user'@'localhost';
+----------------------------------------------------------------------------------------------------------------+
| Grants for hr_user@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `hr_user`@`localhost` IDENTIFIED BY PASSWORD '*27DB7FC6D35D1F5E6A4C37473D9018DD46DC2944' |
| GRANT ALL PRIVILEGES ON `company_db`.* TO `hr_user`@`localhost`                                                |
| GRANT SELECT ON `company_db`.`employees` TO `hr_user`@`localhost`                                              |
+----------------------------------------------------------------------------------------------------------------+

MariaDB [(none)]> SHOW GRANTS FOR 'intern'@'localhost';
+---------------------------------------------------------------------------------------------------------------+
| Grants for intern@localhost                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `intern`@`localhost` IDENTIFIED BY PASSWORD '*A381F474A9DDCAA5BC97DAB83A69431B1C8D8902' |
+---------------------------------------------------------------------------------------------------------------+