Pavan DBA's Blog

The DBA Knowledge Store

How to block TOAD or other tools accessing database?

Posted by Pavan DBA on May 4, 2010


Many DBA’s got this requirement in the past and may be you will get in future.

One fine day, your client / Management asks you to block any connections to database from 3rd party tools like TOAD. In such case, you will start thinking how can i do that?

Please remember we don’t have any ready-made solution for this. but yes we do have a way i.e by creating a trigger

The below script is taken from http://www.psoug.org/snippet/Block_TOAD_and_other_tools_516.htm

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 here.');
  END IF;
END;

The above script will block access to TOAD and other tools mentioned.

Note : If you rename TOAD.EXE to PAVAN.EXE (or some other name), this trigger will not work

So instead of trying to block those tools, try to concentrate on privileges assigned to use

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

 
%d bloggers like this: