Stunnel Security for Oracle

It might be useful to telnet to port 1522, as stunnel will print informative error messages to standard output in case of trouble. The most practical telnet client is likely BusyBox.

Remote connections to port 1522 might be blocked by your Linux firewall. The root user can permit them to pass to stunnel with the following:


iptables -I INPUT -p tcp --dport 1522 --syn -j ACCEPT

The TNS Listener can be instructed to restrict the origin of sessions, and it can be used to ban clear-text traffic completely by adding your IP equivalent to the following fragment of the $ORACLE_HOME/network/admin/sqlnet.ora file on the server:


TCP.INVITED_NODES=(127.0.0.1,1.2.3.4)
TCP.VALIDNODE_CHECKING=yes

Perform this modification after all testing is successful, and note that any configured clients using the TNS Listener will be shut down if and when the configuration is thus restricted.

It is likely wise to use a stunnel binary provided by Oracle Corporation, but the versions that it provides are rather old. If you can load stunnel version 5, you can omit the NO_SSL options shown above. However, the Oracle version 4 stunnel binaries are somewhat more likely to be tolerated in a critical support situation involving Oracle. On the other hand, commercial support from stunnel.org definitely prefers version 5. If support is an important factor, the experience and availability of the use of both versions will be helpful.

Special thanks to Michal Trojnara, the author of stunnel, for his helpful comments on this article and work in stunnel development. Commercial support, licensing and consulting for stunnel is available from his organization; please see this page for his latest release.

Database Client

Using the sqlplus client utility that is bundled with a local database server, a TLS session can be established through the stunnel that was previously configured on the remote server. Doing so requires a new client key that is stored in a "wallet", which is created below.

Use the following commands to configure the local sqlplus:


export ORACLE_SID=yourdb ORACLE_HOME=/home/oracle/Ora12c/db
mkdir /home/oracle/wallet
$ORACLE_HOME/bin/orapki wallet create -wallet /home/oracle/wallet \
        -pwd SECRET123 -auto_login_local
$ORACLE_HOME/bin/orapki wallet add -wallet /home/oracle/wallet \
        -pwd SECRET123 -dn "CN=%yourdb%" -keysize 2048 \
        -self_signed -validity 3650

The output of both calls to the orapki utility above should be this banner:


Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All
rights reserved.

Directives also must be placed to find the new wallet repository—add the following to your sqlnet.ora file:


$ cat $ORACLE_HOME/network/admin/sqlnet.ora

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /home/oracle/wallet)
     )
   )

SSL_CLIENT_AUTHENTICATION = FALSE

Finally, call sqlplus with a database account and a connect descriptor that invokes the TLS port at 1522 (note that the newlines within the single quotes are optional and are included here for clarity):


$ORACLE_HOME/bin/sqlplus RemoteUser@'(description=
(address=
 (protocol=tcps)
 (host=1.2.3.4)
 (port=1522)
)
(connect_data=(sid=mydb)))'

Assuming success, enter the password for your RemoteUser account, then issue an SQL command:


SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 19 13:26:56 2016

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

Enter password:
Last Successful login time: Fri Feb 19 2016 13:15:54 -06: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> SELECT COUNT(*) FROM DBA_OBJECTS;

  COUNT(*)
----------
     19633

A few points to consider:

  • Changing protocol=tcps to protocol=tcp and further modifying port=1521 above will log in with a clear-text session (if your firewall and listener allow access).

  • The host= clause above can reference a DNS hostname instead of an IP address if that is more convenient.

  • The TWO_TASK environment variable can be set with the contents within the single quotation marks above. If this is done, then sqlplus will connect silently to the remote server as if it was local.

  • The connect descriptor definition within the single quotation marks above would likely be moved into your TNSNAMES.ORA or network TNS resolution method (ldap, onames).

  • The wallet is not required on the server—this functionality is handled by stunnel. The Oracle client needs the wallet if the client's TLS implementation will be used. It is possible to configure stunnel in client mode, then dispense with wallets on both sides.

  • While the sqlplus session is active, a stunnel process will appear on the server (be cautious of NPROC or other kernel limits):

    
    # ps -ef | grep stunnel
    
    nobody   16810     1  0 13:26 ?        00:00:00 /usr/bin/stunnel
     ↪/etc/stunnel/ricardo.conf
    
    

______________________

Charles Fisher has an electrical engineering degree from the University of Iowa and works as a systems and database administrator for a Fortune 500 mining and manufacturing corporation.