定位错误密码来源,解决账号因密码错误被锁
1、有审计的话,可以直接查询aud$表:
查询最近1天由于密码错误登陆失败的信息(有开审计的情况下使用)
select sessionid, userid, userhost, comment$text, spare1, ntimestamp#
from aud$
where returncode = 1017
and ntimestamp# > sysdate - 1;
2、使用EVENT触发写入到alert后查看trace文件:
alter system set events ='1017 trace name errorstack level 1';
......
alter system set events ='1017 trace name errorstack off';
3、使用trigger实现写入到alert日志:
create or replace trigger logon_denied_to_alert
after servererror on database
declare
message varchar2(120);
IP varchar2(15);
v_os_user varchar2(80);
v_module varchar2(50);
v_action varchar2(50);
v_pid varchar2(10);
v_sid number;
begin
IF (ora_is_servererror(1017)) THEN
if sys_context('userenv', 'network_protocol') = 'tcp' then
IP := sys_context('userenv', 'ip_address');
else
select distinct sid into v_sid from sys.v_$mystat;
SELECT p.SPID
into v_pid
FROM V$PROCESS p, V$SESSION v
WHERE p.ADDR = v.PADDR
AND v.sid = v_sid;
end if;
v_os_user := sys_context('userenv', 'os_user');
dbms_application_info.READ_MODULE(v_module, v_action);
message := to_char(sysdate, 'Dy Mon dd HH24:MI:SS YYYY') ||
' logon denied from ' || nvl(IP, v_pid) || ' ' || v_os_user ||
' with ' || v_module || ' ' || v_action;
sys.dbms_system.ksdwrt(2, message);
end if;
end;
附登录相关常见错误:
[oracle28009, 00000, "connection as SYS should be as SYSDBA or SYSOPER"
// *Cause: connect SYS/ is no longer a valid syntax
// *Action: Try connect SYS/ as SYSDBA or
// connect SYS/ as SYSOPER
// [oracle
// *Cause: The user has entered wrong password consequently for maximum
// number of times specified by the user's profile parameter
// FAILED_LOGIN_ATTEMPTS, or the DBA has locked the account
// *Action: Wait for PASSWORD_LOCK_TIME or contact DBA [oracle
1017, 00000, "invalid username/password; logon denied"
// *Cause:
// *Action: [oracle
01920, 00000, "user name '%s' conflicts with another user or role name"
// *Cause: There is already a user or role with that name.
// *Action: Specify a different user name. [oracle
01005, 00000, "null password given; logon denied"
// *Cause:
// *Action: [oracle
02002, 00000, "error while writing to audit trail"
// *Cause:
// *Action: