Restricting particular IP addresses from Oracle databases

Have you ever wanted to control whether or not particular IP addresses can connect to your Oracle database? Here's how:

First, you have to create a file named protocol.ora as follows.

tcp.invited_nodes=(XXX.XY1.XY2.Y, XXX.XY2.XY3.Z) 
tcp.validnode_checking=yes 

Let's say you want to allow a user to connect to the database with an IP addresse of 192.168.11.20 or 192.168.10.12 only. You simply configure the protocol.ora file as follows:

tcp.invited_nodes=(192.168.11.20, 192.168.10.12) 
tcp.validnode_checking=yes 

The destination of this file would be same as that of sqlnet.ora and tnsnames.ora. You'll need to stop and restart the listener to make this change work.

Similarly, to exclude certain IP addresses, you have to configure parameter tcp.excluded_nodes as above in the protocol.ora file.

Note: There are certain bugs in this method, so be sure to check with Oracle support or Metalink for your specific platform and version. For example, on Windows NT and Oracle version 8.1.x, you have to create a net8/admin directory and put the protocol.ora file in it rather than the network/admin directory. On Unix and Oracle version 8.1.x, you have to rename protocol.ora to .protocol.ora in the network/admin directory.

For More Information

• What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle tip to offer your fellow DBA's? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, and DB2 gurus are waiting to answer your toughest questions.