Starting from version 23.3, the JDBC Driver can be extended through the Java Service Provider Interface (SPI).

Amongst other extensions, this allows third-party entities to provide connection properties (including sensitive information such as passwords and wallets), simplifying configuration, and secret management.

Follow the diagram below for the suggested approach:

1. Administrators Create / Rotate Credentials and Connection Properties for a Database: they get stored as key value pair in a Configuration Service or Secrets in a Vault Service (such as Azure App Config and Azure Key Vault).
2. Administrators Create / Update these Credentials in the target Database.
3. Administrators Informs to Application Developers and Devops the Provider's URL (which come in the form of jdbc:oracle:thin:@config-<provider> (for example jdbc:oracle:thin:@config-azure://myAppConfig?key=sales_app1&label=development) for the JDBC Driver to be able to locate the Configuration.
4. The JDBC Driver detects an external provider configuration by looking at the URL and retrieves the configuration and credentials from the external service.
5. The JDBC Driver connects to the Database using the retrieved values.

Advantages of this approach

The advanges of this approach are the following:

• Only Administrators can Create / Rotate / Delete Users and Secrets.
• Secrets, Credentials and Database Configuration can be updated with no changes in the Application.
• Secrets are encrypted at rest, on services designed for this purpose.
• Fine tune Database Configuration parameters (time-outs, auto-commit, etc.) are configured only by Administrators in a centralized way.
• The Database connection string is shared across all applications: Java, .Net, Python, and can be integrated with managed services such as OCI DBTools.

Provider Types

Besides allowing third parties and users to build their own providers, Oracle is distributing pre-built providers. Some of them are built-in the JDBC Driver (no extra jar required) and also external ones. These extensions are open-source on GitHub: https://github.com/oracle/ojdbc-extensions and the artifacts are available on MavenCentral. Please refer to the project for code samples and maven coordinates.

• External
  • Azure App Config (with optional reference to Azure Key Vault for secrets).
  • Azure Key Vault.
  • OCI Object Storage (with optional reference to OCI Vault for secrets).
  • OCI Vault.
  • OCI Database Tools Connections (with optional reference to OCI Vault for secrets).
• Built-In
  • HTTPS Provider
  • File Provider

Azure Based Providers

Azure App Config (with optional reference to Azure Key Vault for secrets)

The Oracle DataSource uses a new prefix jdbc:oracle:thin:@config-azure:// to be able to identify that the configuration parameters should be loaded using Azure App Configuration. Users only need to indicate the App Config's name, a prefix for the key-names and the Label (both optionally) with the following syntax:

jdbc:oracle:thin:@config-azure://{appconfig-name}[?key=prefix&label=value&option1=value1&option2=value2...]

This will allow existing applications to use this feature transparently, the only requirements would be to include the provider's jar in the classpath (or the reference in the pom file) and replace the URL with the required values.

With this approach we attach values of a DataSource to a key (prefix of multiple keys in App Config) and a label (both optionally, otherwise it will attach all the values with no labels or prefixes in the configuration).

There are 4 fixed values that are looked at with this key/label.

• connect_descriptor (required)
• user (optional)
• password (optional)
• wallet_location (optional)

The rest are dependent on the JDBC Driver, adding the jdbc/ prefix. We use reflection to retrieve multiple key-values pairs for a specific label and key that are applied to a DataSource. The key values are the properties (constant keys) defined in the OracleConnection interface.

As an example, a datasource URL with the value jdbc:oracle:thin:@config-azure://myappconfig?key=/sales_app1/&label=dev in an App Config name 'myappconfig' with the values below will generate an OracleDatasource with (note the prefix '/sales_app1/' and label 'dev'):

Note: in the JDBC Driver we concatenate the connect descriptor with 'jdbc:oracle:thin:@' internally to set the URL. This is to be able to share the property with other driver implementations.

For this example we copied the "Connection String" that is available in the "DB Connection" tab of the tested Autonomous Database.

Please also note that in App Config Explorer we can have multiple Keys and Labels, for this URL in the example only the ones with the /sales_app1/ prefix and label dev will be picked by the driver for this Datasource.

OCI Based Providers

OCI Object Storage (with optional reference to OCI Vault for secrets)

In this case, the configuration is stored in JSON format (just like with Azure, the format is common to all the clients) where the provider is identified by 'ociobject' provider in the URL: Eg: jdbc:oracle:thin:@config-ociobject://objectstorage.us-phoenix-1.oci.customer-oci.com/n/mytenancy/b/bucket1/o/payload_ojdbc_objectstorage.json

The only required parameter is the object's URL Path (URI). Users can retrieve this value by navigating in the OCI Web Console from: Object Storage / Buckets / Object → Object Details. A value similar to https://objectstorage.us-phoenix-1.oci.customer-oci.com/n/mytenancy/b/bucket1/o/payload_ojdbc_objectstorage.json gets selected.

Following the approach set in for the Azure App Config provider, the spec follows these rules:

There are 4 fixed values that are looked with this prefix/label.

• connect_descriptor (required)
• user (optional)
• password (optional)
• wallet_location (optional)

The rest are dependent on the driver, in our case /jdbc. We use reflection to retrieve multiple key-values pairs for an specific label and prefix that are applied to a DataSource. The key values are the properties (constant keys) defined in the OracleConnection interface.

The password and wallet_location are references to a Vault provider (it can also be Azure Key Vault, using 'vault-azure' as its type.

OCI Database Tools Connections (with optional reference to OCI Vault for secrets)

The Database Tools Connections service is a managed service that can be used to configure "connections" to a database (ADB or mysql). This "connection" object can then be used in the sql worksheet in the web console. It can also be used as a directory of database connection configurations and a provider will allow access to these configurations. Each configuration has an OCID that is used to identify which connection is used. It contains a connectionString, userName, userPassword, keyStores and advancedProperties that, today, are limited to JDBC properties but will soon allow a free format that will work for .Net and OCI. 

JDBC URL that uses the OCI DBTools provider:

jdbc:oracle:thin:@config-ocidbtools://ocid1.databasetoolsconnection.oc1.phx.ama ...

Built-In Providers

The JDBC Driver includes HTTPS and File providers (no extra jar required) based on the same JSON Schema defined in the previous chapter for OCI Object Storage. Please refer to the oracle.jdbc.spi.OracleConfigurationProvider Javadoc for more information.

HTTPS

The JSON configuration document may be provided through an HTTPS endpoint (for example ORDS). HTTP is not supported. Because the JSON configuration document (specified above) may contain multiple aliases, the URL must end up with the alias name that needs to be loaded: https://<URL>/aliasname.

Access to this configuration can be protected by IP ACL, TCPS and Basic HTTP Auth.

The JDBC URL would follow this format: jdbc:oracle:thin:@config-https://<URL>[?key=name&option1=value1...].

Client authentication can be configured with basic HTTP authentication over https using wallet through props.

jdbc:oracle:thin:@config-https://confighost.mydomain.com/oracleconfig?key=name&authentication=BASIC_AUTH&wallet_location=/path/to/wallet

The authentication option is used to make the https config provider use basic HTTP authentication to retrieve the JSON configuration document. Note that the https provider distributed by Oracle will support Basic HTTP Authentication.

File

The same JSON configuration document can also be provided through the file system.

Access to this configuration can be protected by file system protection.

The JDBC URL would follow this format: jdbc:oracle:thin:@config-file://{path-to-file}[?option list] 

The {path-to-file} parameter takes the same rules the java.io.File (for the Java implementation).

The option list includes an attribute to indicate the connection key name: jdbc:oracle:thin:@config-file://path/to/file.json?key=sales_app1

Build your own

Building your own provider is a very simple task in terms on how to integrate it with the JDBC Driver.

The only task you need to do is to implement the interface oracle.jdbc.spi.OracleConfigurationProvider (this interface is in the JDBC Driver) and provide an implementation for:

• Properties getConnectionProperties(String parameters): in this method you will return to the Driver all the Connection Properties you want the driver to use (including, but not limited to, user, password, connection string, timeouts, etc.). You can use the incoming String as the parameters for your business logic.
• String getType(): in this method you will tell the driver how to load your provider. Any url that follows jdbc:oracle:thin:@config-<your-type>:.... will be picked up by the Driver using SPI and the rest of the String after the semi colons will be sent as the parameters for the getConnectionProperties.
• Lastly, your packaged provider need to follow the SPI specification, adding in your jar a file named oracle.jdbc.spi.OracleConfigurationProvider containing the name of your implementation (in below case demo.MyProvider).

For example

Can be used as: