Link
Stellirin's Notes

This site uses Just the Docs, a documentation theme for Jekyll.

Search

Manually configure Oracle DB

Docker is the prefered way of running Oracle DB, but if you need to do it manually, here’s how. This was valid at the end of 2019. If you are reading this in the future, it ma no longer be valid.

Table of contents

  1. Introduction
  2. Installation
  3. Create an Oracle DB certificate wallet
  4. Oracle Wallet Manager
  5. Configure Oracle DB networking
    1. Configure TNS Names
    2. Configure SQL Net for TLS
    3. Configure Listeners
  6. Create client app.truststore.jks
  7. Create client app.keystore.jks
  8. Configure the connection

Introduction

Configuring Oracle DB for TLS connections is fortunately a relatively simple process. We can use industry standard tools like OpenSSL to create and manage our certificates. The only specialized tool is the Oracle Wallet Manager (OWM), which is simply a store for certificates. OWM can import and export PEM format certificates which can be manipulated by OpenSSL natively.

More info: https://docs.oracle.com/cd/B28359_01/network.111/b28530/asossl.htm#i1006526

Installation

Either go through the installation process or practice with a container:

docker volume create oracle-db
docker run -d --name oracle-db \
    -p 1521:1521 \
    -p 2484:2484 \
    -e DB_SID=ORCL \
    -e DB_PDB=STL \
    -e DB_DOMAIN=STELLIRIN \
    --mount source=oracle-db,target=/ORCL \
    store/oracle/database-enterprise:12.2.0.1

The rest of the commands assume you are in the DB environment, so be sure to exec into the running container.

Create an Oracle DB certificate wallet

Start by creating an empty wallet at /ORCL/wallet. This is simply to skip the ‘new wallet’ wizard in Oracle Wallet Manager, which will set some bad defaults if you are not careful.

mkdir -p /ORCL/wallet
orapki wallet create -wallet '/ORCL/wallet' -pwd 'stellirin' -auto_login_local

Oracle Wallet Manager

In Oracle Wallet Manager:

  • Open the wallet at /ORCL/wallet
  • Import a CA public certificate
  • Add then export a certificate request to orcl.csr.pem
  • Create a signed certificate from the certificate request
  • Import the new certificate to the wallet by using the Operations menu and Import User Certificate
  • Set the wallet to auto login and save

Configure Oracle DB networking

Everything in the three files below can be configured with the ‘Oracle Net Manager’. However the GUI can be somewhat confusing as many of the options are not interesting for basic setups. The file locations given are valid for the standard Oracle DB Docker container. Other installations may differ.

For convenience while debugging, the below configurations add the encrypted TCP/IP with SSL connections and keep the existing default unencrypted TCP/IP connections.

Configure TNS Names

Reference: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/netrf/local-naming-parameters-in-tnsnames-ora-file.html

/u01/app/oracle/product/12.2.0/dbhome_1/admin/ORCL/tnsnames.ora

# tnsnames.ora Network Configuration File.
  
STL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCPS)(HOST = 0.0.0.0)(PORT = 2484))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = STL.STELIRIN)
    )
  )
  
ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL.STELLIRIN)
    )
  )

Configure SQL Net for TLS

Reference: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/netrf/parameters-for-the-sqlnet-ora-file.html

/u01/app/oracle/product/12.2.0/dbhome_1/admin/ORCL/sqlnet.ora

# sqlnet.ora Network Configuration File.
  
# Root for the Automatic Diagnostic Repository
ADR_BASE = /u01/app/oracle
  
# Allow both OS (BEQ) Authentication and TLS (TCPS) Authentication
SQLNET.AUTHENTICATION_SERVICES = (BEQ, TCPS)
  
# Allow TLSv1.0, TLSv1.1, and TLSv1.2
SSL_VERSION = (1.2 or 1.1 or 1.0)
  
# We use DB Client Authentication so we mustn't use TLS Client Authentication
SSL_CLIENT_AUTHENTICATION = FALSE
  
# Our wallet with our encryption keys
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /ORCL/wallet)
    )
  )
  
# The cipher suites allowed.
# Please ensure no insecure cyphers are added here.
SSL_CIPHER_SUITES = (
  SSL_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256,
  SSL_ECDHE_ECDSA_WITH_AES_128_CBC_SHA,
  SSL_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256,
  SSL_ECDHE_ECDSA_WITH_AES_256_CBC_SHA,
  SSL_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384,
  SSL_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384,
  SSL_RSA_WITH_AES_128_CBC_SHA256,
  SSL_RSA_WITH_AES_128_GCM_SHA256,
  SSL_RSA_WITH_AES_128_CBC_SHA,
  SSL_RSA_WITH_AES_256_CBC_SHA,
  SSL_RSA_WITH_AES_256_CBC_SHA256
)
  
# Time interval (in minutes) to check that client connections are active.
SQLNET.EXPIRE_TIME = 10

Configure Listeners

Reference: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/netrf/Oracle-Net-Listener-parameters-in-listener-ora-file.html

/u01/app/oracle/product/12.2.0/dbhome_1/admin/ORCL/listener.ora

# listener.ora Network Configuration File.
  
# Root for the Automatic Diagnostic Repository
ADR_BASE_LISTENER = /u01/app/oracle
  
# Allow TLSv1.0, TLSv1.1, and TLSv1.2
SSL_VERSION = (1.2 or 1.1 or 1.0)
  
# We use DB Client Authentication so we mustn't use TLS Client Authentication
SSL_CLIENT_AUTHENTICATION = FALSE
  
# Our wallet with our encryption keys
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /ORCL/wallet)
    )
  )
  
# Our listeners
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = 0.0.0.0)(PORT = 2484))
    )
  )

Create client app.truststore.jks

We identify the Oracle DB server by its public certificate. This certificate should be imported to its own trust store.

The trust store needs the full chain of trust; the server certificate and the CA certificate. Follow the steps in Truststores to create it.

Hint: The Oracle DB server certificate is just the one which you imported into the Oracle Wallet.

Create client app.keystore.jks

A private key & public certificate pair should be generated (New Keys & Certificates) and imported to Java keystores according to Keystores. Configure the app to connect to Oracle over TLS.

Configure the connection

The Connection Descriptor should look like this:

DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=127.0.0.1)(PORT=2484))(CONNECT_DATA=(SERVICE_NAME=STL.STELLIRIN))

If the app doesn’t provide a place to configure the keystores try:

-Djavax.net.ssl.trustStore=/path/to/app.truststore.jks
-Djavax.net.ssl.trustStorePassword=stellirin
-Djavax.net.ssl.keyStore=/path/to/app.keystore.jks
-Djavax.net.ssl.keyStorePassword=stellirin