MySQLで学ぶ受注管理システム開発（4）「トリガーとユーザー権限管理」

開発・動作確認環境：GitHub Codespaces（本ブログで構築した環境を使用）

はじめに
トリガーとは
トリガーを作ろう
1. 注文ステータス変更履歴を記録するトリガー
2. 在庫のマイナス登録を防ぐトリガー
ユーザー権限管理
動作確認
GitHubへの反映
まとめ
コードのダウンロード
1. 関連記事
2. 関連

はじめに

前回は、「MySQLで学ぶ受注管理システム開発（3）「ビューとストアドプロシージャを作ろう」」で、CreateOrderプロシージャの中で在庫の確認・減算処理を自動化した。

今回は視点を変えて、「特定の処理をきっかけに、自動的に別の処理を実行する」トリガーと、「誰がどこまでデータベースを操作できるかを制御する」ユーザー権限管理を学ぼう。
どちらも、複数人でデータベースを運用する実務の現場で欠かせない機能である。

トリガーとは

トリガーは、指定したテーブルへのINSERT・UPDATE・DELETEが実行された直前または直後に、自動的に別のSQL処理を実行する仕組みである。ストアドプロシージャが「CALLで明示的に呼び出す」のに対し、トリガーは「特定の操作をトリガー（きっかけ）として、意識せずとも自動で発火する」という違いがある。

トリガーを作ろう

注文ステータス変更履歴を記録するトリガー

まず、注文のステータスが変更された履歴を記録するためのログ表を用意する。

USE order_db;

CREATE TABLE order_status_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id VARCHAR(10) NOT NULL,
    old_status VARCHAR(20) NOT NULL,
    new_status VARCHAR(20) NOT NULL,
    changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

続けて、orders表のステータスが更新されたタイミングで、自動的にこのログ表へ記録するトリガーを作成する。

DELIMITER //

CREATE TRIGGER trg_orders_status_log
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    IF OLD.status <> NEW.status THEN
        INSERT INTO order_status_log (order_id, old_status, new_status)
        VALUES (NEW.order_id, OLD.status, NEW.status);
    END IF;
END //

DELIMITER ;

ポイント
OLDは更新前の値、NEWは更新後の値を指す。IF OLD.status <> NEW.statusという条件を入れることで、ステータス以外の列だけが更新された場合には、ログを残さないようにしている。

試してみよう。

UPDATE orders SET status = '出荷済' WHERE order_id = 'O005';

SELECT * FROM order_status_log;

実行例

mysql> UPDATE orders SET status = '出荷済' WHERE order_id = 'O005';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM order_status_log;
+--------+----------+------------+------------+---------------------+
| log_id | order_id | old_status | new_status | changed_at          |
+--------+----------+------------+------------+---------------------+
|      1 | O005     | 受付       | 出荷済     | 2026-08-02 10:00:00 |
+--------+----------+------------+------------+---------------------+
1 row in set (0.00 sec)

UPDATE文自体には何もログ処理を書いていないにもかかわらず、自動的に履歴が記録されていることが確認できる。アプリケーション側のコードを一切変更せずに、監査ログの仕組みを後から追加できるのが、トリガーの強みである。

在庫のマイナス登録を防ぐトリガー

前回のストアドプロシージャCreateOrderでは在庫チェックを行ったが、もし誰かがorder_detailsに直接INSERTしてしまった場合、在庫チェックを迂回されてしまう。
そこで、テーブル自体にも防御を掛けておこう。

DELIMITER //

CREATE TRIGGER trg_order_details_stock_check
BEFORE INSERT ON order_details
FOR EACH ROW
BEGIN
    DECLARE v_stock INT;

    SELECT stock_quantity INTO v_stock
    FROM products
    WHERE product_id = NEW.product_id;

    IF v_stock < NEW.quantity THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '在庫が不足しているため登録できません';
    END IF;
END //

DELIMITER ;

このように、ストアドプロシージャ（アプリケーションが呼び出す入口での防御）と、トリガー（テーブルそのものへの防御）を両方仕込んでおくことで、どのような経路でデータが登録されても、業務ルールが破られにくい設計になる。これを「多層防御」と呼ぶ。

ユーザー権限管理

これまでは、常にrootユーザーで全操作を行ってきた。しかし実務では、担当者ごとに「見るだけでよい人」「登録・更新まで行ってよい人」を分けるのが一般的である。

閲覧専用ユーザーの作成

例えば、売上レポートを見るだけの担当者向けに、SELECT権限のみを持つユーザーを作成しよう。

-- ユーザー作成
CREATE USER 'report_viewer'@'%' IDENTIFIED BY 'ViewOnly2026!';

-- order_dbに対してSELECTのみ許可
GRANT SELECT ON order_db.* TO 'report_viewer'@'%';

-- 反映
FLUSH PRIVILEGES;

権限が正しく設定されたか確認する。

SHOW GRANTS FOR 'report_viewer'@'%';

実行例

mysql> SHOW GRANTS FOR 'report_viewer'@'%';
+---------------------------------------------------------+
| Grants for report_viewer@%                              |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO `report_viewer`@`%`                |
| GRANT SELECT ON `order_db`.* TO `report_viewer`@`%`      |
+---------------------------------------------------------+
2 rows in set (0.00 sec)

このユーザーで接続し、実際にSELECT以外ができないことを確認しよう。

mysql -h db -u report_viewer -pViewOnly2026! order_db

-- これは成功する
SELECT * FROM customer_order_summary;

-- これは権限エラーになる
INSERT INTO customers (customer_id, customer_name, email) VALUES ('C999', 'テスト', 'test@example.com');

実行例

mysql> INSERT INTO customers (customer_id, customer_name, email) VALUES ('C999', 'テスト', 'test@example.com');
ERROR 1142 (42000): INSERT command denied to user 'report_viewer'@'%' for table 'customers'

意図した通り、INSERTだけが拒否されている。

登録担当者用ユーザーの作成

続けて、日々の受注登録を行う担当者向けに、CreateOrderプロシージャの実行と、必要最小限のテーブル操作のみを許可するユーザーも作成しておこう。

CREATE USER 'order_staff'@'%' IDENTIFIED BY 'OrderStaff2026!';

-- 必要なテーブルへのSELECT・INSERT・UPDATEのみ許可（DELETEは許可しない）
GRANT SELECT, INSERT, UPDATE ON order_db.orders TO 'order_staff'@'%';
GRANT SELECT, INSERT, UPDATE ON order_db.order_details TO 'order_staff'@'%';
GRANT SELECT ON order_db.customers TO 'order_staff'@'%';
GRANT SELECT ON order_db.products TO 'order_staff'@'%';

-- ストアドプロシージャの実行権限
GRANT EXECUTE ON PROCEDURE order_db.CreateOrder TO 'order_staff'@'%';

FLUSH PRIVILEGES;

このように、「どのテーブルに」「どの操作を」「どのユーザーに」許可するかを1つずつ明示的に設定することで、権限の過不足を防げる。特にDELETE権限は、前回学んだ「論理削除」の運用方針とも合わせて、慎重に付与するかどうかを判断するとよい。

権限のはく奪（REVOKE）

担当替えなどで権限を見直す場合は、REVOKEを使う。

-- order_staffからUPDATE権限を取り消す例
REVOKE UPDATE ON order_db.orders FROM 'order_staff'@'%';

FLUSH PRIVILEGES;

動作確認

現在作成されているトリガーとユーザーの一覧を確認しておこう。

SHOW TRIGGERS IN order_db;
SELECT User, Host FROM mysql.user WHERE User IN ('report_viewer', 'order_staff');

GitHubへの反映

mysql-learning/
├── order-management/
│   ├── 01_schema.sql
│   ├── 02_crud.sql
│   ├── 03_view_procedure.sql
│   └── 04_trigger_permission.sql   ← 今回作成したトリガー・ユーザー権限設定

git add order-management/
git commit -m "トリガーとユーザー権限管理を追加（第4回）"
git push

まとめ

今回は、以下を学んだ。

AFTER UPDATEトリガーによる、ステータス変更履歴の自動記録
BEFORE INSERTトリガーによる、テーブルレベルでの業務ルール防御（多層防御の考え方）
CREATE USER・GRANT・REVOKEによる、役割に応じた権限管理
ストアドプロシージャへのEXECUTE権限の付与

次回（最終回）は、これまで作成してきたデータベースを対象に、EXPLAINを使ったクエリの実行計画の確認、インデックス設計、そしてmysqldumpを使ったバックアップ・リストアの方法を紹介しよう。

コードのダウンロード

今回のコードは、私のGitHubリポジトリからダウンロードできる。

▶ Rocky-Seven/mysql-learning/order-management

MySQLで学ぶ受注管理システム開発（1）「テーブル設計とER図を描こう」
MySQLで学ぶ受注管理システム開発（2）「CRUD操作を極めよう」
MySQLで学ぶ受注管理システム開発（3）「ビューとストアドプロシージャを作ろう」

月	火	水	木	金	土	日
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30
31