MySQLのユーザ削除はdrop user 8.0以降は存在しないユーザの扱いが異なる

MySQLのユーザ削除についてまとめています。

以下の解説は、MySQL 8.0以降を前提にしています。

MySQLのユーザ削除は、drop user

MySQLのユーザ削除の構文

drop user ユーザ名@ホスト名;

以下の例は、現在のユーザ一覧を確認し、ユーザuser0@localhostを削除したあと、再度ユーザ一覧を確認して、削除が完了したことを確認しています。

【関連記事】
▶MySQLのユーザ一覧は、mysql.userを参照 権限一覧やパスワードハッシュ一覧の取得方法

mysql> select user, host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
| select_only      | localhost |
| user0            | localhost |
+------------------+-----------+
8 rows in set (0.02 sec)

mysql> drop user user0@localhost;
Query OK, 0 rows affected (0.24 sec)

mysql> select user, host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
| select_only      | localhost |
+------------------+-----------+
7 rows in set (0.00 sec)

MySQL5.7.5以前では、存在しないユーザを削除しようとした場合にエラーになります。エラー回避のため、以下のようにいったんGRANTでユーザ権限をなしにから、DROP USERを実行するSQLを実行するケースがあります。

ユーザが存在する場合は権限なしに設定され、ユーザが存在しない場合は権限のないユーザが作成されます。

GRANT USAGE ON *.* TO user0 IDENTIFIED BY password;
DROP USER user0;

なお、この記述をすると、MySQL5.7.6以降では以下のwarningが出力されます。

1287 Using GRANT statement to modify existing user's properties other than privileges is deprecated and will be removed in future release. Use ALTER USER statement for this operation.

MySQL 5.7.6以降では、単純に以下のように記述すれば、存在しないユーザをドロップしようとした場合は無視されます。

drop user if exists user0

ユーザパスワードの削除は、alter userでパスワード上書き

MySQLでは、厳密な意味でパスワードの削除を行うことはできません。特権を持ったユーザで、パスワードを上書きすることによって旧パスワードを削除することができます。

mysql> alter user user0@localhost identified by 'pass123';

なお、MySQL5.7以前で利用できたPASSWORD()関数は廃止されています。MySQL8.0以降では、以下のエラーとなります。

mysql> SET PASSWORD FOR user0@localhost=PASSWORD('pass123');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD('pass123')' at line 1

ユーザ権限の削除はrevoke

ユーザ権限を削除（剥奪）するには、以下のSQLを実行します。

revoke all on データベース名.テーブル名 from ユーザ名@ホスト名;

以下の例は、user0@localhostの権限を確認後、全てのデータベースの全てのテーブルから、権限を削除し、再度ユーザ権限を確認しています。

mysql> show grants for user0@localhost;
+--------------------------------------------------------------+
| Grants for user0@localhost                                   |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `user0`@`localhost`                    |
| GRANT ALL PRIVILEGES ON `employees`.* TO `user0`@`localhost` |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> revoke all on *.* from user0@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for user0@localhost;
+-------------------------------------------+
| Grants for user0@localhost                |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `user0`@`localhost` |
+-------------------------------------------+
1 row in set (0.00 sec)

まとめ

ポテパンダの一言メモ

• MySQLでユーザ削除するには、drop user
• MySQL 5.7以前と8.0以降では存在しないユーザのdropに対する扱いが異なる
• ユーザパスワードの削除は、alter userでパスワードを上書きする
• ユーザ権限の削除は、revokeを使用