Logon trigger isn't restricting logon

From: Hostetter, Jay M <JHostetter_at_decommunications.com>
Date: Wed, 2 Mar 2005 09:28:56 -0500
Message-ID: <D67EB7CEECD4334F9C85759227553BBC9198A7@CL-EXCHANGE1.dande.com>

I'm not quite sure what I'm missing here. I created a logon trigger, did some debugging and I confirmed that it does indeed fire. The problem seems to be with RAISE_APPLICATION_ERROR. This trigger is supposed to prevent certain connections from occuring. Instead, a trace file is generated with the following:

Skipped error 604 during the execution of JMH.ON_LOGON *** 2005-03-02 09:19:41.326
ksedmp: internal or fatal error

ORA-00604: error occurred at recursive SQL level 1
ORA-20001: You are not allowed to login
ORA-06512: at line 8

Now, from what I can see, this behavior is expected if the user has ADMINISTER DATABASE TRIGGER or ALTER ANY TRIGGER (Note 265012.1). I think this also happens if you have certain roles (this user does not have any special roles).=20
So I'm trying to figure out why the error is skipped and login is allowed. Here is my trigger:

CREATE OR REPLACE TRIGGER on_logon
AFTER LOGON
ON jmh.schema
declare
hname varchar2(50);
BEGIN
select sys_context('userenv','host') into hname from dual; IF upper(hname) like '%TESTBOX%' THEN

   RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to login'); END IF;
END;
/

Any help would be appreciated.

Thank you,
Jay

**DISCLAIMER

This e-mail message and any files transmitted with it are intended for the =
use of the individual or entity to which they are addressed and may contain=
 information that is privileged, proprietary and confidential. If you are n=
ot the intended recipient, you may not use, copy or disclose to anyone the =
message or any information contained in the message. If you have received t=
his communication in error, please notify the sender and delete this e-mail=
 message. The contents do not represent the opinion of D&E except to the ex=

tent that it relates to their official business.

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Mar 02 2005 - 09:31:00 CST