Call: +44 (0)1904 557620 Call
Blog

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

[Previous entry: "Add A SQL*Net Security Banner And Audit Notice"] [Next entry: "Be Careful of What You Include In SQL*Net Security Banners"]

Oracles Free TNS Firewall - VALIDNODE_CHECKING

I said in a post a couple of days ago that my overall plan to secure an Oracle database; actually my plan is to secure the data in an Oracle database not blindly just secure Oracle. We must focus on securing data first and last and everywhere in-between.

As I said a few days ago the first step is to stop people connecting to the database who should not be allowed to connect. We must limit the actual people who are allowed to directly connect to the database to just and only just those users who need to. no more. Once these users / people are identified then we can further limit how they can connect (i.e. what tools are allowed) and then further strengthen them with strong passwords and least rights; i.e. only have exactly the right privileges to do their job and no more. This is easy to say but in practice hard to do for many many reasons.

Finally after we control the users and their rights we can then think about data security controls including permissions on tables/views etc and even context based security such as VPD, OLS, DV Realms or hand coded solutions with views and triggers all based on factors such as user id, time, where, when, what etc.

Valid node checking is Oracles free simple TNS firewall that exists in the listener. I have been advising clients and others at talks and presentations and training to use this technology for years. Its free and simple but a little brute force - I.e. it works at the IP level and port (because it works on TNS its tied to the current listener port). It would be better if there was a little more flexibility maybe down to the tool level/ user/ ?? . We can do that level with a login trigger though so all is not lost.

OK, lets test valid node checking. First go to the Linux box and go to the $ORACLE_HOME/network/admin and open the sqlnet.ora file and turn on valid node checking by setting TCP.VALIDNODE_CHECKING=yes and then create an invited nodes list - a white list of IP addresses or Hostnames. This can be done with the TCP.INVITED_NODES parameter. See my box as follows to see that I have added the IP Address of the database server only at this point:

[oracle@oel1124 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /u01/app/oracle

TCP.VALIDNODE_CHECKING=yes
TCP.INVITED_NODES=(192.168.56.85)
[oracle@oel1124 admin]$

Now try and connect remotely from a client PC using SQL*Plus:

C:\_aa\PB\bin>sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 31 09:38:01 2020

Copyright (c) 1982, 2013, Oracle. All rights reserved.

SQL> connect system/oracle1@//192.168.56.85:1521/bfora.localdomain
Connected.
SQL>

This clearly doesn't work as I should not have been able to connect from any IP Address except the database server. Let us find out my IP Address first:

C:\_aa\PB\bin>ipconfig

Windows IP Configuration


Ethernet adapter Ethernet 3:

Connection-specific DNS Suffix . :
Link-local IPv6 Address . . . . . : fe80::a11c:3e6e:4d67:b94a%8
IPv4 Address. . . . . . . . . . . : 192.168.56.1
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . :

Ethernet adapter Ethernet 4:

Connection-specific DNS Suffix . :
Link-local IPv6 Address . . . . . : fe80::45f6:ee3f:46b4:bd8f%14
Autoconfiguration IPv4 Address. . : 169.254.189.143
Subnet Mask . . . . . . . . . . . : 255.255.0.0
Default Gateway . . . . . . . . . :

Wireless LAN adapter Local Area Connection* 1:

Media State . . . . . . . . . . . : Media disconnected
Connection-specific DNS Suffix . :

Wireless LAN adapter Local Area Connection* 2:

Media State . . . . . . . . . . . : Media disconnected
Connection-specific DNS Suffix . :

Wireless LAN adapter Wi-Fi:

Connection-specific DNS Suffix . :
Link-local IPv6 Address . . . . . : fe80::8554:bf8f:3b91:e321%13
IPv4 Address. . . . . . . . . . . : 192.168.1.96
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 192.168.1.1

C:\_aa\PB\bin>

We have 192.168.56.1 - this is the gateway for Virtual box. so we should not be able to connect as that IP Address is not in the valid node checking invited nodes list. Restart the listener and re-register it:

LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
LSNRCTL> start
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

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

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 31-MAR-2020 09:37:13
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/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oel1124/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel1124.localdomain)(PORT=1521)))
The listener supports no services
The command completed successfully
LSNRCTL> exit
[oracle@oel1124 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 31 09:37:18 2020

Copyright (c) 1982, 2013, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL> alter system register;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
[oracle@oel1124 admin]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 31-MAR-2020 09:37:28

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status
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 31-MAR-2020 09:37:13
Uptime 0 days 0 hr. 0 min. 16 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oel1124/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel1124.localdomain)(PORT=1521)))
Services Summary...
Service "bfora.localdomain" has 1 instance(s).
Instance "bfora", status READY, has 1 handler(s) for this service...
Service "bforaXDB.localdomain" has 1 instance(s).
Instance "bfora", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>

Now try and connect remotely again from 192.168.56.1 and see what happens:

C:\_aa\PB\bin>sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 31 09:41:13 2020

Copyright (c) 1982, 2013, Oracle. All rights reserved.

SQL> connect system/oracle1@//192.168.56.85:1521/bfora.localdomain
ERROR:
ORA-12547: TNS:lost contact


SQL>

Now the connection is prevented so we have proved that it works. Go in now and change the sqlnet.ora again to include my IP Address so that I can connect to the database from my SQL*Plus client but no one else can:

[oracle@oel1124 admin]$ vi sqlnet.ora
[oracle@oel1124 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /u01/app/oracle

TCP.VALIDNODE_CHECKING=yes
TCP.INVITED_NODES=(192.168.56.85,192.168.56.1)
[oracle@oel1124 admin]$

Now restart the listener again:

[oracle@oel1124 admin]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 31-MAR-2020 09:39:39

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
LSNRCTL> start
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

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

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 31-MAR-2020 09:39:46
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/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oel1124/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel1124.localdomain)(PORT=1521)))
The listener supports no services
The command completed successfully
LSNRCTL> exit
[oracle@oel1124 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 31 09:39:54 2020

Copyright (c) 1982, 2013, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL> alter system register;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
[oracle@oel1124 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 31-MAR-2020 09:40:04

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 31-MAR-2020 09:39:46
Uptime 0 days 0 hr. 0 min. 18 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oel1124/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel1124.localdomain)(PORT=1521)))
Services Summary...
Service "bfora.localdomain" has 1 instance(s).
Instance "bfora", status READY, has 1 handler(s) for this service...
Service "bforaXDB.localdomain" has 1 instance(s).
Instance "bfora", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oel1124 admin]$

Now try the remote connection using SQL*plus from my client PC:

SQL> connect system/oracle1@//192.168.56.85:1521/bfora.localdomain
Connected.
SQL>

Of course it works now. There are two points here. Yesterday in the post "Add A SQL*Net Security Banner And Audit Notice" I showed that for the banner parameters SEC_USER_AUDIT_ACTION_BANNER and SEC_USER_UNAUTHORIZED_ACCESS_BANNER in the sqlnet.ora we had to re-start the database for them to take effect. But, for valid node checking we only need to restart the listener. Inconsistency is not good. The second point is that this is virtual box and my true IP Address is in the 192.168.1.* range but I access the database on virtualbox networking via the gateway 192.168.56.1. This is not ideal if clients are going to access a database on a box in a virtual box network as the gateway needed to be added. Ensure that when you use valid node checking that you do not need to allow all access via a gateway as this will defeat the object of it.

OK, hope this helps, bye from WFH