In Oracle databases, how does one restrict IP address access through the SQLNET. ora file ?, Oraclesqlnet. ora

In Oracle databases, how does one restrict IP address access through the SQLNET. ora file ?, Oraclesqlnet. ora

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.