Oracle里面的user被lock了

今天遇到一个很常见的问题“ORA-28000: the account is locked”，如是也没多想，直接操作

SQL>conn /as sysdba

SQL>alter user test account unlock;

SQL>conn test/test

还是报同样的错误，这就奇怪了。看看dba_users中该用户的状态等信息

SQL>select account_status,lock_date,profile from dba_users where username='TEST';

发现就是今天才锁定的，帐号状态为“LOCKED(TIMED)”，不明白这个TIMED什么意思，搜了一把，原来是密码错误次数超过了系统设置的允许最大次数。

查看现在设置的最大次数

SELECT resource_name,resource_type,limit FROM dba_profiles WHERE profile='DEFAULT';

找到

FAILED_LOGIN_ATTEMPTS            PASSWORD      10
也就是说，原来允许的最大密码错误次数为10次，超过10次自动锁定帐号。难怪我怎么alter unlock都没用，看来还得先把这限制放宽才行。

把限制参数 FAILED_LOGIN_ATTEMPTS 修改成：unlimited 

sql> alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;

或 修改成比较大次数：100000

sql>alter profile default limit FAILED_LOGIN_ATTEMPTS 100000；

再执行如下

SQL>alter user test account unlock;

SQL>conn test/test

就可以正常登录了。

 

下面，就看看dba_users中的account_status运行的各种状态以及对应的说明。

DBA_USERS中的ACCOUNT_STATUS的值有
OPEN
EXPIRED
EXPIRED(GRACE)
LOCKED(TIMED)
LOCKED
EXPIRED & LOCKED(TIMED)
EXPIRED(GRACE) & LOCKED(TIMED)
EXPIRED & LOCKED
EXPIRED(GRACE) & LOCKED
下面分别解释一下open.locked,expired,EXPIRED(GRACE) ,LOCKED(TIMED) ,其余几个概念就是这几个状态的组合。

1. open 当前用户是开放的可以使用的.

SQL> select username,account_status from dba_users where username = 'TONGJW';

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
TONGJW                         OPEN

SQL> conn tongjw
Enter password: *******
Connected.

2. expired 表示用户已经过期, 登录的时候, 如果密码输入正确,系统会提示重新输入密码的.

SQL> conn jfdata/jf
Connected.
SQL> alter user tongjw account unlock;

User altered.

SQL> alter user tongjw password expire;

User altered.

SQL> conn tongjw
Enter password: *******
ERROR:
ORA-28001: the password has expired

Changing password for tongjw
New password: *******
Retype new password: *******
Password changed
Connected.

3. locked 表明当前帐户被锁了, 不可以登录的.

SQL> conn jfdata/jf
Connected.
SQL> alter user tongjw account lock;

User altered.

SQL> conn tongjw
Enter password: *******
ERROR:
ORA-28000: the account is locked

Warning: You are no longer connected to ORACLE.

expired (grace) 与 locked (timed) 是有系统的profile来进行控制的.

4. expired (grace) 指定一个期限, 到达这个期限的时候,系统会给出一个警告并且允许登录(grace), 如果过了这段期限, 对应的密码就会自动过期, 相当于我们的密码过期提醒.

PASSWORD_GRACE_TIME
PASSWORD_LIFE_TIME
Specify the number of days after the grace period begins during which a warning is issued and login is allowed. If the password is not changed during the grace period, the password expires.

SQL> select username,account_status,profile from dba_users where username = 'TONGJW';

USERNAME                       ACCOUNT_STATUS                   PROFILE
------------------------------ -------------------------------- ------------------------------
TONGJW                         OPEN                             DEFAULT

SQL> create profile test limit
  2    password_life_time 5 password_grace_time 5;

Profile created.

SQL> alter user tongjw profile test;

User altered.

SQL> select username,account_status,profile from dba_users where username = 'TONGJW';

USERNAME                       ACCOUNT_STATUS                   PROFILE
------------------------------ -------------------------------- ------------------------------
TONGJW                         OPEN                             TEST

SQL> conn tongjw/playboy
Connected.
SQL> /

这时我们修改系统时间, 将日期往后修改几天

[oracle@TzDbTzcenter2 ]$ su
Password:
[root@TzDbTzcenter2 ]# date
Tue Mar 22 18:42:26 CST 2005
[root@TzDbTzcenter2 ]# date '03281842'
Mon Mar 28 18:42:00 CST 2005
[root@TzDbTzcenter2 ]# exit
[oracle@TzDbTzcenter2 ]$ sqlplus tongjw/tongjw

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Mar 28 18:42:01 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

ERROR:
ORA-28002: the password will expire within 5 days

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> conn jfdata/jf
Connected.
SQL> select username,account_status,profile from dba_users where username = 'TONGJW';

USERNAME                       ACCOUNT_STATUS                   PROFILE
------------------------------ -------------------------------- ------------------------------
TONGJW                         EXPIRED(GRACE)                   TEST

SQL>

再将日期向后修改几天, 我们可以看到此时用户的状态已经改为Expired

[oracle@TzDbTzcenter2 ]$ sqlplus tongjw/tongjw

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Apr 5 18:44:02 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

ERROR:
ORA-28001: the password has expired

Changing password for tongjw
New password:
Retype new password:
ERROR:
ORA-00988: missing or invalid password(s)

Password unchanged
Enter user-name: jfdata/jf

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> select username,account_status,profile from dba_users where username = 'TONGJW';

USERNAME                       ACCOUNT_STATUS                   PROFILE
------------------------------ -------------------------------- ------------------------------
TONGJW                         EXPIRED                          TEST

5. locked(timed)
PASSWORD_LOCK_TIME
Specify the number of days an account will be locked after the specified number of consecutive failed login attempts.
FAILED_LOGIN_ATTEMPTS
Specify the number of failed attempts to log in to the user account before the account is locked.

设定相关的profile属性^_^.

SQL> alter user tongjw profile default;

User altered.

SQL> drop profile test;

Profile dropped.

SQL> create profile test limit
  2    password_lock_time 5 failed_login_attempts 3;

Profile created.

SQL> alter user tongjw profile test;

User altered.

修改系统的时间信息.

[root@TzDbTzcenter2 ]# date
Tue Mar 22 18:55:42 CST 2005
[root@TzDbTzcenter2 ]# date '03281855'
Mon Mar 28 18:55:00 CST 2005
[root@TzDbTzcenter2 ]# exit
[oracle@TzDbTzcenter2 ]$ sqlplus jfdata/jf

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Mar 28 18:55:02 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> conn tongjw/tongjw
Connected.
SQL> conn tongjw/tongj1
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn tongjw/tongj2
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> conn tongjw/tongj3
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> conn tongjw/tongj4
ERROR:
ORA-28000: the account is locked

SQL> conn tongjw/tongjw
ERROR:
ORA-28000: the account is locked

SQL> conn jfdata/jf
Connected.
SQL> select username,account_status,profile from dba_users where username = 'TONGJW';

USERNAME                       ACCOUNT_STATUS                   PROFILE
------------------------------ -------------------------------- ------------------------------
TONGJW                         LOCKED(TIMED)                    TEST

SQL>

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9399028/viewspace-720003/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9399028/viewspace-720003/