ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle的用户帐户管理

Oracle的用户帐户管理

原创 Linux操作系统 作者:JoshuaJin 时间:2011-04-11 11:55:54 0 删除 编辑
用户的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 & LOCKED
10 EXPIRED(GRACE) & LOCKED

9 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 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) , 通过profile的password_grace_time进行控制. password_grace_time 指的是在你的密码已经过期以后, 第一次登录时间开始往后统计, 使系统可以使用的日期限度. SQL> select username,account_status,lock_date,expiry_date,profile 2 from dba_users where username = 'TONGJW' 3 / USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA PROFILE ------------------------------ -------------------------------- --------- --------- ------------------------------ TONGJW OPEN DEFAULT SQL> 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,profile 2 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 ]$ su Password: ******** 我们修改对应的系统时间, 使其在密码过期的日期以后. [root@TzDbTzcenter2 ]# date '04161528' Sat Apr 16 15:28:00 CST 2005 [root@TzDbTzcenter2 ]# exit [oracle@TzDbTzcenter2 ]$ exit SQL> select username,account_status,lock_date,expiry_date,profile 2 from dba_users where username = 'TONGJW' 3 / USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA PROFILE ------------------------------ -------------------------------- --------- --------- ------------------------------ TONGJW OPEN 15-APR-05 TEST SQL> conn tongjw/tongjw ERROR: ORA-28002: the password will expire within 3 days Connected. SQL> conn jfdata/jf Connected. SQL> select username,account_status,lock_date,expiry_date,profile 2 from dba_users where username = 'TONGJW' 3 / USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA PROFILE ------------------------------ -------------------------------- --------- --------- ------------------------------ TONGJW EXPIRED(GRACE) 19-APR-05 TEST SQL> host [oracle@TzDbTzcenter2 ]$ su Password: ******** [root@TzDbTzcenter2 ]# date '04201529' Wed Apr 20 15:29:00 CST 2005 [root@TzDbTzcenter2 ]# exit [oracle@TzDbTzcenter2 ]$ exit SQL> select username,account_status,lock_date,expiry_date,profile 2 from dba_users where username = 'TONGJW' 3 / USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA PROFILE ------------------------------ -------------------------------- --------- --------- ------------------------------ TONGJW EXPIRED(GRACE) 19-APR-05 TEST SQL> conn tongjw/tongjw 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 Warning: You are no longer connected to ORACLE. SQL> conn jfdata/jf Connected. SQL> select username,account_status,lock_date,expiry_date,profile 2 from dba_users where username = 'TONGJW' 3 / USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA PROFILE ------------------------------ -------------------------------- --------- --------- ------------------------------ TONGJW EXPIRED 19-APR-05 TEST SQL> conn tongjw/tongjw ERROR: ORA-28001: the password has expired Changing password for tongjw New password: ****** Retype new password: ****** Password changed Connected. SQL> conn jfdata/ Enter password: ** Connected. SQL> select username,account_status,lock_date,expiry_date,profile 2 from dba_users where username = 'TONGJW' 3 / USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA PROFILE ------------------------------ -------------------------------- --------- --------- ------------------------------ TONGJW OPEN 30-APR-05 TEST SQL> 如果该用户, 没有登录, 对应的expiry_date是不会发生改变的, 只要铜壶登录, 对应的expiry_date就会进行更新, 当然也会提示用户修改对应的密码, 也就是说, 这个日期是在用户登录的时候进行更新的, 更进一步, 我们可由此断定, Oracle是在用户登录的时候, 取出用户对应的这些信息,
if expiry_date > sysdate then

enter into
else if expiry_date < sysdate then
if grace_time is set then
expiry_date = sysdate + expiry_date;
else
prompt your passwors has expired.
end if
end if;
用户的密码过期路径可以大略的表示如下. t1 设置profile的时间/或者用户重新修改密码的时间05-4-5 t2 用户在密码过期以后,第一次登录数据库的时间, 05-4-16 password_change_time + life_time t3 用户密码真正过期的时间, 05-04-19 05-04-16 + grace_time t4 修改密码的时间, 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,profile 2 from dba_users where username = 'TONGJW' 3 / USERNAME                       ACCOUNT_STATUS                   LOCK_DATE EXPIRY_DA PROFILE ------------------------------ -------------------------------- --------- --------- ------------------------------ TONGJW                         OPEN                                                 DEFAULT SQL> create profile test limit 2    failed_login_attempts 3 3    password_lock_time 3 4 / Profile created. SQL> alter user tongjw profile test; User altered. SQL> select username,account_status,lock_date,expiry_date,profile 2 from dba_users where username = 'TONGJW' 3 / USERNAME                       ACCOUNT_STATUS                   LOCK_DATE EXPIRY_DA PROFILE ------------------------------ -------------------------------- --------- --------- ------------------------------ TONGJW                         OPEN                                                 TEST SQL> alter user tongjw identified by tongjw 2 / User altered. SQL> conn tongjw/tong ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. SQL> conn tongjw/tongj ERROR: ORA-01017: invalid username/password; logon denied SQL> conn tongjw/tong1 ERROR: ORA-01017: invalid username/password; logon denied SQL> conn jfdata/jf Connected. SQL> select username,account_status,lock_date,expiry_date,profile 2 from dba_users where username = 'TONGJW' 3 / USERNAME                       ACCOUNT_STATUS                   LOCK_DATE EXPIRY_DA PROFILE ------------------------------ -------------------------------- --------- --------- ------------------------------ TONGJW                         LOCKED(TIMED)                    26-MAR-05           TEST SQL> host [oracle@TzDbTzcenter2 ]$ su Password: ******** [root@TzDbTzcenter2 ]# date '04301603' Sat Apr 30 16:03:00 CST 2005 [root@TzDbTzcenter2 ]# exit [oracle@TzDbTzcenter2 ]$ exit SQL> select username,account_status,lock_date,expiry_date,profile 2 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_USERS describes all users of the database.

Related View

USER_USERS describes the current user. This view does not display the PASSWORD or PROFILE columns.

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 was LOCKED
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

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

请登录后发表评论 登录
全部评论

注册时间:2011-01-05

  • 博文量
    30
  • 访问量
    63530