oracle用户密码错误导致被锁

碰到一个数据库用户总是被锁住

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

NAME                   LCOUNT
------------------------------ ----------
WOLF                      149

从解锁到现在又失败了149次 listner日志里没有可用的内容，被锁时间附近显示的ip配置的密码均正确

开始的想法是做审计，数据库之前已经关闭了审计 要重新开启比较麻烦，需要停机，于是采用触发器的方式

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_host varchar2(60);
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_host:=sys_context('userenv','host');
v_os_user := sys_context('userenv','os_user');
dbms_application_info.READ_MODULE(v_module,v_action);

v_client_id := sys_context('userenv','client_identifier');

message:= to_char(sysdate,'Dy Mon dd HH24:MI:SS YYYY')||
' logon denied '|| 'IP ='||nvl(IP,'localhost')||' pid = '||v_pid||
' os user = '||v_os_user||' client id = '||v_client_id||
' with program= '||v_program||' module ='||v_module||' action='||v_action||' host='||v_host;

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;
/

将相关触发器得到的信息显示在alert.log里，结果如下

Fri Nov 24 10:42:03 2017 logon denied IP =localhost pid = 62360 os user = Administrator client id =  with program= JDBC Thin Client module =JDBC Thin Client action= host=WINDOWS-8KQN6TF
Fri Nov 24 10:42:43 2017
Fri Nov 24 10:42:43 2017 logon denied IP =localhost pid = 70370 os user = Administrator client id =  with program= JDBC Thin Client module =JDBC Thin Client action= host=WINDOWS-8KQN6TF
Fri Nov 24 10:43:23 2017
Fri Nov 24 10:43:23 2017 logon denied IP =localhost pid = 71419 os user = Administrator client id =  with program= JDBC Thin Client module =JDBC Thin Client action= host=WINDOWS-8KQN6TF

找到host名为WINDOWS-8KQN6TF的机器，看到了配置错误密码的tomcat程序

附上userenv参数

Possible values that can be used for the USERENV Namespace:>

(Extracted from SQL Reference Documentation)>

TERMINAL The operating system identifier for the client of the

current session. In distributed SQL statements, this

option returns the identifier for your local session.

In a distributed environment, this is supported only

for remote SELECTs, not for remote INSERTs, UPDATEs,

or DELETEs. (The return length of this parameter may

vary by operating system.)

LANGUAGE The language and territory currently used by your session,

along with the database character set, in this form:

language_territory.characterset

LANG The ISO abbreviation for the language name, a shorter form

than the existing ‘LANGUAGE’ parameter.

SESSIONID The auditing session identifier. You cannot use this

option in distributed SQL statements.

INSTANCE The instance identification number of the current instance.

ENTRYID The available auditing entry identifier. You cannot use

this option in distributed SQL statements. To use this

keyword in USERENV, the initialization parameter AUDIT_TRAIL

must be set to TRUE.

ISDBA TRUE if you currently have the DBA role enabled and FALSE

if you do not.

CLIENT_INFO Returns up to 64 bytes of user session information that can

be stored by an application using the DBMS_APPLICATION_INFO

package.

NLS_TERRITORY The territory of the current session.

NLS_CURRENCY The currency of the current session.

NLS_CALENDAR The current calendar of the current session.

NLS_DATE_FORMAT The date format for the session.

NLS_DATE_LANGUAGE The language used for expressing dates.

NLS_SORT BINARY or the linguistic sort basis.

CURRENT_USER The name of the user whose privilege the current session is

under.

CURRENT_USERID User ID of the user whose privilege the current session is

under.

SESSION_USER Database user name by which the current user is authenticated.

This value remains the same throughout the duration of the

session.

SESSION_USERID Identifier of the database user name by which the current

user is authenticated.

CURRENT_SCHEMA Name of the default schema being used in the current schema.

This value can be changed during the session with an ALTER

SESSION SET CURRENT_SCHEMA statement.

CURRENT_SCHEMAID Identifier of the default schema being used in the current

session.

PROXY_USER Name of the database user who opened the current session on

behalf of SESSION_USER.

PROXY_USERID Identifier of the database user who opened the current session

on behalf of SESSION_USER.

DB_DOMAIN Domain of the database as specified in the DB_DOMAIN

initialization parameter.

DB_NAME Name of the database as specified in the DB_NAME

initialization parameter.

HOST Name of the host machine from which the client has connected.

OS_USER Operating system username of the client process that initiated

the database session.

EXTERNAL_NAME External name of the database user. For SSL authenticated

sessions using V.503 certificates, this field returns the

distinguished name (DN) stored in the user certificate.

IP_ADDRESS IP address of the machine from which the client is connected.

NETWORK_PROTOCOL Network protocol being used for communication, as specified

in the ‘PROTOCOL=protocol’ portion of the connect string.

BG_JOB_ID Job ID of the current session if it was established by an

Oracle background process. Null if the session was not

established by a background process.

FG_JOB_ID Job ID of the current session if it was established by a

client foreground process. Null if the session was not

established by a foreground process.

AUTHENTICATION_TYPE How the user was authenticated:

DATABASE: Username/password authentication.

OS: Operating system external user authentication.

NETWORK: Network protocol or ANO authentication.

PROXY: OCI proxy connection authentication.

AUTHENTICATION_DATA Data being used to authenticate the login user. For

X.503 certificate authenticated sessions, this field

returns the context of the certificate in HEX2 format.>

Note: You can change the return value of the AUTHENTICATION_DATA

attribute using the length parameter of the syntax. Values of

up to 4000 are accepted. This is the only attribute of USERENV

for which Oracle implements such a change.