| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to stop access to prod instance ...
It seems that raise in on_logon trigger is equivalent to logoff or maybe NOT logon :-)
so here is scenario
1.
I tried to connect with gints/passwd_at_tuk
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
With the Partitioning option
JServer Release 8.1.7.0.1 - Production
gints@>
2.
then in another session (SYS) created trigger
sys_at_tuks> CREATE OR REPLACE TRIGGER on_logon
2 AFTER LOGON ON DATABASE
3 BEGIN
4 declare
5 aa exception;
6 begin
7 raise aa;
8 end;
9 end;
10 /
Trigger created.
3.
I tried to connect with gints/passwd_at_tuk
SQL*Plus: Release 3.3.4.0.0 - Production on Mon Jan 07 17:00:03 2002
Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.
ERROR: ORA-00604: error occurred at recursive SQL level 1
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 5
Enter user-name:
4. then in sys session dropped trigger and I was able to connect again!
Also I remeber if there was is an error in after logon trigger then nobody can connect to database even sys
in this particular situation with raise exception clause sys could connect
Gints Plivna
IT Sistēmas, Merķeļa 13, LV1050 Rīga
http://www.itsystems.lv/gints/
"Mercadante,
Thomas F" To: Multiple recipients of list
ORACLE-L <ORACLE-L_at_fatcity.com>
<NDATFM_at_labor.st cc:
ate.ny.us> Subject: RE: How to stop access to prod
instance ...
Sent by:
root_at_fatcity.com
2002.01.07 16:10
Please respond
to ORACLE-L
Waleed,
I did not because what would I attempt to do? There is no "disconnect" or "logoff" command within PL/SQL. Even if I raised the exception, the user would get the exception message and be left at the SQL> prompt.
This is why I am asking Sam if he found another method to prevent the connection from continuing. Maybe there is something new that I am not aware of (it would not be the first time).
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Monday, January 07, 2002 9:00 AM
To: 'Mercadante, Thomas F '; 'Multiple recipients of list ORACLE-L '
Did you check what happens if you raise an exception from logon trigger?
-----Original Message-----
To: Multiple recipients of list ORACLE-L
Sent: 1/7/02 7:30 AM
Sam,
How do you create a LOGON trigger to *prevent* a connection from occurring?
Certainly, the LOGON trigger can track who connects with what tool. I
am
doing the same thing with my web application - tracking connections and
sending me a report every day of all connections.
But I have not found a way to prevent the connection - there is no way
that
I've found to disconnect someone with the LOGON trigger.
Am I missing something - did you figure this out? Please share!
Thanks
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Saturday, January 05, 2002 11:40 PM
To: Multiple recipients of list ORACLE-L
You can create a LOGON Trigger to stop users using anything i.e. SQLPLUS
,MSACCESS etc...
I have applied one at our site and works good. I even log people who try
to
log in and get them to explain to management their motives. That is a
good
deterrent.
The trigger can be written so that only selective users can have access
if
need be.
Sam P.Roberts (M.Sc)
Senior DBA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: rabbit_at_emirates.net.ae
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: G.Plivna_at_itsystems.lv
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon Jan 07 2002 - 09:30:26 CST
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |