Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: trigger problem
"DA Morgan" <damorgan_at_x.washington.edu> a écrit dans le message de news:1113513389.919405_at_yasure...
| Michel Cadot wrote:
|
| > <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
|
| But they won't change the name if you don't tell them what you are doing.
| --
| Daniel A. Morgan
| University of Washington
| damorgan_at_x.washington.edu
| (replace 'x' with 'u' to respond)
To the OP, my post was just a note not intended to solve the problem. For this, you have to answer to Daniel's questions (version, error message).
To Daniel, when you see "ORA-20000: Toad users not allowed on PROD DB!", you know what the DBA does or wants to do.
Regards
Michel Cadot
Received on Thu Apr 14 2005 - 23:20:59 CDT