MySQL Advent Calendar 2025

一時的なユーザーを作成したい

Last updated at 2025-12-24Posted at 2025-12-22

こんな悩みありませんか？

時々、本番DBにログインしてデータ修正を行ったり、DDLを実行したりするんだけど、常にログインできる状態にしておくのは、セキュリティ上、問題なんだよなぁ。

承認制にして、承認が通ったらユーザーを作成する運用にしたとして、使い終わったら確実に削除したい。
許可した時間が経過したら、自動的にユーザーが消えてくれたら便利なのに...

それ、イベントスケジューラーで実現できますよ

承認が通ったら、次のSQLを実行するだけでOKです。

まずはユーザーを作ります。

CREATE USER IF NOT EXISTS '{ユーザー名}'@'{ホスト名}' IDENTIFIED BY '{パスワード}' ;

パスワードを考えるのが面倒な時は、生成させることもできます。

CREATE USER IF NOT EXISTS '{ユーザー名}'@'{ホスト名}' IDENTIFIED BY RANDOM PASSWORD;

次に、任意の権限を付与します。

GRANT SELECT,UPDATE,INSERT,DELETE,ALTER,INDEX ON {データベース}.* TO '{ユーザー名}'@'{ホスト名}';

DBに関する全権限（GRANT ALL）は付与しないでください。
ユーザー削除イベントを削除する権限も所有するため、一時的ユーザーの抜け道ができてしまいます。

最後にイベントを作成します。

このイベントはDDL実行時間から指定時間が経過すると、指定されたユーザーのプロセスをKILLして、ユーザーを削除します。ON COMPLETION NOT PRESERVE を指定しているので、実行完了後、このイベント自体も削除されます。

MySQLの場合

DELIMITER $$
 
CREATE EVENT `ev_tmpuser_expire_{ユーザー名}`
  ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL {制限時間} MINUTE
  ON COMPLETION NOT PRESERVE
  DO
  BEGIN
    DECLARE v_process_id BIGINT;
    DECLARE v_done INT DEFAULT FALSE;
    DECLARE cur_processes CURSOR FOR
        SELECT id FROM information_schema.processlist
        WHERE user = '{ユーザー名}';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
 
    -- アクティブセッションを終了
    OPEN cur_processes;
    kill_loop: LOOP
        FETCH cur_processes INTO v_process_id;
        IF v_done THEN
            LEAVE kill_loop;
        END IF;
         
        -- エラーが出ても続行
        BEGIN
            DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
            SET @kill_sql = CONCAT('KILL ', v_process_id);
            PREPARE kill_stmt FROM @kill_sql;
            EXECUTE kill_stmt;
            DEALLOCATE PREPARE kill_stmt;
        END;
    END LOOP;
    CLOSE cur_processes;
 
    -- 全セッション終了後にユーザー削除
    DROP USER IF EXISTS '{ユーザー名}'@'{ホスト名}';
  END
$$
 
DELIMITER ;

Aurora MySQLの場合

DELIMITER $$
 
CREATE EVENT `ev_tmpuser_expire_{ユーザー名}`
  ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL {制限時間} MINUTE
  ON COMPLETION NOT PRESERVE
  DO
  BEGIN
    DECLARE v_process_id BIGINT;
    DECLARE v_done INT DEFAULT FALSE;
    DECLARE cur_processes CURSOR FOR
        SELECT id FROM information_schema.processlist
        WHERE user = '{ユーザー名}';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
 
    -- アクティブセッションを終了
    OPEN cur_processes;
    kill_loop: LOOP
        FETCH cur_processes INTO v_process_id;
        IF v_done THEN
            LEAVE kill_loop;
        END IF;
         
        -- エラーが出ても続行
        BEGIN
            DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
            CALL mysql.rds_kill(v_process_id);
        END;
    END LOOP;
    CLOSE cur_processes;
 
    -- 全セッション終了後にユーザー削除
    DROP USER IF EXISTS '{ユーザー名}'@'{ホスト名}';
  END
$$
DELIMITER ;

ログイン中にユーザー削除イベントが実行されたらどうなるの？

ユーザーがクエリを実行すると、エラーが発生して、接続を閉じるしか無くなります。

mysql> show databases;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
ERROR 1045 (28000): Access denied for user 'suzukito'@'localhost' (using password: YES)
ERROR:
Can't connect to the server

この一連のDDLを実行するのに必要な権限は？

作成したユーザーに権限を付与したいDBに対するGRANT OPTION付きのALL権限
PROCESS権限
CREATE USER権限
Aurora MySQLの場合はmysql.rds_killの実行権限

CREATE ROLE role_provisioner;

GRANT ALL ON {データベース}.* TO `role_provisioner`@`%` WITH GRANT OPTION;
GRANT PROCESS, CREATE USER ON *.* TO `role_provisioner`@`%`;

-- Aurora MySQLの場合
GRANT EXECUTE ON PROCEDURE mysql.rds_kill TO `role_provisioner`@`%`;

CREATE USER `provisioner`@`%` IDENTIFIED BY '{パスワード}' DEFAULT ROLE `role_provisioner`@`%`;

応用編

普段はSELECT権限だけを付与したユーザーは作成しておいて、承認が通ったらUPDATEやDELETEなど必要な権限を付与して、時間が来たら剥奪するって運用にしても良いですね。

まとめ

イベントスケジューラーの使い途を発見したかも？って思いました。

参考資料

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up