Re: Access violation report

From: <fitzjarrell_at_cox.net>
Date: 17 Mar 2005 15:01:12 -0800
Message-ID: <1111100472.473798.130010@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:01:12 CST