Oracle shield standing in front of computer hardware stack
May 3, 2021

Enabling Oracle SSL/TLS Authentication

Security & Compliance

In our previous posts on Oracle application security, we covered Oracle Native Network encryption and discussed how to enable SSL/TLS encrypted networking for your client and server. In this article, we are going to discuss how we can use SSL/TLS pre-shared keys for authentication.

Back to top

Code to query connection security

Starting from the client and server setup we configured in our last post, we will revise our example to add support for querying the authentication method.

Using this code snippet, we will establish a connection to an Oracle database server and query for connection information.

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') as network_protocol, sys_context('USERENV', 'AUTHENTICATION_METHOD') as authentication_method FROM dual");
const RWDBTable resultTable = result.table();
RWDBReader reader = resultTable.reader();
while (reader()) {
    RWCString protocol, method;
    reader >> protocol >> method;
    std::cout << "Connected using protocol " << protocol << " with authentication method " << method << std::endl;
}

Output

connected using protocol tcps with authentication method PASSWORD

As we can see from the output, the server is reporting that this connection is using the encrypted port (TCPS); however, it is using traditional password authentication. To enable SSL authentication, we will need to generate a client certificate, register it with the server, and create a new SSL authenticated user.

Back to top

Setup the Client

Using the wallet that we created in the previous post, let us create a new self-signed certificate for the client.

We will use the same wallet directory and password as specified above when you created the wallet.

orapki wallet add parameters

parameter

value

dn

A user specified distinguished name. For example: "CN=ORCLCLIENT"

keysize

The certificate's keysize must be one of the three values, either (512|1024|2048)

validity

The number of days the certificate should be valid for.

self_signed

Self-sign the certification.

Here is an example of this call:

orapki wallet add -wallet  -pwd  -dn "CN=ORCLCLIENT" -keysize 2048 -self_signed -validity 365

We now need to export the client certificate so it can be imported into the server wallet.

orapki wallet export -wallet  -pwd  -dn "CN=ORCLCLIENT" -cert client-certificate.crt
Back to top

Update the Client tnsnames.ora

Next, we need to update the file tnsnames.ora in your client to register the server certificate distinguished name.

tnsnames.ora

SERVER =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = SERVER_ADDRESS)(PORT = 2484))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLCDB)
    )
    (SECURITY =
      (SSL_SERVER_CERT_DN = "CN=ORCLCDB,O=testsecurity,C=US")
    )
  )
Back to top

Update the Client sqlnet.ora

We will also need to update the client sqlnet.ora to enable SSL/TLS client authentication with by setting the key SSL_CLIENT_AUTHENTICATION to TRUE. We will need to add TCPS to the SQLNET.AUTHENTICATION_SERVICES property.

While in this file, we also will set the property SSL_SERVER_DN_MATCH to ON (by default it is OFF). By enabling this, we are requiring the client SSL layer to ensure that the certificate received is from the server and not some other bad actor faking the server’s identity.

sqlnet.ora

SSL_CLIENT_AUTHENTICATION = TRUE
SSL_SERVER_DN_MATCH=ON
SQLNET.AUTHENTICATION_SERVICES = (TCPS)
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = )
    )
  )
Back to top

Setup the Server

We now need to install the client certificate we generated into the server wallet to enable symmetrical SSL handshaking.

Back to top

Import the Client Security Certificate

For the handshake to be symmetrical we need to import the client certificate into the server’s wallet to make it available for verification.

We will add the client security certificate (the file client-certificate.crt we generated earlier) to the server wallet:

orapki wallet add -wallet  -pwd  -trusted_cert -cert client-certificate.crt
Back to top

Update the Server listener.ora

We have already set up the file listener.ora to enable a port for secure communication and specify a location for the Oracle wallet.

Unlike before, we are now also concerned with performing SSL/TLS authentication between the client and server and securing their subsequent traffic. To enable this, set the SSL_CLIENT_AUTHENTICATION property to TRUE.

listener.ora

SSL_CLIENT_AUTHENTICATION = TRUE
SECURE_PROTOCOL_LISTENER=(IPC)
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = )
    )
  )
 
LISTENER =
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    (ADDRESS = (PROTOCOL = TCPS)(HOST = 0.0.0.0)(PORT = 2484))
   )
)
Back to top

Update the server sqlnet.ora

The file sqlnet.ora must be updated to enable SSL/TLS authentication. Like the changes we made to the listener.ora file, the SSL_CLIENT_AUTHENTICATION property now needs to be set to TRUE.

To complete enabling SSL/TLS authentication, we need to add the value TCPS to the list of SQLNET.AUTHENTICATION_SERVICES.

sqlnet.ora

SSL_CLIENT_AUTHENTICATION = TRUE
SQLNET.AUTHENTICATION_SERVICES = (TCPS)
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = )
    )
  )

SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)
Back to top

Restart the Listener

With all those configuration changes in place, restart the server's listener to have it begin handling SSL/TLS authentication requests using the lsnrctl stop and lsnrctl start commands.

Back to top

Create a User to Use SSL Authentication

To authenticate with the database using an SSL certificate, we need to create a user that uses the certificate authentication (as opposed to a password).

Traditionally you would create a user that uses password authentication with SQL like this:

CREATE USER "SSLCLIENT2" PROFILE "DEFAULT" IDENTIFIED BY "SSLPASSWORD";

To create a user that instead uses SSL certificate authentication, you will use a SQL statement like:

CREATE USER "SSLCLIENT" IDENTIFIED EXTERNALLY AS 'CN=ORCLCLIENT';

In the new variant you will specify the distinguished name of the client certificate generated that you would like the server to authenticate with.

You can also alter an existing user to use SSL certificate authentication. Assuming we had the SSLCLIENT user created using password authentication, you could use the following SQL statement to switch that user to SSL certificate authentication:

ALTER USER "SSLCLIENT2" IDENTIFIED EXTERNALLY AS 'CN=ORCLCLIENT';
Back to top

A Secure Connection With SSL Authentication

Having made all these changes, we can use our same example from before. We will need to modify our example as we no longer want to pass a username and password to use for authentication and now want to perform authentication based off shared SSL certificates between the client and server.

Modify the line in the example from above like so:

const RWDBDatabase db = 
    RWDBManager::database("ORACLE_OCI", server, "", "", "");

Notice the 3rd and 4th parameters (username and password) will now be passed as empty strings.

After re-compiling, when we run the example now, we see that connection security and authentication type are reported as:

Output

Connected using protocol tcps with authentication method SSL

Password authentication is no longer being reported and the server has now authenticated the client and the client has validated the server’s identity using the pre-shared keys and certificates.

Back to top

Conclusion

As we have seen over this series of blog posts, not only is it relatively simple to set up many different types of encryption and handshaking with the Oracle database, SourcePro DB has allowed you to seamlessly work with any of them and focus on implementing your application to meet your organizations security requirements.

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 a free evaluation of SourcePro to get started.

REQUEST AN EVALUATION

Back to top