Restrictions on IP address access on the Oracle database and precautions

Source: Internet
Author: User
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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.