Configure sqlnet. ora to restrict IP Access to Oracle

-- ======================================
-- Configure sqlnet. ora to restrict IP Access to Oracle
-- ======================================

Similar to the firewall function, Oracle limits and allows a specific IP address or host name to access the database through Oracle Net. This function is implemented by the sqlnet. ora configuration file. This file is usually under the $ ORACLE_HOME/network/admin/directory, which is in the same path as tnsnames. ora and listener. ora. It is easy to use. By using listeners, lightweight access is more efficient than using triggers inside the database.

1. Implementation Method

Add the following records to the sqlnet. ora file.

Tcp. validnode_checking = yes
Tcp. invited_nodes = (hostname1, hostname2, ip1, ip2)
Tcp. excluded_nodes = (10.103.11.17, hostname1, hostname2)

When invited_nodes is used, all IP addresses or hosts that are not included in the invited_nodes value cannot connect to the database through mongoel Net. If you use

In excluded_nodes, except for the IP addresses listed in the excluded_nodes value and the host cannot be accessed, other nodes can access the database. Generally

Use the excluded_nodes parameter.

2. Notes

Use some features of excluded_nodes and invited_nodes
Wildcards are not supported (for example, hostname cannot be written as svhs0 * and IP address cannot be written as 10.103.11 .*)
Excluded_nodes and invited_nodes are mutually exclusive, either using the former or using the latter
If both tcp. invited_nodes and tcp. excluded_nodes exist, tcp. invited_nodes takes precedence.
Add the local address or the address of other nodes in the Cluster to the allowed list. Otherwise, the listener may fail to start.
After modification, you must restart the listener or reload to take effect, instead of restarting the database.
Only support for TCP/IP protocol

3. Practice drills

--> Use tnsping demo92. The connection is normal.
C: \> tnsping demo92

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0-Production on 25-JUN-2011 18:55:39
Copyright (c) 1997,201 0, Oracle. All rights reserved.
Used parameter files:
D: \ app \ Robinson \ Oracle_client \ product \ 11.2.0 \ client_1 \ network \ admin \ sqlnet. ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.103.11.20
9) (PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = demo92 )))
OK (0 msec)

--> View the configuration file
[Oracle @ test admin] $ more sqlnet. ora
# SQLNET. ORA Network Configuration File:/oracle/92/network/admin/sqlnet. ora
# Generated by Oracle configuration tools.

NAMES. DIRECTORY_PATH = (ONAMES, TNSNAMES, HOSTNAME)

# Added by Robinson
Tcp. validnode_checking = yes
Tcp. excluded_nodes = (10.103.11.17)

--> Reload
[Oracle @ test admin] $ lsnrctl reload listener_demo92

LSNRCTL for Linux: Version 9.2.0.8.0-Production on 26-JUN-2011 10:03:11

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

Connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = test) (PORT = 1521 )))
The command completed successfully

--> When tnsping again, the TNS-12547 error is received
C: \> tnsping demo92

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0-Production on 25-JUN-2011 19:01:21
Copyright (c) 1997,201 0, Oracle. All rights reserved.
Used parameter files:
D: \ app \ Robinson \ Oracle_client \ product \ 11.2.0 \ client_1 \ network \ admin \ sqlnet. ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.103.11.20
9) (PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = demo92 )))
TNS-12547: TNS: lost contact

--> In the following example, if both excluded_nodes and invited_nodes exist, invited_nodes takes precedence over
[Oracle @ test admin] $ more sqlnet. ora
# SQLNET. ORA Network Configuration File:/oracle/92/network/admin/sqlnet. ora
# Generated by Oracle configuration tools.

NAMES. DIRECTORY_PATH = (ONAMES, TNSNAMES, HOSTNAME)

# Added by Robinson
Tcp. validnode_checking = yes
Tcp. excluded_nodes = (10.103.11.17)
Tcp. invited_nodes = (10.103.11.17)

1 2 Next Page