Why Doesn’t This Trigger Work – No Developer Tools Allowed in the Database

March 10, 2010

In one of last week’s blog articles I asked why a SQL statement that had appeared in a book, was copied to various websites, and was offered as an example of good SQL simply did not work.  A couple of readers of this blog took up the challenge to offer suggestions.  I think that I found another example of blindly reproducing other’s code without verifying that the code works as expected.  What did I find this time?

http://www.orafaq.com/scripts/security/blocktools.txt

rem -----------------------------------------------------------------------
rem Filename:   NoTOAD.sql
rem Purpose:    Block developers from using TOAD and other tools on
rem             production databases.
rem Date:       19-Jan-2004
rem Author:     Frank Naude
rem -----------------------------------------------------------------------

CONNECT / AS SYSDBA;

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')
    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%' 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 '%EXCEL%'       -- MS-Excel plug-in
  THEN
     RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed on PROD DB!');
  END IF;
END;
/
SHOW ERRORS

If you search the Internet you will find this piece of code, minus the header block, on various websites as recently as 2010, including in this OTN thread.  The idea of blocking development tools in a production database could be a valid goal, so I am able to understand the interest in the trigger.  What is wrong with the trigger?  Is it just a case that SQL*Plus could also be considered a developer tool?  What about the Crystal Reports development enviroment?  What if the developer created his own development tool to access the database?  What if… someone changed the filename of TOAD?  What if… the trigger doesn’t actually block Excel without any tricks such as changing Excel’s filename?

The contents of this blog article were tested with Oracle Database 11.1.0.7 with an Oracle 11.1.0.7 client, Excel 2007, and Windows Vista as the client operating system.  First, I created the trigger when connected as SYS, and then queried V$SESSION to find that only my SQL*Plus connection was listed.

Then in Excel 2007 I selected Get External Data – From Other Sources:

When prompted, I connected to the database as a normal user:

I was then able to freely execute queries against the database using the Microsoft Query Tool, which would then allow me to very easily return the rows to an Excel spreadsheet.

OK, maybe the above is cheating… a little.

But, what is the point in blocking Excel with the logon trigger, is the trigger supposed to stop someone from firing up an Excel macro that uses ADO to connect to the database and retrieve whatever information the developer would like to see?  Kind of like this example:

The logon trigger that appears on several websites did not block my Excel connection – if you take a look at the first screen capture you will see that the logon trigger was created without error, and then I was able to connect with the Microsoft Query Tool and my Excel macro using a normal user’s account.  Someone want to try explaining what went wrong?  Must be that we need to file a Metalink bug report, after all, I saw this example on the news so it must be true.  🙂

Maybe Oracle Database 11.2.0.1 will respond more favorably (Oracle 11.1.0.7 client, Excel 2007, and Windows Vista as the client operating system):

While this code might have worked fine in 2004, it would probably make sense to test this trigger code before copying it to your website.  I think that this is worth repeating:

Lesson 1: if you plan to publish something, whether in book form or on the Internet, make certain that what you publish actually works (or at least looks like you put some effort into it) .

Lesson 2: if you plan to copy someone else’s work and post it on your website/blog make certain that what you copy and pass off as your own actually works.

Lesson 3: don’t trust everything that you read on the Internet or in a book without first verifying that the information is correct, even if you find the information on your favorite website.