oracle修改状态,Oracle用户9种状态

海天：知识分享从未如此幸福

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的限制