Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: trigger problem
<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