create or replace trigger logon_denied_to_alert
after servererror on database
declare
message varchar2(168);
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);
begin
IF (ora_is_servererror(1017)) THEN
— get IP for remote connections:
if upper(sys_context(‘userenv’,'network_protocol’)) = ‘TCP’ then
IP := sys_context(‘userenv’,'ip_address’);
endif;
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);
message:= to_char(sysdate,’Dy Mon dd HH24:MI:SS YYYY’)||
‘ logon denied from ‘||nvl(IP,’localhost’)||’ ‘||v_pid||
‘ ‘||v_os_user||’ with ‘||v_program||’ – ‘||v_module||’ ‘||
v_action;
sys.dbms_system.ksdwrt(2,message);
end;
|
