We want to restrict connecting thru TOAD or SQLPLUS from some of terminals.
for that I write a trigger but it doesn't work. I got this trigger from google search (OTN) :
CREATE OR REPLACE TRIGGER block_tools_from_prod AFTER LOGON ON DATABASE
SELECT program,terminal,sid,serial# INTO v_prog, v_term, v_sid, v_sl
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
RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Why don't you just filter at the listener level based on IP. You can white list only the IP addresses that you want, ie. the application servers.If a developer is connecting from their desktop they are only likely to use sqlplus, Toad, Sql Developer, etc. that way the only databases developers will be able to connect to will be dev. you can also assign different rights based on username. For example give them read only access to production databases. Or you can create a daily clone of production that they can update which will consistently give them one day old data. You really have a lot of options.