Techdata VINA 지식 공유

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

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

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;

/