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
October 5th, 2009 at 4:51 pm
Imkan verin developerlər rahat işləsin də
October 5th, 2009 at 4:55 pm
October 5th, 2009 at 5:14 pm
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.
November 26th, 2009 at 8:15 pm
This solution don’t work if the program executable is renamed.
Best regards!
November 27th, 2009 at 1:33 pm
Sure Socrates you’re right. Here I just showed how we can write a LOGON trigger
April 15th, 2010 at 6:55 pm
hi but if i rename my program i can enter is there any solution for this
my id –rkkpotty@gmail.com
pls reply.
April 16th, 2010 at 11:10 am
True
May 5th, 2010 at 1:02 pm
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;
May 5th, 2010 at 6:40 pm
Dear Limsk
It’s also might be a solution.
Thanks
March 20th, 2011 at 12:13 pm
[…] http://kamranagayev.wordpress.com/2009/10/04/block-developers-from-using-toad-and-other-tools-on-pro… […]
December 4th, 2011 at 1:54 pm
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,
September 5th, 2012 at 6:45 pm
my post logon trigger doesnt work
September 14th, 2012 at 5:16 am
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.