help on database trigger

From: <kishanthakker_at_gmail.com>
Date: Thu, 07 Jun 2007 12:02:22 -0000
Message-ID: <1181217742.402037.27550@q19g2000prn.googlegroups.com>

Dear all,

I have queried for help on database trigger earlier on this forum and I got a very good response from all of you.

I have designed the trigger as per the instructions and following code was written

CREATE OR REPLACE TRIGGER block_tools_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')
  audsid NOT IN (SELECT AUDSID FROM V$SESSION WHERE USERNAME IN ('SYS','FINANCE')); -- 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%' OR -- Toad

     UPPER(v_prog) LIKE '%SQLNAV%' OR	-- SQL Navigator
     UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
     UPPER(v_prog) LIKE '%BUSOBJ%' OR   -- Business Objects
     UPPER(v_prog) LIKE '%SQLPLUSW%' OR   -- Business Objects
     UPPER(v_prog) LIKE '%EXCEL%'       -- MS-Excel plug-in
  THEN
     RAISE_APPLICATION_ERROR(-20000, 'Development tools are not

allowed on PROD DB!');
  END IF;
END; The trigger is perfect if login is through windows98 by TOAD (scott/ tiger) . it is showing the error message and not allowing to login. But strangely if the login is through WindowsXP it is just ignoring the error and getting logged in.

please help me in finding the fault and give the right lead.

thanx in advance....

Kishan Thakker Received on Thu Jun 07 2007 - 07:02:22 CDT