User audit problem
Hi,
I have to create trigger which audit user fail logins.
I created new user and grant DBA to him.
Then i did this table and trigger (bellow).
When i try to connect to DB with false login and pass trigger works but fields username and sess_userid are empty.
What i did wrong?
Regards
Tomas
CREATE TABLE stats$connection_fail_audit_mz (
username VARCHAR2(30) NULL,
sess_userid NUMBER NULL,
ip_address VARCHAR2(16) NULL,
host VARCHAR2(30) NULL,
os_user VARCHAR2(20) NULL,
sessionid NUMBER NULL,
action VARCHAR2(6) NULL,
action_date DATE NULL
)
/
create or replace trigger connection_fail_audit_mz
after servererror
on database
begin
if(IS_SERVERERROR(1017)) then
insert into stats$connection_fail_audit_MZ
values
(sys_context('USERENV','SESSION_USER'),
sys_context('USERENV','SESSION_USERID'),
sys_context('USERENV','IP_ADDRESS'),
sys_context('USERENV','HOST'),
sys_context('USERENV','OS_USER'),
sys_context('USERENV','SESSIONID'),
'-01017',
sysdate);
elsif (IS_SERVERERROR(1005)) then
insert into stats$connection_fail_audit_MZ
values
(sys_context('USERENV','SESSION_USER'),
sys_context('USERENV','SESSION_USERID'),
sys_context('USERENV','IP_ADDRESS'),
sys_context('USERENV','HOST'),
sys_context('USERENV','OS_USER'),
sys_context('USERENV','SESSIONID'),
'-01005',
sysdate);
elsif (IS_SERVERERROR(1004)) then
insert into stats$connection_fail_audit_MZ
values
(sys_context('USERENV','SESSION_USER'),
sys_context('USERENV','SESSION_USERID'),
sys_context('USERENV','IP_ADDRESS'),
sys_context('USERENV','HOST'),
sys_context('USERENV','OS_USER'),
sys_context('USERENV','SESSIONID'),
'-01004',
sysdate);
elsif (IS_SERVERERROR(1035)) then
insert into stats$connection_fail_audit_MZ
values
(sys_context('USERENV','SESSION_USER'),
sys_context('USERENV','SESSION_USERID'),
sys_context('USERENV','IP_ADDRESS'),
sys_context('USERENV','HOST'),
sys_context('USERENV','OS_USER'),
sys_context('USERENV','SESSIONID'),
'-01035',
sysdate);
elsif (IS_SERVERERROR(1045)) then
insert into stats$connection_fail_audit_MZ
values
(sys_context('USERENV','SESSION_USER'),
sys_context('USERENV','SESSION_USERID'),
sys_context('USERENV','IP_ADDRESS'),
sys_context('USERENV','HOST'),
sys_context('USERENV','OS_USER'),
sys_context('USERENV','SESSIONID'),
'-01045',
sysdate);
end if;
end;