如何在Oracle APEX中按用户会话审计CRUD操作（含740张表）

1. 修复并启用APEX内置DML审计 #

你之前启用APEX_ACTIVITY_LOG未生效，大概率是配置不到位：

• 确认应用页面使用APEX原生的**自动行处理(DML)**组件（交互式网格、表单），这类组件的DML操作默认会被审计；
• 进入应用→共享组件→应用定义→编辑属性，将审计级别设为「全部」或「DML」，勾选「记录DML操作」；
• 可通过APEX_WORKSPACE_ACTIVITY_LOG或APEX_AUDIT视图查询审计记录，其中SESSION_ID字段就是APEX会话ID。

注意：该方案仅覆盖APEX原生组件发起的DML，自定义PL/SQL执行的变更需额外处理。

2. 细粒度审计(FGA)结合APEX会话关联 #

FGA比普通schema审计更灵活，能精准捕获删改操作，且可直接关联APEX会话：

• 先创建通用审计处理存储过程：

  CREATE OR REPLACE PROCEDURE AUDIT_DML_HANDLER(
    p_schema VARCHAR2,
    p_table VARCHAR2,
    p_policy VARCHAR2
  ) AS
    l_session_id NUMBER := APEX_UTIL.GET_SESSION_ID();
    l_username VARCHAR2(100) := NVL(APEX_UTIL.GET_USERNAME(l_session_id), USER);
  BEGIN
    INSERT INTO CUSTOM_AUDIT_LOG (
      table_name, operation_type, apex_session_id, username, audit_time
    ) VALUES (
      p_table,
      SYS_CONTEXT('USERENV','CURRENT_STATEMENT_TYPE'),
      l_session_id,
      l_username,
      SYSTIMESTAMP
    );
  END;
  /
  

• 批量为740张表生成FGA策略（用动态SQL避免手动操作）：

  DECLARE
    CURSOR c_tables IS SELECT table_name FROM USER_TABLES; -- 可加过滤条件筛选目标表
  BEGIN
    FOR rec IN c_tables LOOP
      DBMS_FGA.ADD_POLICY(
        object_schema   => USER,
        object_name     => rec.table_name,
        policy_name     => 'AUDIT_' || rec.table_name || '_DML',
        audit_condition => '1=1', -- 捕获所有符合statement_types的操作
        handler_schema  => USER,
        handler_module  => 'AUDIT_DML_HANDLER',
        enable          => TRUE,
        statement_types => 'UPDATE,DELETE'
      );
    END LOOP;
  END;
  /
  

3. 动态生成标准化触发器（优化版） #

虽然手动写740个触发器不现实，但通过动态SQL可批量生成统一模板的触发器，直接关联APEX会话：

• 执行以下脚本批量生成触发器：

  DECLARE
    CURSOR c_tables IS SELECT table_name FROM USER_TABLES; -- 过滤目标表
    l_trigger_sql VARCHAR2(4000);
  BEGIN
    FOR rec IN c_tables LOOP
      l_trigger_sql := 'CREATE OR REPLACE TRIGGER TRG_AUDIT_' || rec.table_name || '
        AFTER UPDATE OR DELETE ON ' || rec.table_name || '
        FOR EACH ROW
        DECLARE
          l_session_id NUMBER := APEX_UTIL.GET_SESSION_ID();
          l_username VARCHAR2(100) := NVL(APEX_UTIL.GET_USERNAME(l_session_id), USER);
        BEGIN
          INSERT INTO CUSTOM_AUDIT_LOG (
            table_name, operation_type, apex_session_id, username, old_data, audit_time
          ) VALUES (
            ''' || rec.table_name || ''',
            CASE WHEN UPDATING THEN ''UPDATE'' ELSE ''DELETE'' END,
            l_session_id,
            l_username,
            JSON_OBJECT_T(:OLD).TO_STRING(), -- 将旧数据转为JSON存储
            SYSTIMESTAMP
          );
        END;';
      EXECUTE IMMEDIATE l_trigger_sql;
    END LOOP;
  END;
  /
  

若不需要记录旧数据，可去掉old_data字段及对应赋值。

4. 统一DML封装层（代码规范约束） #

如果应用所有DML都通过PL/SQL执行，可封装通用DML包强制审计：

• 创建带审计逻辑的DML包：

  CREATE OR REPLACE PACKAGE DML_AUDIT_PKG IS
    PROCEDURE update_table(p_table VARCHAR2, p_set VARCHAR2, p_where VARCHAR2);
    PROCEDURE delete_table(p_table VARCHAR2, p_where VARCHAR2);
  END DML_AUDIT_PKG;
  /
  
  CREATE OR REPLACE PACKAGE BODY DML_AUDIT_PKG IS
    PROCEDURE log_audit(p_table VARCHAR2, p_op VARCHAR2) AS
      l_session_id NUMBER := APEX_UTIL.GET_SESSION_ID();
      l_username VARCHAR2(100) := NVL(APEX_UTIL.GET_USERNAME(l_session_id), USER);
    BEGIN
      INSERT INTO CUSTOM_AUDIT_LOG (table_name, operation_type, apex_session_id, username, audit_time)
      VALUES (p_table, p_op, l_session_id, l_username, SYSTIMESTAMP);
    END;
  
    PROCEDURE update_table(p_table VARCHAR2, p_set VARCHAR2, p_where VARCHAR2) AS
      l_sql VARCHAR2(4000);
    BEGIN
      l_sql := 'UPDATE ' || p_table || ' SET ' || p_set || ' WHERE ' || p_where;
      EXECUTE IMMEDIATE l_sql;
      log_audit(p_table, 'UPDATE');
    END;
  
    PROCEDURE delete_table(p_table VARCHAR2, p_where VARCHAR2) AS
      l_sql VARCHAR2(4000);
    BEGIN
      l_sql := 'DELETE FROM ' || p_table || ' WHERE ' || p_where;
      EXECUTE IMMEDIATE l_sql;
      log_audit(p_table, 'DELETE');
    END;
  END DML_AUDIT_PKG;
  /
  

该方案依赖开发规范，要求所有DML都调用此包，否则无法覆盖直接执行的SQL语句。

关键注意事项 #

• APEX_UTIL.GET_SESSION_ID()仅在APEX会话上下文有效，后台作业或非APEX会话执行的DML会返回NULL，可 fallback 到数据库用户USER；
• 审计表CUSTOM_AUDIT_LOG需提前创建，建议包含主键、表名、操作类型、会话ID、用户名、审计时间、变更数据（可选）等字段；
• FGA和触发器会增加DML开销，高频操作的表可调整审计策略（如只审计特定字段），先在测试环境验证性能。

内容的提问来源于stack exchange，提问作者majid khoshgoftar lali