oracle账号频繁锁定,以前的一个问题,现在有碰到,故记于此,以便后人
现象:
Enter password:
ERROR:
ORA-28000: the account is locked
Enter password:
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.
解决过程
查看锁定时间及锁定类别:
SQL> conn / as sysdba
Connected.
SQL> select username,to_char(lock_date,'yyyy/mm/dd hh24:mi:ss') as locktime,account_status from dba_users where username='***********';
SQL> conn / as sysdba
Connected.
SQL> select username,to_char(lock_date,'yyyy/mm/dd hh24:mi:ss') as locktime,account_status from dba_users where username='***********';
USERNAME LOCKTIME
------------------------------ --------------------
ACCOUNT_STATUS
--------------------------------
*********** 2012/01/13 09:42:21
LOCKED(TIMED)
------------------------------ --------------------
ACCOUNT_STATUS
--------------------------------
*********** 2012/01/13 09:42:21
LOCKED(TIMED)
由此可知,该账号是由于错误登陆次数超过默认次数而锁定
账号解锁
SQL> alter user *********** account unlock;
User altered.
查看监听日志记录,确定账号被锁原因:
目录:$ORACLE_HME/network/log/
$ tail -20 listener.log
发现 大量以下信息
... ...
13-1月 -2012 09:44:48 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=m1hf)(CID=(PROGRAM=C:\Program?Files\utstxsec\utsdaemon.exe)(HOST=***********)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.37)(PORT=62083)) * establish * m1hf * 0
13-1月 -2012 09:44:48 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=m1hf)(CID=(PROGRAM=C:\Program?Files\utstxsec\utsdaemon.exe)(HOST=***********)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.37)(PORT=62084)) * establish * m1hf * 0
13-1月 -2012 09:44:52 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=m1hf)(CID=(PROGRAM=C:\Program?Files\utstxsec\utsdaemon.exe)(HOST=***********)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.37)(PORT=62086)) * establish * m1hf * 0
13-1月 -2012 09:44:52 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=m1hf)(CID=(PROGRAM=C:\Program?Files\utstxsec\utsdaemon.exe)(HOST=***********)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.37)(PORT=62087)) * establish * m1hf * 0
13-1月 -2012 09:44:48 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=m1hf)(CID=(PROGRAM=C:\Program?Files\utstxsec\utsdaemon.exe)(HOST=***********)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.37)(PORT=62084)) * establish * m1hf * 0
13-1月 -2012 09:44:52 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=m1hf)(CID=(PROGRAM=C:\Program?Files\utstxsec\utsdaemon.exe)(HOST=***********)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.37)(PORT=62086)) * establish * m1hf * 0
13-1月 -2012 09:44:52 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=m1hf)(CID=(PROGRAM=C:\Program?Files\utstxsec\utsdaemon.exe)(HOST=***********)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.37)(PORT=62087)) * establish * m1hf * 0
定位原因:
由于在使用 uts 数据同步软件后,修改了一次 *********** 账号的密码,而未修改其在前台应用接口的密码,使得周期调度的数据同步软件 ust 频繁以错误密码登陆,导致数据库用户*********** 频繁被锁。
由于在使用 uts 数据同步软件后,修改了一次 *********** 账号的密码,而未修改其在前台应用接口的密码,使得周期调度的数据同步软件 ust 频繁以错误密码登陆,导致数据库用户*********** 频繁被锁。
解决办法:
1、将相关应用接口的密码修改(推荐采用)
2、修改用户 profile 默认失败登陆次数为无线
2、修改用户 profile 默认失败登陆次数为无线
SQL> select * from dba_profiles where RESOURCE_NAME like 'FAILED_LOGIN_ATTEMPTS';
PROFILE RESOURCE_NAME RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD
10
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD
10
MONITORING_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD
UNLIMITED
UNLIMITED
修改为无线次失败登陆
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26143577/viewspace-722331/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26143577/viewspace-722331/