Oracle Network Encryption

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:

cat /tmp/mydb-certificate.crt

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 12.2.0.1.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 12.2.0.1.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 12.2.0.1.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 12.2.0.1.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 encrypt_test@mydb_ssl

SQL*Plus: Release 12.2.0.1.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 12.2.0.1.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 'oracle@% (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:


MD5
SHA1

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.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles