RE: Logon trigger isn't restricting logon
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 2 Mar 2005 09:51:55 -0500
What version of Oracle are you on? When Oracle first introduced Database
Event Triggers the raise application error routine could not be used to kill
the logon. Instead you had to save the sid and serial# for the session in a
table and launch another session (dbms_job or daemon process) that read the
table and performed the kill operation. This restriction was lifted later
(probably with ver 9).
HTH -- Mark D Powell --
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Hostetter, Jay M
Sent: Wednesday, March 02, 2005 9:29 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Logon trigger isn't restricting logon
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
