DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Not allow sql or toad session from some terminal

  1. #1
    Join Date
    Apr 2001
    Location
    Vadodara, India
    Posts
    249

    Not allow sql or toad session from some terminal

    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

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    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

  3. #3
    Join Date
    Apr 2001
    Location
    Vadodara, India
    Posts
    249
    According to me due to same session. I have also tried with :

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

    Thanks,

    Nirav

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    ...and?
    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.

  5. #5
    Join Date
    Apr 2001
    Location
    Vadodara, India
    Posts
    249
    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

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    you will have to work through that trigger and work out where it is failing for you

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width