Announcing the Oracle Cloud Database Tools Service

Updated 1 September 2023

A brand new Oracle Cloud (OCI) Service launched yesterday, and this post will describe briefly:

• what it is
• why we built it
• what you can do with it today
• what we have planned for the future.

First things first – everything here is free. Defining connections is free. Storing secrets (credentials and passwords) in the Vault is free. Creating bastions to SSH into private endpoints is FREE. This service is all about making your databases easier to access, either directly using our tools or via your apps.

The Database Tools Service isn’t a just new way to create, run, or manage databases on OCI, it’s for WORKING WITH those databases.

You can create many different types of Oracle databases on OCI – Autonomous, Exadata CS, VM based instances, or even do up your own install on a Compute node.

Example: Deep Dive Creating and Using a Connection to our BASE OCI Service

When you’re ready to use those databases, what’s the first thing you might want to do? I can think of a couple of big ones:

• connect to it
• run some queries.

Connections

What is a connection, really? It’s everything you need to know to connect to a database. It’s going to have the location of the database, it’s name, listener port, and a username/password. With that information, you can create a connection in your tools and programs.

So if we were to visualize this say your favorite desktop IDE for Oracle, that could look like this –

As a client application, these are stored on the machine with your program. Which is nice, if you’re on that machine, but it’s 2021, and we’re in the Cloud, and there should be an easier way to deal with our critical resources, like Oracle Databases!

Cloud also introduces questions. Like, how can we connect to our database without making it ‘open’ to the entire Internet.

Most databases in OCI use private subnets (for good reason!), and that poses a challenge when connecting to them. This service makes creating bastions and managing private endpoints MUCH easier for application developers, or people using database tools to connect to those database.

This post will show an example of an ‘open’ database and connecting to it directly.

A Quick Demo to give you an idea of what’s possible.

I can create a connection, stow my password in the OCI Vault, and then use that information to say launch SQLcl to my Autonomous Database, on-the-fly, without doing anything other than clicking a single button.

And don’t forget, Autonomous Databases classically require Client Credentials containing your two way TLS encryption keys, so that the database can trust us as a client. Managing these ‘wallets’ takes a bit of work, even if we’ve coded a lot of that away for you with our tools.

This GIF if pretty short, and a lot happens in a small amount of time, but what you’re seeing is me asking for a SQLcl session to my Autonomous Database, defined in my Connection resource.

Which results in SQL GETTING:

• the database location, name, and particulars
• the TLS Client Credentials
• the database username and password
• the database connection…which leads to me being…
• …in my DB, authenticated and ready to go!

Beyond the scope and nature of a ‘Hello World!’ post, if your database is also accessed via a private endpoint, the SQLcl can also use that information to create an SSH tunnel/Bastion to make the connection as well!

Another Quick Demo

One of the bigger things our team has put together over the past few years is SQL Developer Web. This is surfaced in the Autonomous Database Services as ‘Database Actions.’ And it’s great. But.

But.

You have to go to the database, and THEN launch Database Actions, connect and start your work. Hint: You can bookmark each Autonomous instance to go straight to the connect bits…but it’s always one link, one page, for ONE database.

What we want you to be able to do is, launch your SQL scratchpad, choose your database, run your queries.

Like this:

Let’s Build a Connection!

This is going to be your Day 0, Minute 0 project to get started using the Service. Let’s cover some housekeeping and ground rules.

How to find it –

You’ll find the Database Tools service console pages under ‘Developer Services.’ You can of course just search for it using the mega-menu, or you could navigate to it every single time, or you could create some nice bookmarks using the newer ‘pinning’ feature in the OCI Console.

To build a connection, we’re going to go to that first link, ‘Connections.’

What happens probably if you just jump in without doing your homework…

You’ve been working with databases a long time, no need to ready any docs, yeah?

Well, when it comes to this part of defining your connection, if you don’t already have at least one Vault available…

We need some place to put the password(s). That ‘place’ is the OCI Vault. Or more succinctly:

Oracle Cloud Infrastructure Vault is a managed service that lets you centrally manage the encryption keys that protect your data and the secret credentials that you use to securely access resources. Vaults securely store master encryption keys and secrets that you might otherwise store in configuration files or in code. Specifically, depending on the protection mode, keys are either stored on the server or they are stored on highly available and durable hardware security modules (HSM) that meet Federal Information Processing Standards (FIPS) 140-2 Security Level 3 security certification.

https://docs.oracle.com/en-us/iaas/Content/KeyManagement/Concepts/keyoverview.htm

And to access the things IN your Vault, you also need a Key. Now, these requirements and others are covered in the Database Tools docs – so do your homework and read those. And, we’d like to make this even easier going forward, so stay tuned.

The good news is, creating a vaule is probably something you need to do just once per project/application/group of ‘things’ you want to manage.

It literally took me about 90 seconds to setup my Vault and Key…

Defining the Connection

Each connection will have it’s own username and password. It will be for a single database – be that a PDB, CDB, or classic single instance architecture database (11gR2..21c). And it can be for the very cheapest resource, Always Free, up to the most very expensive shape we offer. These services are all included and free to use with your OCI subscription.

Now, we do offer some very nice touch-features. Choose your type of database –

Here’s where the convenient part comes into play.

We can query the OCI control plane to get a list of your Autonomous Database services. And with those, comes the information we need to get the Client Credentials. Just point, click, and you’re done.

Once you fill everything out on the first page…

…just click Next.

We’re ALMOST done.

Since I’m defining a connection to an Autonomous Shared Infrastructure Database, I MAY need to provide credentials to enable mutual TLS (mTLS).

If you accidently choose the same secret you supplied previously for your database user’s password, you’ll see this:

Getting Confused, Worried? Don’t be, this is still Easy!

Once you click that ‘Create Wallet Content Secret’ button, you’ll see this –

We’re smart enough to just use the Autonomous Database Service APIs to go ask it for the credentials FOR YOU. Now, if you already downloaded your wallet, or if you want to specify exactly what’s being used, just use the ‘Upload’ option.

That’s it, you’re ready! Just hit the ‘Create’ button.

You’ll be back on the Connections screen, and you can start playing around with said connections.

If I open a Connection, I can see it’s treated like any other Top Level OCI Resource – it has an OCI and everything – it can be tagged for easier filtering, or more importantly, you can restrict access to it from your fellow OCI users/apps via Group roles, etc.

The two biggest features today are highlighted first – SQL Worksheet and Launch SQLcl. The Worksheet will open with this Connection active by default. But, I could go to the Worksheet directly and then pick a connection, it’s really up to me, and where I’m at in the console.

One nice ‘debug’ feature is the Validate command –

I’ll cover ‘debugging’ connections in a subsequent post, but this can help you figure out if you got something wrong or if your password has expired, or…

Is this really all it is?

I mean, it would still be cool if THIS was all it was. But, it’s not. It’s really only HALF the story. You see, since it’s an OCI resource, we can use the OCI SDKs, APIS, etc to use these resources.

So, if I had a Python program that needed to connect to a database, it could simply call the Database Tools service to ‘Get’ the connection, bring it down, construct the connection object, and make the connection – all without the developer knowing the details except for the OCIDs.

In the meantime, you can read this start-to-finish post from Dev Advocate Todd Sharp. He has a nice Java based example of using the service.

What’s the future of this service?

For starters the SQL Worksheet is extremely basic. I’ll cover it in a future post, but it doesn’t come close to offering the full power of SQL Developer Web. Porting that Oracle JET app so it can run natively in the OCI Console is an ongoing project and I expect huge progress in 2021.

Also, this isn’t just for us. It’s for any other Database related service in OCI that needs to work with databases. We’ll be at their disposal too, not just yours.

And it’s not necessarily exclusive to Oracle Databases. OCI supports other types of databases, so we’ll be including support for all database application developers in the Oracle Cloud.

And finally, it would be nice if we could do things like say ‘Install ORDS Metadata’ or ‘Upgrade APEX’ for a known Oracle database via a Connection.

The roadmap here is long and filled with many scenic views, so we’re very excited to get there. But, most importantly, we’re excited to get this Service off the ground and running!

Thanks to everyone on all the teams that made this launch possible. Expect many updates in the form of slides, videos, blog posts, Github How-To code examples, podcasts, and more.

The Movie

Finally, we can’t release stuff without having a video! Brian and I talk about and demo connections and our tools. See the video description on YouTube for chapters you can use to jump to specific topics.