(1) what really works in 9i is the sqlnet. ora file. Modifying sqlnet. ora is actually the best and fastest way.
Add the following content to sqlnet. ora:
-----------------------------
#### Attributes from protocol. ora ####
TCP. validnode_checking = Yes
# IP addresses allowed for access
TCP. invited_nodes = (IP1, ip2 ......)
# IP addresses that are not allowed to access
TCP. excluded_nodes = (IP1, ip2 ......)
Then restart the listener.
Note:
1. Both TCP. invited_nodes and TCP. excluded_nodes exist, mainly TCP. invited_nodes.
2. Be sure to permit or disable the IP address of the local server. Otherwise, the listener cannot be started or stopped through LSNRCTL, because the listener accesses the listener through the local IP address, this IP address is disabled, but it is not affected when the service is started or disabled.
3. After modification, you must restart the listener to take effect without restarting the database.
4. Any platform is supported, but only applicable to the TCP/IP protocol.
(2)
When net8 adopts the TCP/IP protocol, you can set the following parameters in sqlnet. ora of the Oracle9i server to restrict or allow users to connect to the database from a specific client.
TCP. validnode_checking = Yes | No
TCP. invited_nodes = (IPaddress | hostname)
TCP. excluded_nodes = (IPaddress | hostname)
The TCP. validnode_checking parameter determines whether to check the Client IP address;
The TCP. invited_nodes parameter lists the IP addresses of the clients that can be connected;
The TCP. excluded_nodes parameter lists the IP addresses of clients that cannot be connected.
(Note: Use the Protocol. ora file in Oracle8i. If this file is not available, create one by yourself .)
For example:
After setting the Oracle9i parameter file, you can control the IP address of the computer.
Add the following to the configuration file $ ORACLE_HOME/Network/sqlnet. ora on 172.28.65.13:
# Enable IP address check
TCP. validnode_checking = Yes
# IP addresses allowed for access
TCP. invited_nodes = (172.28.65.13)
# IP addresses that are not allowed to access
IP. excluded_nodes = (172.27.65.15)
Restart the listener!
$ LSNRCTL RELOAD
LSNRCTL for Solaris: Version 9.2.0.4.0-production on 14-dec-2005 16:59:19
Copyright (c) 1991,200 2, Oracle Corporation. All rights reserved.
Connecting to (description = (address = (Protocol = IPC) (Key = extproc0 )))
The command completed successfully.
Edit the $ ORACLE_HOME/Network/admin/tnsnames. ora file on 172.28.65.15:
DSF =
(Description =
(Address = (Protocol = TCP) (host = 172.28.65.13) (Port = 1521 ))
(CONNECT_DATA = (SID = orcl ))
)
Perform the tnsping test on the 15th:
$ Tnsping DSF
TNS Ping utility for Solaris: Version 9.2.0.4.0-production on 14-dec-2005 17:04:02
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
Used tnsnames adapter to resolve the alias
Attempting to contact (description = (address = (Protocol = TCP) (host = 172.28.65.13) (Port = 1521) (CONNECT_DATA = (SID = orcl )))
TNS-12537: TNS: Connection closed
Connection test:
$ Sqlplus wacos/OSS @ DSF
SQL * Plus: Release 9.2.0.4.0-production on Wed dec 14 17:04:24 2005
Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved.
Error:
ORA-12537: TNS: Connection closed