Re: help on database trigger

On Jun 7, 2:02 pm, "kishanthak..._at_gmail.com" <kishanthak..._at_gmail.com> wrote:
> 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

The obvious course of actions would of course be to disable the trigger and to query v$session in TOAD from your own session. In my case program reads 'TOAD.exe' so I can not reproduce the problem.
While you already are using versions of Windows desupported by Microsoft and by Oracle, your unknown version of TOAD might be also desupported, I'm using 8.6.0.38.
Apart from being inefficient
(You really should select upper(program) instead wrapping everything in upper calls) and requiring too much privilege (why don't you use dbms_application_info.read_module? Works like a charm!!) there is nothing wrong with the code, and the problem is with your copy of TOAD.

--
Sybrand Bakker
Senior Oracle DBA