Introduction
Starting in version 23.4, the Oracle JDBC Driver can be extended through a Java Service Provider Interface (SPI). This SPI allows for providing the URL, user, password, and JDBC parameters from an external source. There is an open-source project at open source project for “Oracle JDBC Driver Extensions” that provides some already-written “providers” that use the SPI to read the parameters from external vaults. It is described in this blog article. There are currently providers for OCI vault, Azure vault, and Open Telemetry.
By using the vault, it’s possible not only to remove the credential information from a local schema file for security reasons. It also allows for the information to be stored in a single location for all applications that use the database. Some applications use hundreds of datasources so this will be a huge advantage. It also centralizes where the database information can be updated with no change to the application.
There are a lot of steps here to get the database up and running, getting set up with OCI, and then using this feature with WebLogic Server. This blog tries to capture all of the steps in one place with references to other links with more information.
Version Support
Driver Version
The 23.3 Oracle JDBC driver and 1.0.0 JDBC Extensions are a “developer” release and should NOT be used in production. This combination is not supported (no patch will be provided). Note that between 23.3 and 23.4, there is a major compatibility change – the URL changes from “jdbc:oracle:thin:@config-ocivault:” to “jdbc:oracle:thin:@config-ocivault://” that will affect all datasource definitions using this feature.
The first production release for using the Oracle JDBC Driver Extensions is 23.4 driver and the 1.0.1 extensions. See Upgrade WebLogic Server to use 23.x JDBC Driver for information on upgrading to use the 23.x driver with WebLogic Server. The extension files are available at https://github.com/oracle/ojdbc-extensions/releases/tag/v1.0.1.
Jersey/WebLogic Server Version
The OCI SDK requires the use of Jersey RESTful Web Services 2.x Framework. When testing, it was found that the earliest version of Jersey that would work is 2.29 (due to the introduction of org.glassfish.jersey.jackson.internal.jackson.jaxrs.json.JacksonJsonProvider). WLS 14.1.1 ships with Jersey 2.29 and this feature works on WLS 14.1.1.
Unfortunately, WLS 12.2.1.4.0 ships with Jersey version 2.22 and this feature will not work on WLS 12.2.1.4.0 or earlier.
Getting Started with OCI
To get started using the OCI vault, it’s necessary to get started using OCI. Start by getting an OCI account at https://www.oracle.com/cloud/sign-in.html . Provide a country/territory, first name, last name, and email address and verify your email address. You will need to select Corporate/Individual (select Corporate for a work-related account) and select a Home Region (pick the closest one so it might have less network delay). You will need to set up a password meeting the criteria and 2FA must be set up using Oracle Authenticator.
Once you get into the account, you will need to set up configuration for access on your client machine. Start by installing the OCI Command Line Interface (CLI). See https://docs.oracle.com/en-us/iaas/Content/API/SDKDocs/cliinstall.htm. For example, on OL7, use
sudo yum install python36-oci-cli
and on OL8 use
sudo dnf -y install oraclelinux-developer-release-el8
sudo dnf install python36-oci-cli
To create the configuration file, run
oci setup config
Use the default value of ~/.oci/config for the file name. To get the User OCID, go to the OCI dashboard, in the upper left corner click on Profile, select My Profile, and Copy the OCID. To get the Tenancy OCID, click on Profile, select Tenancy, and Copy the OCID. Select the region associated with the account (there is a list of regions and associated numbers). Create a new public/private key pair. That should be enough to create the configuration file. One additional step is to upload the public key – see https://docs.oracle.com/en-us/iaas/Content/API/Concepts/apisigningkey.htm#How2 (go to My Profile, select API Keys on left, Add API key, select Paste a public key, and get the public key value by doing cat ~/.oci/oci_api_key_public.pem).
OJDBC Extension Usage
The OJDBC Extension feature stores a URL string, user name, encrypted password, and optionally JDBC parameters in an OCI secret within an OCI vault. You can use an existing database or create a new OCI database. See Creating an Autonomous Transaction Processing (ATP) Database and JSON ATP Database Use with WebLogic server for information on creating a cloud database. While these blog articles go into not only how to create but also use the databases, the critical information related to using the vault is 1) Create the database 2) Use SQL to create a user with a password and minimally grant CREATE SESSION privilege, and 3) get the URL (click on Database Connection, look for the <databasename>_tp TNS name, and click on Copy). You will need the URL to create the secret (there doesn’t seem to be a mechanized way to create the secret). You don’t need to have the wallet and TNS_ADMIN set on the local client (it will be necessary to create a wallet simply for storing it in a secret).
The next step is to create a vault. Select the menu from the upper left corner of the OCI dashboard, select Identity & Security, and select Vault. Select Create Vault, enter a name, and Create Vault. Click on the vault name to enter the vault. Create a Master Encryption Key by selecting Create Key, entering a name, and clicking on Create Key.
The next step is to create a secret for the user password. Click on Secrets in the left margin. Click on Create Secret, give it a name, and click on Manual Secret Generation, paste the password enclosed in double quotes into the text box, and click Create Secret. Click on the name of the secret and click on Copy for the OCID.
To avoid requiring the wallet on the file system and TNS_ADMIN set on every computer that references the dataource, the wallet should also be stored in a secret. First, create and download a wallet (you can throw it away after this step), and unzip it. Next get the base-64 string for the wallet by running
base64 cwallet.sso > output
Edit the output so that it’s a single string with no spaces. Now create a secret with this string by clicking on Secrets, Create Secret, give it a name and click on Manual Secret Generation. It’s critical to change the Secret Type Template to Base64. Then paste the base64 output from the cwallet.sso into the text box. Click Create Secret, click on the name of the secret and save the OCID for the next step.
Now you can create your secret that will be referenced by the driver. Click on Secrets in the left margin. Click on Create Secret, give it a name, and click on Manual Secret Generation. You need to manually create the text and you should probably do it outside of the OCI console. The format is described at https://docs.oracle.com/en/database/oracle/oracle-database/23/jajdb/oracle/jdbc/spi/OracleConfigurationProvider.html
The following is an example. The host and service name come from the TNS name that you saved from the datasource. The user name is the user that you created in the database. The secrets for the password and wallet_location the were just created above in the vault. To be clear, we have a secret that points to two additional secrets.
{
“connect_descriptor”: “(description=(retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.ca-toronto-1.oraclecloud.com))(connect_data=(service_name=myvalue_demoatp_tp.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))”,
“user”: “DEMO1”,
“password”: {
“type”: “ocivault”,
“value”: “ocid1.vaultsecret.oc1.ca-toronto-1.myvaluedngsnoaajxsjica4vqggkcq2f4jxpillfc6nv3x55fs7btedglka”
},
“wallet_location”: {
“type”: “ocivault”,
“value”: “ocid1.vaultsecret.oc1.ca-toronto-1.myvaluedngsnoaaftfj2ll37ex67wru3wqhqsv2q6x2x67epegzl2gvkm6a”
},
“jdbc”: {
“oracle.jdbc.ReadTimeout”: 1006,
“defaultRowPrefetch”: 20
}
}
Paste the Json string into the Secret Contents text box for the manual secret and click on Create Secret. Click on the name of secret that you just created and click Copy next to the OCID. You will use the OCID of the secret to generate the URL of the form: jdbc:oracle:thin:@config-ocivault://OCID-FOR-SECRET, for example
jdbc:oracle:thin:@config-ocivault://ocid1.vaultsecret.oc1.ca-toronto-1.myvaluengsnoaabnyfwwxnmz67flenjpy26ikuylyui45n6p3xfx76h3dq
If for some reason you need to change the secret value, you will need to click on the name of the secret, click on Create Secret Version to create a new version, enter the text, and click on Create Secret Version. Note that when you create a new secret version, it doesn’t change the OCID so you don’t need to update the OCID wherever it is used.
WebLogic Datasource Configuration
Now you are ready to use the URL to create a datasource.
A JDBC System Module exists as a JDBC*.xml file under config/jdbc and it is configured in config/config.xml and deployed at server startup. This is the most popular approach that is used by customers and the WebLogic console has detailed wizards to help in generating the schema. The JDBC System module is available to the entire “system” – to all applications in the domain.
This is an online WLST script to create the JDBC System Module.
$ cat online.py
connect(“weblogic”,”welcome1″)
edit()
dsname=”ds0″
server=”myserver”
cd(“Servers/”+server)
target=cmo
cd(“../..”)
startEdit()
jdbcSR = create(dsname,”JDBCSystemResource”)
theJDBCResource = jdbcSR.getJDBCResource()
theJDBCResource.setName(dsname)
theJDBCResource.setDatasourceType(“GENERIC”)
connectionPoolParams = theJDBCResource.getJDBCConnectionPoolParams()
connectionPoolParams.setStatementCacheSize(0)
connectionPoolParams.setTestTableName(“SQL ISVALID”)
dsParams = theJDBCResource.getJDBCDataSourceParams()
dsParams.addJNDIName(dsname)
dsParams.setGlobalTransactionsProtocol(“OnePhaseCommit”)
driverParams = theJDBCResource.getJDBCDriverParams()
driverParams.setUrl(“jdbc:oracle:thin:@config-ocivault://ocid1.vaultsecret.oc1.ca-toronto-1.myvaluengsnoaap6niso6s5mcjv45ekgwxc3vkgpku6nh3j3qegqdcldha”)
driverParams.setDriverName(“oracle.jdbc.replay.OracleDataSourceImpl”)
driverProperties = driverParams.getProperties()
proper = driverProperties.createProperty(“oracle.jdbc.fanEnabled”)
proper.setValue(“false”)
jdbcSR.addTarget(target)
save()
activate(block=”true”)
java weblogic.WLST online.py
This can also be created using REST. See ATP Database use with WebLogic Server for an example.
The following is what a JDBC System module looks like. The differences from a normal system module is that the actual database URL is replaced with a “config-ocivault” URL format to point to an OCI secret, and there is no user or password specified. If this is created with the administration console, it doesn’t know about the new URL format so it’s necessary to provide the requested information (host, port, leave the user and password blank) and then paste in the new URL format into a later screen in the creation process.
<?xml version=’1.0′ encoding=’UTF-8′?>
<jdbc-data-source xmlns=http://xmlns.oracle.com/weblogic/jdbc-data-source xmlns:sec=http://xmlns.oracle.com/weblogic/security xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance xmlns:wls=http://xmlns.oracle.com/weblogic/security/wls xsi:schemaLocation=http://xmlns.oracle.com/weblogic/jdbc-data-source http://xmlns.oracle.com/weblogic/jdbc-data-source/1.5/jdbc-data-source.xsd>
<name>ds0</name>
<datasource-type>GENERIC</datasource-type>
<jdbc-driver-params>
<url>jdbc:oracle:thin:@config-ocivault://ocid1.vaultsecret.oc1.ca-toronto-1.myvaluedngsnoaap6niso6s5mcjv45ekgwxc3vkgpku6nh3j3qegqdcldha</url>
<driver-name>oracle.jdbc.replay.OracleDataSourceImpl</driver-name>
<properties>
<property>
<name>oracle.jdbc.fanEnabled</name>
<value>false</value>
</property>
</properties>
</jdbc-driver-params>
<jdbc-connection-pool-params>
<test-table-name>SQL ISVALID</test-table-name>
<statement-cache-size>0</statement-cache-size>
</jdbc-connection-pool-params>
<jdbc-data-source-params>
<jndi-name>ds0</jndi-name>
<global-transactions-protocol>OnePhaseCommit</global-transactions-protocol>
</jdbc-data-source-params>
</jdbc-data-source>
The datasource descriptor is referenced in config/config.xml as follows:
<jdbc-system-resource>
<name>ds0</name>
<target>myserver</target>
<descriptor-file-name>jdbc/ds0-jdbc.xml</descriptor-file-name>
</jdbc-system-resource>
This datasource can be used in any application code with the following.
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
private DataSource ds = null;
try {
InitialContext ic = new InitialContext();
this.ds = (DataSource)ic.lookup(“ds0”); // name from schema
} catch (NamingException e) {
e.printStackTrace();
}
A JDBC Application Module exists in a Java EE Application in a web.xml schema within a “.war” file or in an application.xml schema within an “.ear” file, and deployed after the server is running. The datasource is scoped to the application in which it is defined. The following is an example of an equivalent web.xml for the datasource above. Note that there are Java EE standard attributes and also WebLogic-proprietary attributes that start with “weblogic.” to allow for full support of all WebLogic JDBC datasources. There is no WebLogic console support for generating this schema (use your favorite XML editor).
<web-app xmlns=http://java.sun.com/xml/ns/javaee
xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance
xsi:schemaLocation=http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd
version=”3.0″>
<data-source>
<name>java:/comp/env/datasources/ocivault</name>
<class-name>oracle.jdbc.replay.OracleDataSourceImpl</class-name>
<url>jdbc:oracle:thin:@config-ocivault://ocid1.vaultsecret.oc1.ca-toronto-1.myvaluedngsnoaap6niso6s5mcjv45ekgwxc3vkgpku6nh3j3qegqdcldha</url>
<property><name>oracle.jdbc.fanEnabled</name><value>false</value></property>
<property><name>weblogic.TestConnectionsOnReserve</name><value>true</value></property>
<property><name>weblogic.DatasourceType</name><value>GENERIC</value></property>
<property><name>weblogic.TestTableName</name><value>SQL ISVALID</value></property>
<property><name>weblogic.Scope</name><value>Global</value></property>
<property><name>weblogic.GlobalTransactionsProtocol</name><value>OnePhaseCommit</value></property>
<property><name>weblogic.FanEnabled</name><value>false</value></property>
</data-source>
</web-app>
This datasource can be used in a servlet (the datasource is scoped to the application within the .war file) with the following code:
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import oracle.jdbc.pool.OracleDataSource;
@WebServlet({“/SampleOCIVault”})
public class Servlet extends HttpServlet {
private DataSource ds = null;
public void init() throws ServletException {
try {
InitialContext ic = new InitialContext();
this.ds = (DataSource)ic.lookup(“java:comp/env/datasources/ocivault”);
} catch (NamingException e) {
e.printStackTrace();
}
}
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
try {
Connection conn = this.ds.getConnection();
try {
Statement st = conn.createStatement();
try {
PrintWriter pw = response.getWriter();
ResultSet rs = st.executeQuery(“select * from dual”);
if (rs.next())
pw.println(“select ok -> ” + rs.getString(1));
pw.println(“Served from servlet: ” + request.getContextPath() + “/SampleOCIVault”);
if (st != null)
st.close();
} catch (Throwable throwable) {
if (st != null)
try {
st.close();
} catch (Throwable throwable1) {
throwable.addSuppressed(throwable1);
}
throw throwable;
}
if (conn != null)
conn.close();
} catch (Throwable throwable) {
throwable.addSuppressed(throwable1);
}
throw throwable;
}
if (conn != null)
conn.close();
} catch (Throwable throwable) {
if (conn != null)
try {
conn.close();
} catch (Throwable throwable1) {
throwable.addSuppressed(throwable1);
}
throw throwable;
}
} catch (SQLException e) {
e.printStackTrace();
response.sendError(888, e.getMessage());
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
The servlet would be compiled and stored in the war file at WEB-INF/classes/Servlet.class and the descriptor would be stored at WEB-INF/web.xml. For testing, the war file can be copied to the autodeploy subdirectory under the domain.
Runtime for OCI SDK and OJDBC Extensions
Now that we have all of the pieces ready, we need to get the runtime assembled.
The base is the use of the OCI SDK. Testing was done on version 3.41.2 of the OCI SDK. These are the jar files (get them from Maven Central, e.g., https://repo1.maven.org/maven2/):
com/oracle/oci/sdk/oci-java-sdk-common-httpclient-jersey/3.41.2/oci-java-sdk-common-httpclient-jersey-3.41.2.jar
com/oracle/oci/sdk/oci-java-sdk-circuitbreaker/3.41.2/oci-java-sdk-circuitbreaker-3.41.2.jar
com/oracle/oci/sdk/oci-java-sdk-common/3.41.2/oci-java-sdk-common-3.41.2.jar
com/oracle/oci/sdk/oci-java-sdk-common-httpclient/3.41.2/oci-java-sdk-common-httpclient-3.41.2.jar
com/oracle/oci/sdk/oci-java-sdk-common-httpclient-jersey/3.41.2/oci-java-sdk-common-httpclient-jersey-3.41.2.jar
com/oracle/oci/sdk/oci-java-sdk-database/3.41.2/oci-java-sdk-database-3.41.2.jar
com/oracle/oci/sdk/oci-java-sdk-databasetools/3.41.2/oci-java-sdk-databasetools-3.41.2.jar
com/oracle/oci/sdk/oci-java-sdk-identitydataplane/3.41.2/oci-java-sdk-identitydataplane-3.41.2.jar
com/oracle/oci/sdk/oci-java-sdk-objectstorage/3.41.2/oci-java-sdk-objectstorage-3.41.2.jar
com/oracle/oci/sdk/oci-java-sdk-objectstorage-extensions/3.41.2/oci-java-sdk-objectstorage-extensions-3.41.2.jar
com/oracle/oci/sdk/oci-java-sdk-objectstorage-generated/3.41.2/oci-java-sdk-objectstorage-generated-3.41.2.jar
com/oracle/oci/sdk/oci-java-sdk-secrets/3.41.2/oci-java-sdk-secrets-3.41.2.jar
com/oracle/oci/sdk/oci-java-sdk-workrequests/3.41.2/oci-java-sdk-workrequests-3.41.2.jar
There are many dependencies for these jar files and most of them overlap with jar files already provided by WLS (Jersey, Jackson, asm, etc.). The following jar files are not in the WLS classpath:
io/github/resilience4j/resilience4j-circuitbreaker/1.7.1/resilience4j-circuitbreaker-1.7.1.jar
io/github/resilience4j/resilience4j-core/1.7.1/resilience4j-core-1.7.1.jar
org/slf4j/slf4j-api/1.7.36/slf4j-api-1.7.36.jar
org/slf4j/slf4j-simple/1.7.36/slf4j-simple-1.7.36.jar
io/vavr/vavr-match/0.10.2/vavr-match-0.10.2.jar
io/vavr/vavr /0.10.2/vavr-0.10.2.jar
org/apache/httpcomponents/httpclient/4.5.13/httpclient-4.5.13.jar
org/apache/httpcomponents/httpcore/4.4.13/httpcore-4.4.13.jar
com/fasterxml/jackson/datatype/jackson-datatype-jsr310/2.17.1/jackson-datatype-jsr310-2.17.1.jar
com/fasterxml/jackson/core/jackson-core/2.17.1/jackson-core-2.17.1.jar
We need two ojdbc provider jar files:
Finally, we need to use the 23.4 version of the Oracle driver jar files. See Upgrade WebLogic Server to use 23x JDBC Driver for using the 23.x version on WLS 14.1.1.0.0.
All of these jar files need to be added to the WLS CLASSPATH. The 23.x JDBC Driver jar files need to be at the front of the CLASSPATH. If using ‘java weblogic.Server’ for testing, you can just update CLASSPATH. If using startWebLogic.sh, the standard approach is to update PRE_CLASSPATH.
Assuming that the above dependent jars are in a subdirectory “jars” and the ucp/ojdbc jar files are in the current directory, this script would add the jar files to the CLASSPATH.
@ cat setcp.sh
#v=8
v=11
CP=”`pwd`/ojdbc${v}.jar:`pwd`/ucp${v}.jar”
for i in `ls jars/*.jar`
do
CP=”$CP:`pwd`/$i”
done
echo “CP=$CP”
echo ‘Add $CP to CLASSPATH’
. ./setcp.sh
export CLASSPATH=”$CP:$CLASSPATH”
export PRE_CLASSPATH=”$CP”
Of course, you may be utilizing your favorite tool to get this done; ant, gradle, maven, etc.
Known Bugs
There is one WLS known issue. Bug 36514531 – JDBC EXTENSION: JAVA.LANG.ASSERTIONERROR: REMAINING NAME: DEFAULTMANAGEDEXECUTORSERVICE . This fix is available for WebLogic Server 14.1.1.0.0 in 36528799 – BLR BACKPORT OF BUG 36514531 ON TOP OF 14.1.1.0.0. This fix should be included in the July 2024 PSU for WLS 14.1.1.0.0.
There is one known UCP issue. Bug 36680139 – JDBC DRIVER EXTENSIONS: NPE IS REPORTED WITH UCP XA CONNECTIONS. This occurs only with a UCP datasource type using an XA driver.