Configuring Oracle database to use SSL for Data Warehouse

You must enable SSL for the Oracle database before setting up the CDP Private Cloud Data Services. Enabling SSL establishes a secure channel between the client (CDP-side) and the server (Oracle database server).

To enable SSL, you need to configure SSL only on the server side. The client-side configurations are present in CDP.

  1. SSH into the Oracle database server host.
  2. Change to the "oracle" user as follows:
    sudo -su oracle
  3. Append the location of ORACLE_HOME to the PATH environment variable by running the following commands:
    export ORACLE_HOME=/opt/oracle/product/19c/dbhome_1
    export PATH=${PATH}:${ORACLE_HOME}/bin
  4. Create an auto-login wallet by running the following command:
    orapki wallet create -wallet /opt/oracle/product/19c/dbhome_1/wallet -auto_login
    An auto-login wallet uses SSL's single sign-on functionality. The users do not need to specify password each time they open the wallet.
  5. Add a self-signed certificate to this wallet by running the following command:
    orapki wallet add -wallet /opt/oracle/product/19c/dbhome_1/wallet -dn "CN=server" -keysize 4096 -self_signed -validity 365
  6. Export the certificate from the Oracle wallet by running the following command:
    orapki wallet export -wallet /opt/oracle/product/19c/dbhome_1/wallet -dn "CN=server" -cert server_ca.cert
    This exports a certificate with the subject's distinguished name (-dn) (CN=server) from a wallet to the file that is specified by -cert (server_ca.cert).
  7. Add the following lines to the /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora configuration file:
    SSL_CLIENT_AUTHENTICATION = FALSE
    WALLET_LOCATION =
      (SOURCE =
        (METHOD = FILE)
        (METHOD_DATA =
          (DIRECTORY = /opt/oracle/product/19c/dbhome_1/wallet)
        )
      )
    Register a new address in LISTENER:
    (ADDRESS = (PROTOCOL = TCPS)(HOST = [***HOST***])(PORT = 2484))
  8. Add the following lines to the /opt/oracle/product/19c/dbhome_1/network/admin/sqlnet.ora profile configuration file:
    SSL_CLIENT_AUTHENTICATION = FALSE
    WALLET_LOCATION =
      (SOURCE =
        (METHOD = FILE)
        (METHOD_DATA =
          (DIRECTORY = /opt/oracle/product/19c/dbhome_1/wallet)
        )
      )
  9. Add the following lines to the /opt/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora configuration file:
    ORCLPDB1_SSL =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCPS)(HOST = [***HOST***])(PORT = 2484))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = ORCLPDB1)
          )
          (SECURITY =
            (MY_WALLET_DIRECTORY = /opt/oracle/product/19c/dbhome_1/wallet)
          )
        )
  10. Restart the listener by running the following commands:
    lsnrctl stop
    lsnrctl start
  11. Copy the content of the certificate that you exported earlier and add it to the keystore on the base cluster instances.
    Paste the copied content to the ca-cert.pem file.
  12. Fetch the keystore password from the /etc/hadoop/conf/ssl-client.xml file by running the following command:
    /usr/java/default/bin/keytool -importcert -alias oracle -file ca-cert.pem -keystore /var/lib/cloudera-scm-agent/agent-cert/cm-auto-global_truststore.jks -storetype jks -noprompt -storepass [***PASSWORD***]
  13. Log in to Cloudera Manager as an Administrator.
  14. Go to Clusters > Hive service > Configuration > Hive Metastore Server Advanced Configuration Snippet (Safety Valve) for hive-site.xml and click to add the following:
    • Name: javax.jdo.option.ConnectionURL
    • Value: jdbc:oracle:thin:@tcps://[***BASE_CLUSTER_HOSTNAME***]:2484/ORCLPDB1?javax.net.ssl.trustStore=/var/lib/cloudera-scm-agent/agent-cert/cm-auto-global_truststore.jks&javax.net.ssl.trustStorePassword=[***PASSWORD***]&oracle.net.ssl_server_dn_match=false
  15. Change the port to 2484 in the Hive Metastore Database Port field.
  16. Click Save Changes.
  17. Restart the Hive service.