Restricting Access/Connections to the Database

In order to restrict the connections to the database we can add some parameters in the sqlnet.ora file under ORACLE_HOME form where listener is running. This would make sure that sessions to the database are coming only from specific list of hosts, and any session from all other hosts is not allowed to connect to the database. Following are the parameters we can use for such restriction.

TCP.VALID_NODE_CHECKING = YES

This parameter is used to enable or disable the filtering of sessions.

TCP.INVITED_NODES

This parameter is use to specify the hosts that are allowed to connect with the database instance(s) running on this host. Only a comma separated list of hosts mentioned in this parameter would be allowed to connect to the database.  Wild card entries are allowed for this parameter; for example, 172.25.20.*

TCP.EXCLUDED_NODES

This parameter is used to specify the list of hosts that are not allowed to make connections with the database, all other hosts would be able to connect. This parameter TCP.INVITED_NODES takes precedence over this parameter which means that if you specify a host in both of these parameters, INVITED_NODES would take precedence and that host would be allowed to make connection. A wild card entry is allowed for this parameter; for example, 172.25.*

Following is an example of these parameters in sqlnet.ora file

TCP.VALID_NODE_CHECKING = YES TCP.INVITED_NODES = (172.25.20.29, ) TCP.EXCLUDED_NODES = (172.25.*, 172.23.22.19)

After adding these parameters you should restart/reload the listener.

TNS-12547: TNS:lost contact

After adding these parameters and restricting the access, any host that is not allowed to connect with the database instance would receive TNS-12547: TNS:lost contact.

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

I have experienced that when I set TCP.VALID_NODE_CHECKING and TCP.INVITED_NODES parameters on one of my database server, the allowed hosts started receiving ORA-12514. Then I realized that database instance is not registered with the listener after adding this parameter.

[grid@myhost]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 26-SEP-2017 23:16:51 Copyright (c) 1991, 2013, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS of the LISTENER ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date                26-SEP-2017 23:16:06 Uptime                    0 days 0 hr. 0 min. 0 sec Trace Level               off Security                  ON: Local OS Authentication SNMP                      OFF Listener Parameter File   /u01/app/11204/grid/network/admin/listener.ora Listener Log File         /u01/app/grid/diag/tnslsnr/my_host/listener/alert/log.xml Listening Endpoints Summary...   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myhost.mudomain.com)(PORT=1521))) The listener supports no services The command completed successfully

Once I added the database host name in the TCP.INVITED_NODES list, and reloaded/restarted the listener, only after that listener was able to register my database instance, and allowed hosts were able to connect with the database. It means that you should always mentioned database host name in TCP.INVITED_NODES parameter

[grid@myhost admin]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 26-SEP-2017 23:16:51 Copyright (c) 1991, 2013, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS of the LISTENER ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date                26-SEP-2017 23:16:06 Uptime                    0 days 0 hr. 0 min. 45 sec Trace Level               off Security                  ON: Local OS Authentication SNMP                      OFF Listener Parameter File   /u01/app/11204/grid/network/admin/listener.ora Listener Log File         /u01/app/grid/diag/tnslsnr/myhost/listener/alert/log.xml Listening Endpoints Summary...   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myhost.mydomain.com)(PORT=1521))) Services Summary... Service "+ASM" has 1 instance(s).   Instance "+ASM", status READY, has 1 handler(s) for this service... Service "MYDBXDB" has 1 instance(s).   Instance "mydb", status READY, has 1 handler(s) for this service... Service "MYDB" has 1 instance(s).   Instance "mydb", status READY, has 1 handler(s) for this service... Service "mydb" has 1 instance(s).   Instance "mydb", status READY, has 1 handler(s) for this service... The command completed successfully