Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: trigger problem

Re: trigger problem

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Thu, 14 Apr 2005 17:43:39 +0200
Message-ID: <425e8f83$0$6820$636a15ce@news.free.fr>

<nitin_kaul_at_rediffmail.com> a écrit dans le message de news:1113478441.113979.12840_at_l41g2000cwc.googlegroups.com...
| hi
| i created a trigger to block developers using TOAD on Production db.
| following is the trigger:
|
| CONNECT / AS SYSDBA;
|
| CREATE OR REPLACE TRIGGER block_toad_from_prod
| AFTER LOGON ON DATABASE
| DECLARE
| v_prog sys.v_$session.program%TYPE;
| BEGIN
| SELECT program INTO v_prog FROM sys.v_$session
| WHERE audsid = USERENV('SESSIONID')
| AND audsid != 0 -- Don't Check SYS Connections
| AND rownum = 1; -- Parallel processes will have the same AUDSID's
|
| IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' THEN
| RAISE_APPLICATION_ERROR(-20000, 'Toad users not allowed on PROD
| DB!');
| END IF;
| END;
|
|
| It works fine and does what it was created for.But the problem is that
| i also have multimaster replication on prod db..and the repadmin jobs
| are faling due to this trigger..i saw the error in alert file as the
| jobs were continously failing...it was giving me error at recursive sql
| statement..
|
| so is there some problem with trigger....or any other way to write it.
|
| regards
|

This will not work if a user change the name of toad executable to, for instance, gotcha.exe.

Regards
Michel Cadot Received on Thu Apr 14 2005 - 10:43:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US