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
|
No comments:
Post a Comment