Re: raise_application_error generates trace file

On 30 Nov 2001 06:23:28 -0800, robvl66_at_hotmail.com (rob) wrote:

>I've created a logon trigger to check if users ip adres is in out
>range. The strange thing is : it now and then generates a trace file.
>Could someone explain why.
>Oracle8i Enterprise Edition Release 8.1.7.2.0
>SunOS 5.8
>I've edited the ip-range in the trigger for security reasons
>
>Alert.log entry:
>Errors in file /u01/app/oracle/admin/hlpdtest/udump/sid_ora_7112.trc:
>ORA-00604: error occurred at recursive SQL level 1
>ORA-20001: This service is for authorized personnel only
>ORA-06512: at line 32
>
>The trigger:
>CREATE OR REPLACE TRIGGER on_logon
>AFTER LOGON
>ON DATABASE
>DECLARE
> v_ip varchar2(15);
> v_usr varchar2(40);
> v_ses varchar2(40);
> v_host varchar2(40);
> v_date date;
>BEGIN
> SELECT SYS_CONTEXT('USERENV','IP_ADDRESS')
> , SYS_CONTEXT('USERENV','HOST')
> , SYS_CONTEXT('USERENV','OS_USER')
> , SYS_CONTEXT('USERENV','SESSION_USER')
> , sysdate
> INTO v_ip
> , v_usr
> , v_ses
> , v_host
> , v_date
> FROM dual;
>
> IF v_ip NOT LIKE 'xxx.xxx.%'
> THEN
> INSERT
> INTO intruders
> VALUES (v_ip
> ,v_usr
> ,v_ses
> ,v_host
> ,v_date);
> COMMIT;
> RAISE_APPLICATION_ERROR(-20001, 'This service is for authorized
>personnel only');
> END IF;
>END;
>/

Yup, if you don't have serveroutput, this is what will happen. However, Oracle Connection Manager can block ip-addresses. You'll probably better configure it, and remove checks like this. If you don't do so you have to revise the trigger each time someone revises the range of ip-addresses. Not a good idea, IMO

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Fri Nov 30 2001 - 08:42:29 CST