What is the access control policy of oracle sqlnet.ora 01/16 Update SLTechnology News&Howtos

What is the access control policy of oracle sqlnet.ora

2025-01-16 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Servers >

Shulou(Shulou.com)06/01 Report--

What is the access control policy of oracle sqlnet.ora? in order to solve this problem, this article introduces the corresponding analysis and answer in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

The following parameters are set in sqlnet.ora to restrict or allow users to connect to the database from a specific client.

Tcp.validnode_checking=yes | no

Tcp.invited_nodes= (ip | hostname,...)

Tcp.excluded_nodes= (ip | hostname,...)

# # for hostname, you need to configure the corresponding ip in / etc/hosts

The tcp.validnode_checking parameter determines whether to check the client IP address

The tcp.invited_nodes parameter lists the IP addresses of clients that are allowed to connect

The tcp.excluded_nodes parameter enumerates the IP addresses of clients that are not allowed to connect.

Points to pay attention to:

1. Both tcp.invited_nodes and tcp.excluded_nodes exist, mainly tcp.invited_nodes.

2. Be sure to license or not disable the native IP address of the server, otherwise you will not be able to start or stop listening through lsnrctl, because the process listener will access the listener through the local IP, and the IP is prohibited, but starting or shutting down through the service does not affect it.

3. After the revision, there are two situations.

If you are using sqlnet.ora files for the first time, you need to restart the database.

If you have previously used sqlnet.ora, you do not need to restart the database, just reload snooping!

4. Any platform is fine, but it is only applicable to TCP/IP protocol

The following experiments are done to test access control:

Environment:,

Database: yangdb hostname: rac3 ip 10.250.7.241

Hostname: rac1 ip 10.250.7.225

Set the sqlnet.ora on the yangdb and access it on the rac1 server!

Scenario 1: modify the file without starting monitoring

Oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin > vi sqlnet.ora

Tcp.validnode_checking=yes

# ip allowed to be accessed

Tcp.invited_nodes = (10.250.7.241pm 10.250.7.225)

# ip that is not allowed to access

# tcp.excluded_nodes= (ip1,ip2, … X...)

Access on the rac1 side to display TNS-12547: TNS:lost contact

Oracle@rac1:/opt/rac/oracle/11.2.0/dbs/network/admin > tnsping yangdb

TNS Ping Utility for Linux: Version 11.2.0.1.0-Production on 27-SEP-2011 21:50:35

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.250.7.241) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = yangdb))

TNS-12547: TNS:lost contact

Oracle@rac1:/opt/rac/oracle/11.2.0/dbs/network/admin > tnsping yangdb

TNS Ping Utility for Linux: Version 11.2.0.1.0-Production on 27-SEP-2011 21:53:58

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.250.7.241) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = yangdb))

TNS-12547: TNS:lost contact

Oracle@rac1:/opt/rac/oracle/11.2.0/dbs/network/admin > tnsping yangdb

TNS Ping Utility for Linux: Version 11.2.0.1.0-Production on 27-SEP-2011 21:54:49

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.250.7.241) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = yangdb))

TNS-12537: TNS:connection closed~

Issue the reload command on rac3:

Oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin > lsnrctl reload

LSNRCTL for Linux: Version 11.2.0.1.0-Production on 27-SEP-2011 21:55:05

Connecting to (ADDRESS= (PROTOCOL=tcp) (HOST=) (PORT=1521))

The command completed successfully

If you visit yangdb again, you can visit

Create a table on yangdb

YANG@yangdb-rac3 > create table yang1 as select * from dba_objects

Table created.

Oracle@rac1:/opt/rac/oracle/11.2.0/dbs/network/admin > tnsping yangdb

TNS Ping Utility for Linux: Version 11.2.0.1.0-Production on 27-SEP-2011 21:55:10

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.250.7.241) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = yangdb))

OK (10 msec)

Oracle@rac1:/opt/rac/oracle/11.2.0/dbs/network/admin > sqlplus yang/yang@yangdb

SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 27 21:55:17 2011

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Yang@YANGDB > select count (*) from yang1

COUNT (*)

72508

Yang@YANGDB > exit

Scenario 2: modify the sqlnet.ora file on rac3, perform reload operation, and rac1 access to the yangdb of rac3 is restricted

Oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin > vi sqlnet.ora

Tcp.validnode_checking=yes

# ip allowed to be accessed

# tcp.invited_nodes = (10.250.7.241pm 10.250.7.225)

Tcp.invited_nodes = (10.250.7.241)

# ip that is not allowed to access

# tcp.excluded_nodes= (ip1,ip2, … X...)

Oracle@rac1:/opt/rac/oracle/11.2.0/dbs/network/admin > tnsping yangdb

TNS Ping Utility for Linux: Version 11.2.0.1.0-Production on 27-SEP-2011 21:57:20

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.250.7.241) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = yangdb))

TNS-12537: TNS:connection closed

Oracle@rac1:/opt/rac/oracle/11.2.0/dbs/network/admin > tnsping yangdb

TNS Ping Utility for Linux: Version 11.2.0.1.0-Production on 27-SEP-2011 21:58:11

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.250.7.241) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = yangdb))

TNS-12547: TNS:lost contact

Scenario 3 sets tcp.invited_nodes,tcp.excluded_nodes to tcp.invited_nodes in sqlnet.ora at the same time!

Oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin > vi sqlnet.ora

Tcp.validnode_checking=yes

# ip allowed to be accessed

Tcp.invited_nodes = (10.250.7.241pm 10.250.7.225)

# tcp.invited_nodes = (10.250.7.241)

# ip that is not allowed to access

Tcp.excluded_nodes= (10.250.7.225) "sqlnet.ora" 7L, 186C has been written

Oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin >

Oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin > lsnrctl reload

LSNRCTL for Linux: Version 11.2.0.1.0-Production on 27-SEP-2011 21:58:19

Connecting to (ADDRESS= (PROTOCOL=tcp) (HOST=) (PORT=1521))

The command completed successfully

Oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin > oracle@rac1:/opt/rac/oracle/11.2.0/dbs/network/admin > tnsping yangdb

TNS Ping Utility for Linux: Version 11.2.0.1.0-Production on 27-SEP-2011 21:58:25

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.250.7.241) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = yangdb))

OK (0 msec)

This is the answer to the question about what the access control policy of oracle sqlnet.ora is. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.