【DB笔试面试543】Oracle用户的状态有几种？分别表示什么含义？
【DB笔试面试543】Oracle用户的状态有几种？分别表示什么含义？
♣
题目部分
Oracle用户的状态有几种？分别表示什么含义？
♣
答案部分
Oracle用户的状态是由密码来决定的，而Oracle中的密码是由PROFILE来配置的。PROFILE是口令限制、资源限制的命令集合。当建立数据库时，Oracle会自动建立名称为DEFAULT的PROFILE。当创建用户而没有指定PROFILE选项时，Oracle就会将DEFAULT分配给用户。
通过如下的命令可以查出与密码相关的PROFILE的值：
SELECT *
FROM DBA_PROFILES D
WHERE D.PROFILE = 'DEFAULT'
AND (D.RESOURCE_NAME LIKE '%PASSWORD%' OR D.RESOURCE_NAME = 'FAILED_LOGIN_ATTEMPTS');
每个参数的含义如下所示：
l FAILED_LOGIN_ATTEMPTS 设定登录到Oracle数据库时可以失败的次数。一旦某用户尝试登录数据库的次数达到该值时，该用户的帐户就被锁定，只能由DBA解锁。
l PASSWORD_LIFE_TIME 设定口令的有效时间（天数），一旦超过这一时间，必须重新设口令。缺省为UNLIMITED。
l PASSWORD_REUSE_TIME 许多系统不许用户重新启用过去用过的口令。该资源项设定了一个失效口令要经过多少天，用户才可以重新使用该口令，缺省为180天。
l PASSWORD_REUSE_MAX 重新启用一个先前用过的口令前必须对该口令进行重新设置的次数（重复用的次数）。
l PASSWORD_LOCK_TIME 设定帐户被锁定的天数（当登录失败达到FAILED_LOGIN_ATTEMPTS时）。
l PASSWORD_GRACE_TIME 设定在口令失效前，给予的重新设置该口令的宽限天数。当口令失效之后，在登录时会出现警告信息显示该天数。如果没有在宽限天内修改口令，那么口令将失效。该参数默认为7天。
l PASSWORD_VERITY_FUNCTION 该资源项允许调用一个PL/SQL来验证口令。Oracle已提供该应用的脚本，为$ORACLE_HOME/rdbms/admin/utlpwdmg.sql。但是，用户可以制定自己的验证脚本。该参数的设定就是PL/SQL函数的名称，缺省为NULL。
用户的状态可以由以下脚本查询获得：
SYS@lhrdb> 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
以上九种可以分为两大类：1.基本状态；2.组合状态。前五种是基本状态，后四种是组合状态。具体分类如下图所示：
每种状态的解释如表 3-16所示：
表 3-16 用户状态表
状态序号 | 状态 | 解释 | 处理办法 |
|---|---|---|---|
0 | OPEN | OPEN表示用户处于正常状态 | |
1 | EXPIRED | 密码是否过期是通过修改PROFILE中的PASSWORD_LIFE_TIME实现的，表示该帐户被设置为口令到期，要求用户在下次登录的时候修改口令（系统会在该账户被设置为EXPIRED后的第一次登陆是提示修改密码）。可以通过SQL语句（ALTER USER LHRSYS PASSWORD EXPIRE;）来显式地让用户密码过期 | 修改密码：SELECT NB.PASSWORD FROM USER$ NB WHERE NB.NAME ='LHRSYS';ALTER USER SCOTT IDENTIFIED BY VALUES 'F894844C34402B67'; |
2 | EXPIRED(GRACE) | 当设置了GRACE以后（第一次成功登录后到口令到期后有多少天时间可改变口令。在这段时间内，帐户被提醒修改口令并可以正常登陆，ACCOUNT_STATUS显示为EXPIRED(GRACE) | 修改密码：SELECT NB.PASSWORD FROM USER$ NB WHERE NB.NAME ='LHRSYS';ALTER USER SCOTT IDENTIFIED BY VALUES 'F894844C34402B67'; |
4 | LOCKED(TIMED) | 表示失败的登录次数超过了FAILED_LOGIN_ATTEMPTS的值，被系统自动锁定。需要注意的是，从Oracle 10g开始，默认的DEFAULT值是10次，这个限制是由PROFILE中的FAILED_LOGIN_ATTEMPTS控制的，该信息可以通过DBA_PROFILES视图查询 | 解锁用户：ALTER USER LHRSYS ACCOUNT UNLOCK; |
8 | LOCKED | DBA显式地通过SQL语句对用户进行锁定（ALTER USER LHRSYS ACCOUNT LOCK;） | ALTER USER LHRSYS ACCOUNT UNLOCK; |
5 | EXPIRED & LOCKED(TIMED) | 表示用户密码过期后，失败登录次数超过PROFILE中的FAILED_LOGIN_ATTEMPTS的限制 | 将用户UNLOCK并修改密码：ALTER USER LHRSYS ACCOUNT UNLOCK;ALTER USER LHRSYS IDENTIFIED BY LHR; |
6 | EXPIRED(GRACE) & LOCKED(TIMED) | 表示用户在密码过期后的有效期内，失败登录次数超过PROFILE中的FAILED_LOGIN_ATTEMPTS的限制 | 将用户UNLOCK并修改密码：ALTER USER LHRSYS ACCOUNT UNLOCK;ALTER USER LHRSYS IDENTIFIED BY LHR; |
9 | EXPIRED & LOCKED | EXPIRED & LOCKED状态表示用户密码过期且同时处于锁定状态 | 将用户UNLOCK并修改密码：ALTER USER LHRSYS ACCOUNT UNLOCK;ALTER USER LHRSYS IDENTIFIED BY LHR; |
10 | EXPIRED(GRACE) & LOCKED | 表示用户在密码过期后的有效期内被DBA手工锁定 | 将用户UNLOCK并修改密码：ALTER USER LHRSYS ACCOUNT UNLOCK;ALTER USER LHRSYS IDENTIFIED BY LHR; |
在Oracle中，若用户的密码变为锁定状态（LOCKED、LOCKED(TIMED)）时，DBA可以直接执行“ALTER USER用户名 ACCOUNT UNLOCK”来解锁。但是，如果用户的状态变成过期状态（EXPIRED、EXPIRED(GRACE)），那么DBA必须要更改用户的密码账户才能重新使用。但有些时候，因为各种原因并不知道原密码的明文是什么，这时候可以有如下2种办法来更新密码。
1、用原密码的密文来更改密码
在Oracle 10g中，DBA_USERS视图的PASSWORD字段提供了密码的密文形式，而在Oracle 11g中，该字段被弃用了，内容为空，但是在基表USER$中的PASSWORD字段依然有记录密文形式，所以可以通过如下的形式来获取密码的密文形式：
SELECT D.USERNAME,
D.ACCOUNT_STATUS,
D.LOCK_DATE,
D.EXPIRY_DATE,
D.PROFILE,
NVL(D.PASSWORD,(SELECT NB.PASSWORD FROM USER$ NB WHERE NB.NAME = D.USERNAME)) PASSWORD
FROM DBA_USERS D
WHERE D.USERNAME = 'LHRSYS';
另外，可以通过DBMS_METADATA.GET_DDL包或者expdp、exp命令来获取创建用户的语句从而获取密码的密文形式。
SYS@lhrdb> set long 9999
SYS@lhrdb> SELECT DBMS_METADATA.GET_DDL('USER', 'LHRSYS') DDL_SQL FROM DUAL;
DDL_SQL
--------------------------------------------------------------------------------
CREATE USER "LHRSYS" IDENTIFIED BY VALUES 'S:853EA80BAE11F79D6946453F38059E30313FE84C96AE2EE4F3AA35A648BD;F809740420A44EFC'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
获取了密码的密文后就可以用如下的命令来修改了，注意：使用密文的命令中多了一个VALUES关键字：
SYS@lhrdb> alter user LHRSYS identified by values 'F809740420A44EFC';
User altered.
SYS@lhrdb> CONN LHRSYS/lhr@192.168.59.129/lhrdb
Connected.
LHRSYS@192.168.59.129/lhrdb> conn / as sysdba
Connected.
SYS@lhrdb> alter user LHRSYS identified by values 'S:853EA80BAE11F79D6946453F38059E30313FE84C96AE2EE4F3AA35A648BD;F809740420A44EFC';
User altered.
SYS@lhrdb> CONN LHRSYS/lhr@192.168.59.129/lhrdb
Connected.
所以，即使不知道用户的原密码是什么，也可以用它的密文来更改密码。这样既保持了密码不改变，又可以把EXPIRED的状态更改掉。
在MOS The Impact of PASSWORD_LIFE_TIME Database Profile Parameter Default to 180 Days on Network Charging and Control (文档 ID 1543668.1)中搜到了如下的命令也可以直接获取密码：
SELECT SQLTEXT
FROM (SELECT NAME,'ALTER USER ' || NAME || ' IDENTIFIED BY VALUES ''' ||PASSWORD || ''';' SQLTEXT
FROM USER$
WHERE SPARE4 IS NULL
AND PASSWORD IS NOT NULL
UNION
SELECT NAME,
'ALTER USER ' || NAME || ' IDENTIFIED BY VALUES ''' || SPARE4 || ';' ||PASSWORD || ''';' SQLTEXT
FROM USER$
WHERE SPARE4 IS NOT NULL
AND PASSWORD IS NOT NULL)
WHERE NAME = 'LHRSYS';
2、直接更新USER$基表
不管用户的状态是什么，通过更新USER$表可以让用户处于OPEN状态：
SYS@lhrdb> SELECT USERNAME, ACCOUNT_STATUS,EXPIRY_DATE,LOCK_DATE FROM DBA_USERS WHERE USERNAME = 'LHRSYS';
USERNAME ACCOUNT_STATUS EXPIRY_DATE LOCK_DATE
------------------------------ -------------------------------- ------------------- -------------------
LHRSYS EXPIRED 2016-12-02 10:40:09
SYS@lhrdb> UPDATE USER$ SET ASTATUS=0 WHERE NAME='LHRSYS';
1 row updated.
SYS@lhrdb> commit;<<<<<<<<<------及时提交
Commit complete.
SYS@lhrdb> SELECT USERNAME, ACCOUNT_STATUS,EXPIRY_DATE,LOCK_DATE FROM DBA_USERS WHERE USERNAME = 'LHRSYS';
USERNAME ACCOUNT_STATUS EXPIRY_DATE LOCK_DATE
------------------------------ -------------------------------- ------------------- -------------------
LHRSYS OPEN
& 说明：
有关用户密码的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2129595/
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。