必须用ON DATABASE级别触发器,SCHEMA级别无效;须由SYS或具备ADMINISTER DATABASE TRIGGER权限用户创建,结合ora_sysevent、ora_dict_obj_owner等动态变量细粒度判断,并用raise_application_error中止操作。

必须用 ON DATABASE 触发器,SCHEMA 级别完全无效
普通用户删自己 schema 的表,ON SCHEMA 触发器能拦;但 DBA 执行 DROP TABLE prod_user.emp 时,触发器根本不会在 prod_user 模式下运行——它实际在 SYS 或 SYSTEM 上下文里执行,而 ON SCHEMA 只响应“当前 schema 内发生的 DDL”,对跨 schema 操作无感。真正能捕获所有用户(包括 DBA)的 DROP 行为的,只有 ON DATABASE 级别触发器,且必须由 SYS 或具备 ADMINISTER DATABASE TRIGGER 权限的账号创建。
常见错误是用 SYSTEM 账号直接建,结果部署完毫无反应;正确做法是显式以 / AS SYSDBA 登录后执行:CREATE OR REPLACE TRIGGER block_prod_drop BEFORE DROP ON DATABASE。
判断逻辑要细粒度,不能一刀切拦 DROP
全库禁止 DROP 会卡死归档脚本、分区轮转、部署任务等合法运维操作。实际拦截必须结合四个动态上下文变量做组合判断:
-
ora_sysevent IN ('DROP', 'TRUNCATE'):统一处理删表和清空,避免漏掉TRUNCATE TABLE -
ora_dict_obj_type = 'TABLE':只盯表,放过SEQUENCE、SYNONYM等非核心对象 -
ora_dict_obj_owner IN ('PROD_SCHEMA_A', 'PROD_SCHEMA_B'):明确限定目标 schema,不波及其他环境 -
ora_login_user NOT IN ('SYS', 'SYSTEM', 'dba_admin'):给 DBA 留紧急通道,否则真出事没法救 -
LOWER(ora_dict_obj_name) NOT LIKE '%_tmp' AND LOWER(ora_dict_obj_name) NOT LIKE '%_bak':允许删临时表,否则开发联调跑不通
注意:TRUNCATE 在 Oracle 10g+ 被捕获为 ora_sysevent = 'DDL',不是独立事件,所以条件里必须显式写 IN ('DROP', 'TRUNCATE'),不能只靠 ora_sysevent = 'DROP'。
中止操作只能用 raise_application_error,DBMS_OUTPUT 完全无效
DBMS_OUTPUT.PUT_LINE('no drop') 或空 RETURN 不起任何作用——DDL 会照常执行。唯一能中止的是 raise_application_error(-20001, '禁止删除核心表'),且必须满足两个硬性条件:
- 错误号在
-20001到-20999范围内(Oracle 强制要求) - 必须在
BEFORE事件里抛出;AFTER DROP只能记录日志,无法阻止
别在触发器里查 USER_TABLES 或拼 SQL 判断表是否存在——触发器执行时表可能已被删了一半,查不到反而误放行。直接用 ora_dict_obj_owner 和 ora_dict_obj_name 这两个系统变量最安全。
权限与调试最容易踩的坑
建触发器本身需要 CREATE ANY TRIGGER,但要让它生效,还必须有 ADMINISTER DATABASE TRIGGER。很多 DBA 忘了后者,导致触发器始终不触发。
调试时如果用 DBMS_OUTPUT.PUT_LINE,务必先确认会话已开 SET SERVEROUTPUT ON,否则你以为没输出,其实是被吞了。
更隐蔽的问题是权限泄漏:触发器里若写了 INSERT INTO audit_log VALUES (...),而 audit_log 表没对 PUBLIC 授权 INSERT,就会因权限不足抛 ORA-00604,进而让整个 DROP 失败但报错信息不指向触发器——容易误判为数据库异常。
复杂点在于:DBA 有时需要删表,但又不能完全放开权限。最终防线其实是“行为审计 + 防火墙兜底”——触发器只拦非白名单操作,而数据库防火墙负责识别高危语义(如 DROP TABLE.*IF EXISTS)、绑定客户端指纹、触发实时告警。单靠触发器,永远只是半条腿走路。

















