MySQLのユーザー作成とGRANT権限付与の実務手順｜SHOW GRANTSで確認・REVOKEで剥奪まで

この記事のポイント

・ CREATE USER ～ GRANT ～ FLUSH PRIVILEGES 不要（MySQL 8.0）の正しい流れ
・ '%' と 'localhost' の違いを誤ると接続できない現象が起きる
・ SHOW GRANTS で付与済み権限を即確認、REVOKE で即剥奪できる
・ 最小権限（SELECT/INSERT/UPDATE/DELETE のみ）設計が本番の基本

# RHEL 9 / CentOS Stream 9 の場合
$ sudo mysql -u root -p

# Ubuntu 24.04 の場合（初期インストール後は auth_socket 認証のため sudo で入る）
$ sudo mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.36 MySQL Community Server - GPL

mysql>

CREATE USER 'ユーザー名'@'接続元ホスト' IDENTIFIED BY 'パスワード';

mysql> CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'Passw0rd!secure';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT User, Host FROM mysql.user WHERE User = 'webapp';
+--------+-----------+
| User   | Host      |
+--------+-----------+
| webapp | localhost |
+--------+-----------+
1 row in set (0.00 sec)

GRANT 権限リスト ON データベース名.テーブル名 TO 'ユーザー名'@'接続元ホスト';

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'webapp'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'webapp'@'localhost';
+-----------------------------------------------------------------------------------+
| Grants for webapp@localhost                                                       |
+-----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `webapp`@`localhost`                                        |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `myapp_db`.* TO `webapp`@`localhost`      |
+-----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

# webapp ユーザーで接続中の場合
mysql> SHOW GRANTS;
+-----------------------------------------------------------------------------------+
| Grants for webapp@localhost                                                       |
+-----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `webapp`@`localhost`                                        |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `myapp_db`.* TO `webapp`@`localhost`      |
+-----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

# DELETE権限だけを剥奪する例
mysql> REVOKE DELETE ON myapp_db.* FROM 'webapp'@'localhost';
Query OK, 0 rows affected (0.01 sec)

# 確認
mysql> SHOW GRANTS FOR 'webapp'@'localhost';
+-----------------------------------------------------------------------------------+
| Grants for webapp@localhost                                                       |
+-----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `webapp`@`localhost`                                        |
| GRANT SELECT, INSERT, UPDATE ON `myapp_db`.* TO `webapp`@`localhost`              |
+-----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> DROP USER 'webapp'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT User, Host, plugin FROM mysql.user WHERE User = 'webapp';
+--------+-----------+-----------------------+
| User   | Host      | plugin                |
+--------+-----------+-----------------------+
| webapp | localhost | caching_sha2_password |
+--------+-----------+-----------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR 'webapp'@'localhost';

# 旧来の mysql_native_password で作成する（互換性対応）
mysql> CREATE USER 'webapp'@'localhost'
       IDENTIFIED WITH mysql_native_password BY 'Passw0rd!secure';