Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

User audit problem

user517148Jun 30 2009 — edited Apr 9 2010
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;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 7 2010
Added on Jun 30 2009
13 comments
1,583 views