MariaDBユーザー一覧からゾンビアカウントを洗い出す｜last_passwordなど拡張カラム活用

この記事のポイント

・SELECT文でmysql.userテーブルからユーザー一覧を取得できる
・password_last_changedが数年前のアカウントはゾンビ候補になる
・last_login（10.11以降）で実際のログイン日時も絞り込める
・DROP USER前に依存オブジェクトの確認が鉄則

# mariadb ユーザー一覧を取得するSQL
mariadb -u root -p -e "SELECT User, Host, account_locked, password_expired FROM mysql.user ORDER BY User;"

+--------------------+-----------+----------------+------------------+
| User               | Host      | account_locked | password_expired |
+--------------------+-----------+----------------+------------------+
| app_user           | 127.0.0.1 | N              | N                |
| backup_user        | localhost | N              | Y                |
| deploy_user        | 10.0.0.%  | Y              | N                |
| mariadb.sys        | localhost | Y              | N                |
| mysql              | localhost | Y              | N                |
| root               | localhost | N              | N                |
| test               | %         | N              | N                |
+--------------------+-----------+----------------+------------------+

# 特定ユーザーの権限一覧を取得する
SHOW GRANTS FOR 'backup_user'@'localhost';

+--------------------------------------------------------------+
| Grants for backup_user@localhost                             |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO @              |
| GRANT SELECT, LOCK TABLES ON .* TO @ |
+--------------------------------------------------------------+

# 2年以上パスワード変更がないアカウントを抽出する（ゾンビ候補）
SELECT User, Host, password_last_changed,
       DATEDIFF(NOW(), password_last_changed) AS days_since_change,
       account_locked, password_expired
FROM mysql.user
WHERE password_last_changed < DATE_SUB(NOW(), INTERVAL 2 YEAR)
  AND User NOT IN ('mariadb.sys', 'mysql')
ORDER BY password_last_changed;

+-------------+-----------+-----------------------+-------------------+----------------+------------------+
| User        | Host      | password_last_changed | days_since_change | account_locked | password_expired |
+-------------+-----------+-----------------------+-------------------+----------------+------------------+
| test        | %         | 2020-05-20 14:10:00   |              2337 | N              | N                |
| backup_user | localhost | 2021-08-01 12:00:00   |              1869 | N              | Y                |
| deploy_user | 10.0.0.%  | 2022-11-30 18:45:23   |              1411 | Y              | N                |
+-------------+-----------+-----------------------+-------------------+----------------+------------------+

# ロック済みかつ期限切れのアカウントを列挙する（削除筆頭候補）
SELECT User, Host, password_last_changed, account_locked, password_expired
FROM mysql.user
WHERE account_locked = 'Y' AND password_expired = 'Y'
  AND User NOT IN ('mariadb.sys', 'mysql');

# last_loginカラムの有無を確認する（10.11未満ではエラーになる）
SELECT User, Host, last_login
FROM mysql.user
WHERE last_login IS NULL OR last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR)
  AND User NOT IN ('mariadb.sys', 'mysql')
ORDER BY last_login;

# 対象ユーザーが所有するビューを確認する（例: test@%）
SELECT TABLE_SCHEMA, TABLE_NAME, DEFINER
FROM information_schema.VIEWS
WHERE DEFINER = 'test@%';

# ストアドプロシージャ・ファンクションを確認する
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, DEFINER
FROM information_schema.ROUTINES
WHERE DEFINER = 'test@%';

# ユーザーを削除する（Host指定を必ず入れる）
DROP USER 'test'@'%';

# 削除されたことを確認する
SELECT User, Host FROM mysql.user WHERE User = 'test';

Empty set (0.001 sec)

# バージョンを確認する
SELECT VERSION();

+------------------+
| VERSION()        |
+------------------+
| 10.6.18-MariaDB  |
+------------------+