There's a brand new tool in town, and it's ready to make your life a whole lot easier if you work with Oracle DB in the cloud. It's called "Database Tools", and despite the rather boring sounding name, I can assure you that this service is super, extra, magnificently awesome! If you're a doc reader, have at 'em. But if you're like me - an adventurer who jumps in head first - then let's take a deep dive into Database Tools and see how to use them!
For your navigational pleasure, here is a Table of Contents:
Database Tools lets you create "Connections" to existing databases. These connections are a way to store all of the credentials that you need to connect to that database in a safe, secure place in the cloud. Why? Because security is important! Storing credentials offline can be a security risk and when you create a connection in the Oracle Cloud you can be assured that your credentials are encrypted and safe. Additionally, once they are created they can then be used to connect to other services in the Oracle Cloud like SQL Worksheets and SQLcl in the Cloud Shell without having to type a single username or password (and without downloading a wallet!).
Before we create a connection, we'll need to create (or make sure we already have) a 'Vault' in the Oracle Cloud. To do this, search for 'Vault' and select 'Vault' under 'Services'.
Click 'Create Vault' on the Vault list page.
Click 'Create' and wait a few minutes (security is paramount, but not always fast).
Once the vault is created, click on 'Create Key' Under 'Master Encryption Keys' in the vault details.
In the key details dialog, name it (#1), choose 'RSA...' (the key must be asymmetric - #2), and a Key Shape of '2048 bits'.
When the vault is ready, search for 'database tools' and select 'Connections'.
Click 'Create Connection'.
Name it (#1), select the compartment used to store it (#2), choose 'Select Database' (#3 - since we're planning on using this with Autonomous DB), choose 'Autonomous Database' (#4), choose the DB's compartment (#5), enter the 'User Name' (#6), and click 'Create Password Secret' (#7).
In the 'Create Password Secret' dialog, name the secret, choose the vault and encryption key, and enter/confirm the password.
Uncheck 'Network Connectivity via Private Endpoint' (unless you intend to use a private endpoint).
On step 2, select 'SSO Wallet' under 'Wallet Format'.
Click 'Create Wallet Content Secret' and then name your secret (#1), choose the vault to store it in (#2), the key to use to encrypt the secret (#3), and select 'Retrieve regional auto login wallet from Autonomous Database' to have the secret generated automatically for you (how nice!).
Click 'Create' and in just a few moments your Database Connection is ready to use!
Now we can use the connection! On the connection details page, you can click either 'SQL Worksheet' or 'Launch SQLcl' to use the connection immediately.
SQL Worksheet is a lightweight version of SQL Developer Web that allows you to save, load, and run simple queries against a connection.
If your preference is command lines, clicking 'Launch SQLcl' will open Cloud Shell and automatically configure and connect to the database you specified in the connection.
In cooler news for developers, Database Tools Connections can be retrieved via the OCI Java SDK. This means no storing credentials in code or environment variables or manually creating vault secrets for each and storing the OCIDs for each vault secret. You just need the OCID of the Database Tools Connection and the SDK does the rest! Let's look at an example using Java to retrieve the connection and use it to query Autonomous DB.
First, you'll need some dependencies. We need a few modules from the OCI Java SDK (to retrieve the connection and the secret content).
And we'll need the OJDBC driver:
Next, create a class. I'm calling mine
Demo.java. We'll need to pass in the OCID of the connection that we created earlier, and in the constructor, we'll set up a few clients to make calls to the SDK.
Instead of returning the secrets directly, the SDK will return OCIDs pointing to the secret in the vault. Once the content is retrieved, we'll need to decode them from Base64, so create an instance of the
Now we can construct a request to get the
DatabaseToolsConnection and use the client to send the request.
Grab the connect string and username:
If you're interested, grab the KeyStore type:
Next, grab the KeyStore secret contents OCID and make a request via the
SecretsClient to retrieve the content and decode it.
Similarly, grab the DB password secret OCID, construct a request, retrieve the secret and decode it.
Now we can start creating our datasource. First, create a
Properties object to store the username and password and construct the URL from the connect string.
Now we can create an "in-memory" wallet from the decoded bytes of our SSO secret contents and create an SSL context that we'll set on our
DataSource in just a bit. Huge credit to Simon for his examples on GitHub!
OracleDataSource, set the SSL context, URL, and connection properties.
Finally, create a Connection and execute a query.
If all goes well, your output should look similar to the following when you use this class.
In this post, we learned about the new Database Tools available in the Oracle Cloud Console. We created a connection and used that connection to launch a SQL Worksheet, and Cloud Shell instance with SQLcl. We then looked at how to download the connection info with the OCI Java SDK and use the info in the connection to create an in-memory wallet and datasource to query Autonomous DB. If you'd like to see this example in its entirety, check it out on GitHub.
Photo by Hunter Haley on Unsplash
I've written many blog posts about connecting to an Autonomous DB instance in the past. Best practices evolve as tools, services, and frameworks become...
Email delivery is a critical function of most web applications in the world today. I've managed an email server in the past - and trust me - it's not fun...
In my last post, we looked at the technical aspects of my Brain to the Cloud project including much of the code that was used to collect and analyze the...