Re: trigger problem

nitin_kaul_at_rediffmail.com wrote:
> 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
>

You *do* realize that TOAD has read-only licenses?!? Lets you connect and poke around, but not change anything: data nor procedures.

I do not know if it's possible to configure that, based on the connect string, but their (quests) support should be able to answer that.

-- 
Regards,
Frank van Bortel