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

10032010

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?

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

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.

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

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.

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.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: