海天:知识分享从未如此幸福
Oracle默认用户只有2种状态 "OPEN","EXPIRED
& LOCKED",随着Oracle用户限制策略的不同和人为操作可以把Oracle用户置为9种状态。
Oracle用户状态:
SQL> select * from
user_astatus_map;
STATUS# STATUS
---------- --------------------------------
0 OPEN
1 EXPIRED
2 EXPIRED(GRACE)
4 LOCKED(TIMED)
8 LOCKED
5 EXPIRED & LOCKED(TIMED)
6 EXPIRED(GRACE) & LOCKED(TIMED)
9 EXPIRED & LOCKED
10 EXPIRED(GRACE) & LOCKED
9 rows selected.
Oracle用户9种状态的解释:
1.OPEN:当前帐户是开放的用户可以自由登录
SQL> select username,
account_status from dba_users where username =
'TIANBAOBAO';
USERNAME ACCOUNT_STATUS
------------------------------
--------------------------------
TIANBAOBAO OPEN
2.EXPIRED:当前帐户已经过期,
用户必须在修改密码以后才可以登录系统, 在登录的时候, 系统会提示修改密码
SQL> alter user tianbaobao
password expire;
User altered.
SQL> select username,
account_status from dba_users where username =
'TIANBAOBAO';
USERNAME ACCOUNT_STATUS
------------------------------
--------------------------------
TIANBAOBAO EXPIRED
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release
11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data
Mining
and Real Application Testing options
[oracle@capp2010 ~]$ sqlplus tianbaobao/oracle
SQL*Plus: Release 11.2.0.3.0 Production on Thu Sep 4 08:45:43
2014
Copyright (c) 1982, 2011, Oracle. All rights
reserved.
ERROR:
ORA-28001: the password has expired
Changing password for tianbaobao
New password:
Retype new password:
Password changed
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
Production
With the Partitioning, Automatic Storage Management, OLAP, Data
Mining
and Real Application Testing options
SQL> select username,
account_status from dba_users where username =
'TIANBAOBAO';
USERNAME ACCOUNT_STATUS
------------------------------
--------------------------------
TIANBAOBAO OPEN
3.EXPIRED(GRACE):参数PASSWORD_LIFE_TIME设置为非UNLIMITED时,密码过期后状态为EXPIRED(GRACE),密码即将过期时可以使用PASSWORD_GRACE_TIME参数控制延迟几天过期,这段期间可以修改密码保证用户正常使用。
PASSWORD_LIFE_TIME 在11g中默认为180天。
PASSWORD_GRACE_TIME 在11g中默认为7天。
4..LOCKED:帐户是锁定的,用户不可以登录,
必须由管理员将帐户打开用户才可以登录
SQL> alter user tianbaobao account
lock;
User altered.
SQL> select username,
account_status from dba_users where username =
'TIANBAOBAO';
USERNAME ACCOUNT_STATUS
------------------------------
--------------------------------
TIANBAOBAO LOCKED
SQL> alter user tianbaobao
account unlock;
User altered.
SQL> select username,
account_status from dba_users where username =
'TIANBAOBAO';
USERNAME ACCOUNT_STATUS
------------------------------
--------------------------------
TIANBAOBAO OPEN
5.LOCKED(TIMED):参数FAILED_LOGIN_ATTEMPTS设置为非UNLIMITED时,即规定了登录可以失败的次数,一旦用户尝试登录错误的次数超过这个值,账户就会被锁定,只能由管理员解锁。如何设置了参数PASSWORD_LOCK_TIME后,这样当输入多次密码导致帐号被锁定后,只要超过了设置时间后,帐号自动解锁。
FAILED_LOGIN_ATTEMPTS 在11g中默认为10次。
PASSWORD_LOCK_TIME 在11g中默认为1天。
6.EXPIRED &
LOCKED:DBA手动锁定用户,用户过期的同时处于锁定状态。
SQL> alter user tianbaobao account
lock password expire;
User altered.
SQL> select username,
account_status from dba_users where username =
'TIANBAOBAO';
USERNAME ACCOUNT_STATUS
------------------------------
--------------------------------
TIANBAOBAO EXPIRED & LOCKED
7.EXPIRED &
LOCKED(TIMED):错误密码尝试次数超过PROFILE中的FAILED_LOGIN_ATTEMPTS的限制,DBA手动手动设置EXPIRED状态。
SQL> select username,
account_status from dba_users where username =
'TIANBAOBAO';
USERNAME ACCOUNT_STATUS
------------------------------
--------------------------------
TIANBAOBAO EXPIRED & LOCKED(TIMED)
8.EXPIRED(GRACE) &
LOCKED:用户时间时间超过PASSWORD_LIFE_TIME后过期,DBA手动锁定用户。
9.EXPIRED(GRACE) &
LOCKED(TIMED):用户在密码过期后的有效期内,不成功登录次数超过PROFILE中的FAILED_LOGIN_ATTEMPTS的限制