ORACLE 帳戶 狀態說明

大概的給解釋一下open.locked,expired,EXPIRED(GRACE) ,LOCKED(TIMED) ,
其餘幾個概念就是這幾個狀態的組合:-)

1. open 當前是開放的可以使用的.
[color=gray]SQL> select username,account_status from dba_users where username = 'TONGJW';

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

SQL> conn tongjw
Enter password: *******
Connected.
[/color]
2. expired 表示使用者已經過期, 登入的時候, 如果密碼輸入正確,系統會提示重新輸入密碼的.
[color=gray]
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.
[/color]

3. locked 表明當前帳戶被鎖了, 不可以登入的.
[color=gray]
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 .
[/color]
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.
[color=gray]
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> /
[/color]
[color=red]這是我們修改系統時間, 將日期往後修改幾天[/color]
[color=gray]
[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:
[b]ORA-28002: the password will expire within 5 days[/b]



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>
[/color]
[color=red]再將日期向後修改幾天, 我們可以看到此時使用者的狀態已經改為Expired[/color]
[color=gray]
[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
[/color]


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.

（在PROFILE中設定最大錯誤密碼輸入次數，而被LOCK掉了）
FAILED_LOGIN_ATTEMPTS
Specify the number of failed attempts to log in to the user account before the account is locked.

設定相關的profile屬性^_^.
[color=gray]
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.
[/color]
修改系統的時間資訊.
[color=gray]
[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