首页 » ORACLE 9i-23ai » 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).
捕捉登录失败的几种方法:Audit (SQL> audit session whenever not successful; )、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，但是取到了机器名,通过机器名可以继续去listener log中匹配对应的IP。 问题的源头就是kdwbtest的主机上testweb用启部署的应用JDBC使用的数据库用户anbob尝试连接节点1多次失败，导致的应用程序使用相同用户连接节点2时因用户被锁抛出上面的错误。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。