Introduction

Oracle Cloud Infrastructure's Autonomous Database is a fully managed, preconfigured database environment. After provisioning, the number of CPU cores or the storage capacity of the database can be scaled at any time without impacting availability or performance. Autonomous Database handles creating the database, and the following maintenance tasks:

• Backing up the database
• Patching the database
• Upgrading the database
• Tuning the database

In this article, we will configure the Denodo Platform to access Oracle Autonomous Database via JDBC.

Connections to Autonomous Database are made either over the public Internet or using a private endpoint inside a Virtual Cloud Network (VCN) in a tenancy. When we specify a private endpoint configuration, this only allows traffic from the virtual cloud network and blocks access to the database from all public IPs or VCNs.

Many applications provide support for more than one connection type, but each type of connection to Oracle Autonomous Database uses certificate authentication and TCPS (Secure TCP) database connection using standard TLS 1.2. This ensures that there is no unauthorized access to the Oracle Autonomous Database and that communications between the client and server are fully encrypted and cannot be intercepted or altered.

Oracle Autonomous Database supports Mutual TLS (mTLS) connections. Both mTLS and TLS connections can be configured.

Using Mutual Transport Layer Security (mTLS), clients connect through a TCPS (Secure TCP) database connection using standard TLS 1.2 with a trusted client Certificate Authority (CA) certificate. With mutual authentication both the client application and Autonomous Database authenticate each other. Autonomous Database uses mTLS authentication by default.

Using Transport Layer Security (TLS), clients connect through a TCPS (Secure TCP) database connection using standard TLS 1.2 with a root Certificate Authority (CA) certificate.

When connecting with TLS authentication a wallet to secure the connection to the Autonomous Database instance is not needed. TLS authentication enables the client to verify the identity of the Autonomous Database service to provide secure communication.

TLS Authentication

TLS authentication is easily configurable as a standard Oracle TLS (SSL) connection. The Autonomous Database needs to be configured to receive TLS connections. For more information see JDBC Thin TLS Connections without a Wallet.

mTLS Authentication

Oracle Autonomous Database mandates a secure connection that uses Transport Layer Security (TLSv1.2). Java applications that use the JDBC Thin driver require either Oracle Wallet or a Java KeyStore (JKS). The wallet and keystore files are included in the client credentials zip file that is available by clicking on  DB Connection on the Oracle Cloud Infrastructure console.

Here we’ll show how to configure a secure JDBC Thin connection via Oracle Wallet.

To configure a JDBC Thin connection via Oracle Wallet, Oracle prerequisites for a JDBC Thin driver connection need to be met by the client application.

Copy the Oracle Autonomous Database wallet file into the Denodo Server

Download the Oracle Wallet from the Autonomous Database and transfer it to the Denodo server.

To download the wallet, go to the OCI console. Then, from the Autonomous DB home page, go to the “Database Connection” tab and then click on “Download Wallet”.

Transfer the wallet zip file to the Denodo server. The wallet file can be copied to any directory and will need to be unzipped in the same directory. Make sure that the user that runs the Denodo processes, has full permissions on this directory.

Modify the “DIRECTORY” entry of sqlnet.ora file included in the wallet file accordingly, similarly to the following example:

Oracle Driver Installation

Download the latest Oracle JDBC driver. The driver will include the following libraries:

• oraclepki.jar
• osdt_core.jar
• osdt_cert.jar

Create a new driver following the instructions here: Importing a JDBC Driver for a Database that is not Listed.

Enter oracle-21c-custom as Custom version.

Select the files that you have downloaded and also the files under the <DENODO_HOME>/lib/extensions/jdbc-drivers/oracle-21c folder of the Denodo server.

Create a Denodo JDBC data source

From the Denodo Virtual Dataport Administration Tool follow these steps:

1. Create a new Denodo data source by selecting File > New > Data source > JDBC

See Importing JDBC Sources for more information.

2. Configure the JDBC data source:

1. Name: enter any name, for instance, ds_asw.
2. Database adapter property: Oracle (with the version, e.g. 19c and higher) 
3. Driver class path property: use the value entered when creating the new driver (oracle-21c-custom).
4. Driver Class property: use default value provided.
5. Database URI property: jdbc:oracle:thin:@service_name?TNS_ADMIN=directory_with_wallet_file
6. Login properties: provide username and password.

Save the configuration and press the “Test Connection” button

Now Denodo Base Views can be created from Oracle Autonomous Database tables as needed.

1. Click on “Create Base view”.

2. Browse the Oracle catalog and schemas to select the table to be imported.

3. Create the corresponding base view.

4. Save it.

5. Execute it by going to the Execution Panel and then click on Execute.

Conclusion

Denodo can easily connect to different flavors of  Oracle databases with the standard JDBC thin driver already provided with Denodo. We provide here a simple way to do the same with Oracle Autonomous Database, leveraging the latest Oracle JDBC driver and the needed Wallet file for mTLS authentication.