Re: trigger problem

"DA Morgan" <damorgan_at_x.washington.edu> a écrit dans le message de news:1113572773.177478_at_yasure...
| 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.

You're right.
I have the (not so) bad practice to always think in a hacker point of view.

|
| 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.

I fully agree.

| --
| Daniel A. Morgan
| University of Washington
| damorgan_at_x.washington.edu
| (replace 'x' with 'u' to respond)

Regards
Michel Cadot Received on Fri Apr 15 2005 - 09:57:16 CDT