Restrict network access to listener

If I have a limited number of db clients which are authorized to access my listener, I can restrict access to my listener by setting only two parameters in sqlnet.ora.

TCP.INVITED_NODES = (dbclient001,chltlxlsc1)

chltlxlsc1 is my db server, I include it in the list, it is required to start the listener locally.

From dbclient001, I can connect :

$ sqlplus scott/tiger@lsc02

SQL*Plus: Release - Production on Wed Nov 22 09:47:43 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release - Production
With the Partitioning option
JServer Release - Production

From dbclient002, I cannot connect

$ sqlplus scott/tiger@lsc02

SQL*Plus: Release - Production on Wed Nov 22 09:48:26 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ORA-12547: TNS:lost contact

By Laurent Schneider

Oracle Certified Master


  1. Laurent,
    TCP/IP node checking works welll if we have a small list. The last time when I implemented node checking , I had a huge list of AP addresses and eventually it became quite a headache to maintain this list and to keep track of who had access to which database? —>is there a better way we can do this assuming we have a huge list.

  2. It could have been a great feature.

    Its great, if you want to delimit database access only to, for instance, your application server.

    It would have been even greater, if you could use wild cards like 10.10.10.*, but alas, you can’t.

  3. yes, one application server, one enterprise manager grid control, one database server is fine. More is headache.

    Since it is in sqlnet.ora, a single client addition means restarting all the listeners from that oracle home (no reload of course)

    Exclude_node also exists, for example to prevent some ugly developer to use toad 😉

  4. hello,

    in the same idea i was wondering if it possible to restrict access to listener not for db clients but for db users?
    i mean user scott can only connect to port 1520?


  5. Hi,
    Is there any way that we can restrict access to particular .exe ‘s like TOAD.exe , or PLSQLDEV.exe. This are being used by the users to fire huge select query’s during peak hours.
    Can we restrict such exe’s so, if yes than how can this be done.

  6. you could create a logon trigger to check this,

    create trigger NOTOAD after logon on database begin
    if (sys_context('userenv','module')='TOAD') then 
    raise_application_error(-20001,'Please no toad yet, try again later');

    and enable this trigger during peak hours

    Do you want that 😕

  7. You can do all of these restrictions very easily, as well as many others if you use a Database Firewall. There are a few decent ones on the market that help restrictr by all kindsa stuff, including IP, application, hostname, OS user, time of day, username, etc…..

    The best on the market is by Imperva, 2nd best is Guardium. check into those products…

Leave a comment

Your email address will not be published.