Home > Blogroll, dba, security > Restrict network access to listener

Restrict network access to listener

November 22nd, 2006
Goto comments Leave a comment

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

  1. Vidya Balasubramanian
    November 22nd, 2006 at 20:08 | #1
    Reply | Quote

    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
    November 22nd, 2006 at 22:36 | #2
    Reply | Quote

    ocm maybe

  3. Marco Gralike
    November 23rd, 2006 at 14:35 | #3
    Reply | Quote

    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
    November 23rd, 2006 at 15:59 | #4
    Reply | Quote

    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
    December 9th, 2006 at 11:18 | #5
    Reply | Quote

    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
    December 9th, 2006 at 16:41 | #6
    Reply | Quote

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

  7. Bhavesh Marolia
    March 30th, 2007 at 09:23 | #7
    Reply | Quote

    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
    March 30th, 2007 at 10:01 | #8
    Reply | Quote

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

  9. laurentschneider
    March 30th, 2007 at 11:41 | #9
    Reply | Quote

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

  1. No trackbacks yet.