Not allow sql or toad session from some terminal
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.');
Thanks in advance,
well what happens then, just saying doesnt work doesnt help
You can see what the code does and what it is checking for - see where it is failing for you
According to me due to same session. I have also tried with :
EXECUTE IMMEDIATE "ALTER SESSION KILL SESSION ......"
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
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.
ORA-00604: error occurred at recursive SQL level 1
ORA-00027: cannot kill current session
As I have mentioned earlier it is due to same session.
My question is how to control TOAD and SQLPLUS from some terminal. Only from 1 or 2 machine we want to allow TOAD connection.
you will have to work through that trigger and work out where it is failing for you
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.
this space intentionally left blank
Click Here to Expand Forum to Full Width