ORA-28000: the account is locked 查哪個具體ip地址造成

ORA-28000: the account is locked 查哪個具體ip地址造成

查系統預設的策略，連續驗證10次錯誤帳戶即會被鎖

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        180

RESOURCE_NAME            LIMIT
-------------------------------- ----------------------------------------
PASSWORD_REUSE_TIME        UNLIMITED
PASSWORD_REUSE_MAX        UNLIMITED
PASSWORD_VERIFY_FUNCTION    NULL
PASSWORD_LOCK_TIME        1
PASSWORD_GRACE_TIME        7

16 rows selected.

 

查看使用者被鎖狀態

 

SQL> select username,account_status from dba_users where username='USER1';

USERNAME              ACCOUNT_STATUS
------------------------------ --------------------------------
USER1                  LOCKED(TIMED)
SQL> select name,lcount from user$ where name='USER1';

NAME                  LCOUNT
------------------------------ ----------
USER1                      10

 

先處理問題，將驗證錯誤次數改為不受限制，解鎖使用者

 

SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;

Profile altered.

SQL> alter user user1 account unlock;

User altered.

 

再查看使用者驗證的錯誤次數，如果此帳戶一直在驗證，可以看到次數一直在增加

SQL> select name,lcount from user$ where name='USER1';

通過記錄檔/u01/app/Oracle/diag/tnslsnr/localhost/listener/alert/log.xml追查請求來源ip，但是效果不理想

1.看不到請求的使用者名稱，看不到請求結果，對請求來源ip判斷可能有誤

2.日誌過多，暫時想不到關鍵字過濾

 

[oracle@localhost adump]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 12-MAY-2016 11:46:39

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=iZ11y546tzlZ)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                    LISTENER
Version                  TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                10-MAY-2016 09:44:40
Uptime                    2 days 2 hr. 1 min. 59 sec
Trace Level              off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File  /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File        /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...

[oracle@localhost ~]$ tail -f /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml

<msg time='2016-05-12T11:52:33.423+08:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='localhost'
 host_addr='10.174.70.172'>
 <txt>12-MAY-2016 11:52:33 * (CONNECT_DATA=(SERVICE_NAME=orcl)(CID=(PROGRAM=C:\Program?Files??x86?\PremiumSoft\Navicat?Premium\navicat.exe)(HOST=HUJF-PC)(USER=hujf))) * (ADDRESS=(PROTOCOL=tcp)(HOST=110.82.160.106)(PORT=59584)) * establish * orcl * 0
 </txt>
</msg>

 

設定格式，查returncode為1017的，可以很清楚看到驗證的使用者(userid)電腦名稱(userhost，區域網路有用)請求來源ip(comment$text)

 

SQL> set pagesize 100;
SQL> set linesize 150;
SQL> select sessionid,userid,userhost,comment$text,spare1,ntimestamp# from aud$ where returncode=1017;
    53080 USER1
WORKGROUP\HUJF-PC
Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=110.82.160.106)(PORT=59584))
hujf
12-MAY-16 03.52.34.569085 AM

    53085 SYSTEM
WORKGROUP\HUJF-PC
Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=110.82.160.106)(PORT=6720))
hujf
12-MAY-16 03.55.39.857892 AM

 

 

[oracle@localhost ~]$ oerr ora 28000
28000, 00000, "the account is locked"
// *Cause:  The user has entered wrong password consequently for maximum
//          number of times specified by the user's profile parameter
//          FAILED_LOGIN_ATTEMPTS, or the DBA has locked the account
// *Action:  Wait for PASSWORD_LOCK_TIME or contact DBA
[oracle@localhost ~]$ oerr ora 1017
01017, 00000, "invalid username/password; logon denied"
// *Cause:
// *Action: