登录触发器中获取待执行存储过程名称失败问题排查

我们需要获取登录触发器内即将执行的存储过程名称并保存，尝试创建了如下AFTER LOGON触发器：

CREATE OR REPLACE TRIGGER log_login_procedure
AFTER LOGON ON DATABASE
DECLARE
    v_depth       PLS_INTEGER;
    v_unit_name   VARCHAR2(255);
    v_subprogram  VARCHAR2(255);
BEGIN
    -- Get the total depth of the current PL/SQL call stack
    v_depth := UTL_CALL_STACK.backtrace_depth;
        
    -- Iterate or pick the specific depth you want to log
    -- (Depth 1 is usually the trigger itself; higher depths are the callers)
    IF v_depth >= 1 THEN
        v_unit_name  := UTL_CALL_STACK.concatenate_subprogram(UTL_CALL_STACK.subprogram(v_depth));
            
        -- Insert into your custom audit log table
        INSERT INTO login_audit_log (log_date, username, called_unit)
        VALUES (SYSDATE, USER, v_unit_name);
    END IF;
END;

但LOGIN_AUDIT_LOG表中无任何记录，触发器仿佛未执行且无报错信息，请问可能遗漏了什么？或是否有其他获取存储过程名称的方法？

可能的遗漏点 #

• 触发器权限不足：创建数据库级AFTER LOGON触发器需要ADMINISTER DATABASE TRIGGER权限，若创建时未授予该权限，触发器无法正常触发。可通过以下语句检查并授权：

  -- 检查当前用户权限
  SELECT privilege FROM user_sys_privs WHERE privilege = 'ADMINISTER DATABASE TRIGGER';
  -- 授予权限（需DBA角色执行）
  GRANT ADMINISTER DATABASE TRIGGER TO your_user;
  

• 调用栈逻辑偏差：UTL_CALL_STACK.backtrace_depth在AFTER LOGON触发器执行时，调用栈深度通常仅为1（即触发器本身），此时获取的是触发器名称而非登录后要执行的存储过程。因为登录操作本身不触发存储过程调用，AFTER LOGON触发器在登录完成后立即执行，后续的存储过程调用尚未发生。
• 表权限与异常静默：触发器执行时，当前用户可能没有LOGIN_AUDIT_LOG表的插入权限，或者插入操作抛出异常（如字段类型不匹配、表不存在）。系统事件触发器的异常会被静默吞掉，不会返回给用户。可添加异常捕获块排查：

  CREATE OR REPLACE TRIGGER log_login_procedure
  AFTER LOGON ON DATABASE
  DECLARE
      v_depth       PLS_INTEGER;
      v_unit_name   VARCHAR2(255);
      v_subprogram  VARCHAR2(255);
  BEGIN
      v_depth := UTL_CALL_STACK.backtrace_depth;
      IF v_depth >= 1 THEN
          v_unit_name := UTL_CALL_STACK.concatenate_subprogram(UTL_CALL_STACK.subprogram(v_depth));
          INSERT INTO login_audit_log (log_date, username, called_unit)
          VALUES (SYSDATE, USER, v_unit_name);
      END IF;
  EXCEPTION
      WHEN OTHERS THEN
          -- 需先创建log_errors表存储异常信息
          INSERT INTO log_errors (error_date, error_msg)
          VALUES (SYSDATE, SQLERRM);
  END;
  

• 触发器被禁用：检查触发器状态，若已禁用则需启用：

  SELECT status FROM user_triggers WHERE trigger_name = 'LOG_LOGIN_PROCEDURE';
  -- 启用触发器
  ALTER TRIGGER log_login_procedure ENABLE;
  

其他获取存储过程名称的方法 #

如果需求是捕获登录后执行的存储过程，AFTER LOGON触发器无法直接实现，可采用以下替代方案：

• 数据库审计：启用数据库审计功能，审计存储过程执行事件并关联登录会话：

  -- 启用存储过程执行审计
  AUDIT EXECUTE ON PROCEDURE BY ACCESS;
  -- 查询审计记录
  SELECT username, obj_name, timestamp FROM dba_audit_trail WHERE action_name = 'EXECUTE' AND obj_type = 'PROCEDURE';
  

• AFTER EXECUTE触发器：针对存储过程执行事件创建触发器（Oracle 12c及以上支持），捕获执行的过程名称并关联会话登录信息：

  CREATE OR REPLACE TRIGGER audit_procedure_exec
  AFTER EXECUTE ON SCHEMA
  DECLARE
      v_session_id NUMBER := SYS_CONTEXT('USERENV', 'SESSIONID');
      v_login_time DATE;
  BEGIN
      SELECT logon_time INTO v_login_time FROM v$session WHERE sid = v_session_id;
      INSERT INTO proc_exec_audit (logon_time, username, proc_name, exec_time)
      VALUES (v_login_time, USER, ORA_DICT_OBJ_NAME, SYSDATE);
  END;
  

• 会话级SQL跟踪：使用DBMS_MONITOR启用会话跟踪，后续分析跟踪文件获取执行的存储过程：

  -- 启用指定会话的SQL跟踪
  EXEC DBMS_MONITOR.session_trace_enable(session_id => your_session_id, waits => FALSE, binds => FALSE);
  

内容的提问来源于stack exchange，提问作者Landon Statis