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.
Now. 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. Through the listener's
Limits to achieve lightweight access, which 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 <br/> TCP. invited_nodes = (hostname1, hostname2, IP1, ip2) <br/> TCP. excluded_nodes = (10.103.11.17, hostname1, hostname2) <br/>

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. <br/> C: \> tnsping demo92 </P> <p> TNS Ping utility for 32-bit windows: version 11.2.0.1.0-production on 25-jun-2011 18:55:39 <br/> copyright (c) 1997,201 0, Oracle. all rights reserved. <br/> used parameter files: <br/> D: \ app \ Robinson \ oracle_client \ product \ 11.2.0 \ client_1 \ Network \ admin \ sqlnet. ora </P> <p> used tnsnames adapter to resolve the alias <br/> attempting to contact (description = (address_list = (address = (Protocol = TCP) (host = 10.103.11.20 <br/> 9) (Port = 1521) (CONNECT_DATA = (Server = dedicated) (SERVICE_NAME = demo92 ))) <br/> OK (0 msec) </P> <p> --> View the configuration file <br/> [Oracle @ test admin] $ more sqlnet. ora <br/> # sqlnet. ora network configuration file:/Oracle/92/Network/admin/sqlnet. ora <br/> # generated by Oracle configuration tools. </P> <p> names. directory_path = (onames, tnsnames, hostname) </P> <p> # added by Robinson <br/> TCP. validnode_checking = Yes <br/> TCP. excluded_nodes = (10.103.11.17) </P> <p> --> reload <br/> [Oracle @ test admin] $ lsnrctl reload listener_demo92 </P> <p> LSNRCTL for Linux: version 9.2.0.8.0-production on 26-jun-2011 10:03:11 </P> <p> copyright (c) 1991,200 6, Oracle Corporation. all rights reserved. </P> <p> connecting to (description = (address = (Protocol = TCP) (host = test) (Port = 1521 ))) <br/> the command completed successfully </P> <p> --> TNS-12547 error received when tnsping again <br/> C: \> tnsping demo92 </P> <p> TNS Ping utility for 32-bit windows: Version 11.2.0.1.0-production on 25-jun-2011 19:01:21 <br/> copyright (c) 1997,201 0, Oracle. all rights reserved. <br/> used parameter files: <br/> D: \ app \ Robinson \ oracle_client \ product \ 11.2.0 \ client_1 \ Network \ admin \ sqlnet. ora </P> <p> used tnsnames adapter to resolve the alias <br/> attempting to contact (description = (address_list = (address = (Protocol = TCP) (host = 10.103.11.20 <br/> 9) (Port = 1521) (CONNECT_DATA = (Server = dedicated) (SERVICE_NAME = demo92) <br/> TNS-12547: TNS: lost contact </P> <p> --> In the following example, if both excluded_nodes and invited_nodes exist, invited_nodes takes precedence, no longer demo <br/> [Oracle @ test admin] $ more sqlnet. ora <br/> # sqlnet. ora network configuration file:/Oracle/92/Network/admin/sqlnet. ora <br/> # generated by Oracle configuration tools. </P> <p> names. directory_path = (onames, tnsnames, hostname) </P> <p> # added by Robinson <br/> TCP. validnode_checking = Yes <br/> TCP. excluded_nodes = (10.103.11.17) <br/> TCP. invited_nodes = (10.103.11.17) <br/>

4. Use a trigger to restrict a single user or IP segment

--> Restrict single-user logon from a single IP address. The following limits Scott user logon from a client <br/> Create or replace trigger disablelogin <br/> after logon on Scott. schema --> note that the usage is username. schema <br/> declare <br/> ipaddr varchar2 (30); <br/> begin <br/> select sys_context ('userenv', 'IP _ address ') <br/> into ipaddr <br/> from dual; <br/> If ipaddr = '10. 103.11.17 'then <br/> raise_application_error ('-20001', <br/> 'you can not login, please contact administrator '); <br/> end if; <br/> end disablelogin; <br/>/</P> <p> --> restrict IP segment logon <br/> Create or replace trigger chk_ip_range <br/> after logon on Scott. schema <br/> declare <br/> ipaddr varchar2 (30); <br/> begin <br/> select sys_context ('userenv', 'IP _ address ') <br/> into ipaddr <br/> from dual; <br/> If ipaddr like ('10. 103.11.% ') Then <br/> raise_application_error ('-20001 ', <br/> 'you can not login, please contact administrator'); <br/> end if; <br/> end chk_ip_range; <br/>/<br/>

5. More references

Http://psoug.org/reference/net_services.html

Http://forums.oracle.com/forums/thread.jspa? Messageid = 4566449

6. Quick Reference

For performance optimization, see

Oracle hard parsing and soft Parsing

Sharedpool Tuning)

Buffercache adjustment and optimization (1)

Use of Oracle table cache (cachingtable)

 

For the Oracle architecture, see

Oracle tablespace and data files

Oracle Password File

Oracle parameter file

Oracle online redo log file)

Oracle Control File)

Oracle archiving logs

Oracle rollback and undo)

Oracle database instance startup and Shutdown Process

Automated Management of Oracle10g SGA

Oracle instances and Oracle databases (Oracle Architecture)

 

For more information about the flash back feature, see

Flashback Database)

Flashback drop & recyclebin)

Oracle flash back features (flashback query, flashbacktable)

Oracle flash back feature (flashback version, flashback transaction)

 

For more information about user-managed backup and recovery, see

Oracle cold backup

Oracle Hot Backup

Concept of Oracle backup recovery

Oracle instance recovery

Oracle recovery based on user management (describes media recovery and processing in detail)

System tablespace management and Backup Recovery

Sysaux tablespace management and recovery

 

For information on RMAN backup recovery and management, see

RMAN overview and architecture

RMAN configuration, Monitoring and Management

Detailed description of RMAN backup

RMAN restoration and recovery

Create and use rmancatalog

Create RMAN storage script based on catalog

Catalog-based RMAN backup and recovery

Use RMAN to migrate a file system database to ASM

RMAN backup path confusion (when using plus archivelog)

 

For Oracle faults, see

Error Handling for ORA-32004

ORA-01658 error.

CRS-0215 error handling

ORA-00119, ORA-00132 error handling

Another spfile setting error causes the database to fail to start.

Misunderstanding and setting of the parameter fast_start_mttr_target = 0

Spfile error causing database startup failure (ORA-01565)

 

For more information about ASM, see

Create an ASM instance and an ASM Database

Management of ASM disks and directories

Use asmcmd to manage the ASM directory and files

 

For more information about SQL and PLSQL, see

Common sqlplus commands

Replace variables with SQL * Plus Environment Settings

SQL plus paging using uniread

SQL Basics--> SELECT query

SQL Basics--> Use of new_value

SQL Basics--> Set operation (Union and Union all)

SQL Basics--> Common functions

SQL Basics--> View (createview)

SQL Basics--> Create and manage tables

SQL Basics--> Multi-Table query

SQL Basics--> Filtering and sorting

SQL Basics--> Subquery

SQL Basics--> Grouping and grouping Functions

SQL Basics--> Hierarchical query (startby... connect by prior)

SQL Basics--> Rollup and cube operators implement data aggregation

PL/SQL--> Cursor

PL/SQL--> Exception Handling)

PL/SQL--> Language basics

PL/SQL--> Process Control

PL/SQL--> PL/SQL records

PL/SQL--> Create and manage packages

PL/SQL--> Implicit cursor (SQL % found)

PL/SQL--> Package overloading and initialization

PL/SQL--> Use of dbms_ddl package

PL/SQL--> DML triggers

PL/SQL--> Instead of trigger

PL/SQL--> Stored Procedure

PL/SQL--> Function

PL/SQL--> Dynamic SQL

PL/SQL--> Common Errors of dynamic SQL

 

Other Oracle features

Common Oracle directory structure (10 Gb)

Use OEM, SQL * Plus, and iSQL * Plus to manage Oracle instances

Logging mode (logging, force logging, nologging)

Logging and nologging on table and index segments

Oralceomf Functions

Oracle users, object permissions, and system Permissions

Oracle role and configuration file

Oracle Partition Table

Oracle External table

Use external tables to manage Oracle alarm logs (alaert _ $ Sid. Log)

Cluster table and cluster Table Management (index clustered tables)

Use of Data Pump expdp export tool

Use of Data Pump impdp import tool

Import and Export Oracle Partition Table Data

SQL * loader usage

Enable User Process Tracking

Configure dynamic service registration for non-default ports

Configure the Oracle client to connect to the database

Difference between systemsys and sysoper sysdba

Oracle_sid, db_name, instance_name, db_domian, global_name

Complete Oracle patches (Oracle 9i 10g 11g path)

Upgrade oracle10.2.0.1 to 10.2.0.4

Oracle kill session