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.VALIDNODE_CHECKING = yes
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 10.1.0.4.2 - Production on Wed Nov 22 09:47:43 2006

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

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

From dbclient002, I cannot connect

$ sqlplus scott/tiger@lsc02

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

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

ERROR:
ORA-12547: TNS:lost contact

11 thoughts on “Restrict network access to listener”

  1. Vidya Balasubramanian

    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.

  3. Marco Gralike

    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.

  4. Laurent Schneider

    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 😉

  5. olivier

    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?

    thanks

  7. Bhavesh Marolia

    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.
    Thanks,

  9. laurentschneider Post author

    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');
end;
/

    and enable this trigger during peak hours

    Do you want that 😕

  10. oracle dude

    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…

Comments are closed.