Block access to database oracle

This is a script to create a logon trigger for blocking third party tools like Toad, SQl Navigator etc from accesing a production database.
The script is taken from: http://www.psoug.org/snippet/Block_TOAD_and_other_tools_516.htm and modified to include SQL Developer as well. Since these days it is the defacto tool to work on databases by developers.

[sourcecode language="sql"]
CREATE OR REPLACE TRIGGER SYS.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 '%SQL%DEVELOPER%' OR -- SQL Developer
UPPER(v_prog) LIKE '%EXCEL%' -- MS-Excel plug-in
THEN
RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');
END IF;
END;
/
[/sourcecode]

 

Category: Uncategorized

Tags:

One comment

Leave a Reply

Article by: Shadab Mohammad