Logon trigger isn't restricting logon
- From: "Hostetter, Jay M" <JHostetter@xxxxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 2 Mar 2005 09:28:56 -0500
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
