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

10 03 2010

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.


Actions

Information

5 responses

10 03 2010
Pete Finnigan

Hi Charles,

I liked your post and it inspired me to write a little about the problems of blocking applications – http://www.petefinnigan.com/weblog/archives/00001313.htm

cheers

Pete

10 03 2010
Charles Hooper

Hello Pete,

Thanks for stopping by and adding value to my blog article.

The blog article that you authored in reponse to my blog article essentially addresses the bigger picture of this blog article. This blog article is not so much about a trigger that singled out Excel as a developer tool, but completely failed to prevent Excel from connecting to the database – and how we fix the trigger to block Excel. Additionally, this blog article is not primarily intended to scold people for perpetuating inaccurate information on the Internet without taking a moment to stop, think, and understand. The bigger picture is how would someone limit what programs/users may or may not access a database.

I am impressed with the speed with which you put together your blog article – this must be a frequent request from your customers.

10 03 2010
Pete Finnigan

Thanks Charles,

Yes i wanted to really focus on the bigger picture as its something I have discussed many times over the years. I think the bottom line is that this idea (the idea of blocking any application) is hard to acheive.. its just a sad fact of using Oracle. Oracle provide API’s that essentially allow you spoof values, which is sad really

Thanks for your comment Charles and also about my speed, I write very fast..:-) i guess

cheers

Pete

11 09 2011
Pete Finnigan

Hi Charles,

I just posted to Julian Dontchefs blog and for some reason wordpress sent me also an email to sign up for this post? no idea why but anyway i am glad it did as I have written a presentation on accountability in the database and of spoofing values. I presented it at a UKOUG a while ago and will present it at the confernce in december and after that i will post the slides to my site. If you want a copy for info, drop me an email and i will send you a pdf.

cheers

Pete

11 09 2011
Charles Hooper

Hi Pete,

That is interesting that you were sent an email to subscribe to this blog article. I see that you were commenting on this blog article:
http://juliandontcheff.wordpress.com/2011/09/11/the-role-of-the-dba-in-the-private-database-cloud/#comments

You mentioned something in your comment in the above link about security in cloud-based networking where all databases in the world are shared on one server. I wonder if someone is trying to send a message to you that you have a point. :-)

I do find that it is interesting that you were sent a link to sign up for this article. I checked the WordPress control panel and did not see an option where sending those notices was a configurable option. Was the above linked comment your first comment on WordPress in the past year? I don’t recall for certain, but I do not think that it was possible to subscribe to blog articles when I set up this blog in November 2009 – maybe WordPress is sending out the notices retroactively when a new comment is added to a different WordPress blog?

It would be fantastic if you included a link to your presentation in a comment in this article when you make those slides available in December. I will definitely send an email to you requesting the slides. Thank you for the offer.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 139 other followers

%d bloggers like this: