Oracle Apex实现登录失败3次锁定账户及代码问题排查

问题分析 #

当前登录函数存在两个核心问题导致锁定触发次数不符合预期：

• 用户名大小写未统一处理：日志表usuarios_log的匹配和插入未转换为统一大小写，用户输入不同大小写的用户名会生成多条独立计数记录，导致失败次数被分散，需要多次输入才会达到阈值。
• 阈值与判断条件不匹配：设置的最大失败次数l_max_failed_cnt :=4，且判断条件为l_failed_cnt > l_max_failed_cnt，意味着需要5次失败才会锁定，和需求的3次不符。

修复后的代码 #

create or replace function LOGIN2021 ( p_username varchar2, p_password varchar2 ) return boolean is  
   l_rcnt number;
   l_failed_cnt number;
   -- 设置最大允许失败次数为3次
   l_max_failed_cnt number :=3;
   l_lock_flag varchar2(1);
   -- 统一转换为大写用户名，避免大小写差异问题
   l_upper_username varchar2(100) := upper(p_username);
   
begin
    select count(1) into l_rcnt
    from usuarios t1
    where upper(t1.USUARIO) = l_upper_username
     and upper(t1.CLAVE) = upper(p_password)
     and t1.enabled_flag = 'Y';
     
    if l_rcnt > 0 then
        -- 查询用户锁定状态时，仅按用户名匹配（无需重复校验密码）
        select is_locked into l_lock_flag 
        from usuarios 
        where upper(usuario) = l_upper_username;
            
        if l_lock_flag = 'N' then
            -- 清理该用户所有失败登录日志（不区分大小写）
            delete from usuarios_log where upper(usuario) = l_upper_username;
            return true;
        elsif l_lock_flag = 'Y' then
            apex_util.set_custom_auth_status (p_status => '您的账户已锁定！请联系系统管理员解锁，谢谢。');
            return false;
        end if;
    else
        -- Merge操作统一使用大写用户名匹配，确保同用户仅一条记录
        merge into usuarios_log u
        using dual 
        on (upper(u.usuario) = l_upper_username)
        when matched then
            update set login_fallido_cont = login_fallido_cont+1, update_on = sysdate
        when not matched then
            insert (usuario, login_fallido_cont, update_on) values
            (l_upper_username, 1, sysdate);
            
        -- 查询该用户累计失败次数（统一大小写）
        select sum(login_fallido_cont) into l_failed_cnt
        from usuarios_log 
        where upper(usuario) = l_upper_username;
        
        -- 累计失败次数达到或超过阈值时锁定账户
        if l_failed_cnt >= l_max_failed_cnt then
            update usuarios set is_locked = 'Y' where upper(usuario) = l_upper_username;
            apex_util.set_custom_auth_status (p_status => '您的账户因多次登录失败已锁定！请联系系统管理员解锁，谢谢。');
        end if;
        return false;
    end if;
exception 
    when others then
        return false;
end;    

关键修改说明 #

1. 统一用户名大小写：新增l_upper_username变量存储转换后的大写用户名，所有涉及用户名匹配的SQL均使用该变量，避免因大小写差异生成多条日志记录。
2. 调整阈值与判断条件：将l_max_failed_cnt设为3，判断条件改为l_failed_cnt >= l_max_failed_cnt，确保第3次无效登录时立即锁定账户。
3. 优化日志计数查询：使用sum(login_fallido_cont)统计该用户所有日志记录的累计失败次数，即使存在历史大小写不一致的记录也能正确统计。
4. 简化成功登录校验：查询用户锁定状态时无需重复校验密码，仅按用户名匹配即可，提升性能。

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