oracle 用户登录失败审计--用途：数据库修改了新密码，但是部分客户端没修改导致账号被锁

1、打开审计
alter system audit_trail='DB' scope=spfile;
shutdown immediate;
startup;

2、审计失败用户
audit session whenever not successful;


3、查看失败用户登录信息：


select  to_char(a.timestamp,'yyyy-mm-dd hh24:mi:ss'),a.returncode from dba_audit_session a
     where a.username='SCOTT' order by 1;


TO_CHAR(A.TIMESTAMP RETURNCODE
------------------- ----------
2024-08-30 11:33:02	  1017
2024-08-30 11:44:14	  1017
2024-08-30 13:13:10	  1017
2024-08-30 13:23:10	  28000


returncode 描述:
01017: “invalid username/password; logon denied”
28000: “the account is locked”
0  ：连接成功


select USERHOST,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') timestamp,ACTION_NAME ,returncode ,CLIENT_ID from dba_audit_session where username='HISOFT_RECORD' and  returncode=1017;


USERHOST                       TIMESTAMP                              ACTION_NAME                                              RETURNCODE
------------------------------ -------------------------------------- -------------------------------------------------------- ----------
bltzweb03                      2024-08-30 17:14:19                    LOGON                                                          1017
bltzweb01                      2024-08-30 17:14:29                    LOGON                                                          1017
bltzweb03                      2024-08-30 17:14:39                    LOGON                                                          1017
bltzweb01                      2024-08-30 17:14:49                    LOGON                                                          1017
bltzweb03                      2024-08-30 17:14:59                    LOGON                                                          1017
bltzweb01                      2024-08-30 17:15:09                    LOGON                                                          1017



set linesize  1000
col COMMENT$TEXT for a100
select   comment$text  from sys.aud$ where USERHOST='bltzweb01' and rownum <10;
COMMENT$TEXT
----------------------------------------------------------------------------------------------------
Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=XX.XX.XX.XX)(PORT=42070))
Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=XX.XX.XX.XX)(PORT=42078))
Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=XX.XX.XX.XX)(PORT=42192))
Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=XX.XX.XX.XX)(PORT=48078))
Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=XX.XX.XX.XX)(PORT=48080))




4、关闭审计

NOAUDIT CONNECT BY SCOTT;


--如果想对所有用户开启登陆失败审计，则用下面的命令：
AUDIT SESSION WHENEVER NOT SUCCESSFUL;
NOAUDIT CONNECT;  --关闭审计


备注：
如果审计数据太多可以清空
truncate table sys.aud$;