Restrict IP address access through the sqlnet. ora File

Link: http://www.eygle.com/archives/2008/01/sqlnetora_ip_limit.html
In the Oracle database, we can use the sqlnet. ora file to implement address access restrictions.

Set the following parameters in the sqlnet. ora file to implement IP access restrictions:

TCP. validnode_checking = Yes
TCP. invited_nodes = (IP1, ip2 ......)
TCP. excluded_nodes = (IP1, ip2 ......)

Before these parameters are set, the test database can be accessed normally:

D:/> tnsping eygle

TNS Ping utility for 32-bit windows: Version 10.2.0.3.0-production on 2008 14:52:52

Copyright (c) 1997,200 6, Oracle. All rights reserved.

Used parameter files ,:
C:/Oracle/10.2.0/Network/admin/sqlnet. ora

Alias resolved using tnsnames Adapter
Attempting to contact (description = (address_list = (address = (Protocol = TCP) (host = 172.16.33.11) (Port = 1521) (CONNECT_DATA = (SERVICE_NAME = eygle )))
OK (30 ms)

After setting the parameters:

[Oracle @ jumper admin] $ cat sqlnet. ora
# Sqlnet. ora network configuration file:/opt/Oracle/product/9.2.0/Network/admin/sqlnet. ora
# Generated by Oracle configuration tools.

Names. directory_path = (tnsnames, onames, hostname)

TCP. validnode_checking = Yes
TCP. invited_nodes = (172.16.33.11, 172.16.34.89)

Restart the listener to make the settings take effect:

[Oracle @ jumper admin] $ LSNRCTL start

LSNRCTL for Linux: Version 9.2.0.4.0-production on 28-Jan-2008 14:42:01
Copyright (c) 1991,200 2, Oracle Corporation. All rights reserved.
Starting/opt/Oracle/product/9.2.0/bin/tnslsnr: Please wait...

Tnslsnr for Linux: Version 9.2.0.4.0-Production
System parameter file is/opt/Oracle/product/9.2.0/Network/admin/listener. ora
Log messages written to/opt/Oracle/product/9.2.0/Network/log/listener. Log
Trace information written to/opt/Oracle/product/9.2.0/Network/Trace/listener. TRC
Listening On: (description = (address = (Protocol = TCP) (host = 172.16.33.11) (Port = 1521 )))

Connecting to (description = (address = (Protocol = TCP) (host = 172.16.33.11) (Port = 1521 )))
Status of the listener
------------------------
Alias listener
Version tnslsnr for Linux: Version 9.2.0.4.0-Production
Start date 28-Jan-2008 14:42:01
Uptime 0 days 0 HR. 0 min. 0 sec
Trace Level support
Security on
SNMP off
Listener parameter file/opt/Oracle/product/9.2.0/Network/admin/listener. ora
Listener log file/opt/Oracle/product/9.2.0/Network/log/listener. Log
Listener trace file/opt/Oracle/product/9.2.0/Network/Trace/listener. TRC
Listening endpoints summary...
(Description = (address = (Protocol = TCP) (host = 172.16.33.11) (Port = 1521 )))
Services summary...
Service "eygle" has 1 instance (s ).
Instance "eygle", status unknown, has 1 handler (s) for this service...
Service "Julia" has 1 instance (s ).
Instance "eygle", status unknown, has 1 handler (s) for this service...
The command completed successfully

Let's take a look at the client access:

D:/> tnsping eygle

TNS Ping utility for 32-bit windows: Version 10.2.0.3.0-production on 2008 14:53:19
Copyright (c) 1997,200 6, Oracle. All rights reserved.

Used parameter files:
C:/Oracle/10.2.0/Network/admin/sqlnet. ora

Alias resolved using tnsnames Adapter
Attempting to contact (description = (address_list = (address = (Protocol = TCP) (host = 172.16.33.11) (Port = 1521) (CONNECT_DATA = (SERVICE_NAME = eygle )))
TNS-12547: TNS: Lost connection

Note that you must add the local address or the addresses of other nodes in the cluster to the allowed list. Otherwise, the listener may fail to start.
After modifying the parameters, restart the listener settings to take effect.

Listener restrictions are usually lightweight, which is more efficient than using triggers in a database.

Website related articles | related articles

Oracle listener password and listener Security

Case study: no space left on device error caused by inode depletion

How to change the name of the listener Log File