Kamran Agayev's Oracle Blog

Oracle Certified Master

Block developers from using TOAD and other tools on production databases

Posted by Kamran Agayev A. on October 4th, 2009

Today, I’ve seen a nice example at www.psoug.org on how to prevent users from using additional tools to connect to production database. This is the AFTER LOGON trigger create ON DATABASE as follows:

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 here.');
  END IF;
END;
/
SHOW ERRORS
http://www.psoug.org/snippet/Block_TOAD_and_other_tools_516.htm

13 Responses to “Block developers from using TOAD and other tools on production databases”

  1. Ramin Orucov Says:

    Imkan verin developerlər rahat işləsin də :)

  2. Kamran Agayev A. Says:

    :) Test database-də sizin rahat işləməyiniz üçün hər cür şərait yaradarıq :)

  3. Ramin Orucov Says:

    Test serveri nəzərdə tutmurdum ki, onsuz da o əlimizdədir :) Amma lazım olanda production serverə Toad ilə qoşulmaq imkanından məhrum olmaq və sqlplus ilə işləmək narahatdır.

  4. socrates Says:

    This solution don’t work if the program executable is renamed.

    Best regards!

  5. Kamran Agayev A. Says:

    Sure Socrates you’re right. Here I just showed how we can write a LOGON trigger

  6. rajesh Says:

    hi but if i rename my program i can enter is there any solution for this

    my id –rkkpotty@gmail.com

    pls reply.

  7. Kamran Agayev A. Says:

    True

  8. limsk Says:

    Since it is not possible to block every conceivable executable or renamed executable that connects to the database, you may be able to do the reverse by allowing only the application programs to connect, for example:

    IF program = xxxx OR (whatever special conditions here e.g. SYS, SYSTEM, PERFSTAT or other admin users) THEN
    … allow connect
    ELSE
    … disallow
    END IF;

  9. Kamran Agayev A. Says:

    Dear Limsk

    It’s also might be a solution.

    Thanks

  10. Restrict Access to the Database With Tools like Toad « Shrikant's Blog Says:

    […] http://kamranagayev.wordpress.com/2009/10/04/block-developers-from-using-toad-and-other-tools-on-pro… […]

  11. Mohammed Says:

    Dears,

    I think we can mix between two solutions to avoid renaming exe file bu users as below:
    1- create trigger as mentioned above.
    2- Specify in sqlnet.ora the trusted IPs only to exclude the developers PCs IPs.
    3- Specify another PC (DEV-PC as example) for all users and give them “Remote desktop group only/Hidden share access” only. So, in this case no any user can rename any exe file and no one can pass the trigger.

    You agree with me ?

    Thanks & Regards,

  12. nelson Says:

    my post logon trigger doesnt work

  13. Kamran Agayev A. Says:

    Dear Nelson. Your question looks like:
    Look likes – “My car doesn’t work, please fix my car” :)
    Could you please elaborate more as I don’t know how did you created the trigger, how did you test it and so on.

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>