报错信息：
一、问题描述：
测试环境数据库发现有个用户经常用段时间就会被锁定，报错信息为ORA-28000:the account is locked，这是由于用户在输入错误10次密码后，会将该用户锁定。此处我的用户名为“HOLYTAX”。
FAILED_LOGIN_ATTEMPTS参数，表示为用户在登录时连续输入错误密码的最大次数，超过该值后锁定该账号。以下语句可查询：
SQL> select * from dba_profiles s where s.profile='DEFAULT' and resource_name='FAILED_LOGIN_ATTEMPTS';
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
---------- ------------------------------ --------------- ----------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
二、问题处理方法：
1. 检查锁定时间
[oracle@tora01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 23 15:26:28 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> set lines 120
SQL> select username,ACCOUNT_STATUS,lock_date from dba_users where username='HOLYTAX';
USERNAME ACCOUNT_STATUS LOCK_DATE
------------------------------ -------------------------------- -------------------
HOLYTAX LOCKED(TIMED) 2015-03-23 15:02:51
2. 通过listener.log日志，查看此时哪个ip或机器在连接数据库，该机器应该就是在连接数据库时输错密码的机器。
[oracle@tora01 trace]$ pwd
/home/oracle/app/diag/tnslsnr/tora01/listener/trace
[oracle@tora01 trace]$ ls
listener.log
|
Mon Mar 23 15:01:28 2015
23-MAR-2015 15:01:28 * (CONNECT_DATA=(SID=HOLYTEST)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.7.44)(PORT=45531)) * establish * HOLYTEST * 0
23-MAR-2015 15:01:37 * (CONNECT_DATA=(SID=HOLYTEST)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.7.39)(PORT=51338)) * establish * HOLYTEST * 0
Mon Mar 23 15:02:44 2015
23-MAR-2015 15:02:44 * (CONNECT_DATA=(SID=HOLYTEST)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.7.39)(PORT=51357)) * establish * HOLYTEST * 0
Mon Mar 23 15:03:52 2015
23-MAR-2015 15:03:52 * (CONNECT_DATA=(SID=HOLYTEST)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.7.39)(PORT=51372)) * establish * HOLYTEST * 0
Mon Mar 23 15:04:52 2015
23-MAR-2015 15:04:52 * (CONNECT_DATA=(SID=HOLYTEST)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.7.39)(PORT=51391)) * establish * HOLYTEST * 0
Mon Mar 23 15:05:44 2015
23-MAR-2015 15:05:44 * (CONNECT_DATA=(SID=HOLYTEST)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=wls02))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.7.44)(PORT=45532)) * establish * HOLYTEST * 0
23-MAR-2015 15:05:52 * (CONNECT_DATA=(SID=HOLYTEST)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.7.39)(PORT=51405)) * establish * HOLYTEST * 0
|
3. 修改FAILED_LOGIN_ATTEMPTS的值，加大数值至30次或无限次。（为安全起见，不建议使用无限次）
SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS 30;
SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;
4. 检查修改是否成功
SQL> select * from dba_profiles s where s.profile='DEFAULT' and resource_name='FAILED_LOGIN_ATTEMPTS';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
总结：该参数是为了保护oracle用户安全，建议生产环境不要进行unlimited设置。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26148431/viewspace-1471865/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
误认为linux机器内存使用光光
请登录后发表评论
登录
全部评论
