Enabling Oracle SSL/TLS Encryption

In our previous post on securing Oracle applications, we discussed how to enable Oracle Native Network Encryption for your client and server. In the conclusion however, we noted some of the shortcomings of using that security mechanism (potential for man-in-the-middle attacks, inability to control secure certificates, etc.).

Here we begin to address those issues by instead enabling SSL/TLS encryption between the Oracle client and database.

Code to Query Connection Security

Starting from a client and server that do not have any security features enabled let us first run a test application to determine the connection type currently reported by the server.

Using this code snippet, we will establish a connection to an Oracle database server and query for connection information. With an SSL connection, encryption is occurring around the Oracle network service, so it is unable to report itself. Instead, we must query the network connection itself to determine if the connection is encrypted.

const RWDBDatabase db = RWDBManager::database("ORACLE_OCI", server, username, password, "");
const RWDBConnection conn = db.connection();
{
    RWDBResult result = conn.executeSql("SELECT sys_context('USERENV', 'NETWORK_PROTOCOL') FROM dual");
    const RWDBTable resultTable = result.table();
    RWDBReader reader = resultTable.reader();
    while (reader()) {
        RWCString protocol;
        reader >> protocol;
        std::cout << "Connected using protocol " << protocol << std::endl;
    }
}

Connected using protocol tcp

As we can see from the output, the server is reporting that this connection is a standard unencrypted TCP connection. Let us now walkthrough how we can enable SSL/TLS encryption and see what the server reports with SSL encryption enabled (note, because SSL/TLS generates a cryptographically secure message digest, we will get data integrity as part of the SSL/TLS connection).

Alter the Server to Use TCPS Connections Instead of TCP

We will use an Oracle Wallet to securely store and retrieve credentials such as certificates, certificate requests, and private keys. The wallet will be created in a directory of your choice on your database server. In examples that follow, we use the placeholder <server_wallet_directory> to represent that directory name.

We create the wallet using the Oracle Public Key Infrastructure (orapki) utility. The wallet will be created with two additional options.

The auto_login_local option allows the database to read the values of the wallet without requiring interactive password authentication. Additionally, it enforces that the wallet may only be opened by the user that created the wallet on the machine where the wallet was created. Because of this security requirement you will need to create the wallet as the same user that your database executes as.

The wallet also requires that a password be specified for it. We will use the placeholder <server_wallet_password> in the examples to represent that value.

orapki wallet create -wallet < server_wallet_directory> -auto_login_local -pwd < server_wallet_password>

Now that we have a wallet created, we will add a new self-signed certificate for this server to the wallet. We will use the same directory and password values as specified above when you created the wallet.

orapki wallet add parameters

Here is an example of this call:

orapki wallet add -wallet <server_wallet_directory> -pwd <server_wallet_password> -dn "CN=ORCLCDB,O=testsecurity,C=US" -keysize 2048 -self_signed -validity 365</server_wallet_directory>

Querying the wallets contents verify the certificate we created is present:

> orapki wallet display -wallet <server_wallet_directory> -pwd < server_wallet_password>
Oracle PKI Tool Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Subject:        CN=ORCLCDB,O=testsecurity,C=US
Trusted Certificates:
Subject:        CN=ORCLCDB,O=testsecurity,C=US
</server_wallet_directory>

Finally we will export the server certificate from the wallet to a file named oracle-db-certificate.crt to use later with the client wallet.

orapki wallet export -wallet <server_wallet_directory> -pwd <server_wallet_password> -dn "CN=ORCLCDB,O=testsecurity,C=US" -cert /tmp/oracle-db-certificate.crt

Update the Server listener.ora

The file listener.ora must be updated to enable a port for secure communication and specify the location of the Oracle wallet.

Please note, when specifying the value for the DIRECTORY parameter make sure to use a fully qualified path to the <server_wallet_directory> location.

For now, we are going to continue to use username/password authentication, so we will disable SSL client authentication on the server by setting the SSL_CLIENT_AUTHENTICATION property to FALSE.

Next, we will add the key SECURE_PROTOCOL_LISTENER and set it to use the more secure Oracle IPC (which only allows communication with other processes on the system) rather than the TCP protocol. By setting this we are instructing the listener to only accept administration and registration requests from the IPC protocol.

Lastly, update the listener entry to enable support for TCPS traffic by changing the protocol from TCP to TCPS and the PORT to 2484.

SSL_CLIENT_AUTHENTICATION = FALSE
SECURE_PROTOCOL_LISTENER=(IPC)
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = <server_wallet_directory>)
    )
  )

LISTENER =
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    (ADDRESS = (PROTOCOL = TCPS)(HOST = 0.0.0.0)(PORT = 2484))
   )
)
</server_wallet_directory>

Update the Server sqlnet.ora

The file sqlnet.ora must also be updated to enable secure communications. Like the changes we made to the listener.ora file, the SSL_CLIENT_AUTHENTICATION and WALLET_LOCATION need to be set.

Once again, when specifying the value for DIRECTORY, make sure to use a fully qualified path to the <server_wallet_directory> location you specified.

Lastly, we need to specify the property SSL_CIPHER_SUITES to define which encryption algorithms the server should use/require to encrypt network traffic.

SSL_CLIENT_AUTHENTICATION = FALSE
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = < server_wallet_directory >)
    )
  )

SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)

Register a New Server Local Listener

Earlier we specified in the listener.ora file that the listener would only listen for administration and registration with the IPC protocol. We now need to update the server, so it uses IPC to communicate with the listener. First, stop the listener using the lsnrctl stop command.

On the server hosting your database, open a connection using SQL*Plus as SYSDBA and execute the following command to update the protocol the server will use to register itself with the listener:

sqlplus / as SYSDBA
alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))' SCOPE=BOTH;

With that change made, restart the server's listener with the lsnrctl start command.

And finally, again as SYSDBA trigger the server’s listener registration:

sqlplus / as SYSDBA
alter system register;

Setup the Client

Now that we have the server set up for encrypted communication, we also need to set up encryption in the client as well. The client will also use an Oracle Wallet to store the server security certificate to encode and decode communication with the server.

As noted in the assumptions section, this article assumes you have a machine with the Oracle database client installed. We will create another Oracle Wallet (this time on the client) with the following command, again setting the wallet to enable the auto_login_local option and setting a password. As above we will use a placeholder <client_wallet_directory> in examples that follow to represent the directory name you have chosen.

orapki wallet create -wallet <client_wallet_directory> -auto_login_local -pwd <client_wallet_password>

Now we need to add the server security certificate (the file oracle-db-certificate.crt we generated from the database server) to the client wallet to facilitate encrypted communication. For example:

orapki wallet add -wallet <client_wallet_directory> -pwd <client_wallet_password> -trusted_cert -cert oracle-db-certificate.crt

Update the Client tnsnames.ora

First, we will need to update the file tnsnames.orain your client to register the new TCPS listener on your database server. You should replace SERVER_ADDRESS with the IP Address or FQDN of the server hosting your database.

Update the net_service_name entry (in this example named SERVER) to enable support for TCPS traffic by changing the protocol from TCP to TCPS and the PORT to 2484.

SERVER =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = SERVER_ADDRESS)(PORT = 2484))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLCDB)
    )
  )

Update the Client sqlnet.ora

Now we need to update the file sqlnet.ora to add the encryption options to the client. Again, as we are only concerned with enabling encrypted communication and not authentication, we will set SSL_CLIENT_AUTHENTICATION to FALSE.

When specifying the value for DIRECTORY, make sure to use a fully qualified path to the <client_wallet_directory> location you specified.

SSL_CLIENT_AUTHENTICATION = FALSE
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = <server_wallet_directory>)
    )
  )

A Secure Connection

Once all the above changes have been made, we can use our same example from before. Because we did not alter the net_service_name, we do not need to make any code changes or re-compile the executable for SourcePro DB to take advantage of the SSL encryption. Now when we run the example the connection protocol information is reported as the secure tcps protocol:

Connected using protocol tcps

Conclusion

We now can establish an SSL/TLS connection to the server. By switching to using TLS and encryption ciphers that use pre-shared keys we have addressed the potential for a man-in-the-middle attack. We now also have much more control over the certificates for encryption and the cipher and checksum algorithms used.

We still however must pass a username and password to the server to authenticate. It would be nice if we could instead use SSL/TLS pre-shared keys. In our next aritcle, we will discuss how to enable SSL/TLS authentication.

In the meantime, you can request a free evaluation of SourcePro. With SourcePro, you write your code once and deploy it on any platform. This helps you reduce time-to-market, increase reliability, and extend the life of your applications.

Request An Evaluation