Table of contents
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:
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)
