Re: trigger problem

DA Morgan wrote:
>
> Michel Cadot wrote:
>
> > "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
>
> If you are so sophisticated that you know all about v$session, etc. The
> person with that knowledge is not an end user.
>
> But lets rewrite your error message and see what will happen next.
>
> "ORA-20000: You have attempted to use TOAD to access a production
> database in violation of company policy. An email has been sent to
> HR and your manager notifying that you have violated company policy"
>
> If a little UTL_MAIL or UTL_SMTP is in the mix to actually do it my
> suspicion is that they won't ever risk trying it again. Locked doors
> have never stopped a thief. A certainty of being caught does.
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace 'x' with 'u' to respond)

We adopted a different strategy - when developers log on to our development databases we see if you've come through the allowable channel (ie, our list of allowable development products).

if not, we submit a job to check through v$open_cursor - most products have some "signature" queries that reveal what they are, even if the developer has renamed toad etc etc.

We did this for licensing issues (ie, we are not TOAD licensed but heaps of developers had brought in their own hacked versions).

But the same process could be used for the OP's requirement.

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"

------------------------------------------------------------