In response to customer requirements, we need to impose some restrictions on the ip addresses of Oracle databases accessing the production environment, that is, only the ip addresses that pass the review can access the database, and all other ip addresses cannot access the database. In Oracle, you can set the sqlnet. ora file or use a trigger to restrict access to a specific ip address. 1. Modify the sqlnet. ora file: Step:
In response to customer requirements, we need to impose some restrictions on the ip addresses of Oracle databases accessing the production environment, that is, only the ip addresses that pass the review can access the database, and all other ip addresses cannot access the database. In Oracle, you can set the sqlnet. ora file or use a trigger to restrict access to a specific ip address. 1. Modify the sqlnet. ora file: Step:
In response to customer requirements, we need to impose some restrictions on the ip addresses of Oracle databases accessing the production environment, that is, only the ip addresses that pass the review can access the database, and all other ip addresses cannot access the database.
In Oracle, you can set the sqlnet. ora file or use a trigger to restrict access to a specific ip address.
1. Modify the sqlnet. ora file:
Steps:
A) Test the database logon status of a client before being set:
C: \ Documents ents and Settings \ ThinkPad> sqlplusapp/app @ uat17
SQL * Plus: Release 9.2.0.1.0-Production on Thu Aug 30 11:02:21 2012
Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved.
Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v $ database;
NAME
---------
PROD
SQL>
-- Indicates that the connection is successful.
B) Add:
Tcp. validnode_checking = yes
Tcp. invited_nodes = (192.168.1.61)
Tcp. excluded_nodes = (192.168.1.90)
Note:
The meaning of the first line: Enable the IP address restriction function;
The meaning of the second line: List of IP addresses allowed to access the database. Separate multiple IP addresses with commas (,). In this example, we write the IP addresses of the database server;
Meaning of Row 3: List of IP addresses that are prohibited from accessing the database. Separate multiple IP addresses with commas. Here we will write the IP address 192.168.1.90 to be restricted.
C) restart the listener on the server, or use the lsnrctl reload method to make the modification take effect in the listener.
[Oracle @ APPDB-PROD admin] $ lsnrctl reload
LSNRCTL for Linux: Version 10.2.0.4.0-Production on 30-AUG-2012 10:17:43
Copyright (c) 1991,200 7, Oracle. All rights reserved.
Connecting to (ADDRESS = (PROTOCOL = tcp) (HOST =) (PORT = 1521 ))
The command completed successfully
D) Verify the client logon status (192.168.1.90:
C: \ Documents ents and Settings \ ThinkPad> sqlplusapp/app @ uat17
SQL * Plus: Release 9.2.0.1.0-Production on Thu Aug 30 11:12:46 2012
Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved.
ERROR:
ORA-12537: TNS: connection closed
Enter user-name:
Reported: ORA-12537: TNS: connection closed error, indicating that the limit is successful.
Note:
1). the first item added above must be written. It can be used on any platform, but only applicable to TCP/IP protocol.
2) The second and third rows can write any row. If both tcp. invited_nodes and tcp. excluded_nodes both exist, tcp. invited_nodes is the main one.
3 ). be sure to permit or disable the IP address of the local server. Otherwise, lsnrctl cannot start or stop the listener, because the listener accesses the listener through the local IP address, this IP address is disabled, but it does not affect the Service Startup or shutdown. If it is rac, it is best to allow access from both the physical ip address and virtual ip address.
2. Trigger implementation:
Conn/as sysdba
Create or replace trigger check_ip
After logon on app. schema
Declare
Ipaddr VARCHAR2 (30 );
Begin
Select sys_context ('userenv', 'IP _ address') into ipaddr from dual;
If ipaddr like ('192. 168.1.90') then
Raise_application_error ('-20001', 'you can not logon by app ');
End if;
End;
/
However, this limit can only be applied to a user.
