Restrict specified machine IP access to Oracle database

By using the database server side of the Sqlnet.ora file can be implemented to prohibit the specified IP host access to the database, which is a great help to improve the security of the database, at the same time, this technology for us to manage and constrain the database access control provides an effective means.
The following are the specific steps to achieve this purpose for reference only:
1. The contents of the default server-side Sqlnet.ora file
# Sqlnet.ora Network Configuration File:d:\server\oracle\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
# This file was actually generated by NETCA. But if customers choose to
# install ' Software only ', this file wont exist and without the native
# Authentication, they'll is not being able to connect to the database on NT.
Sqlnet. Authentication_services= (NTS)
NAMES. Directory_path= (TNSNames)
2. Confirm the IP address of the client:
C:\Documents and Settings\administrator>ipconfig
3. Use the tnsping command and the Sqlplus command on the client side to verify the connectivity of the database:
C:\Documents and Settings\administrator>tnsping Irmdb
C:\Documents and Settings\administrator>sqlplus/nolog
Here is a description of the connectivity of the database as evidenced by both methods in the client.
4. Restrict client IP address 9.123.112.16 access to the current Irmdb database:
We only need to add the following content to the server-side Sqlnet.ora file.
# Sqlnet.ora Network Configuration File:d:\server\oracle\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
# This file was actually generated by NETCA. But if customers choose to
# install ' Software only ', this file wont exist and without the native
# Authentication, they'll is not being able to connect to the database on NT.
Sqlnet. Authentication_services= (NTS)
NAMES. Directory_path= (TNSNames)
Tcp.validnode_checking=yes
Tcp.invited_nodes= (9.123.112.34)
Tcp.excluded_nodes= (9.123.112.16)
The meaning of the first line: Turn on the IP limit function;
The second line means: Allow access to the database IP address list, multiple IP addresses separated by commas, in this case we write to the database server IP address;
Meaning of the third line: Prohibit access to the database IP address list, multiple IP addresses separated by commas, here we write to limit the IP address 9.123.112.16.
5. Restart the server-side listener to take effect (this can also be achieved by Lsnrctl reload mode):
C:\Documents and Settings\administrator>lsnrctl stop
1) What really works in 9i is the Sqlnet.ora file, we modify Sqlnet.ora is actually the best and quickest way.
Add the following section to the Soracle\product\10.2.0\db_1\network\admin\qlnet.ora
Tcp.validnode_checking=yes
#允许访问的IP
Tcp.invited_nodes= (Ip1,ip2 ...)
#禁止访问的IP
Tcp.excluded_nodes= (Ip1,ip2 ...)
Then restart the Listener
Places to be aware of:
1, Tcp.invited_nodes and tcp.excluded_nodes are present, to Tcp.invited_nodes-based
2, must permit or do not prohibit the server native IP address, otherwise through LSNRCTL will not be able to start or stop listening, because the process listener through the local IP access listener, and the IP is forbidden, but through the service startup or shutdown does not affect.
3, after the modification, it is necessary to restart the monitoring to take effect, and do not need to restart the database
4, any platform can be, but only applicable to the TCP/IP protocol
(2) The second method uses a trigger to implement
1, this trigger realizes the 192.168.137 start IP cannot access the function of test user
Create or Replace Trigger CHK_IP
After logon on Test.schema
Declare
IPAddr VARCHAR2 (30);
Begin
Select Sys_context (' Userenv ', ' ip_address ') into the ipaddr from dual;
If ipaddr like (' 192.168.137.% ') then
Raise_application_error (' -20001 ', ' can not be logon by test ');
End If;
End;
/
By setting the oracle9i parameter file, you can control the IP address of the access computer.
Added to the configuration file $oracle_home/network/sqlnet.ora on 172.28.65.13 this machine:
#开启对ip地址的检查
Tcp.validnode_checking=yes
#允许访问的ip
Tcp.invited_nodes= (172.28.65.13)
#禁止访问的ip
Ip.excluded_nodes= (172.27.65.15)
Restart the monitor!
$ lsnrctl Reload
Lsnrctl for solaris:version 9.2.0.4.0-production on 14-dec-2005 16:59:19
Copyright (c) 1991, 2002, 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 the 172.28.65.15 machine:
Here you can add a new service (DSF):
DSF =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (host= 172.28.65.13) (Port = 1521))
(Connect_data = (SID = ORCL))
)
Perform tnsping tests on 15:
$ 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/[email protected]
Sql*plus:release 9.2.0.4.0-production on Wed Dec 14 17:04:24 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
ERROR:
Ora-12537:tns:connection closed
--------------------------------------------------------------------------------------------------------------- ----
Tcp. Validnode_checking, this parameter must be set, the value must also be yes, otherwise it is not enabled
Tcp. Validnode_checking=yes
White list setting parameters, this address list must contain the address of the machine, or the listener may start to fail
Tcp. Invited_nodes= (10.10.2.100,10.10.2.101)
Setting parameters for the blacklist:
Tcp. Excluded_nodes= (10.10.1.100)

This article is from the "11300506" blog, please be sure to keep this source http://11310506.blog.51cto.com/11300506/1971313

Restrict specified machine IP access to Oracle database