You might want to add ora-01004 & ora-01035
--
Terry Dykstra
<fitzjarrell_at_cox.net> wrote in message
news:1111100472.473798.130010_at_z14g2000cwz.googlegroups.com...
>
> T.Kindermann wrote:
> > Hello everybody,
> >
> > we have the SOX audit here and i must implemente an access violation
> > report.
> > We running Oracle 9i with the ERP System Baan IV.
> >
> > The list must contain all not successfully logins to the database,
> > perfectly with username, IP-Adress, time stamp.....
> >
> > Have someone a solution for this, or have someone the same problem ?
> >
> >
> > Thanks in advance
> >
> > Thomas Kindermann
> > Database Administrator
> > Germany
> >
> >
> >
> >
> ------------------------------------------------------------------------
> > Thomas Kindermann
> > E-MAIL: Reply to TKINDER<x>@GMX.DE without <x>
>
> Try this:
>
> create table connection_audit (
> username varchar2(20),
> sess_userid number,
> ip_address varchar2(16),
> host varchar2(30),
> os_user varchar2(20),
> sessionid number,
> action varchar2(6),
> action_date date)
> tablespace tools;
>
> create or replace trigger logon_failure
> after servererror
> on database
> begin
> if(IS_SERVERERROR(1017)) then
> insert into connection_audit
> values
> (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 connection_audit
> values
> (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(1045)) then
> insert into connection_audit
> values
> (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;
> /
>
> Such should get the information you require.
>
>
> David Fitzjarrell
>
Received on Thu Mar 17 2005 - 17:49:58 CST
