Oracle offers the possibility of encrypted TNS connections through the TCPS protocol. This requires the use of SSL, and as a result, certificates to verify both the client and the server. Creating such a configuration is not difficult; the rest of this article will provide the steps and commands to successfully complete this task.
An SSL configuration requires, on the Oracle side, a wallet, similar to TDE. A server-side wallet is required to run a listener using the TCPS protocol and the orapki utility is used to create it. A client-side wallet also needs to be created; if using the Instant Client this can be done in a ’round-about’ fashion. The example shown here involves two wallets with the client-side not sending any information. The wallet on the client-side is present only to allow Oracle to negotiate connections.
Creating a server-side wallet with orapki isn’t difficult; a directory will be created to contain the server-side wallet files:
mkdir -p /my/tns
Next the wallet is created with orapki, and auto-login is required:
orapki wallet create -wallet "/my/tns" -pwd [password] -auto_login_local
Next the self-signed certificate for the server is created. To ensure a proper configuration the certificate name (CN) is the TNS service name. The -keysize is the size, in bytes, of the certificate and the -validity parameter specifies, in days, the time period that the certificate is valid. The following command is used:
orapki wallet add -wallet "/my/tns" -pwd [password] -dn "CN=mydb" -keysize 1024 -self_signed -validity 3650
It’s good to see that the wallet is valid and that it contains what is expected. The following command provides that information:
orapki wallet display -wallet "/my/tns"
If the client side will have a wallet the server certificate needs to be exported:
orapki wallet export -wallet "/my/tns" -pwd [password] -dn "CN=mydb" -cert /tmp/mydb-certificate.crt
Verify the contents of the exported file:
For a full client installation, the client-side certificate can be created on the client machine using the same commands with the exception of the local wallet directory. For instant client installations a certificate is needed but it can be created on the destination server and copied to the client location. Creating a client-side certificate in location /my/tns/client named simon, the steps would be:
mkdir -p /my/tns/client orapki wallet create -wallet "/my/tns/client" -pwd [password] -auto_login_local orapki wallet add -wallet "/my/tns/client" -pwd [password] -dn "CN=simon" -keysize 1024 -self_signed -validity 3650 orapki wallet display -wallet "/my/tns/client" orapki wallet export -wallet "/my/tns/client" -pwd [password] -dn "CN=simon" -cert /tmp/simon-certificate.crt cat /tmp/simon-certificate.crt
For instant client installations the wallet just created needs to be copied to the client machine; the directory does not need to match the directory where the wallet was created on the server, but it does need to be correctly specified in the local sqlnet.ora file. Once this is completed move on to the next step, discussed below.
Both certificates are ready to be shared. The client-side wallet needs the server certificate added to it and the following command completes that step:
orapki wallet add -wallet /my/tns/client -pwd [password] -trusted_cert -cert /tmp/mydbserver-certificate.crt
The server certificate is named for the database it will connect to. Display the contents of the client-side certificate:
orapki wallet display -wallet "/my/tns/client" -pwd [password]
The output for this example is:
Oracle PKI Tool : Version 184.108.40.206.0 Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved. Requested Certificates: User Certificates: Subject: CN=simon Trusted Certificates: Subject: CN=mydb Subject: CN=simon
The client-side wallet is ready for use. Adding the client certificate to the server wallet uses basically the same command:
orapki wallet add -wallet /my/tns -pwd [password] -trusted_cert -cert /tmp/simon-certificate.crt
Displaying the contents of the server-side wallet should provide similar output:
Oracle PKI Tool : Version 220.127.116.11.0 Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved. Requested Certificates: User Certificates: Subject: CN=mydb Trusted Certificates: Subject: CN=mydb Subject: CN=simon
The server-side sqlnet.ora file now needs the following additions:
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /my/tns) ) ) SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS,BEQ) SSL_CLIENT_AUTHENTICATION = FALSE SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)
(SSL_CLIENT_AUTHENTICATION is set to FALSE when client-side information is not required. When two-way authentication is required then this parameter must be set to TRUE. The remainder of this article presumes that one-way authentication is in use. Make the necessary parameter changes should two-way authentication be required.)
These additions provide the wallet location, the available authentication services and SSL cipher suites to use. The TCPS protocol must be specifically listed to allow connections to a listener using that protocol. The listener.ora file also needs to be modified by including the following additions:
SSL_CLIENT_AUTHENTICATION = FALSE WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /my/tns) ) )
The listener configuration then needs the TCPS protocol running on a separate port:
(ADDRESS = (PROTOCOL = TCPS)(HOST = mydbserver.youbetcha.com)(PORT = 2921))
This can be done in a single listener.ora file or in the listener.ora file for a dedicated listener installation. The complete listener.ora file for the dedicated listener installation for this example is shown below:
SSL_CLIENT_AUTHENTICATION = FALSE WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /my/tns) ) ) LISTENER_ENCRYPT = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = mydbserver.youbetcha.com)(PORT = 2921)) ) ) INBOUND_CONNECT_TIMEOUT_LISTENER_ENCRYPT = 120 CONNECT_TIMEOUT_LISTENER_ENCRYPT = 120 SID_LIST_LISTENER_ENCRYPT = (SID_LIST = (SID_DESC= (SID_NAME=PLSExtProc) (ORACLE_HOME=/u04/oracle/listener_encpt) (PROGRAM=extproc) (ENVS = "EXTPROC_DLLS=ANY,LD_LIBRARY_PATH=/u04/oracle/listener_encpt/lib") ) (SID_DESC = (ORACLE_HOME = /u04/app/oracle/snord) (SID_NAME = snord) (ENVS="LD_LIBRARY_PATH=/u04/oracle/snord/lib") ) )
In this example the database in question is already on another port using the standard TCP protocol, thus local_listener cannot be changed. The database is configured, in this listener, as a static configuration. Starting the listener and obtaining the status:
LSNRCTL for *******: Version 18.104.22.168.0 - Production on 20-SEP-2018 09:41:44 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=mydbserver.youbetcha.com)(PORT=2921))) STATUS of the LISTENER ------------------------ Alias listener_encpt Version TNSLSNR for ******** Version 22.214.171.124.0 - Production Start Date 20-SEP-2018 08:26:12 Uptime 0 days 1 hr. 15 min. 32 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u04/oracle/listener_encpt/network/admin/listener.ora Listener Log File /u04/oracle/diag/tnslsnr/mydbserver/listener_encpt/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=mydbserver.youbetcha.com)(PORT=2921))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "snord" has 1 instance(s). Instance "snord", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
With the listener successfully started the next step is to get the client side configured. The client-side sqlnet.ora file will need to contain the directory location using the following example as a guide:
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /my/tns/wallet) ) ) SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS,BEQ) SSL_CLIENT_AUTHENTICATION = FALSE SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)
The client tnsnames.ora file will also need an entry for the encrypted database connection:
snord_ssl = (DESCRIPTION=(ADDRESS= (PROTOCOL=tcps) (PORT=2921) (HOST=mydbserver.youbetcha.com)) (CONNECT_DATA=(SID=snord)))
Obviously, the port for the encrypted listener will need to be opened prior to testing the configuration.
At this point all of the necessary steps have been completed and testing can begin. The client should successfully connect through the encrypted port and behave as any basic TNS connection would:
$ sqlplus [email protected]_ssl SQL*Plus: Release 126.96.36.199.0 Production on Wed Sep 26 15:54:35 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Enter password: Last Successful login time: Wed Sep 26 2018 13:07:50 -06:00 Connected to: Oracle Database 12c Enterprise Edition Release 188.8.131.52.0 - 64bit Production ENCRYPT_TEST @ mydb_ssl >
Verifying that the connection is utilizing the TCPS protocol is simply a matter of executing the following queries:
-- -- NULL line indicates TCPS, all other protocols -- will display -- select NETWORK_SERVICE_BANNER from v$session_connect_info where SID = sys_context('USERENV','SID') union select SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') from dual order by 1 nulls first / column program format a45 set linesize 150 select * from (select sid,program, case when program not like '[email protected]% (P%)' then (select max(case when NETWORK_SERVICE_BANNER like '%TCP/IP%' then 'TCP' when NETWORK_SERVICE_BANNER like '%Bequeath%' then 'BEQUEATH' when NETWORK_SERVICE_BANNER like '%IPC%' then 'IPC' when NETWORK_SERVICE_BANNER like '%SDP%' then 'SDP' when NETWORK_SERVICE_BANNER like '%NAMED P%' then 'Named pipe' when NETWORK_SERVICE_BANNER is null then 'TCPS' end) from V$SESSION_CONNECT_INFO i where i.sid=s.sid) end protocol from v$session s) where protocol is not null / set linesize 80
The TCPS protocol can be included in a single listener configuration, however if managing the encrypted traffic requires a service shutdown this would also stop the TCP listener and prevent any remote connections to the affected database. It is recommended that a second, dedicated listener be configured to handle the encrypted traffic, so that management and monitoring is simplified, and should problems arise, the encrypted listener can be safely shut down.
With the apparently high incidence of breaches into databases of any vendor, setting up encrypted traffic may be the best solution to curb or eliminate intrusions. With one-way authentication the wallet simply needs to exist and certification information from that wallet does not need to be imported into the server-side wallet. With two-way authentication configurations, any client connections from systems not having certificates registered in the server wallet will be disconnected. Of course, if the port isn’t open to the public no client connections can get through, which should be the first step in securing enterprise database systems. The port number should be one that isn’t widely used or published (thus 2921 is NOT a good choice), although that won’t stop creative and dedicated hackers as they will likely resort to port scans. The netstat utility can reveal the ports Oracle databases are listening on, however the TCPS protocol is not listed for encrypted listener configurations. Using the TCP protocol on a port using TCPS provides this response:
ERROR: ORA-12547: TNS:lost contact
Since the TCPS protocol will not likely be reported by port scanners having the port number, with a listener configured for encryption, does the hacker no good. This is when the encrypted database network traffic can possibly prevent data loss. Remember too, that this is only one aspect of a robust database security configuration; encrypted connections can only do so much to deter attackers.
Another form of encryption is available with Oracle, called native encryption. This type uses settings in the sqlnet.ora file on both the client and the server and does NOT use the TCPS protocol. It is also less robust as connections succeed whether or not the traffic is encrypted. Configuring native encryption uses the following sqlnet.ora settings on the server:
sqlnet.encryption_server=required sqlnet.encryption_types_server=(AES256) sqlnet.crypto_checksum_server=required sqlnet.crypto_checksum_types_server=(SHA1)
On the client side:
sqlnet.encryption_client=required sqlnet.encryption_types_client=(AES256) sqlnet.crypto_checksum_client=required sqlnet.crypto_checksum_types_client=(SHA1)
The key setting is sqlnet.encryption_[client|server]; depending on how the client and server are set determines if native encryption is used. The allowable values for both client and server are:
ACCEPTED REJECTED REQUESTED REQUIRED
Valid encryption types are:
RC4_256 RC4_128 RC4_56 RC4_40 AES256 AES192 AES128 3DES168 3DES112 DES DES40
Valid checksum types are:
The following combinations of settings for sqlnet.encryption_[client|server] disables native encryption:
Client Setting Server Setting Encryption and Data Negotiation ========================= ============================= =============================== REJECTED REJECTED OFF ACCEPTED REJECTED OFF REQUESTED REJECTED OFF ACCEPTED ACCEPTED OFF (DEFAULT VALUE) REJECTED ACCEPTED OFF REJECTED REQUESTED OFF
The following setting combinations cause connections to be rejected:
Client Setting Server Setting Encryption and Data Negotiation ========================= ============================= =============================== REJECTED REQUIRED Connection fails REQUIRED REJECTED Connection fails
All other combinations enable native encryption. Since connections can succeed without encryption enabled this is best used as a secondary encryption method, on top of using an SSL configuration to enable the TCPS protocol. It can be used in place of TCPS, but it isn’t as robust and can allow unencrypted connections to the database. Think carefully about using this strategy as the sole method of encryption.
Encrypting database network traffic may not be the simplest option to deploy but it may just give the DBA (and the CEO) a better night’s sleep knowing that the network packets are unreadable to the outside world. It’s worth keeping this in mind when designing and implementing sensitive database systems.