Restricting and securing your database network

Preamble

In our never ending SOX (Sarbanes–Oxley Act) journey the need to restrict network access to our highly critical databases came onto the table. Thinking of it three different ideas came to my mind:

Use default Linux firewall to create rule to allow or disallow access to hostname network directly.
Restrict access to listener with standard sqlnet.ora parameters. Feature called valid node checking.
Use Connection Manager (CMAN) that has better administrative option versus previous method. Other added values of CMAN have poor interest when restricting database network access.

Blog post is based on Oracle release 12cR1 (12.1.0.2.0). I have a database server called server1.domain.com (192.168.56.101) and Connection Manager has been installed in a different Oracle home than the database but on same server. My two clients are server2.domain.com (192.168.56.102) and server3.domain.com (192.168.56.103). My three servers are virtual machines (Virtualbox) and are using Oracle Linux Server release 7.3.

In a second part we will see how to secure network layer to ensure no one has modified the content between your clients and your database server (man-in-the-middle attack (MITM)).

Linux firewall

Start and optionally enabled firewalld process onto your database server with:

[root@server1 ~]# systemctl start firewalld
[root@server1 ~]# systemctl enable firewalld
Created symlink from /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service to /usr/lib/systemd/system/firewalld.service.
Created symlink from /etc/systemd/system/basic.target.wants/firewalld.service to /usr/lib/systemd/system/firewalld.service.
[root@server1 ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
   Active: active (running) since Tue 2017-01-24 16:39:49 CET; 1 day 1h ago
     Docs: man:firewalld(1)
 Main PID: 13157 (firewalld)
   CGroup: /system.slice/firewalld.service
           └─13157 /usr/bin/python -Es /usr/sbin/firewalld --nofork --nopid
 
Jan 24 16:39:49 server1.domain.com systemd[1]: Starting firewalld - dynamic firewall daemon...
Jan 24 16:39:49 server1.domain.com systemd[1]: Started firewalld - dynamic firewall daemon.
[root@server1 ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: active (running) since Tue 2017-01-24 16:39:49 CET; 24h ago
     Docs: man:firewalld(1)
 Main PID: 13157 (firewalld)
   CGroup: /system.slice/firewalld.service
           └─13157 /usr/bin/python -Es /usr/sbin/firewalld --nofork --nopid
 
Jan 24 16:39:49 server1.domain.com systemd[1]: Starting firewalld - dynamic firewall daemon...
Jan 24 16:39:49 server1.domain.com systemd[1]: Started firewalld - dynamic firewall daemon.

to ease administration I strongly suggest to install Linux firewall graphical configuration tool (firewall-config), but you can still use the command line too called firewall-cmd (I will try to supply commands as much as possible):

[root@server1 ~]# yum install firewall-config

I solved the font issue:

[root@server1 ~]# firewall-config
 
(firewall-config:10381): Pango-WARNING **: failed to choose a font, expect ugly output. engine-type='PangoRenderFc', script='common'
 
(firewall-config:10381): Pango-WARNING **: failed to choose a font, expect ugly output. engine-type='PangoRenderFc', script='latin'
GLib-GIO-Message: Using the 'memory' GSettings backend.  Your settings will not be saved or shared with other applications.

By issuing:

[root@server1 ~]# yum install dejavu-fonts-common dejavu-sans-fonts.noarch dejavu-serif-fonts.noarch

Finally got the Firewall graphical interface working:

On my virtual machine I have two (three with loopback) network devices, one for internal non routable network and one for Internet access:

[root@server1 ~]# ip link
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN mode DEFAULT
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
2: enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP mode DEFAULT qlen 1000
    link/ether 08:00:27:bc:4d:c3 brd ff:ff:ff:ff:ff:ff
3: enp0s8: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP mode DEFAULT qlen 1000
    link/ether 08:00:27:20:2e:d6 brd ff:ff:ff:ff:ff:ff

By default they are all in public zone:

[root@server1 ~]# firewall-cmd --get-active-zones
public
  interfaces: enp0s3 enp0s8

Public zone is:

For use in public areas. You do not trust the other computers on the network to not harm your computer. Only selected incoming connections are accepted.

Once you have activated the firewall then your database is not accessible from outside:

[oracle@server2 ~]$ tnsping //server1.domain.com:1531/pdb1
 
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 25-JAN-2017 17:51:16
 
Copyright (c) 1997, 2014, Oracle.  All rights reserved.
 
Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
 
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=pdb1))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.101)(PORT=1531)))
TNS-12543: TNS:destination host unreachable

[oracle@server3 ~]$ tnsping //server1.domain.com:1531/pdb1
 
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 25-JAN-2017 18:05:33
 
Copyright (c) 1997, 2014, Oracle.  All rights reserved.
 
Used parameter files:
 
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=pdb1))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.101)(PORT=1531)))
TNS-12543: TNS:destination host unreachable

The example we will setup is give access from server2.domain.com and keep forbidden access from server3.domain.com. You can either do it with graphical interface or command line:

[root@server1 ~]# firewall-cmd --zone=public --add-rich-rule='rule family="ipv4" source address="192.168.56.102" destination address="192.168.56.101" port port="1531" protocol="tcp" accept'
success
[root@server1 ~]# firewall-cmd --list-rich-rules
rule family="ipv4" source address="192.168.56.102" destination address="192.168.56.101" port port="1531" protocol="tcp" accept

Remark:
You need to issue same command with –permanent option to make it permanent across restart and reboot.

From graphical interface:

Connection from server2.domain.com is now possible while connection from server3.domain.com is still forbidden:

[oracle@server2 ~]$ tnsping //server1.domain.com:1531/pdb1
 
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 30-JAN-2017 12:25:05
 
Copyright (c) 1997, 2014, Oracle.  All rights reserved.
 
Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
 
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=pdb1))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.101)(PORT=1531)))
OK (0 msec)
[oracle@server2 ~]$ sqlplus yjaquier/'secure_password'@//server1.domain.com:1531/pdb1
 
SQL*Plus: Release 12.1.0.2.0 Production on Mon Jan 30 12:25:20 2017
 
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
 
Last Successful login time: Mon Jan 30 2017 12:24:54 +01:00
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
SQL>

This method is by far the most simple and is available with very low effort. The biggest drawback is to be obliged to work with IP addresses. If you work with servers that have fixed IP addresses then no issue but if you need to allocate end users’ desktops/laptops where they most probably get their IP addresses by DHCP then it might become a nightmare…

Valid node checking

The listener.ora file I use is the one created at database installation where I have just customized the listening port to avoid 1521:

LISTENER_ORCL =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server1.domain.com)(PORT = 1531))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1531))
    )
  )

Valid node checking feature is based on three parameters that you set in sqlnet.ora file:

TCP.VALIDNODE_CHECKING: To enable and disable valid node checking for incoming connections.
TCP.EXCLUDED_NODES: To specify which clients are denied access to the database.
TCP.INVITED_NODES: To specify which clients are allowed access to the database. This list takes precedence over the TCP.EXCLUDED_NODES parameter if both lists are present.

To reproduce a complete blackout from outside I have set:

tcp.validnode_checking=yes
tcp.excluded_nodes=(*)

And reload your listener configuration file to activate the sqlnet.ora parameters:

[oracle@server1 ~]$ lsnrctl reload listener_orcl
 
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 30-JAN-2017 17:09:34
 
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server1.domain.com)(PORT=1531)))
The command completed successfully

This is first mistake not to do as the listener will not even look for SQL*Net local connection i.e. no services supported because PMON will not register the database:

[oracle@server1 ~]$ lsnrctl status listener_orcl
 
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 30-JAN-2017 17:07:05
 
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server1.domain.com)(PORT=1531)))
TNS-12547: TNS:lost contact
 TNS-12560: TNS:protocol adapter error
  TNS-00517: Lost contact
   Linux Error: 104: Connection reset by peer
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1531)))
STATUS of the LISTENER
------------------------
Alias                     listener_orcl
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                30-JAN-2017 16:52:27
Uptime                    0 days 0 hr. 14 min. 38 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/server1/listener_orcl/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server1)(PORT=1531)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1531)))
The listener supports no services
The command completed successfully

So you have to add the database server name in tcp.invited_nodes node parameters:

tcp.validnode_checking=yes
tcp.excluded_nodes=(*)
tcp.invited_nodes=(server1.domain.com)

And reload configuration again:

[oracle@server1 ~]$ lsnrctl reload listener_orcl
 
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 30-JAN-2017 17:11:10
 
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server1.domain.com)(PORT=1531)))
TNS-12547: TNS:lost contact
 TNS-12560: TNS:protocol adapter error
  TNS-00517: Lost contact
   Linux Error: 104: Connection reset by peer
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1531)))
The command completed successfully

After this services are again supported and as expected connection from my two clients is not working:

[oracle@server2 ~]$ tnsping //server1.domain.com:1531/pdb1
 
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 30-JAN-2017 17:14:25
 
Copyright (c) 1997, 2014, Oracle.  All rights reserved.
 
Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
 
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=pdb1))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.101)(PORT=1531)))
TNS-12547: TNS:lost contact

Same as before if I want to only give access to server2.domain.com I change my sqlnet.ora to (and reload listener to activate it):

tcp.validnode_checking=yes
tcp.excluded_nodes=(*)
tcp.invited_nodes=(server1.domain.com,server2.domain.com)

Now I can connect from server2.domain.com:

[oracle@server2 ~]$ tnsping //server1.domain.com:1531/pdb1
 
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 30-JAN-2017 17:21:33
 
Copyright (c) 1997, 2014, Oracle.  All rights reserved.
 
Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
 
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=pdb1))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.101)(PORT=1531)))
OK (10 msec)
[oracle@server2 ~]$ sqlplus yjaquier/'secure_password'@//server1.domain.com:1531/pdb1
 
SQL*Plus: Release 12.1.0.2.0 Production on Mon Jan 30 17:21:43 2017
 
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
 
Last Successful login time: Mon Jan 30 2017 13:00:07 +01:00
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
SQL>

While access from server3.domain.com is still forbidden (note the different TNS message versus method one):

[oracle@server3 ~]$ tnsping //server1.domain.com:1531/pdb1
 
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 30-JAN-2017 18:13:10
 
Copyright (c) 1997, 2014, Oracle.  All rights reserved.
 
Used parameter files:
 
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=pdb1))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.101)(PORT=1531)))
TNS-12547: TNS:lost contact

One drawback I have found in MOS is the listener start failure is case there is there is an invalid hostname in TCP.INVITED_NODES parameter. For famous databases in your organisation the management of all client names in a single line might also become cumbersome…

Connection Manager

Connection Manager is a proxy server (SQL proxy) that will forward connections to databases (or other proxy servers). You normally install it on a standalone server different from your database server but to ease my testing I will install it on my database server that is clearly not recommended. After few glitches (mainly due to unique sqlnet.ora file) while using same Oracle home as my Oracle database I have decided to install Connection Manager in its own separated Oracle home. To install it I have used Oracle Database 12c Release 1 Client (12.1.0.2.0) for Linux x86-64 (64-bit) zip file.

From official documentation Connection Manager main features are:

Access control: To use rule-based configuration to filter user-specified client requests and accept others.
Session multiplexing: To funnel multiple client sessions through a network connection to a shared server destination.

Installation

Custom installation:

Language:

Choosing a separate Oracle home than my Oracle database:

Taking only Connection Manager, other required parts will be automatically chosen by installer:

Summary:

Installation:

Default network configuration (I had to stop the default listener afterward):

Root.sh:

Successful installation:

Configuration

To create the first Connection Manager configuration I have used My Oracle Support (MOS) note called Connection Manager Configuration Utility (Doc ID 1435277.1). Execute the provided tool with something like (I have extracted the zip in /tmp directory):

[oracle@server1 ~]$ java -jar /tmp/CmanConfig.jar

Choose you configuration mode:

Fill in few required information (name, port, Oracle home and hostname):

Successful execution message:

This small tool generate a file called CMAN_Basic_Next_Step.rtf where you have run it. This file only contains a copy command from generated Connection Manager configuration file called /home/oracle/CMAN_Config/cman.ora (in my case):

[oracle@server1 ~]$ cat /home/oracle/CMAN_Config/cman.ora
CMAN=
 (CONFIGURATION=
  (ADDRESS=(PROTOCOL=TCP)(HOST=server1.domain.com)(port=1541))
  (RULE_LIST =
    (RULE=(SRC=*) (DST=*) (SRV=*) (ACT=ACCEPT)))
 (PARAMETER_LIST =
  (LOG_LEVEL=OFF)
  (LOG_DIRECTORY=/u01/app/oracle/product/12.1.0/dbhome_2/network/log)
  (DIAG_ADR_ENABLED=OFF)
  (TRACE_LEVEL=OFF)
  (TRACE_TIMESTAMP=ON)
  (TRACE_DIRECTORY=OFF)
  ##(TRACE_FILELEN=1024)
  ##(TRACE_FILENO=10)
  (ASO_AUTHENTICATION_FILTER=OFF)
  (CONNECTION_STATISTICS=NO)
  (IDLE_TIMEOUT=0)
  (INBOUND_CONNECT_TIMEOUT=0)
  ##(MAX_CMCTL_SESSIONS=4)
  ##(MAX_CONNECTIONS=1024)
  ##(MAX_GATEWAY_PROCESSES=4)
  ##(MIN_GATEWAY_PROCESSES=4)
  (OUTBOUND_CONNECT_TIMEOUT=0)
  (SESSION_TIMEOUT=0)
  )
 )

I have customized it and in following cman.ora example we target to listen on port 1541 and redirect connection for pdb1 service to database running on same server. For all clients whatever their IP. You can add as many rules as you wish:

CMAN=
 (CONFIGURATION=
  (ADDRESS=(PROTOCOL=TCP)(HOST=server1.domain.com)(port=1541))
  (RULE_LIST =
    (RULE=(SRC=*) (DST=server1.domain.com) (SRV=pdb1) (ACT=ACCEPT))
  )
 (PARAMETER_LIST =
  (LOG_LEVEL=OFF)
  (LOG_DIRECTORY=/u01/app/oracle/product/12.1.0/dbhome_2/network/log)
  (DIAG_ADR_ENABLED=OFF)
  (TRACE_LEVEL=OFF)
  (TRACE_TIMESTAMP=ON)
  (TRACE_DIRECTORY=OFF)
  (ASO_AUTHENTICATION_FILTER=OFF)
  (CONNECTION_STATISTICS=NO)
  (IDLE_TIMEOUT=0)
  (INBOUND_CONNECT_TIMEOUT=0)
  (OUTBOUND_CONNECT_TIMEOUT=0)
  (SESSION_TIMEOUT=0)
  )
 )

To forbid direct connection to listener you need to restrict it only to server where is running your connection manager with valid node checking feature we have seen just above:

tcp.validnode_checking=yes
tcp.excluded_nodes=(*)
tcp.invited_nodes=(server1.domain.com)

On Unix the executable to control Connection Manager is called cmctl. My first startup attempt miserably failed:

[oracle@server1 ~]$ cmctl
 
CMCTL for Linux: Version 12.1.0.2.0 - Production on 31-JAN-2017 11:37:29
 
Copyright (c) 1996, 2014, Oracle.  All rights reserved.
 
Welcome to CMCTL, type "help" for information.
 
CMCTL> administer cman
Current instance cman is not yet started
Connections refer to (ADDRESS=(PROTOCOL=TCP)(HOST=server1.domain.com)(port=1541)).
The command completed successfully.
CMCTL:cman> startup
TNS-04012: Unable to start Oracle Connection Manager instance.
CMCTL:cman>

In $ORACLE_HOME/network/log/cman_alert.log file I have found:

(LOG_RECORD=(TIMESTAMP=31-JAN-2017 11:37:51)(EVENT=CMAN.ORA contains no rule for local CMCTL connection)(Add (rule=(src=server1)(dst=127.0.0.1)(srv=cmon)(act=accept)) in rule_list)

So added it to cman.ora file:

    (RULE=(SRC=server1.domain.com) (DST=127.0.0.1) (SRV=cmon) (ACT=ACCEPT))

Second startup attempt also failed:

CMCTL:cman> startup
Starting Oracle Connection Manager instance cman. Please wait...
TNS-04013: CMCTL timed out waiting for Oracle Connection Manager to start

Replaced by what is explained in CMAN Fails to Start and Throws the Following Errors: TNS-04013 and TNS-12529 (Doc ID 1059938.1). It is related to IPv6:

    (RULE=(SRC=server1.domain.com) (DST=::1) (SRV=cmon) (ACT=ACCEPT))

Third startup attempt failed for:

CMCTL:cman> startup
TNS-04012: Unable to start Oracle Connection Manager instance.

In $ORACLE_HOME/network/log/cman_alert.log file I have found:

Error listening on: (ADDRESS=(PROTOCOL=TCP)(HOST=server1.domain.com)(port=1541))
TNS-12542: TNS:address already in use
 TNS-12560: TNS:protocol adapter error
  TNS-00512: Address already in use
   Linux Error: 98: Address already in use
)(OPN=77)(NS1=12564)(NS2=0)(NT1=0)(NT2=0))

Killed remaining processes:

[root@server1 tmp]# ps -ef | grep cman |grep -v grep
oracle   21261     1  0 11:45 ?        00:00:00 /u01/app/oracle/product/12.1.0/dbhome_2/bin/cmadmin cman -inherit
oracle   21264     1  0 11:45 ?        00:00:00 /u01/app/oracle/product/12.1.0/dbhome_2/bin/tnslsnr ifile=/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/cman.ora cman -inherit -mode proxy
oracle   21267     1  0 11:45 ?        00:00:00 /u01/app/oracle/product/12.1.0/dbhome_2/bin/cmgw cmgw0 0 16 cman SNLSM:99224000
oracle   21269     1  0 11:45 ?        00:00:00 /u01/app/oracle/product/12.1.0/dbhome_2/bin/cmgw cmgw1 1 16 cman SNLSM:99224000

And finally worked this time:

CMCTL:cman> startup
Starting Oracle Connection Manager instance cman. Please wait...
TNS-04077: WARNING: No password set for the Oracle Connection Manager instance.
CMAN for Linux: Version 12.1.0.2.0 - Production
Status of the Instance
----------------------
Instance name             cman
Version                   CMAN for Linux: Version 12.1.0.2.0 - Production
Start date                31-JAN-2017 11:56:21
Uptime                    0 days 0 hr. 0 min. 9 sec
Num of gateways started   2
Average Load level        0
Log Level                 OFF
Trace Level               OFF
Instance Config file      /u01/app/oracle/product/12.1.0/dbhome_2/network/admin/cman.ora
Instance Log directory    /u01/app/oracle/product/12.1.0/dbhome_2/network/log
Instance Trace directory  OFF
The command completed successfully.

In tnsnames.ora file of my database server Oracle home I have added (listener_orcl was already there):

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = server1.domain.com)(PORT = 1531))
 
CMAN =
  (ADDRESS = (PROTOCOL = TCP)(HOST = server1.domain.com)(PORT = 1541))

Then I register remote listener for my Oracle database:

SQL> ALTER SYSTEM SET remote_listener=cman;
 
SYSTEM altered.
 
SQL> show parameter listener
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string
local_listener                       string      LISTENER_ORCL
remote_listener                      string      CMAN
 
SQL> ALTER SYSTEM register;
 
SYSTEM altered.

Now services are well server by Connection Manager:

CMCTL:cman> show services
Services Summary...
Proxy service "cmgw" has 1 instance(s).
  Instance "cman", status READY, has 2 handler(s) for this service...
    Handler(s):
      "cmgw001" established:0 refused:0 current:0 max:256 state:ready
         <machine: ::1, pid: 26392>
         (ADDRESS=(PROTOCOL=tcp)(HOST=::1)(PORT=31192))
      "cmgw000" established:0 refused:0 current:0 max:256 state:ready
         <machine: ::1, pid: 26390>
         (ADDRESS=(PROTOCOL=tcp)(HOST=::1)(PORT=44850))
Service "cmon" has 1 instance(s).
  Instance "cman", status READY, has 1 handler(s) for this service...
    Handler(s):
      "cmon" established:1 refused:0 current:1 max:4 state:ready
         <machine: ::1, pid: 26384>
         (ADDRESS=(PROTOCOL=tcp)(HOST=::1)(PORT=64226))
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=server1)(PORT=1531))
Service "pdb1" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=server1)(PORT=1531))
The command completed successfully.

Connection are also well redirected:

[oracle@server2 ~]$ tnsping //server1.domain.com:1541/pdb1
 
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 31-JAN-2017 16:06:28
 
Copyright (c) 1997, 2014, Oracle.  All rights reserved.
 
Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
 
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=pdb1))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.101)(PORT=1541)))
OK (0 msec)

Problem is that direct access to listener is still possible. So far access is possible from any clients:

[oracle@server2 ~]$ tnsping //server1.domain.com:1531/pdb1
 
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 31-JAN-2017 16:06:30
 
Copyright (c) 1997, 2014, Oracle.  All rights reserved.
 
Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
 
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=pdb1))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.101)(PORT=1531)))
OK (0 msec)

To forbid direct access to my listener I use valid node checking feature we have seen just above and allow only connection from Connection Manager hostname, that is also server1.domain.com. And I reload listener parameters:

tcp.validnode_checking=yes
tcp.excluded_nodes=(*)
tcp.invited_nodes=(server1.domain.com)

Now from any clients direct listener connections are no more possible and I must go through Connection Manager:

[oracle@server2 ~]$ tnsping //server1.domain.com:1531/pdb1
 
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 31-JAN-2017 17:02:57
 
Copyright (c) 1997, 2014, Oracle.  All rights reserved.
 
Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
 
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=pdb1))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.101)(PORT=1531)))
TNS-12547: TNS:lost contact
[oracle@server2 ~]$ tnsping //server1.domain.com:1541/pdb1
 
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 31-JAN-2017 17:02:59
 
Copyright (c) 1997, 2014, Oracle.  All rights reserved.
 
Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
 
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=pdb1))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.101)(PORT=1541)))
OK (10 msec)

Now same as before I want to forbid access from server3.domain.com only. I change the rules part of my cman.ora file with:

  (RULE_LIST =
    (RULE=(SRC=server2.domain.com) (DST=server1.domain.com) (SRV=pdb1) (ACT=ACCEPT))
    (RULE=(SRC=server1.domain.com) (DST=::1) (SRV=cmon) (ACT=ACCEPT))
  )

You need to reload connection manager and you can display active rules with:

CMCTL:cman> reload
The command completed successfully.
CMCTL:cman> show rules
Number of filtering rules currently in effect: 2
(rule_list=
  (rule=
    (SRC=server2.domain.com)
    (DST=server1.domain.com)
    (SRV=pdb1)
    (ACT=ACCEPT)
  )
  (rule=
    (SRC=server1.domain.com)
    (DST=::1)
    (SRV=cmon)
    (ACT=ACCEPT)
  )
)
The command completed successfully.

Behavior remains unchanged for server2.domain.com while on server3.domain.com it’s working, partially, I would say. Tnsping is still answering while connection are not possible as expected:

[oracle@server3 ~]$ tnsping //server1.domain.com:1541/pdb1
 
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 31-JAN-2017 18:15:30
 
Copyright (c) 1997, 2014, Oracle.  All rights reserved.
 
Used parameter files:
 
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=pdb1))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.101)(PORT=1541)))
OK (0 msec)
[oracle@server3 ~]$ sqlplus yjaquier/'secure_password'@//server1.domain.com:1541/pdb1
 
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 31 18:15:33 2017
 
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
 
ERROR:
ORA-12529: TNS:connect request rejected based on current filtering rules

I have tried to explicitly drop or reject connection for any servers with below rule but tnsping is still positively answering:

  (rule=
    (SRC=*)
    (DST=server1.domain.com)
    (SRV=pdb1)
    (ACT=drop)
  )

If you ask yourself the possible value of ACT parameter and more precisely the difference between reject and drop it is in Oracle official documentation: accept to accept incoming requests, reject to reject incoming requests, or drop to reject incoming requests without sending an error message.

I would have expected drop option to refuse even tnsping request but it is not the case…

Restricting database network conclusion

The Linux firewall method is the most simple, only drawback is to be obliged to work with IP addresses so not really compatible with a client population and DHCP. This would be the solution you implement in the case of a database not directly accessible by end users.

The valid node checking is better in this way as you can specify hostname and client names. There is apparently no particular limitation in parameter length in case you have a long list of clients. It also accepts, even if I have not tested it, wildcards in IP addresses. The only (big) drawback is that the listener will not start if one of the hostnames or IP addresses cannot be resolved with either ping or nslookup. It might also become complex to handle in case you have a long list of clients. This would be my recommended solution in case of a simple intranet need.

Finally Connection Manager is more a solution you would use in case of a demilitarized zone (DMZ) which would drastically decrease the number of rules you need to add in your firewall. Meaning that all DMZ clients will go through Connection Manage to access to the database that could be in your intranet. Connection Manager accept wildcard in IP addresses only in the form of x.x.x.x/nn where nn represents a subnet mask that comprises nn left-most bits. This is a solution I would use in the case of an internet need or in the case of plenty of direct clients access as the management would be a little easier. And in the case of multiple clients connection multiplexing could also be implemented.

References

Using Firewalls
What is TCP_VALIDNODE_CHECKING and How to Use It (Doc ID 462933.1)
Listener Fails to Start Using TCP.VALIDNODE_CHECKING: TNS-12532 TNS:Invalid Argument (Doc ID 287500.1)
Connection Manager Configuration Utility (Doc ID 1435277.1)
CMAN Fails to Start and Throws the Following Errors: TNS-04013 and TNS-12529 (Doc ID 1059938.1)

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts

IT World

Restricting and securing your database network – part 1

Table of contents