转载：http://blog.ixpub.net/1636/viewspace-423134
Oracle的用户帐户管理里面大有学问了, 控制非常灵活, 设计非常精巧, 如果我们能够在我们自己的数据库应用程序中应用这种技术, 相信用户也会非常开心的^_^.用户的Accountstatus一共有一下几种情况.SQL> select * from user_astatus_map;STATUS# STATUS---------- --------------------------------0 OPEN 当前帐户是开放的用户可以自由登录1 EXPIRED 当前帐户已经过期, 用户必须在修改密码以后才可以登录系统, 在登录的时候, 系统会提示修改密码2 EXPIRED(GRACE) 这是有password_grace_time定义的一个时间段, 在用户密码过期以后的第一次登录, 系统会提示用户,密码在指定的时间段以后会过期, 需要及时修改系统密码.4 LOCKED(TIMED) 这是一个有条件的帐户锁定日期, 由password_lock_time进行控制, 在lock_date加上password_lock_time的日期以后,帐户会自动解锁.8 LOCKED 帐户是锁定的,用户不可以登录, 必须由安全管理员将帐户打开用户才可以登录.5 EXPIRED & LOCKED(TIMED)6 EXPIRED(GRACE) & LOCKED(TIMED)9 EXPIRED & LOCKED10 EXPIRED(GRACE) & LOCKED9 rows selected.下面大概解释一下open.locked,expired,EXPIRED(GRACE) ,LOCKED(TIMED) ,其余几个概念就是这几个状态的组合:-), 我就不多言了.1. open 当前用户是开放的可以使用的.SQL> select username,account_status from dba_users where username = 'TONGJW';USERNAME ACCOUNT_STATUS------------------------------ --------------------------------TONGJW OPENSQL> conn tongjwEnter password: *******Connected.2. expired 表示用户已经过期, 登录的时候, 如果密码输入正确,系统会提示重新输入密码的.SQL> conn jfdata/jfConnected.SQL> alter user tongjw account unlock;User altered.SQL> alter user tongjw password expire;User altered.SQL> conn tongjwEnter password: *******ERROR:ORA-28001: the password has expiredChanging password for tongjwNew password: *******Retype new password: *******Password changedConnected.3. locked 表明当前帐户被锁了, 不可以登录的.SQL> conn jfdata/jfConnected.SQL> alter user tongjw account lock;User altered.SQL> conn tongjwEnter password: *******ERROR:ORA-28000: the account is lockedWarning: You are no longer connected to ORACLE.expired (grace) 与 locked (timed) 是有系统的profile来进行控制的.4. expired (grace) , 通过profile的password_grace_time进行控制.password_grace_time 指的是在你的密码已经过期以后, 第一次登录时间开始往后统计, 使系统可以使用的日期限度.SQL> select username,account_status,lock_date,expiry_date,profile2 from dba_users where username = 'TONGJW'3 /USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA PROFILE------------------------------ -------------------------------- --------- --------- ------------------------------TONGJW OPEN DEFAULTSQL> create profile test limit password_life_time 10 password_grace_time 3;Profile created.SQL> alter user tongjw profile test;User altered.SQL> select username,account_status,lock_date,expiry_date,profile2 from dba_users where username = 'TONGJW'3 /USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA PROFILE------------------------------ -------------------------------- --------- --------- ------------------------------TONGJW OPEN 15-APR-05 TEST为什么这个地方是 05-4-15日到期, 我还需要进一步学习^_^.SQL> host[oracle@TzDbTzcenter2 ]$ suPassword: ********我们修改对应的系统时间, 使其在密码过期的日期以后.[root@TzDbTzcenter2 ]# date '04161528'Sat Apr 16 15:28:00 CST 2005[root@TzDbTzcenter2 ]# exit[oracle@TzDbTzcenter2 ]$ exitSQL> select username,account_status,lock_date,expiry_date,profile2 from dba_users where username = 'TONGJW'3 /USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA PROFILE------------------------------ -------------------------------- --------- --------- ------------------------------TONGJW OPEN 15-APR-05 TESTSQL> conn tongjw/tongjwERROR:ORA-28002: the password will expire within 3 daysConnected.SQL> conn jfdata/jfConnected.SQL> select username,account_status,lock_date,expiry_date,profile2 from dba_users where username = 'TONGJW'3 /USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA PROFILE------------------------------ -------------------------------- --------- --------- ------------------------------TONGJW EXPIRED(GRACE) 19-APR-05 TESTSQL> host[oracle@TzDbTzcenter2 ]$ suPassword: ********[root@TzDbTzcenter2 ]# date '04201529'Wed Apr 20 15:29:00 CST 2005[root@TzDbTzcenter2 ]# exit[oracle@TzDbTzcenter2 ]$ exitSQL> select username,account_status,lock_date,expiry_date,profile2 from dba_users where username = 'TONGJW'3 /USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA PROFILE------------------------------ -------------------------------- --------- --------- ------------------------------TONGJW EXPIRED(GRACE) 19-APR-05 TESTSQL> conn tongjw/tongjwERROR:ORA-28001: the password has expiredChanging password for tongjwNew password:Retype new password:ERROR:ORA-00988: missing or invalid password(s)Password unchangedWarning: You are no longer connected to ORACLE.SQL> conn jfdata/jfConnected.SQL> select username,account_status,lock_date,expiry_date,profile2 from dba_users where username = 'TONGJW'3 /USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA PROFILE------------------------------ -------------------------------- --------- --------- ------------------------------TONGJW EXPIRED 19-APR-05 TESTSQL> conn tongjw/tongjwERROR:ORA-28001: the password has expiredChanging password for tongjwNew password: ******Retype new password: ******Password changedConnected.SQL> conn jfdata/Enter password: **Connected.SQL> select username,account_status,lock_date,expiry_date,profile2 from dba_users where username = 'TONGJW'3 /USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA PROFILE------------------------------ -------------------------------- --------- --------- ------------------------------TONGJW OPEN 30-APR-05 TESTSQL>如果该用户, 没有登录, 对应的expiry_date是不会发生改变的,只要铜壶登录, 对应的expiry_date就会进行更新, 当然也会提示用户修改对应的密码,也就是说, 这个日期是在用户登录的时候进行更新的,更进一步, 我们可由此断定, Oracle是在用户登录的时候, 取出用户对应的这些信息,if expiry_date > sysdate thenenter intoelse if expiry_date < sysdate thenif grace_time is set thenexpiry_date = sysdate + expiry_date;elseprompt your passwors has expired.end ifend if;用户的密码过期路径可以大略的表示如下.t1 设置profile的时间/或者用户重新修改密码的时间05-4-5t2 用户在密码过期以后,第一次登录数据库的时间, 05-4-16 password_change_time + life_timet3 用户密码真正过期的时间, 05-04-19 05-04-16 + grace_timet4 修改密码的时间, 05-04-19...t1 t2 t3 t4|<-----------密码正常时期------------------>|<------grace expird----->|<-------expired------------>||---------------------------------------------------------------------------------------------------------------------5. lock (timed) 可以这么理解,a. 当前系统是锁定的, b. 这个锁定有一个时间限制,是timed. 这个时间跨度有profile的password_ lock_time来进行控制.SQL> select username,account_status,lock_date,expiry_date,profile2 from dba_users where username = 'TONGJW'3 /USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA PROFILE------------------------------ -------------------------------- --------- --------- ------------------------------TONGJW OPEN DEFAULTSQL> create profile test limit2 failed_login_attempts 33 password_lock_time 34 /Profile created.SQL> alter user tongjw profile test;User altered.SQL> select username,account_status,lock_date,expiry_date,profile2 from dba_users where username = 'TONGJW'3 /USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA PROFILE------------------------------ -------------------------------- --------- --------- ------------------------------TONGJW OPEN TESTSQL> alter user tongjw identified by tongjw2 /User altered.SQL> conn tongjw/tongERROR:ORA-01017: invalid username/password; logon deniedWarning: You are no longer connected to ORACLE.SQL> conn tongjw/tongjERROR:ORA-01017: invalid username/password; logon deniedSQL> conn tongjw/tong1ERROR:ORA-01017: invalid username/password; logon deniedSQL> conn jfdata/jfConnected.SQL> select username,account_status,lock_date,expiry_date,profile2 from dba_users where username = 'TONGJW'3 /USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA PROFILE------------------------------ -------------------------------- --------- --------- ------------------------------TONGJW LOCKED(TIMED) 26-MAR-05 TESTSQL> host[oracle@TzDbTzcenter2 ]$ suPassword: ********[root@TzDbTzcenter2 ]# date '04301603'Sat Apr 30 16:03:00 CST 2005[root@TzDbTzcenter2 ]# exit[oracle@TzDbTzcenter2 ]$ exitSQL> select username,account_status,lock_date,expiry_date,profile2 from dba_users where username = 'TONGJW'3 /USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA PROFILE------------------------------ -------------------------------- --------- --------- ------------------------------TONGJW LOCKED(TIMED) 26-MAR-05 TEST
DBA_USERS
DBA_USERSdescribes all users of the database.
Related View
USER_USERSdescribes the current user. This view does not display thePASSWORDorPROFILEcolumns.
Column
Datatype
NULL
Description
USERNAME
VARCHAR2(30)
NOT NULL
Name of the user
USER_ID
NUMBER
NOT NULL
ID number of the user
PASSWORD
VARCHAR2(30)
Encrypted password
ACCOUNT_STATUS
VARCHAR2(32)
NOT NULL
Account status:
· OPEN
· EXPIRED
· EXPIRED(GRACE)
· LOCKED(TIMED)
· LOCKED
· EXPIRED & LOCKED(TIMED)
· EXPIRED(GRACE) & LOCKED(TIMED)
· EXPIRED & LOCKED
· EXPIRED(GRACE) & LOCKED
LOCK_DATE
DATE
Date the account was locked if account status wasLOCKED
EXPIRY_DATE
DATE
Date of expiration of the account
DEFAULT_TABLESPACE
VARCHAR2(30)
NOT NULL
Default tablespace for data
TEMPORARY_TABLESPACE
VARCHAR2(30)
NOT NULL
Default tablespace for temporary table
CREATED
DATE
NOT NULL
User creation date
PROFILE
VARCHAR2(30)
NOT NULL
User resource profile name
INITIAL_RSRC_CONSUMER_GROUP
VARCHAR2(30)
Initial resource consumer group for the user
EXTERNAL_NAME
VARCHAR2(4000)
User external name
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch2410.htm#1315256
