昨天有应用反应server日志中有如下错误，提示用户locked，但是重启了下web server又恢复了正常，但是这期间我们也没有人为的给db user unlock, 下面记录一下, 其实事情的经过是这样子的…
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (ORA-28000: the account is locked)
下面是中间件的connect pool 配置
— db version: 11.2.0.3 2-nodes RAC on hpux
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.
-- sample trigger to write diagnostic info to alert.log
-- for failed login attempts (ora-1017)
-- I had add HOSTNAME and DB user (anbob.com)
果然没多久在node 1的DB alert 中发现了trigger写入的信息
Summary:
无法取得client的IP，但是取到了机器名, 源头就是kdwbtest的主机上testweb用启部署的应用JDBC使用的数据库用户anbob尝试连接节点1多次失败，导致的应用程序使用相同用户连接节点2时因用户被锁抛出上面的错误。
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时因用户被锁抛出上面的错误。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
