Oracle AUD审计 找出锁定用户的客户端IP

问题描述：运用AUD审计找出锁定用户的客户端IP

1.查询被锁用户

SELECT USERNAME, ACCOUNT_STATUS, LOCK_DATE FROM DBA_USERS WHERE ACCOUNT_STATUS = 'LOCKED(TIMED)';

SELECT USERNAME, ACCOUNT_STATUS, LOCK_DATE FROM DBA_USERS WHERE ACCOUNT_STATUS = 'LOCKED';

USERNAME               ACCOUNT_STATUS            LOCK_DATE

------------------------------ -------------------------------- ---------

TEST01                   LOCKED                20-AUG-21

2.备份审计表

select count(*) from aud$;

  COUNT(*)

----------

  21419082

create table audit_20210823 TABLESPACE DATA_AUDI as select * from sys.aud$;

truncate table sys.aud$;

3.检查审计功能

show parameter audit_trail

NAME                     TYPE     VALUE

------------------------------------ ----------- ------------------------------

audit_trail                 string     DB

如果没开启就是NONE，改参数，重启实例生效；

alter system set audit_trail=db scope=spfile;

4.开启对锁定用户的审计功能

--对目标用户登陆失败进行审计

AUDIT SESSION BY A_MKU_XH  WHENEVER NOT SUCCESSFUL;

5.检查审计日志

alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

SELECT A.TIMESTAMP, A.RETURNCODE FROM DBA_AUDIT_SESSION A WHERE A.USERNAME = 'TEST01' ORDER BY 1;

       TIMESTAMP    RETURNCODE

109    2021/8/20 13:58:17    28000

110    2021/8/20 14:00:38    28000

71    2021/8/20 13:41:46    1017

72    2021/8/20 13:41:56    1017

73    2021/8/20 13:42:06    1017

68    2021/8/20 13:41:16    1017

69    2021/8/20 13:41:26    1017

可以看出从16:33:25开始，对用户解锁，接着连续10次的1017密码错误，随后继续28000用户被锁。

01017, 00000, “invalid username/password; logon denied”

28000, 00000, “the account is locked”

从COMMENT$TEXT 连接串找到客户端IP，通知用户使用人处理；

SELECT A.COMMENT$TEXT FROM SYS.AUD$ A WHERE USERID = 'TEST01';

       COMMENT$TEXT

14    Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.163.1)(PORT=63364))

15    Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.163.1)(PORT=63365))

27    Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.163.1)(PORT=55396))

28    Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.163.1)(PORT=55397))

29

30    Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.163.1)(PORT=55409))

6.关闭审计

NOAUDIT CONNECT TEST01;

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

Oracle AUD审计 找出锁定用户的客户端IP的相关教程结束。