« backup your blog!
Chinese Chess Swiss Champion 2006 »

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

This entry was posted on Wednesday, November 22nd, 2006 at 10:18 and is filed under oracle, dba, security. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Post to : del.icio.us - Digg it - Furl - reddit - Spurl

Put your code in <code> and </code> tags

9 Responses to “Restrict network access to listener”

  1. Vidya Balasubramanian Says:
    November 22nd, 2006 at 20:08

    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. Laurent Schneider Says:
    November 22nd, 2006 at 22:36

    ocm maybe

  3. Marco Gralike Says:
    November 23rd, 2006 at 14:35

    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 Says:
    November 23rd, 2006 at 15:59

    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 Says:
    December 9th, 2006 at 11:18

    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

  6. laurentschneider Says:
    December 9th, 2006 at 16:41

    well, a db user is identified by the db , so it has to connect thru the listener before being authentifcated

  7. Bhavesh Marolia Says:
    March 30th, 2007 at 09:23

    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,

  8. laurentschneider Says:
    March 30th, 2007 at 10:01

    no, you cannot check the application used with a sqlnet.ora setting

  9. laurentschneider Says:
    March 30th, 2007 at 11:41

    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 :?

Leave a Reply

Use <code> and </code> to post code