OracleでDROP操作を安全に制御！

自律トランザクションでログ記録するDDLトリガーの構築手順

重要なテーブルを誤ってDROPしてしまう…。
そんな悲劇を防ぎつつ、操作ログも残したいというニーズに応えるのが「DDLトリガー＋自律トランザクション」の組み合わせです。

この記事では、ログ記録を別スキーマに逃がす構成でORA-600などのリスクを避けつつ、確実にDROP操作をブロックして監査ログも残す方法を紹介します。

---

🗂構成概要

+----------------------+                  +--------------------------+
| USER_A               |                  | AUDIT_USER               |
| （操作対象）         |                  | （ログ保存専用）         |
| - EMPなどの表        |   →DROP操作検知→  | - DDL_BLOCK_LOG表        |
| - DDLトリガー         | ←INSERTログ記録 |                          |
+----------------------+                  +--------------------------+

---

🧾手順

① ログ保存用スキーマ AUDIT_USER を作成（DBAで）

CREATE USER audit_user IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;

GRANT CONNECT, RESOURCE TO audit_user;

---

② ログ用テーブルを作成（AUDIT_USERで）

CONNECT audit_user/password

CREATE TABLE ddl_block_log (
  obj_name VARCHAR2(100),
  event    VARCHAR2(20),
  log_time DATE
);

---

③ 操作対象ユーザー USER_A を作成（DBAで）

CREATE USER user_a IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;

GRANT CONNECT, RESOURCE TO user_a;

---

④ USER_A にログ記録権限を付与

GRANT INSERT ON audit_user.ddl_block_log TO user_a;

---

⑤ USER_Aでトリガーとテストテーブルを作成

CONNECT user_a/password

-- テスト用テーブル
CREATE TABLE emp (
  empno NUMBER,
  ename VARCHAR2(100)
);

---

🧠 DDLトリガー（DROP操作のブロック＋ログ記録）

CREATE OR REPLACE TRIGGER trg_block_ddl
AFTER DROP ON SCHEMA
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO audit_user.ddl_block_log
  VALUES (ora_dict_obj_name, ora_sysevent, SYSDATE);

  COMMIT;

  RAISE_APPLICATION_ERROR(-20005, 'このオブジェクトのDROPは禁止されています。');
END;
/

🔎 ポイント解説

記述	説明
PRAGMA AUTONOMOUS_TRANSACTION	トリガー内で独立したトランザクションを開始
INSERT	audit_user.ddl_block_log にDROP操作の詳細を記録
COMMIT	自律トランザクション内の変更を即時反映
RAISE_APPLICATION_ERROR	DROP操作をユーザー定義エラーで停止

---

✅ 動作確認

DROP TABLE emp;

出力されるエラー（DROPはブロックされる）：

ORA-20005: このオブジェクトのDROPは禁止されています。

---

🔍 ログ確認（AUDIT_USERで）

CONNECT audit_user/password

SELECT * FROM ddl_block_log;

OBJ_NAME	EVENT	LOG_TIME
EMP	DROP	2025/06/29 01:45:00

SQL> CREATE USER audit_user IDENTIFIED BY password
  2  DEFAULT TABLESPACE users
  3  TEMPORARY TABLESPACE temp
  4  QUOTA UNLIMITED ON users;

ユーザーが作成されました。

SQL> GRANT CONNECT, RESOURCE TO audit_user;

権限付与が成功しました。

SQL> CONNECT audit_user/password
接続されました。
SQL> CREATE TABLE ddl_block_log (
  2    obj_name VARCHAR2(100),
  3    event    VARCHAR2(20),
  4    log_time DATE
  5  );

表が作成されました。

SQL> CONNECT / as sysdba
接続されました。
SQL> CREATE USER user_a IDENTIFIED BY password
  2  DEFAULT TABLESPACE users
  3  TEMPORARY TABLESPACE temp
  4  QUOTA UNLIMITED ON users;

ユーザーが作成されました。

SQL> GRANT CONNECT, RESOURCE TO user_a;

権限付与が成功しました。

SQL> GRANT INSERT ON audit_user.ddl_block_log TO user_a;

権限付与が成功しました。

SQL> CONNECT user_a/password
接続されました。
SQL> CREATE TABLE emp (
  2    empno NUMBER,
  3    ename VARCHAR2(100)
  4  );

表が作成されました。

SQL> CREATE OR REPLACE TRIGGER trg_block_ddl
  2  AFTER DROP ON SCHEMA
  3  DECLARE
  4    PRAGMA AUTONOMOUS_TRANSACTION;
  5  BEGIN
  6    INSERT INTO audit_user.ddl_block_log
  7    VALUES (ora_dict_obj_name, ora_sysevent, SYSDATE);

  8    COMMIT;
  9
 10    RAISE_APPLICATION_ERROR(-20005, 'このオブジェクトのDROPは禁止されています。');
 11
 12  END;
 13  /

トリガーが作成されました。

SQL> DROP TABLE emp;
DROP TABLE emp
           *
行1でエラーが発生しました。:
ORA-04088: トリガー'USER_A.TRG_BLOCK_DDL'の実行中にエラーが発生しました ORA-00604:
再帰SQLレベル1でエラーが発生しました。 ORA-20005:
このオブジェクトのDROPは禁止されています。 ORA-06512: 行9



SQL> CONNECT audit_user/password
接続されました。
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';

セッションが変更されました。

SQL> set lin 1000
SQL> col obj_name for a20
SQL> col event for a20
SQL> SELECT * FROM ddl_block_log;

OBJ_NAME             EVENT                LOG_TIME
-------------------- -------------------- -------------------
EMP                  DROP                 2025 06 29 15:25:29

---

🧩まとめ

項目	内容
DROPの防止	RAISE_APPLICATION_ERRORで確実に停止
ログの記録先	別スキーマのテーブル audit_user.ddl_block_log
安全な構成に必要な工夫	PRAGMA AUTONOMOUS_TRANSACTIONの利用
権限設定	INSERT権限を user_a に付与

[参考]
14.12 CREATE TRIGGER文

スキーマとオブジェクト権限についての基本解説

Oracleデータベースにおけるスキーマとオブジェクト権限は、データベースの管理と操作において非常に重要な概念です。初心者にとってこれらの用語が難しく感じられることもありますが、理解すればデータベースの効率的な運用に役立ちます。本記事では、...

oracle-master00000.com

2024.10.01