【DB筆試面試543】Oracle用戶的狀態有幾種？分別表示什麼含義？
- 2019 年 10 月 10 日
- 筆記
♣
題目部分
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/[email protected]/lhrdb Connected. [email protected]/lhrdb> conn / as sysdba Connected. SYS@lhrdb> alter user LHRSYS identified by values 'S:853EA80BAE11F79D6946453F38059E30313FE84C96AE2EE4F3AA35A648BD;F809740420A44EFC'; User altered. SYS@lhrdb> CONN LHRSYS/[email protected]/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程式設計師面試筆試寶典》，作者：李華榮。
