Oracle has integrated modern Transport Layer Security (TLS) network encryption into their eponymous database product, and TLS usage no longer requires the Advanced Security option beginning with the 10.2 database release. Legacy configurations lacking TLS exchange encrypted passwords, but the session payload is transmitted cleartext and is easily intercepted by anyone with control over the intermediate network. Databases holding sensitive content should avoid cleartext traffic configurations.
It is possible to use the stunnel utility to "invisibly" wrap the Oracle Transparent Network Substrate (TNS) Listener with TLS encryption as an isolated process, and this configuration appears to be compatible both with Oracle's sqlplus command line utility, and with database links which are used for distributed transactions between multiple database instances. There are several benefits to stunnel over the TNS Listener's native TLS implementation:
Let's proceed with adding stunnel TLS services to Oracle.
It is assumed that the reader is familiar with Oracle databases, and the procedures to startup an instance and the TNS Listener. For reference, we assume that a database SID "mydb" is running, and an example listener daemon is launched on the IP address 22.214.171.124 with the following commands:
export ORACLE_SID=mydb ORACLE_HOME=~oracle/Ora12c/db $ORACLE_HOME/bin/lsnrctl start
The listener will generate a startup message similar to the output below:
LSNRCTL for Linux: Version 126.96.36.199.0 - Production on 19-FEB-2016 13:18:55 Copyright (c) 1991, 2014, Oracle. All rights reserved. Starting /home/oracle/Ora12c/db/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 188.8.131.52.0 - Production System parameter file is /home/oracle/Ora12c/db/network/admin/listener.ora Log messages written to /home/oracle/Ora12c/diag/tnslsnr/HOSTNAME/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=184.108.40.206)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=220.127.116.11)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 18.104.22.168.0 - Production Start Date 19-FEB-2016 13:18:55 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Parameter File /home/oracle/Ora12c/db/network/admin/listener.ora Listener Log File /home/oracle/Ora12c/diag/tnslsnr/HOSTNAME/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=22.214.171.124)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC))) Services Summary... Service "mydb" has 1 instance(s). Instance "mydb", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
It is important that the listener not engage in "port redirection" of clients to separate server ports (most commonly seen in MTS/Shared Server). Any feature causing the TNS Listener to engage in such activity must be disabled.
To configure stunnel, the root user must create a keypair for TLS. This keypair can be "signed" by a Certificate Authority (CA) if desired - this is conventionally useful for website encryption (https) since the lack of a recognized CA signature will trigger browser security warnings. Oracle clients can only verify server keys when signed by a recognized CA, which is addressed in the final section of this article. To obtained signed keys, follow the instructions on the stunnel website. Otherwise, for more informal use, a self-signed key can be generated with the following commands:
cd /etc/pki/tls/certs make stunnel.pem
The process of key generation will ask a number of questions:
Generating a 2048 bit RSA private key .........................................................................+++ .................................+++ writing new private key to '/tmp/openssl.hXP3gW' ----- You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [XX]:US State or Province Name (full name) :IL Locality Name (eg, city) [Default City]:Chicago Organization Name (eg, company) [Default Company Ltd]:ACME Corporation Organizational Unit Name (eg, section) :Widget Division Common Name (eg, your name or your server's hostname) :darkstar Email Address :firstname.lastname@example.org
The key produced above will be set for expiration in 365 days from the day it was created. If you would like to generate a key with a longer life, you can call openssl directly:
openssl req -new -x509 -days 3650 -nodes \ -out stunnel.pem -keyout stunnel.pem
The key will look something like this:
# cat /etc/pki/tls/certs/stunnel.pem -----BEGIN PRIVATE KEY----- MIIEvgIBADANBgkqhkiG9w0BAQEFAASCBKgwggSkAgEAAoIBAQC23m+w0BLxI2zB /p8/TiuFcEurTLbLCQwcO/FE+vNcJpddckuF6/VgpBAJk+d9i7NZNqrjMH711H18 3AYhewZTCbRUMQE3ndaYEIxSt4Qhbm8XbfUfx6Fmg4CnWh/XzE7B8Z7XbHpwRQ4d kQOzICzb1nt96QKdWoAob73+hv7qdi3UjJ3/20z3Cx5LWfWoa32Y50//tvBjBtcQ H7QpiE2tfLWHTQ5tztkqVY/MZJWVgoT5LnqQlZeZB/C4izSYNo9EGAnw4ThaFJ/y NdvmyK6sYaO3Dq4eFO78O+zzqyfhPCtcfb8lMuRTZa8uiv7ziVf0A3eGSwKYonUf iL7su0kJAgMBAAECggEASyeDk5EQF9ZNPjUc0XGY5VBPaOkwPqVLOtdPwt+34Glj z93HOBTPVZZXmPgWLTyaytFyzcgChZl8sTHjuyLKaJoWaHtzWp4dsYUrhlsxjGPM eD6SfSsYI/9rglvBtnia7Y4Vj8dfUoCu2mvcr2NLzFWLjyWSE4U8ImI6HT7xyP1y 5Ab8JYX5C0qODTjjPldovz8Fm9XE7yKVg5XQ9aA8axq1uWY7ruzxXli/+h7TJLPe v/yCCeKLqL+gSkORjhg8ZsK/9p6InMN6uFUeSvb0Y9eLnLnJ6tCUGh4m+cgXulrD UleXxeFzxnS3ydCNHVDngXyaJ1U+bLGVeHOLRcZHAQKBgQDY5jU+rU8VPXrbPJae fZ8swf3Pi00QUKN4P5Zyy5cs18KMgDRHRaUdYmVCpTISR7Wi0XHEI7iHDpKlL//k zCT0LW+fk+4A7bP5yUhLLtd175RUpR3ieBuXbAZRqDaPMDJ1xpZj+hDiShPv+OGR k7ETnBDm/zk1T+F2Lu6qebLbcQKBgQDX1b6I7eRDkyFZITZX1v+S6PkoTMQMeCGX cSwVvuDuiszgbf7IImWvvFxd7h+WJZEVv4jV645LOsvkY6XyFXdW7iNJGkKgThg6 YNE3X5f1oGvo5E3+HNXS3vGs76YVKTraDd0SKIRT98m2jiXCVCw+KWlR5GR+xp2A 8exCoTYLGQKBgQDJdcmu1brGt7wNNlGQFI5sPCNLSs/hf4TWg/lx1rgr5pvFdK8a JA4hJOt444eGgySqfm91Bti2WUrMM7EzCoqoYitzxSsjoaWxNMv5SSDHYigcFuGT IIxAMQ4NenhytwmnazT016gnBzdNhZW+abfnxuXMKPMyGWgJJb54iWEfgQKBgQDV N3xgfNHwx5o8GIk8wVH86VWqMBvETbCxkMWCPeyq+kdmtoLpZsGZl7SPvjtJ8paf K3WcDnWlb9IYLzCyM+6O2/XTs7N59WwNz7MexrqxlebETTWXARlilYed1aj2YqKW 4vcvhwMiiDimtUor7Uc/qV033y4/5ymVRmilceiXkQKBgEbFhAKXP9qZMdfLevWp VXAZCc5mQ2hxQOSQRAL5VvTUXm6ZwVXVf/U42JH3YXiVXbwDbEjjxS/8MtSbQU9z LoVQ/+bvc3xQ08u8kxdQiWzTzwRxHJM/znxpoD9astItq4uWU58hCoUNItHEKGJt 60bczdu3rZLZIB1n2zSM6soF -----END PRIVATE KEY----- -----BEGIN CERTIFICATE----- MIID/TCCAuWgAwIBAgIJALT/9skCvdR5MA0GCSqGSIb3DQEBCwUAMIGUMQswCQYD VQQGEwJVUzELMAkGA1UECAwCSUwxEDAOBgNVBAcMB0NoaWNhZ28xGTAXBgNVBAoM EEFDTUUgQ29ycG9yYXRpb24xGDAWBgNVBAsMD1dpZGdldCBEaXZpc2lvbjERMA8G A1UEAwwIZGFya3N0YXIxHjAcBgkqhkiG9w0BCQEWD2xpbnVzQHBvc2l4Lm9yZzAe Fw0xNTEwMzAwMzI2NTJaFw0yNTEwMjcwMzI2NTJaMIGUMQswCQYDVQQGEwJVUzEL MAkGA1UECAwCSUwxEDAOBgNVBAcMB0NoaWNhZ28xGTAXBgNVBAoMEEFDTUUgQ29y cG9yYXRpb24xGDAWBgNVBAsMD1dpZGdldCBEaXZpc2lvbjERMA8GA1UEAwwIZGFy a3N0YXIxHjAcBgkqhkiG9w0BCQEWD2xpbnVzQHBvc2l4Lm9yZzCCASIwDQYJKoZI hvcNAQEBBQADggEPADCCAQoCggEBALbeb7DQEvEjbMH+nz9OK4VwS6tMtssJDBw7 8UT681wml11yS4Xr9WCkEAmT532Ls1k2quMwfvXUfXzcBiF7BlMJtFQxATed1pgQ jFK3hCFubxdt9R/HoWaDgKdaH9fMTsHxntdsenBFDh2RA7MgLNvWe33pAp1agChv vf6G/up2LdSMnf/bTPcLHktZ9ahrfZjnT/+28GMG1xAftCmITa18tYdNDm3O2SpV j8xklZWChPkuepCVl5kH8LiLNJg2j0QYCfDhOFoUn/I12+bIrqxho7cOrh4U7vw7 7POrJ+E8K1x9vyUy5FNlry6K/vOJV/QDd4ZLApiidR+Ivuy7SQkCAwEAAaNQME4w HQYDVR0OBBYEFEgCdENJ+1y0STMyQtuz3uqDON3NMB8GA1UdIwQYMBaAFEgCdENJ +1y0STMyQtuz3uqDON3NMAwGA1UdEwQFMAMBAf8wDQYJKoZIhvcNAQELBQADggEB AITW7cBNdWBcgagsinGKfyESBlJ7JvxvsMzYVhI8myC8ht/3nFMyTHmBgtmxdNbW mCCzDdeSigQf/iEzH02k/EK7L7I3DATGBW6w9WiYBdqrZJl98CIxoY9j+GV0AeL1 INMSb5G4R2ygnekXVNTJsICeVHTRujBJsD4psZB5dhSI888rA2MrdQ8jAFGDk7Z4 VYckA2gQ+70yXXxpFSD4n2ecq3ebNtej07zR2wAtAkt/JtuGiUjbl1m4ZFTPoTwr xDYMcezEgopMzYMihv6CQ0CEU+qL+92CYtEDsd1hzn74SlBK9HMKjMLrbBZPhbE4 /JMRW5oa/+TFZIRcacTxgAw= -----END CERTIFICATE-----
The PRIVATE KEY section above is the most sensitive portion of the file - ensure that it is not seen or copied by anyone that you do not trust, and any recordings on backup media should be encrypted. The BEGIN CERTIFICATE section is presented to TLS clients (i.e. sqlplus) when they connect to stunnel.
It is likely wise to compute custom primes for the Diffie-Hellman key exchange algorithm, following guidance from the stunnel manual page:
openssl dhparam 2048 >> stunnel.pem
The previous command will add another section to your stunnel.pem file for high-security Diffie-Hellman primes:
-----BEGIN DH PARAMETERS----- MIIBCAKCAQEAoHi5jzY5ZVwGCFFm1EhVsePXxNwCSs/eQbaC3rc+iXENL8xk21uq 6eSwYIQWUeDN/h6wBBDe6dpFoNDJQeqKCmUa8aojGHnkcqsJBdVUKVF5/7rWb1Yi TzvbeZt8UvYnNUErJEpgBMiKPDYipE2BZ6k61WwkK6WV6svGAHpIc3o/9kU+72uf dPFaNIygAb2HLaJYvXq9OYGvrMsmyZTh3fnpg2RiZSVJf+i4BfyeLiYkwnSZozAS 2rQ4hf2E5WY6jiAcNZBLKvqR8lUuIaXd9+VkiCSV0c2pXzb2ElxOk8sheAHliwip SaKC694z9l63eNKQW2J4WI97wkil0qa4MwIBAg== -----END DH PARAMETERS-----
The Oracle TNS Listener conventionally runs at port 1521. In this exercise, we will run Oracle TLS services at port 1522, which has the current service name:
# grep 1522 /etc/services ricardo-lm 1522/tcp # Ricardo North America License Manager ricardo-lm 1522/udp # Ricardo North America License Manager
Place the following file to control stunnel for our "ricardo" service (alter the IP address 126.96.36.199 to the location of your TNS Listener):
# cat /etc/stunnel/ricardo.conf sslVersion = TLSv1.2 options = NO_SSLv3 options = NO_SSLv2 options = SINGLE_DH_USE options = SINGLE_ECDH_USE options = CIPHER_SERVER_PREFERENCE cert = /etc/pki/tls/certs/stunnel.pem FIPS = no debug = 6 syslog = yes chroot = /var/empty setuid = nobody setgid = nobody connect = 188.8.131.52:1521 ; best-practice ciphers: ; https://hynek.me/articles/hardening-your-web-servers-ssl-ciphers/ ciphers=ECDH+AESGCM:DH+AESGCM:ECDH+AES256:DH+AES256:ECDH+AES128:DH+AES:ECDH+3DES:DH+3DES:RSA+AESGCM:RSA+AES:RSA+3DES:!aNULL:!MD5:!DSS
Note above that we are configuring TLS for best practice encryption with the highest quality protocols and ciphers. The Oracle clients appear compatible with these settings. Note that Michal Trojnara, the author of stunnel, does "not recommend using DH ciphersuites in the hardened set. ECDH ciphersuites are much more secure and much faster - RFC 7525 should be considered outdated after the recent attacks on DH." On the other hand, there have been recent questions of software patents on Elliptic Curve, although Sun/Oracle contributed the ECC implemention in OpenSSL and used great care to avoid patented methods. RedHat/Fedora went further in enabling only the Suite B subset of NIST ECC curves for protection from Certicom (whether this is a sufficent courtroom defense against CryptoPeak is another matter). Beyond that, in my previous coverage of the Stribika SSH Guide, the author is "...advising against the use of NIST elliptic curves because they are notoriously hard to implement correctly. So much so, that I wonder if it's intentional. Any simple implementation will seem to work but leak secrets through side channels. Disabling them doesn't seem to cause a problem; clients either have Curve25519 too, or they have good enough DH support." Trojnara has responded that the question of "side-channel attacks on ECDHE is pure nonsense, since by definition (the last 'E' stands for 'ephemeral'), there is no persistent secret here an attacker might retrieve with [any available] side-channel attacks." In any case, Hynek Schlawack's website on the subject has not endorsed one over the other so far, while his silence on the growing questions behind Diffie-Hellman key exchange is somewhat unsettling. Your legal environment and encryption stance will decide your cipher string.
Use the following systemd unit files to configure stunnel for inetd-style operation. If you aren't using an OS based on systemd, see my previous articles for a discussion of [x]inetd.
# cat /etc/systemd/system/ricardo.socket [Unit] Description=oracle stunnel [Socket] ListenStream=1522 Accept=yes [Install] WantedBy=sockets.target # cat /etc/systemd/system/ricardo@.service [Unit] Description=oracle stunnel service [Service] ExecStart=-/usr/bin/stunnel /etc/stunnel/ricardo.conf StandardInput=socket
Assuming that the above unit files are in place, connections on 1522 can be enabled both at boot and for the present environment with these commands:
systemctl start ricardo.socket systemctl enable ricardo.socket
The enable command will place systemd's startup link:
Created symlink from /etc/systemd/system/sockets.target.wants/ricardo.socket to /etc/systemd/system/ricardo.socket.
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 can be used to completely ban cleartext traffic by adding your IP equivalent to the following fragment of the $ORACLE_HOME/network/admin/sqlnet.ora file on the server:
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 an stunnel binary provided by Oracle Corporation, but the versions that they provide are rather old. If stunnel version 5 can be loaded, then the NO_SSL options above can be omitted. 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 visit http://www.stunnel.org/support.html for his latest release.
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 184.108.40.206 Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
Directives must also 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=220.127.116.11) (port=1522) ) (connect_data=(sid=mydb)))'
Assuming success, enter the password for your RemoteUser account, then issue a SQL command:
SQL*Plus: Release 18.104.22.168.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 22.214.171.124.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:
# ps -ef | grep stunnel nobody 16810 1 0 13:26 ? 00:00:00 /usr/bin/stunnel /etc/stunnel/ricardo.conf
With two or more Oracle database servers, sessions and transactions can be initiated between them to gather and modify data in "two-phase commits." Linkages between accounts and servers are established with the command below (if you have moved tcps hosts into your TNSNAMES.ORA, you can reference them here also):
SQL> CREATE DATABASE LINK MyDBLink CONNECT TO RemoteUser IDENTIFIED BY PassWord USING '(description= (address= (protocol=tcps) (host=126.96.36.199) (port=1522) ) (connect_data=(sid=mydb)))'; Database link created.
Once the link is established, remote tables can be suffixed by the link name (which can be joined to other local or remote tables):
SQL> SELECT COUNT(*) FROM ALL_OBJECTS@MyDBLink; COUNT(*) ---------- 1851
It may be necessary for keys to be verified on either side of the connection to assure authorized use. The native Oracle TLS implementation requires all keys subject to verification to be signed by a recognized CA (the CA's public keys may need to be added to the certificate store used by Oracle).
Please note that stunnel can also verify keys and act as a client as well as a server. The stunnel verification options are much more flexible than Oracle's, and if CA signatures are not desired but TLS verification is mandated, then Oracle's TLS should be disabled entirely.
In the examples below, we will assume that the server's public key has a CA signature. To extract that public key, the following awk pattern is useful:
awk '/BEGIN CERTIFICATE/,/END CERTIFICATE/' \ /etc/pki/tls/certs/stunnel.pem > /tmp/pkey
Move the /tmp/pkey file to the client, then load it into the wallet:
$ORACLE_HOME/bin/orapki wallet add -wallet /home/oracle/wallet \ -pwd SECRET123 -trusted_cert -cert /tmp/pkey
After loading the key, verify that it is now present in the wallet:
$ORACLE_HOME/bin/orapki wallet display -wallet /home/oracle/wallet \ -pwd SECRET123
The key should appear in the Trusted Certificates section:
Oracle PKI Tool : Version 188.8.131.52 Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved. Requested Certificates: User Certificates: Subject: CN=%yourdb% Trusted Certificates: Subject: EmailAddressemail@example.com,CN=184.108.40.206,OU=Widget Division,O=ACME Corporation,L=Chicago,ST=IL,C=US Subject: CN=%yourdb%
The client can verify the server keys with the SSL_SERVER_CERT_DN clause in the TNS descriptor:
$ORACLE_HOME/bin/sqlplus fishecj@'(description= (address= (protocol=tcps) (host=220.127.116.11) (port=1522) ) (connect_data= (sid=mydb) (security=(SSL_SERVER_CERT_DN="CN=18.104.22.168,OU=Widget Division,O=ACME Corporation,L=Chicago,ST=IL,C=US") )))'
If the CA signature is not recognized, then the sqlplus login will fail with the following:
ERROR: ORA-29024: Certificate validation failure
Additionally, stunnel will record the following in /var/log/secure:
LOG7: SSL alert (read): fatal: unknown CA LOG3: SSL_accept: 14094418: error:14094418:SSL routines:SSL3_READ_BYTES:tlsv1 alert unknown ca
Such errors indicate that the CA is not properly loaded into the bundle used by the database.
Oracle database security has received pointed criticism through the years and releases, which has slowly improved the architecture and closed exploitable weaknesses. For many, these improvements are inadequate in both speed and scope. In such cases, stunnel is a valuable tool for authentication, isolation, and privacy of critical data within Oracle.
Oracle has been assigned standard ports for TNS/SQL*Net:
ncube-lm 1521/tcp # nCube License Manager ttc 2483/tcp # Oracle TTC (Two-Task Communication) ttc 2483/udp # Oracle TTC ttc-ssl 2484/tcp # Oracle TTC SSL ttc-ssl 2484/udp # Oracle TTC SSL
The ncube port 1521 was originally administered by a company that was (at one time) partially owned by Larry Ellison. Oracle's usage of cleartext port 1521 should migrate to port 2483.
Impressions of Oracle's TLS implementation in the database client: