Implement Oracle Database Firewall using Valid Node Checking

Introduction

One of the Oracle Database Listener features for protecting your Oracle databases from malicious attacks from unauthorized locations is by implementing the Valid Node Checking (VNC) feature. Through this feature, access to the database can be restricted based on the IP address (or host name) of the client machine attempting to connect to the database. The Oracle database Listener validates the IP address (or host name) of the incoming client machine against the “allow” or “deny” rules specified in sqlnet.ora file before opening up a connection to the database.

Although this is not a new feature and has been available since Oracle8, it has not garnered traction within the Oracle database community mainly due to the following reasons:

(1) Many organizations do not see a need for additional security controls such as database Valid Node Checking. Since today’s enterprise databases are behind the network firewalls, they reckon that the security risk to the databases is low. Although this may be true in some cases, we recommend that the defense-in-depth methodology be adopted to protect today’s mission critical databases. Without Database-based Valid Node Checking, anyone with network connectivity could attempt to connect to the database. With this feature enabled, only pre-authorized IP addresses would be permitted to connect to the database, reducing the scope of internal threats as well as attack surfaces/vectors from which malicious attacks can be launched.

(2) Many organizations assume that they may have a large number of IP addresses that should be allowed to connect to the database, rendering VNC feature difficult to implement and cumbersome to manage. This is true for the client-server computing model which has become outdated. Instead most of today’s databases are accessed through a small but finite number of middle-tier application servers. Oracle DBAs need to specify IP addresses of these application servers as allowed nodes in tcp.invited_nodes. In addition, you will need to enter IP addresses of administrative and operational team members’ machines so that they can connect directly to the database through SQL*Plus, TOAD, SQL*Developer or other tools.

(3) There is a widely-held notion that each and every IP address must be explicitly specified and that wild cards are not allowed, making the IP address management process quite complex and difficult to manage. That is no longer true. Beginning with Oracle11g R2, allow and deny rules, which are specified via tcp.invited_nodes and tcp.excluded_nodes parameters, can include wild cards, greatly simplifying implementation of this feature. In addition, CIDR notations are allowed as well.

Implementation

Valid Node Checking feature is enabled by specifying the tcp.validnode_checking parameter in sqlnet.ora. Once enabled, you can configure allow rules through tcp.invited_nodes or deny rules through tcp.excluded_nodes in sqlnet.ora file as shown below:

tcp.validnode_checking=yes
tcp.invited_nodes=(localhost,192.168.10.101,192.168.10.102)

With the above configuration, the listener will accept connections from localhost, 192.168.10.101 and 192.168.10.102. Please note that if you don’t include “localhost” or IP addresses of the local host, Oracle listener will not start and you won’t be able to connect to the database at all. All other nodes will get the following error:

C:\Users\jmehta>sqlplus scott/tiger@testdb

SQL*Plus: Release 11.2.0.4.0 Production on Mon May 16 18:15:34 2016

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

ERROR: ORA-12537: TNS:connection closed

 The listener log will show the following error:

12-MAY-2016 21:32:18 * service_update * CDB2 * 0

Incoming connection from 10.0.2.2 rejected

12-MAY-2016 21:32:38 * 12546

TNS-12546: TNS:permission denied

 TNS-12560: TNS:protocol adapter error

  TNS-00516: Permission denied

You can also specify tcp.excluded_nodes to deny access to the specific nodes, but there is no reason to do so. Note that if you only specify invited nodes with tcp.invited_nodes, all other nodes will be excluded anyway. If both are present, then tcp.invited_nodes takes precedence over tcp.excluded_nodes parameter.

These parameters can use wildcards for IPv4 addresses and CIDR notation for IPv4 and IPv6 addresses, as shown below:

tcp.invited_nodes=(192.168.10.*,192.162.*.*)

tcp.invited_nodes=(sales,hr,192.168.*,2001:DB8:200C:433B/32)

tcp.invited_nodes=(192.168.10.1/24,192.168.20.1/24)

Obtain IP Addresses through Database Auditing

Although VNC implementation appears to be quite simple, it may not be in real-life. The challenge is to obtain and identify all IP addresses that should be allowed to connect to the database. If you miss an IP or two, then those users (including application servers, remote databases, devices, etc.) won’t be able to connect to the database, causing unwarranted issues and/or outages.

Instead of requesting all the users to supply their IP addresses, you should enable auditing on logon and then query Oracle audit trail to obtain a complete list of IP addresses that have ever connected to the database. You need to make sure that the audit trail covers sufficient period of time so that all the users, including those who connect infrequently, are also included in the audit trail.

As shown below, enable logon auditing in pre-11g databases or in 12c with conventional mode auditing. You need to wait for sufficient time to have audit trail populated with connection information from all users and then execute the following query.

audit create session ;

select distinct substr(comment_text,instr(comment_text,’HOST=’)+5,

instr(comment_text,’PORT=’)-instr(comment_text,’HOST=’)-7)

from dba_audit_trail

where action = 100

order by 1;

If you are running in 12c unified auditing mode, then create an audit policy and query UNIFIED_AUDIT_TRAIL table to get a complete list of all IP addresses over the given period of time:

create audit policy ora_logon

actions logon;

audit policy ora_logon;

select distinct substr(authentication_type,instr(authentication_type,’host=’)+5,

instr(authentication_type,’PORT=’)-instr(authentication_type,’host=’)-7)

from unified_audit_trail

order by 1;

Ensure that IP Addresses are Correctly Specified

The best approach to ensure that you have specified tcp.invited_nodes correctly and it works as intended is through listener tracing at ADMIN or DEBUG level. Enable listener tracing by setting TRACE_LEVEL_<listener_name> = ADMIN in listener.ora which would generate trace of Oracle Listener’s actions and you would be able to see the IP addresses being loaded into Valid Node Checking table.

Gateway Server for Large User Base

If you have a very large number of database users requiring direct connection to the database, then IP address management task could be complex. An ideal solution to remediate this issue would be to configure a gateway server such as Citrix with requisite software (SQL*Plus, TOAD, SQL*Developer, other tools etc.) that would be used to access the database. You add the IP address of the Citrix server to tcp.invited_nodes to allow access from the Citrix server. All users should be asked to connect to the Citrix to connect to the database. With this approach, the security is further bolstered as only path to the database would be from the Citrix server. A gateway server not only resolves IP address management issue, but it can also help implement additional security controls which would prevent data dump, printing, copying, etc.

Special Considerations

It’s important to note that the listener must be stopped and started (not reloaded through lsnrctl reload) for valid node checking to become active. Tcp.invited_nodes and tcp.exlcuded_nodes parameters are read only during the listener startup. This means that the IP addresses and hostnames cannot be dynamically added or removed. Although listener restart (stop and start) usually takes less than few seconds, your database is essentially down for the incoming database connections for this small duration. You need to consider its impact on your high availability requirements. Please note that the listener can be restarted without impacting existing database connection

All hostnames specified in tcp.invited_nodes or tcp.excluded_nodes must be resolvable. Otherwise, Oracle Listener fails to start, aborting with “TNS-00584: Valid node checking configuration error” message. When enabled by setting TRACE_LEVEL_LISTENER=admin, Listener tracing records “ntvllt:Problem in Loading tcp.invited_nodes” as error message.

However, the good news is that unresolvable IP addresses don’t cause any issues during the listener startup. If listener tracing is enabled, the trace file does show that all IP addresses and hostnames are processed by the listener and populated into Valid Node table appropriately.

Oracle support note says that all IP addresses or host names must be specified on one line in sqlnet.ora file, but I was able to specify multiple IP addresses on multiple lines. In fact, I specified only one IP address on each line, and it worked fine in Oracle 12c (12.1.0.2). I do know that it didn’t work in previous releases so do test your configuration against the Oracle versions/releases that you are running to check whether it works or not.

Oracle does seem to perform basic validation on the hostnames and IP addresses specified in tcp.invited_nodes and tcp.excluded_nodes parameters, but documentation doesn’t elaborate on the level of validations being performed, causing misunderstanding, confusion and unnecessary issues. Oracle does seem to ignore duplicated IP addresses or host names. Two consecutive comma causes Oracle Listener to ignore the rest of the entries. Some invalid IP addresses are accepted but some are not. To make matter worse, Oracle Listener quite often simply ignores the incorrect syntax or specifications without warning which results in unexpected behaviors and outcomes.

Please note that VNC feature may not protect you against advanced attacks, involving IP address and hostname spoofing. Other options available to implement capabilities similar to VNC are server-based firewall and Oracle Connection Manager. You should consider pros and cons of each approach and choose an option that best meets your needs.

Conclusion

Oracle’s VNC feature is an interesting feature akin to a basic firewall. With support for wild cards and access through middle-tier application servers, IP address management might not be as complex as initially thought. As pointed out in this article, although there are few special considerations you need to be aware of, VNC is still a powerful security control that should be considered as part of defense-in-depth methodology and overall security posture.

References

Oracle 11g R2 Net Services Reference: https://docs.oracle.com/cd/E11882_01/network.112/e10835/sqlnet.htm#NETRF006

Oracle 12c Net Services Reference: https://docs.oracle.com/database/121/NETRF/sqlnet.htm#NETRF237

[su_box title=”About Jay Mehta” style=”noise” box_color=”#336588″][short_info id=’69491′ desc=”true” all=”false”][/su_box]