Oracle用户的密码如果频繁输入错误,超过了用户profile定义的重试次数,就会被locked,我们碰到过用户改了密码,但是某个应用配置中没改,导致频繁重试,频繁被锁。
这篇文章介绍了一种通过AUD$审计功能定位用户的操作,可以借鉴学习,https://www.modb.pro/db/102291?utm_source=index_ai
一台测试库,业务反馈某个用户频繁被锁定,但又不好判断是哪台机器,
SYS>SELECT USERNAME, ACCOUNT_STATUS, LOCK_DATE
FROM DBA_USERS
WHERE ACCOUNT_STATUS = 'LOCKED(TIMED)';
2 3
USERNAME ACCOUNT_STATUS LOCK_DATE
---------- ----------------- -----------
TEST LOCKED(TIMED) 23-JAN-21
审计信息记录在sys.aud$表里,当前数据有2000多w,效率不高,先备份再truncate,
SYS>select count(*) from aud$;
COUNT(*)
----------
21419082
create table audit_20210123 TABLESPACE DATA_AUDI as select * from sys.aud$;
truncate table sys.aud$;
检查审计功能,
SYS>show parameter audit_trail
NAME TYPE VALUE
------------ -------- -------
audit_trail string DB
开启了DB模式,审计日志记录到aud$,如果没开启就是NONE,改参数,重启实例生效,
alter system set audit_trail=db scope=spfile;
开启审计,
--对目标用户登陆失败进行审计
AUDIT SESSION BY TEST WHENEVER NOT SUCCESSFUL;
检查审计日志,
SELECT A.TIMESTAMP, A.RETURNCODE
FROM DBA_AUDIT_SESSION A
WHERE A.USERNAME = 'TEST'
ORDER BY 1;
返回信息如下,
TIMESTAMP RETURNCODE
-------------------- ------------
2021-01-23 13:01:01 28000
2021-01-23 13:02:15 28000
2021-01-23 13:03:17 1017
2021-01-23 13:04:05 1017
2021-01-23 13:05:19 1017
2021-01-23 13:06:02 1017
2021-01-23 13:07:15 1017
2021-01-23 13:08:10 1017
2021-01-23 13:09:03 1017
2021-01-23 13:10:00 1017
2021-01-23 13:11:03 1017
2021-01-23 13:12:37 1017
2021-01-23 13:13:11 28000
可以看出从13:02:15开始,对用户解锁,接着连续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 = 'TEST';
关闭审计,
NOAUDIT CONNECT BY TEST;
如果想对所有用户开启登陆失败审计,用下面的命令,
AUDIT SESSION WHENEVER NOT SUCCESSFUL;
NOAUDIT CONNECT; --关闭审计
如果您认为这篇文章有些帮助,还请不吝点下文章末尾的"点赞"