ORA-28000: the account is locked使用者鎖定問題排查解決

ORA-28000: the account is locked使用者鎖定問題排查解決

今天同事反映一個問題，某個測試庫修改了密碼，並改了相關應用使用的密碼後，仍出現一會賬戶就被鎖住，報ORA-28000: the account is locked的錯誤。
 
檢查過程：
 1. 查看資源限制生效參數
 SQL> show parameter resource
 NAME                                TYPE        VALUE
 ------------------------------------ ----------- ------------------------------
 resource_limit                      boolean    FALSE
 FALSE表示未啟動資源限制。
 
2. 查看該使用者所用的PROFILE
 SQL> select resource_name, limit from dba_profiles where profile='DEFAULT';
 RESOURCE_NAME                    LIMIT
 -------------------------------- ----------------------------------------
 COMPOSITE_LIMIT                  UNLIMITED
 SESSIONS_PER_USER                UNLIMITED
 CPU_PER_SESSION                  UNLIMITED
 CPU_PER_CALL                    UNLIMITED
 LOGICAL_READS_PER_SESSION        UNLIMITED
 LOGICAL_READS_PER_CALL          UNLIMITED
 IDLE_TIME                        UNLIMITED
 CONNECT_TIME                    UNLIMITED
 PRIVATE_SGA                      UNLIMITED
 FAILED_LOGIN_ATTEMPTS            10
 PASSWORD_LIFE_TIME              UNLIMITED
 PASSWORD_REUSE_TIME              UNLIMITED
 PASSWORD_REUSE_MAX              UNLIMITED
 PASSWORD_VERIFY_FUNCTION        NULL
 PASSWORD_LOCK_TIME              1
 PASSWORD_GRACE_TIME              7
 其中FAILED_LOGIN_ATTEMPTS表示連續登陸失敗的次數，這裡表示連續登陸10次失敗則鎖定使用者。
 
3. 解除使用者鎖定ALTER USER pss3 ACCOUNT UNLOCK;後觀察現象
 SQL> select name, lcount from user$ where name='PSS3';
 NAME                              LCOUNT
 ------------------------------ ----------
 PSS3                                  10
 不到一分鐘，登陸失敗次數就到10次了。
 
初步結論：
 可能有應用仍使用舊的密碼登陸，登陸失敗後重複嘗試，直到10次為止。
 
但問題就來了：
 1. FAILED_LOGIN_ATTEMPTS設定為10次，但未啟動resource_limit，為什麼還受到10次的限制呢？
 2. 怎麼知道還有哪些應用由於未修改密碼導致ORA錯誤呢？
 
問題1：FAILED_LOGIN_ATTEMPTS設定為10次，但未啟動resource_limit，為什麼還受到10次的限制呢？
 這篇MOS文章160528.1(Profile Limits (Resource Parameter(s)) Are Not Enforced / Do Not Work)文章說了一些：
 After creating a new profile or altering an old one to limit the following profile resources there is no change:
 SESSIONS_PER_USER
 CPU_PER_SESSION
 CPU_PER_CALL
 CONNECT_TIME
 IDLE_TIME
 LOGICAL_READS_PER_SESSION
 COMPOSITE_LIMIT
 PRIVATE_SGA
 The resource usage limits are not enforced and the users that are assigned the profile continue to use resources beyond profile's limits.
 CAUSE
 
The initialization parameter RESOURCE_LIMIT is set to FALSE (default).
 
由於未設定RESOURCE_LIMIT為TRUE，以上變數修改後不會生效。
 
這裡沒有提到FAILED_LOGIN_ATTEMPTS，換句話說，像FAILED_LOGIN_ATTEMPTS這些變數是不受RESOURCE_LIMIT參數限制的，再看FAILED_LOGIN_ATTEMPTS這種變數屬於使用者口令管理方面的，像上面這些變數則屬於資源管理方面的，猜測Oracle對於資源管理的限制則需要RESOURCE_LIMIT為TRUE，對於口令管理方面的限制並不受RESOURCE_LIMIT的影響。
 
OCP教材中正好說了：“Profiles are a useful way of managing passwords and resources but can really only apply in an environment where every application user has their own database user account.”注意到這裡他將profile分成管理密碼和資源兩大類，雖然沒有明說，但結合以上兩段參考，以及上述實際碰到的問題，有理由相信口令管理方面的限制並不受RESOURCE_LIMIT參數的影響。
 
問題2：怎麼知道還有哪些應用由於未修改密碼導致ORA錯誤呢？
 上面嘗試了UNLOCK賬戶後不到一分鐘LCOUNT登入失敗次數就到了10次，說明這段時間有應用頻繁重試密碼，進一步，如果我們能找到這段時間訪問庫的IP，再篩選可能的IP和密碼修改的應用，就可能找到“罪魁禍首”。
 要想找到訪問庫的IP，可以通過設定監聽日誌，尋找IP。
 監聽器的日誌類似於alert日誌，文章中說日誌預設路徑是$ORACLE_HOME/network/log/listener.log，但我用的11g，不知道是否修改過，並沒有找到這個目錄。至於怎麼找到的，接下來會說到。
 按照@secooler的教程，開啟監聽器日誌的方式有兩種：
 1. 不需要重啟監聽器的情況下通過設定log_status參數為off來實現。
 2. listener.ora檔案中增加LOGGING_<listener_name>=OFF參數，然後重啟監聽器實現
 可以根據不同需要選擇不同的方式。
 這裡我選擇第一種，執行lsnrctl後執行set log_status on，然後需要找到日誌路徑：
 ora11g@vm-kvm-ora$ lsnrctl status
 LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 20-AUG-2014 11:56:27
 Copyright (c) 1991, 2011, Oracle.  All rights reserved.
 Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
 STATUS of the LISTENER
 ------------------------
 Alias                    LISTENER
 Version                TNSLSNR for Linux: Version 11.2.0.3.0 - Production
 Start Date            30-APR-2014 15:22:19
 Uptime                111 days 20 hr. 34 min. 8 sec
 Trace Level            off
 Security                ON: Local OS Authentication
 SNMP                  OFF
 Listener Parameter File  /oracle/ora11g/product/11.2.0/network/admin/listener.ora
 Listener Log File        /oracle/ora11g/diag/tnslsnr/vm-kvm-ora/listener/alert/log.xml
 這裡我們看到有個資訊Listener Log File，後面就是對應的日誌路徑和記錄檔名。
 11g中使用了log.xml這種xml格式記錄監聽日誌。
 內容類別似於：
 <txt>20-AUG-2014 10:07:30 * (CONNECT_DATA=(SERVICE_NAME=pss3)(CID=(PROGRAM=dcs_data_sync)(HOST=v490h4-tux-t)(USER=dcsopen))) * (ADDRESS=(PROTOCOL=tcp)(HOST=x.x.x.x)(PORT=37339)) * establish * pss3 * 0
 </txt>
 因此只需要找到解鎖使用者後仍登入的IP，然後再篩選可能的應用就行了。
 
這裡還有個知識點，就是FAILED_LOGIN_ATTEMPTS設定的是連續登入失敗的次數，還是累計登入失敗的次數？
 FAILED_LOGIN_ATTEMPTS表示連續登入失敗的次數。

Oracle 11g 在RedHat Linux 5.8_x64平台的安裝手冊

Linux-6-64下安裝Oracle 12C筆記

在CentOS 6.4下安裝Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虛擬機器中安裝步驟

Debian 下 安裝 Oracle 11g XE R2