首页 » ORACLE » How to find out who caused the database user locked(ora-1017 or ORA-28000)(捕捉登录失败)

How to find out who caused the database user locked(ora-1017 or ORA-28000)(捕捉登录失败)

昨天有应用反应server日志中有如下错误,提示用户locked,但是重启了下web server又恢复了正常,但是这期间我们也没有人为的给db user unlock, 下面记录一下, 其实事情的经过是这样子的…
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (ORA-28000: the account is locked)

下面是中间件的connect pool 配置

anbob1 
 jdbc:oracle:thin:anbob/123456@133.96.60.62:1521:weejar2 

— db version: 11.2.0.3 2-nodes RAC on hpux

SQL> @us anbob
Show database usernames from dba_users matching %anbob%

USERNAME                  DEFAULT_TABLESPACE        TEMPORARY_TABLE    USER_ID CREATED           PROFILE    ACCOUNT_STATUS
------------------------- ------------------------- --------------- ---------- ----------------- ---------- ----------------------------------------------------------------
ANBOB                      DATA999                   TEMP                    54 20100604 08:39:17 JFZX_APP   OPEN

SQL> @printtab "select * from user$ where name=''ANBOB''"
USER#                         : 54
NAME                          : anbob
TYPE#                         : 1
PASSWORD                      : 0B2CBE182B78C6D9
DATATS#                       : 12
TEMPTS#                       : 3
CTIME                         : 04-jun-2010 08:39:17
PTIME                         : 23-oct-2013 02:58:55
EXPTIME                       :
LTIME                         : 15-jul-2015 14:53:35
RESOURCE$                     : 3
AUDIT$                        :
DEFROLE                       : 1
DEFGRP#                       :
DEFGRP_SEQ#                   :
ASTATUS                       : 0
LCOUNT                        : 0
DEFSCHCLASS                   : DEFAULT_CONSUMER_GROUP
EXT_USERNAME                  :
SPARE1                        : 0
SPARE2                        :
SPARE3                        :
SPARE4                        : S:58898C72C6E2F666ED286C7DC5C82531D389C9C01343A06B968AE2968A57
SPARE5                        :
SPARE6                        :
-----------------

PL/SQL procedure successfully completed.


SQL>select * from dba_profiles 

PROFILE                   RESOURCE_NAME                                    RESOURCE_TYPE    LIMIT
------------------------- ------------------------------------------------ ---------------- ---------------------------------------------
DEFAULT                   SESSIONS_PER_USER                                KERNEL           UNLIMITED
DEFAULT                   CPU_PER_SESSION                                  KERNEL           UNLIMITED
DEFAULT                   PASSWORD_LOCK_TIME                               PASSWORD         .0006
DEFAULT                   PASSWORD_GRACE_TIME                              PASSWORD         10
DEFAULT                   FAILED_LOGIN_ATTEMPTS                            PASSWORD         3
DEFAULT                   PASSWORD_VERIFY_FUNCTION                         PASSWORD         NULL
DEFAULT                   PASSWORD_REUSE_MAX                               PASSWORD         UNLIMITED
DEFAULT                   PASSWORD_REUSE_TIME                              PASSWORD         UNLIMITED
DEFAULT                   PASSWORD_LIFE_TIME                               PASSWORD         UNLIMITED
DEFAULT                   PRIVATE_SGA                                      KERNEL           UNLIMITED
DEFAULT                   CONNECT_TIME                                     KERNEL           UNLIMITED
DEFAULT                   IDLE_TIME                                        KERNEL           UNLIMITED
DEFAULT                   LOGICAL_READS_PER_CALL                           KERNEL           UNLIMITED
DEFAULT                   LOGICAL_READS_PER_SESSION                        KERNEL           UNLIMITED
DEFAULT                   CPU_PER_CALL                                     KERNEL           UNLIMITED
DEFAULT                   COMPOSITE_LIMIT                                  KERNEL           UNLIMITED
JFZX_APP                  LOGICAL_READS_PER_CALL                           KERNEL           DEFAULT
JFZX_APP                  PASSWORD_GRACE_TIME                              PASSWORD         UNLIMITED
JFZX_APP                  CONNECT_TIME                                     KERNEL           DEFAULT
JFZX_APP                  PRIVATE_SGA                                      KERNEL           DEFAULT
JFZX_APP                  FAILED_LOGIN_ATTEMPTS                            PASSWORD         DEFAULT
JFZX_APP                  PASSWORD_REUSE_TIME                              PASSWORD         DEFAULT
JFZX_APP                  LOGICAL_READS_PER_SESSION                        KERNEL           DEFAULT
JFZX_APP                  CPU_PER_CALL                                     KERNEL           DEFAULT
JFZX_APP                  CPU_PER_SESSION                                  KERNEL           DEFAULT
JFZX_APP                  SESSIONS_PER_USER                                KERNEL           =3 
JFZX_APP                  COMPOSITE_LIMIT                                  KERNEL           DEFAULT
JFZX_APP                  PASSWORD_LOCK_TIME                               PASSWORD         .0004
JFZX_APP                  PASSWORD_VERIFY_FUNCTION                         PASSWORD         VERIFY_FUNCTION
JFZX_APP                  PASSWORD_REUSE_MAX                               PASSWORD         5
JFZX_APP                  PASSWORD_LIFE_TIME                               PASSWORD         UNLIMITED
JFZX_APP                  IDLE_TIME                                        KERNEL           DEFAULT

Note :
反应问题时看数据库用户是open的,从user$.ltime确认上次在问题时间点是有locked过,用户属于JFZX_APP PROFILE, JFZX_APP的FAILED_LOGIN_ATTEMPTS 继承了DEFAULT, Default profile的FAILED_LOGIN_ATTEMPTS =3 表示连续尝试密码错误3次用户将被lock, 但是JFZX_APP PROFILE 的PASSWORD_LOCK_TIME约0.0004 days,表示lock 用户30秒后又会unlock, 当前应用程序的密码没有配置错误,这也解释了为什么程序重启后又恢复了正常。下面就是想法找出尝试登录导致失败的源头(当然此时可以把FAILED_LOGIN_ATTEMPTS调成unlimited在找到源头之前,防止应用user再被lock).

捕捉登录失败的几种方法MOS上有写:Audit 、database trigger、 event errorstack、 sqlnet trace ,因为audit 在数据库已禁,下面使用创建一个Trigger,把登录失败信息写入DB alert log.

SQL> show parameter audit

PARAMETER_NAME                                               TYPE        VALUE
------------------------------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
audit_file_dest                                              string      /oracle/app/oracle/product/11.2.0.3/dbhome_1/rdbms/audit
audit_sys_operations                                         boolean     TRUE
audit_syslog_level                                           string
audit_trail                                                  string      NONE    <<<<<

— sample trigger to write diagnostic info to alert.log
— for failed login attempts (ora-1017)
— I had add HOSTNAME and DB user (anbob.com)

CREATE OR REPLACE TRIGGER logon_denied_to_alert
   AFTER SERVERERROR
   ON DATABASE
DECLARE
   MESSAGE         VARCHAR2 (256);
   IP              VARCHAR2 (15);
   v_os_user       VARCHAR2 (80);
   v_module        VARCHAR2 (50);
   v_action        VARCHAR2 (50);
   v_pid           VARCHAR2 (10);
   v_sid           NUMBER;
   v_program       VARCHAR2 (48);
   v_client_id     VARCHAR2 (64);
   v_dbuser        VARCHAR2 (100);
   v_client_host   VARCHAR2 (80);
BEGIN
   IF (ora_is_servererror (1017))
   THEN
      -- get IP for remote connections:
      IF SYS_CONTEXT ('userenv', 'network_protocol') = 'TCP'
      THEN
         IP := SYS_CONTEXT ('userenv', 'ip_address');
      END IF;

      SELECT DISTINCT sid INTO v_sid FROM sys.v_$mystat;

      SELECT p.SPID, v.PROGRAM
        INTO v_pid, v_program
        FROM V$PROCESS p, V$SESSION v
       WHERE p.ADDR = v.PADDR AND v.sid = v_sid;

      v_os_user := SYS_CONTEXT ('userenv', 'os_user');
      DBMS_APPLICATION_INFO.READ_MODULE (v_module, v_action);

      v_client_host := SYS_CONTEXT ('USERENV', 'HOST');

      v_client_id := sys_context ('userenv', 'client_identifier');
      v_dbuser := sys_context ('USERENV', 'AUTHENTICATED_IDENTITY');

      MESSAGE :=
            TO_CHAR (SYSDATE, 'Dy Mon dd HH24:MI:SS YYYY')
         || ' logon denied '
         || 'IP ='
         || NVL (IP, 'unknown')
         || ' client_host='
         || v_client_host
         || ' pid = '
         || v_pid
         || ' os user = '
         || v_os_user
         || ' client id = '
         || v_client_id
         || ' with program= '
         || v_program
         || ' module ='
         || v_module
         || ' action='
         || v_action
         || ' dbuser='
         || v_dbuser;

      sys.DBMS_SYSTEM.ksdwrt (2, MESSAGE);
   -- remove comments from next line to let it hang for 5 minutes
   -- to be able to do more diagnostics on the operating system:
   -- sys.dbms_lock.sleep(300);
   END IF;
END;
/

果然没多久在node 1的DB alert 中发现了trigger写入的信息

Thu Jul 16 14:31:32 2015
Thu Jul 16 14:31:32 2015 logon denied IP =unknow client_host=kdwbtest pid = 54526500 os user = testweb client id =  with program= JDBC Thin Client module =JDBC Thin Client action= dbuser=anbob
Thu Jul 16 14:31:52 2015
Thu Jul 16 14:31:52 2015 logon denied IP =unknow client_host=kdwbtest pid = 38601008 os user = testweb client id =  with program= JDBC Thin Client module =JDBC Thin Client action= dbuser=anbob
Thu Jul 16 14:33:32 2015
Thu Jul 16 14:33:32 2015 logon denied IP =unknow client_host=kdwbtest pid = 26411550 os user = testweb client id =  with program= JDBC Thin Client module =JDBC Thin Client action= dbuser=anbob
Thu Jul 16 14:34:40 2015
Thu Jul 16 14:34:40 2015 logon denied IP =unknow client_host=kdwbtest pid = 46596250 os user = testweb client id =  with program= JDBC Thin Client module =JDBC Thin Client action= dbuser=anbob

SQL> conn / as sysdba
Connected.
SQL> select utl_inaddr.get_host_address('kdwbtest') from dual;
select utl_inaddr.get_host_address('kdwbtest') from dual
*
ERROR at line 1:
ORA-29257: host kdwbtest unknown
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1

Summary:
无法取得client的IP,但是取到了机器名, 源头就是kdwbtest的主机上testweb用启部署的应用JDBC使用的数据库用户anbob尝试连接节点1多次失败,导致的应用程序使用相同用户连接节点2时因用户被锁抛出上面的错误。

打赏

对不起,这篇文章暂时关闭评论。