Setup OCI DBTools Connections with an ADB Access Control List

TL/DR: To use OCI Database Tools (DBTools) Connections to connect to ADB with a public IP and access control list (ACL), DBTools requires the use of a DBTools Private Endpoint (PE). DBTools connections setup with a PE can route traffic to the ADB instance through a Service Gateway (preferred) or through a NAT gateway. The ACL would be configured to accept traffic from the VCN where the PE is created with the former or from the public IP of the NAT gateway with the latter.

Keep reading for more information and lots of screenshots!

Introduction

In Oracle Cloud Infrastructure (OCI), an Autonomous Database - Serverless (ADB-S) instance can be setup with different types of network access. At the time of this writing ADB running in OCI supports:

  • Public IP with mTLS
  • Public IP with ACL
  • Private endpoint access

The DBTools service helps customers create and use connections for any of these networking scenarios. Here is a look at the general Oracle Database use cases DBTools supports with some useful diagrams for each:

Infrastructure Database Tools
The Database Tools service allows you to create connections to Oracle database systems running in Oracle Cloud Infrastructure. Connections work with Autonomous Database (ADB) Shared, ADB Dedicated, Oracle Base Database (VM, BM, and Exadata DB Systems), and customer-managed Oracle databases running on OCI compute instances.

In this post I focus on setting up DBTools connections with an ADB-S instance with public IP + ACL. This setup will look something like this:

OCI Database Tools Connection to ADB with public IP and access control list.

Prerequisites

This post doesn't cover every click, menu, etc. I assume some resources already exist, including:

  1. A virtual cloud network (VCN) with a private subnet
  2. A DBTools PE created in the above subnet
  3. An OCI Vault with an encryption key where secrets will be stored
  4. An ADB instance with public IP and ACL enabled

Note: The default security list is fine for what follows but if yours is customized then you may have additional steps not covered here. For example, I did not change anything in the settings since traffic will flow outbound from the PE to the ADB instance.

For visual reference, here are some examples of the above VCN resources.

OCI virtual cloud network.
A private subnet within an OCI virtual cloud network.

Here is an example of a DBTools PE created within the above private subnet.

OCI Database Tools Private Endpoint with private IP addresses.

Note the first of the two reverse connection private IP addresses shown above. We will see that again later in this post. (i.e. 10.0.1.27) And finally, here is an example of an ADB-S database instance with public IP and ACL enabled.

Autonomous Database - Serverless database with ACL enabled.
CAUTION: If your ADB instance has private endpoint access or does not have Access control list: Enabled then you should be aware that what follows does not apply to that ADB instance. Please only change this setting if you'd like to follow along and you are aware that changing this will likely break any existing connections.

The Database Tools Connection

Once you have the resources above created you can setup a DBTools connection. You should create a DBTools connection and associate it with the PE. The connection creation wizard is pretty intuitive if you want to jump right in and get your hands dirty or you can read about the process here:

Infrastructure Database Tools

Here is an example of a DBTools connection setup to point to the ADB database example shown above.

OCI Database Tools Connection with associated Private Endpoint.

Notice how the connection shows the configured private endpoint. This PE will allow the DBTools service to send SQL statements to the target VCN and this is important for the steps that follow.

Now lets validate or new connection by pressing Validate!

OCI Database Tools Connection with a validation error due to login timeout.

That would have been too easy but the timeout error makes sense. We have a connection resource setup but the ACL has no idea from where the traffic is coming, yet. We will fix the above issue very shortly.

VCN Configuration (Service Gateway)

The PE we created above setup a virtual network interface card (VNIC) in our private subnet but right now the VCN is not configured to send that traffic to ADB. As the title of this section implies, we will setup a Service Gateway in the VCN and then configure a route to allow OCI service traffic to use the gateway.

First, open your VCN in the OCI console and create a Service Gateway.

OCI Virtual Cloud Network Service Gateway.

In this example, I called my Service Gateway jb-dbtools-sgw-test. Furthermore, enable All <region> Service in Oracle Services network which allows traffic from OCI services (like DBTools service) to travel over the Oracle Services Network.

Next, configure the Route Rules for the VCN to use this Service Gateway. Once that is setup you may have something like this:

OCI Virtual Cloud Network Route Rules showing various gateway options.
💡
In the example above I show both the NAT Gateway and the Service Gateway, but you should ignore the NAT Gateway for now. I will reference it again later for "option 2". You do not need both gateway types for your connection to work properly.

Autonomous Database ACL Configuration

The last step to connect the network dots is to allow the VCN to speak to ADB. This is done by configuring the ACL and adding a new access control rule. Ignore the public IP address shown here for now, and focus on the VCN OCID.

Click on Add access control rule and then choose the VCN where you previously setup the DBTools PE. You should see an option to pick the human readable name and once the rule is saved, the OCI console will update this to the correct OCID.

Autonomous Database access control list rules with a VCN OCID selected.

With this setup in place we should now be able to validate and use the DBTools connection for real.

Validate the Connection and Prove It

Return to your DBTools connection and once again press Validate and you should be presented with something similar to the following.

OCI Database Tools connection validation response example.

A successful validation tells you that the DBTools service was able to connect to the ADB database at the other end of the connection. If you followed along based on the setup above, then this traffic is further sent to the ADB instance over the Oracle Services Network from the private IP address of the PE VNIC in your VCN.

Lets gather some proof that we are indeed connecting from the PE. First open the SQL Worksheet using this DBTools connection and run the following SQL.

select sys_context('userenv', 'ip_address') from dual;

When you execute this statement you should see something similar to the following output in the SQL Worksheet.

OCI DBtools SQL Worksheet showing the sys_context value for private ip_address.

Remember when I said we should note the first of the two private IP addresses created with the PE. (i.e. 10.0.1.27) In the example above you can see that ADB is communicating with the private IP of our DBTools PE.

Cool!

So, what about the NAT gateway option? This is very similar to the Service Gateway. Let's take a look.

Rewind a Little Bit

Let's rewind a little bit to the point where our connection was not able to validate and pretend that we never setup the Service Gateway. If for some reason you must use a NAT gateway then you can do so. Once your VCN, DBTools PE and connection are setup, create a NAT gateway in your VCN.

OCI Virtual Cloud Network NAT gateway example.

Notice that when you create a NAT gateway you should get a public IP address. In this example, I got a public address in the 129.15n.nnn.nnn range. I've masked the value shown to protect the innocent but you get the idea.

With the NAT gateway setup you then need to configure a route rule for it. Warning, shamefully copy/pasting screenshots here. This is the same image you saw above.

In this case, notice the first rule listed is for the NAT Gateway and this is the one you need to setup (or something like it) so that your VCN traffic can reach the ADB public IP address via NAT gateway.

OCI Virtual Cloud Network Route Rules showing various gateway options.
💡
In the example above I show both the NAT Gateway and the Service Gateway, but you should ignore the Service Gateway. You do not need both gateway types for your connection to work properly.

Autonomous Database ACL Configuration (Option 2)

Now that you have a NAT gateway setup you need to configure the ACL in your ADB instance to allow traffic from the public IP of your NAT gateway.

Autonomous Database access control list showing an IP address filter

Edit the ACL of your database and press Add access control rule and then choose the IP address and enter the public IP of your NAT gateway.

Wrapping Up

Finally, return to your DBTools connection and validate it. It should validate as shown up above and if we want to prove to ourselves that the traffic is coming from the NAT gateway, you know the trick already.

Don't worry, I repeat it here. Open the DBTools SQL Worksheet using your validated DBTools connection and run the following SQL.

select sys_context('userenv', 'ip_address') from dual;

When you execute this statement you should see something similar to the following output in the SQL Worksheet.

OCI DBtools SQL Worksheet showing the sys_context value for public ip_address.

You can see from the partially redacted example above that the proof we are looking for shows the public IP of a NAT gateway. And that's all there is to getting DBTools connections to work with ADB instances that use an Access Control List.

💡
If you are still getting the private IP address of your PE instead of the public IP address of your NAT gateway then you likely have both the Service Gateway and the NAT gateway setup. You can force the traffic from DBTools to use your NAT gateway by removing the Service Gateway from your route rules.

I hope you found this useful. Until next time.

Cheers!