Limit connections to listener by source


1If you want to limit access to database from specific host names or ip addresses you must use feature called valid node checking. This feature allows you to define two lists :

  • invited nodes defines a set of nodes from which connections will be accepted
  • excluded_nodes – defines a list from which connections will be rejected

If you use invited_nodes then any node not in this list will be rejected by listener or if you use excluded_nodes then any node not in this list will be accepted by the listener. If you both of these lists, then invited_nodes take precedence over the excluded_nodes. To test invited_nodes list you must add following lines to the sqlnet.ora file:

[oracle@orcl ~]$ vi /u01/app/oracle/product/11.2/db_1/network/admin/sqlnet.ora 
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
ADR_BASE = /u01/app/oracle
tcp.validnode_checking=YES
tcp.invited_nodes=(ValehPC,192.168.100.13)

[oracle@orcl u01]$ lsnrctl stop
[oracle@orcl u01]$ lsnrctl start

After changes applied, when you try to connect from any node apart from these two you will get an error like bellow:

when trying to connect with SQL Plus

C:UsersValeh>sqlplus test/test@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.177)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=db11g)))

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 18 05:01:57 2015

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

ERROR:
ORA-12537: TNS:connection closed

when trying connect with Oracle SQL Developer

1

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s