能否在动态SQL执行前创建触发程序拦截危险SQL语句？

咱们一个个来拆解你的问题：

1. 是否存在可在动态SQL执行前触发的程序？ #

当然有！Oracle提供了数据库级/模式级的系统触发器，可以在任何SQL语句（包括EXECUTE IMMEDIATE或DBMS_SQL.EXECUTE执行的动态SQL）执行前触发。

最常用的是BEFORE STATEMENT级别的系统触发器，它会在每一条SQL语句执行前触发，不管这条SQL是静态编写的还是动态生成的。举个简单的捕获所有语句执行前事件的触发器框架：

CREATE OR REPLACE TRIGGER trg_before_all_statement
BEFORE STATEMENT ON DATABASE
DECLARE
    v_sql_text VARCHAR2(4000);
BEGIN
    -- 获取当前执行的SQL文本（处理长SQL需要循环拼接数组）
    FOR i IN 1..ORA_SQL_TXT.LAST LOOP
        v_sql_text := v_sql_text || ORA_SQL_TXT(i);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('即将执行的SQL: ' || v_sql_text);
EXCEPTION
    WHEN OTHERS THEN
        NULL;
END;
/

需要注意：这类触发器会捕获所有SQL语句，不仅是动态SQL。如果需要只针对动态SQL做处理，你可以在触发器里结合会话信息（比如USERENV('MODULE')或自定义应用上下文）判断语句来源，但Oracle没有专门只针对动态SQL的触发器事件。

2. 基于自定义权限表的动态SQL拦截功能能否实现？ #

完全可以实现！不过需要结合系统触发器、SQL解析和自定义权限逻辑来搭建，下面是核心步骤和注意事项：

核心实现思路 #

• 创建系统触发器：使用BEFORE STATEMENT ON DATABASE（或你的应用专属模式）触发器，拦截所有SQL执行请求。
• 获取并解析SQL语句：
  1. 用ORA_SQL_TXT函数获取当前执行的SQL文本（注意处理超过4000字符的长SQL，需要循环拼接数组元素）。
  2. 借助DBMS_SQL.PARSE解析SQL，判断操作类型（比如是DROP TABLE、EXECUTE PROCEDURE这类危险操作，还是普通查询）。
• 验证自定义权限：
  1. 从你的权限表中查询当前用户（或应用层用户，建议用DBMS_SESSION.SET_CONTEXT设置应用上下文存储用户标识）的权限。
  2. 结合SQL操作类型、目标对象（比如要执行的存储过程名称）判断是否允许执行。
• 拦截非法请求：如果权限验证不通过，抛出自定义异常终止执行，比如：

  RAISE_APPLICATION_ERROR(-20001, '权限不足，禁止执行该SQL语句');
  

关键注意事项 #

• 性能优化：触发器会在每条SQL执行时触发，所以逻辑要尽可能高效——比如避免复杂查询或大量计算，必要时可以缓存权限数据。
• SQL解析准确性：动态SQL可能包含绑定变量、嵌套语句等复杂情况，解析时要考虑这些场景，避免误判。可以用DBMS_SQL包获取解析后的SQL类型和对象信息，比正则匹配更可靠。
• 触发器权限：创建数据库级系统触发器需要ADMINISTER DATABASE TRIGGER权限，且触发器运行在特权上下文，要确保只有DBA能修改触发器，避免被恶意篡改。
• 绕过风险：如果用户拥有ALTER TRIGGER或DROP TRIGGER权限，可能会禁用拦截逻辑，所以要严格控制这些特权的授予，只给必要的管理员。

举个简化的权限验证触发器示例（假设你的权限表是APP_PERMISSIONS，包含USER_ID、ALLOWED_OPERATION、TARGET_OBJECT字段）：

CREATE OR REPLACE TRIGGER trg_enforce_custom_permissions
BEFORE STATEMENT ON DATABASE
DECLARE
    v_sql_text VARCHAR2(4000);
    v_operation VARCHAR2(100);
    v_target_obj VARCHAR2(100);
    v_app_user VARCHAR2(100);
    v_has_permission NUMBER := 0;
BEGIN
    -- 获取应用层用户（假设通过应用上下文存储）
    v_app_user := SYS_CONTEXT('APP_CONTEXT', 'CURRENT_USER');
    
    -- 获取SQL文本
    FOR i IN 1..ORA_SQL_TXT.LAST LOOP
        v_sql_text := v_sql_text || ORA_SQL_TXT(i);
    END LOOP;
    v_sql_text := UPPER(v_sql_text);
    
    -- 解析SQL操作和目标对象（实际场景建议用DBMS_SQL解析）
    IF v_sql_text LIKE 'EXECUTE%' THEN
        v_operation := 'EXECUTE_PROCEDURE';
        -- 提取要执行的存储过程名称（简化逻辑，实际需更严谨）
        v_target_obj := REGEXP_SUBSTR(v_sql_text, 'EXECUTE\s+(\w+\.)?(\w+)', 1, 1, NULL, 2);
    ELSIF v_sql_text LIKE 'DROP%' THEN
        v_operation := 'DROP_OBJECT';
        v_target_obj := REGEXP_SUBSTR(v_sql_text, 'DROP\s+\w+\s+(\w+)', 1, 1, NULL, 1);
    END IF;
    
    -- 检查自定义权限
    IF v_operation IS NOT NULL THEN
        SELECT COUNT(1) INTO v_has_permission
        FROM APP_PERMISSIONS
        WHERE USER_ID = v_app_user
          AND ALLOWED_OPERATION = v_operation
          AND (TARGET_OBJECT = v_target_obj OR TARGET_OBJECT = '*'); -- *表示允许所有对象
        
        IF v_has_permission = 0 THEN
            RAISE_APPLICATION_ERROR(-20002, '权限不足：禁止执行[' || v_operation || ']操作，目标对象：' || v_target_obj);
        END IF;
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20003, '未找到用户权限信息');
END;
/

内容的提问来源于stack exchange，提问作者Martin Leung