基于oracle user locked(timed) 故障处理 _MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 2203 | 回复: 0

主题： 基于oracle user locked(timed) 故障处理

liandong

注册用户

等级：新兵
经验：51
发帖：61
精华：0
注册：2011-11-14
状态：离线
发送短消息息给liandong

加好友发送短消息息给liandong

发消息

发表于：

2015-7-23 15:07:19 | [全部帖] [楼主帖]

楼主

故障现象：

SQL> connect scott/scott
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect scott/scott
ERROR:
ORA-28000: the account is locked

表明:oracle 中 scott用户因密码不对，10次以后，就被locked。

SQL> select account_status,lock_date,profile from dba_users where username='SCOTT';
ACCOUNT_STATUS TO_CHAR(LOCK_DATE,'D PROFILE
-------------------------------- -------------------- ------------------------------
LOCKED(TIMED) 31-MAY-2004 19:33:53 DEFAULT

这样，表明再添加oracle session时，就报错，无法连接上去。

故障解决：

（1）给当前用户解锁：

sql> alter user scott account unlock;

（2）查看当前用户的限制：

SQL> SELECT resource_name,resource_type,limit FROM dba_profiles WHERE profile='DEFAULT';
RESOURCE_NAME RESOURCE_TYPE LIMIT
-------------------------------- ------------- ----------------------------------------
COMPOSITE_LIMIT KERNEL UNLIMITED
SESSIONS_PER_USER KERNEL UNLIMITED
CPU_PER_SESSION KERNEL UNLIMITED
CPU_PER_CALL KERNEL UNLIMITED
LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
LOGICAL_READS_PER_CALL KERNEL UNLIMITED
IDLE_TIME KERNEL UNLIMITED
CONNECT_TIME KERNEL UNLIMITED
PRIVATE_SGA KERNEL UNLIMITED
FAILED_LOGIN_ATTEMPTS PASSWORD 10
PASSWORD_LIFE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_MAX PASSWORD UNLIMITED
PASSWORD_VERIFY_FUNCTION PASSWORD NULL
PASSWORD_LOCK_TIME PASSWORD UNLIMITED
PASSWORD_GRACE_TIME PASSWORD UNLIMITED

表明： 10次以后，就 failed_login_attempts 就报错，锁定用户。

需要修改成：unlimited

sql> alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;

或修改成：次数比较大：100000

sql>alter profile default limit FAILED_LOGIN_ATTEMPTS 100000；

然后再查看资源情况：

SQL> SELECT resource_name,resource_type,limit FROM dba_profiles WHERE profile='DEFAULT';
RESOURCE_NAME RESOURCE_TYPE LIMIT
-------------------------------- ------------- ----------------------------------------
COMPOSITE_LIMIT KERNEL UNLIMITED
SESSIONS_PER_USER KERNEL UNLIMITED
CPU_PER_SESSION KERNEL UNLIMITED
CPU_PER_CALL KERNEL UNLIMITED
LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
LOGICAL_READS_PER_CALL KERNEL UNLIMITED
IDLE_TIME KERNEL UNLIMITED
CONNECT_TIME KERNEL UNLIMITED
PRIVATE_SGA KERNEL UNLIMITED
FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
PASSWORD_LIFE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_MAX PASSWORD UNLIMITED
PASSWORD_VERIFY_FUNCTION PASSWORD NULL
PASSWORD_LOCK_TIME PASSWORD UNLIMITED
PASSWORD_GRACE_TIME PASSWORD UNLIMITED
16 rows selected
SQL>

这样就不会用：client端因输入密码错误，造成用户被locked(timed)

--转自