Restricting database access to IP address using sqlnet.ora

Oracle

                  FROM MY CLIENT MACHINE:

C:\Windows\System32>tnsping pdborcl1

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 – Production on 07-MAR-2016 15:32:20

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:
C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.215.XX4.XX)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdbo
cl1)))
OK (0 msec)


NOW I want my DB to be only be accessible from this machine and restrict all other. For this I will need to make changed to my DB server sqlnet.ora :


ON DB SERVER:

[email protected] admin]$ cat sqlnet.ora

sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora

Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

tcp.validnode_checking = yes ## I have just added this line to test if it works

[[email protected] admin]$ vi sqlnet.ora
[[email protected] admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 07-MAR-2016 15:20:03

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
TNS-00584: Valid node checking configuration error
TNS-12560: TNS:protocol adapter error

As expected it doesn’t work as listener needs to know where to accept or deny connections from.
Now I tweak the sqlnet.ora file to below

[[email protected] admin]$ cat sqlnet.ora

sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora

Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

tcp.validnode_checking = yes
tcp.invited_nodes = (Linux03) ## I only want to accept connections from my localhost i.e Linux03

RESTART THE LISTENER NOW.

[[email protected] admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 07-MAR-2016 15:21:06

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
The command completed successfully

(OR)

[[email protected] admin]$ lsnrctl stop

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 07-MAR-2016 15:28:36

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
The command completed successfully
[[email protected] admin]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 07-MAR-2016 15:28:41

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 12.1.0.2.0 – Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux03)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))

STATUS of the LISTENER

Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production
Start Date 07-MAR-2016 15:28:41
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux03)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully


NOW I TRY TO CONNECT FROM CLIENT AGAIN :


C:\Windows\System32>tnsping pdborcl1

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 – Production on 07-MAR-2016 15:32:57

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:
C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.215.XX4.XX)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdbo
cl1)))
TNS-12537: TNS:connection closed

C:\Windows\System32>

Nope it doesn’t allow me to connect.


Lets try to remove the tcp.validnode_checking & tcp.invited_nodes and see


[[email protected] admin]$ cat sqlnet.ora

sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora

Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

[[email protected] admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 07-MAR-2016 15:31:58

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
The command completed successfully
[[email protected] admin]$ vi sqlnet.ora
[[email protected] admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 07-MAR-2016 15:32:16

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
The command completed successfully

C:\Windows\System32>tnsping pdborcl1

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 – Production on 07-MAR-2016 15:32:36

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:
C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.215.XX4.XX)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdbo
cl1)))
OK (0 msec)


SUCCESS YOU HAVE SUCCESFULLY IMPLEMENTED ANOTHER LAYER OF SECURITY ON DB SERVER


tcp.validnode_checking = yes
tcp.invited_nodes = (Linux03,HOSTNAME1,HOSTNAME2 …)

YOU CAN ALSO EXPLICITLY EXCLUDE NODE LIKE THIS :

tcp.excluded_nodes = (192.168.100.101,HOSTNAME2,)

But : If you only specify invited nodes with tcp.invited_nodes, all other nodes will be excluded, so there is really no reason to do both. The same is true for excluded nodes. If you put tcp.excluded_nodes = (192.168.100.101,192.168.100.160) then IP containing 192.168.100.101 and 192.168.100.160 will be excluded/denied to connect to database as a database user while allowing others to connect.