Deny Access to DB for Specific IPs

We can configure the sqlnet.ora file to allow and deny access to the database via the validnode checking parmeters.

TCP.VALIDNODE_CHECKING
Use to specify whether to screen access to the database.Value is either YES or NO

TCP.EXCLUDED_NODES
Use to specify which clients using the TCP/IP protocol are denied access to the database. Hostname and ipaddress can be used

TCP.INVITED_NODES
Use to specify which clients using the TCP/IP protocol are allowed access to the database. Hostname and ipadddress can be used.  

Example sqlnet.ora file (set where database is running) , I did on the 10.10.2.46 (srv2) machine.

TCP.VALIDNODE_CHECKING = YES
#TCP.EXCLUDED_NODES= (192.168.12.81)
TCP.INVITED_NODES=(172.55.12.101, srv2)

Please note that if the Local Server's address (where the listener is located) is not included in the TCP.INVITED_NODES listing, then the valid node checking seems to be null and void.

Now just stop and start the listener

LSNRCTL> set current_listener lsnrfradb
Current Listener is lsnrfradb

LSNRCTL> stop
Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=srv2.domain)(PORT=1621))
The command completed successfully

LSNRCTL> start
Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
System parameter file is D:\oracle\product\10.2.0\db_1\network\admin\listener.ora
Log messages written to D:\oracle\product\10.2.0\db_1\network\log\lsnrfradb.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srv2.domain)(PORT=1621)))

Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=srv2.domain)(PORT=1621))
STATUS of the LISTENER
------------------------
Alias                     lsnrfradb
Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date                19-DEC-2010 14:43:26
Uptime                    0 days 0 hr. 0 min. 3 sec
Trace Level               off
Security                  ON: Password or Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\oracle\product\10.2.0\db_1\network\admin\listener.ora
Listener Log File         D:\oracle\product\10.2.0\db_1\network\log\lsnrfradb.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srv2.domain)(PORT=1621)))
Services Summary...
Service "FRADB" has 1 instance(s).
  Instance "FRADB", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Test

Test on client (172.55.12.101) which is in the invited list

C:\Documents and Settings\inam>sqlplus scott/tiger@fradb

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Dec 19 14:55:53 2010

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

Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL@ fradb>

Test on client (192.168.12.81) which is not in the invited list

 C:\Documents and Settings\inam>sqlplus scott/tiger@fradb

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Dec 19 14:56:43 2010

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

ERROR:
ORA-12537: TNS:connection closed


Enter user-name:

Ref:
263030.1
257700.1
402933.1
287500.1