Oracle JDBC Config Provider for OCI Database Tools Service

With Oracle's Java Database Connectivity (JDBC) driver starting with 23ai it is now possible to provide the configuration of a connection to Oracle Database using the opaque identifier of a Database Tools connection. This means we can abstract away the configuration of database connection details by using the Database Tools service in Oracle Cloud Infrastructure (OCI). Cool!

tl/dr: here is snippet of where the post below is headed. If you want to skip the read and just try it out, the coordinates of the artifacts are mentioned later in the post and here is a snippet to get you started.

// the opaque identifier of a Database Tools connection to be used at runtime
var id = "ocid1.databasetoolsconnection.oc1.phx.yourconnectionocid";
        
// a JDBC url using the new config-ocidbtools provider
var url = "jdbc:oracle:thin:@config-ocidbtools://" + id;

try (var conn = DriverManager.getConnection(url);) {
    // ...voila!
}

Getting started

Let's start with a basic connection scenario.

In this example we will connect to an ADB instance with public access enabled and for the sake of brevity, I have to make some assumptions:

1. A working OCI CLI configuration with a DEFAULT profile
2. The ADB instance to which you will connect already exists and has public access enabled (which will require mTLS to connect)
3. A working Database Tools connection is set up (which implies a Vault with secrets is already set up also)
4. Policies, by principal of least privilege, unless you are the tenancy administrator. (you will not have access to OCI resources without policies!)

Item one above is only important for the example in this post because the OCI provider for JDBC connection configuration is going to use the OCI SDK to make calls to OCI on behalf of whatever profile you configure (DEFAULT,... by default.)

In a deployed application this would probably be instance principal-based authentication, but for development and testing locally you need something already set up that allows your code to authenticate with OCI.

If you have an always-free tenancy or you are starting from zero here are a couple of related links that will probably help. (Follow the OCI CLI setup part at a minimum)

Command Line Interface (CLI)

The command line interface (CLI) is a tool that enables you to work with Oracle Cloud Infrastructure objects and services at a command line.

You can learn more about other authentication methods here:

OCI SDK Authentication Methods

sdk authentication methods intro

This is probably obvious from everything stated above but for the sake of clarity: we need a working Database Tools Connection.

Get connected to the database

The connection resource in OCI defines the Oracle Database connection string, the database username, as well as opaque identifiers of the encrypted secrets in a vault (the password and SSO wallet).

If this is your first time using Database Tools you can find more information about it in the following post.

Database Tools Connections

TL/DR: if you are an Oracle Cloud Infrastructure (OCI) customer running Oracle Database or MySQL in OCI, use Database Tools (DBTools) private endpoints and connections to simplify database access in the cloud. DBTools connections support a number of use cases, for example: * They are great for developers, consultants, analysts,

ICODEALOTJustin Biard

Here are some properties relevant to this example that are defined in a Database Tools connection resource.

$ oci dbtools connection get --connection-id ocid1.databasetoolsconnection.oc1.phx.yourconnectionocid
{
  "data": {
    "type": "ORACLE_DATABASE",
    "user-name": "<your-database-username>",
    "user-password": {
      "secret-id": "ocid1.vaultsecret.oc1.phx.yoursecretocid1",
      "value-type": "SECRETID"
    },
    "connection-string": "<your-adb-connection-string>",
    "id": "ocid1.databasetoolsconnection.oc1.phx.yourconnectionocid",
    "key-stores": [
      {
        "key-store-content": {
          "secret-id": "ocid1.vaultsecret.oc1.phx.yoursecretocid2",
          "value-type": "SECRETID"
        },
        "key-store-type": "SSO"
      }
    ],
    ...
  }
}

Once we have a connection set up we can fire up a Java project and add dependencies. (I am using Maven for this example) The coordinates of the dependencies are shown below with versions that were relevant at the time of this writing. You will probably have other dependencies but this example is about using ojdbc with ojdbc-provider-oci.

    <dependencies>
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc17</artifactId>
            <version>23.8.0.25.04</version>
        </dependency>
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc-provider-oci</artifactId>
            <version>1.0.4</version>
        </dependency>
    </dependencies>

Although I show specific versions above, you should uptake whatever latest version of Oracle JDBC 23ai makes sense and update dependencies according to your standard bill of material (BOM) update procedures.

Here is a very simple example using plain JDBC calls to get connected:

import oracle.jdbc.OracleConnection;

import java.sql.DriverManager;

public class ConnectionExample {
    public static void main(String[] args) throws Exception {
        // the opaque ID of a Database Tools connection to be used at runtime
        var id = "ocid1.databasetoolsconnection.oc1.phx.yourconnectionocid";

        // a JDBC url using the new config-ocidbtools provider
        var url = "jdbc:oracle:thin:@config-ocidbtools://" + id;

        // nothing should be hard-coded and we are using mTLS
        try (var conn = (OracleConnection) DriverManager.getConnection(url)) {
            System.out.println("Connected to the database");
            ...
            // "Connection URL: " + conn.getMetaData().getURL());
            // "Connection User: " + conn.getMetaData().getUserName());
            // "Connection TLS: " + conn.getEncryptionAlgorithmName());
        }
    }
}

Casting the connection here is academic. If you don't need Oracle-type interface methods you can remove the cast to (OracleConnection). Inspecting attributes of the connection established above provides details to the application such as:

Connected to the database
Connection URL: jdbc:oracle:thin:@(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.us-phoenix-1.oraclecloud.com))(connect_data=(service_name=[myadbinstancehere]_low.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))
Connection User: ADMIN
Connection TNS: TLS_RSA_WITH_AES_256_GCM_SHA384

And here is an example of how this connection data is stored in OCI:

Sample code for the above can be found here:

examples/jdbc-connection-demo at main · icodealot/examples

Example snippets for reference based on blog posts at https://icodealot.com - icodealot/examples

GitHubicodealot

Here are some additional links that may be helpful along the way:

• Oracle JDBC Extensions on Github
• Oracle JDBC Service Provider Extensions
• Common parameters for the OCI provider (for authenticating with other OCI principal types, or specifying OCI configuration profiles)

Hopefully the example above helps you get started with testing your own cloud-integrated solutions using Oracle JDBC 23ai and the OCI config provider.

Thanks for reading and I will see you next time.

Cheers!