Oracle数据库级触发器实现登录追踪方案咨询

一、通过触发器实现全登录行为追踪 #

1. 先创建存储登录日志的表 #

先建一张表来存储所有登录相关记录，字段覆盖关键信息：

CREATE TABLE LOGIN_AUDIT_LOG (
    LOG_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    LOGIN_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP,
    USERNAME VARCHAR2(30),
    LOGIN_STATUS VARCHAR2(10), -- 标记SUCCESS/FAILED
    CLIENT_IP VARCHAR2(40),
    OS_USER VARCHAR2(100),
    MACHINE_NAME VARCHAR2(100),
    AUTH_TYPE VARCHAR2(20)
);

2. 创建登录成功触发器 #

捕捉所有成功登录的行为：

CREATE OR REPLACE TRIGGER AFTER_LOGON_TRIGGER
AFTER LOGON ON DATABASE
DECLARE
    v_ip VARCHAR2(40);
    v_os_user VARCHAR2(100);
    v_machine VARCHAR2(100);
BEGIN
    SELECT SYS_CONTEXT('USERENV','IP_ADDRESS'),
           SYS_CONTEXT('USERENV','OS_USER'),
           SYS_CONTEXT('USERENV','HOST')
    INTO v_ip, v_os_user, v_machine
    FROM DUAL;

    INSERT INTO LOGIN_AUDIT_LOG (
        USERNAME, LOGIN_STATUS, CLIENT_IP, OS_USER, MACHINE_NAME, AUTH_TYPE
    ) VALUES (
        USER, 'SUCCESS', v_ip, v_os_user, v_machine, SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE')
    );
END;
/

3. 创建登录失败触发器 #

专门捕捉登录失败（包括密码错误、账户锁定）的场景：

CREATE OR REPLACE TRIGGER AFTER_LOGON_FAILURE_TRIGGER
AFTER SERVERERROR ON DATABASE
DECLARE
    v_user VARCHAR2(30);
    v_ip VARCHAR2(40);
    v_os_user VARCHAR2(100);
    v_machine VARCHAR2(100);
BEGIN
    -- 只处理登录相关错误：ORA-01017(无效密码/用户)、ORA-28000(账户锁定)、ORA-01005(空密码)
    IF SERVERERROR(1) IN (1017, 28000, 1005) THEN
        SELECT SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY'),
               SYS_CONTEXT('USERENV','IP_ADDRESS'),
               SYS_CONTEXT('USERENV','OS_USER'),
               SYS_CONTEXT('USERENV','HOST')
        INTO v_user, v_ip, v_os_user, v_machine
        FROM DUAL;

        INSERT INTO LOGIN_AUDIT_LOG (
            USERNAME, LOGIN_STATUS, CLIENT_IP, OS_USER, MACHINE_NAME, AUTH_TYPE
        ) VALUES (
            v_user, 'FAILED', v_ip, v_os_user, v_machine, SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE')
        );
    END IF;
END;
/

4. 配置必要权限 #

确保创建触发器的用户拥有对应权限：

GRANT ADMINISTER DATABASE TRIGGER TO YOUR_ADMIN_USER;
GRANT INSERT ON LOGIN_AUDIT_LOG TO YOUR_ADMIN_USER;

二、其他定位账户锁定原因的实用方案 #

• 启用Oracle自带审计功能
  开启标准审计后，可直接追踪登录失败和用户状态变更操作：

  -- 开启审计（需重启数据库生效）
  ALTER SYSTEM SET AUDIT_TRAIL=DB,EXTENDED SCOPE=SPFILE;
  -- 审计登录失败和用户修改操作
  AUDIT LOGON FAILURE BY ACCESS;
  AUDIT ALTER USER BY ANY BY ACCESS;
  

  查看审计记录：

  SELECT USERNAME, TIMESTAMP, ACTION_NAME, RETURNCODE, OS_USERNAME, USERHOST
  FROM DBA_AUDIT_TRAIL
  WHERE ACTION_NAME IN ('LOGON', 'ALTER USER') AND RETURNCODE IN (1017, 28000);
  

• 检查数据库告警日志
  告警日志（alert_<SID>.log）会记录账户锁定的时间和用户名，结合操作系统日志（Linux的/var/log/secure、Windows事件查看器）可快速定位来源IP或机器。

• 排查应用连接池
  多数账户锁定是因为应用连接池配置了错误密码、密码过期未更新，导致批量失败登录触发锁定。检查所有应用节点的数据库连接配置，尤其是自动重试的连接池。

• Oracle Database Vault（授权环境可用）
  对于高安全需求的环境，Database Vault可提供更精细的访问控制和审计，直接追踪到发起账户锁定操作的用户。

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