Not allow sql or toad session from some terminal

[bhattnirav]

Dear All,

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
DECLARE
v_prog sys.v_$session.program%TYPE;
V_TERM sys.v_$session.terminal%TYPE;
v_sid sys.v_$session.sid%TYPE;
v_sl sys.v_$session.serial#%TYPE;
BEGIN

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
THEN

RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');
END IF;
END;


Thanks in advance,

Nirav

[davey23uk]

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

[bhattnirav]

According to me due to same session. I have also tried with :

EXECUTE IMMEDIATE "ALTER SESSION KILL SESSION ......"

Thanks,

Nirav

[PAVB]

...and?

[bhattnirav]

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.

Regards,

Nirav

[davey23uk]

you will have to work through that trigger and work out where it is failing for you

[gandolf989]

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.