How to access Oracle base database in private subnet using bastion service

Here I have an Oracle database in a private subnet.

1For quickly accessing my database I am using bastion service and port forwarding sessions. This way I don't need to worry about the bastion host security as it is taken care of by Oracle. First I create the bastion. As I will be joining from public internet I don't put any IP/CIDR restrictions.

It can also be created with the following oci cli command

2Then for accessing database on port 1521, I am creating a port forwarding session

Here is the command for port forwarding session with oci cli

I can create managed SSH session for root access. Or creating a port forwarding session on 22 and ssh'ing into localhost can also do the same thing.

3Once the session is created I copy the command, now I need to provide my ssh key and local port 10521 this time

4Now the tunnel is up, and I can connect to database using service name that I find on console. It can be CDB or PDB.

5For SQL*PLus connection string can be formed with many different connect identifiers, here I use the simplest.

Part 4: Securing APEX Admin Resources with Load Balancer

So in Part 2 I've installed an ORDS instance in a private subnet. Then in Part 3 I've configured a public load balancer to serve APEX to public internet. Now I want to protect admin resources to make sure they are not exposed. For that purpose I will use Load Balancer URL redirect rules .

1Currently I can login through load balancer public IP and see database actions

2But I don't want to expose database actions and other administrator resources to public internet. So I have added following URL redirect rules to my load balancer.

3And they are working, now I can't login to database actions through load balancer, immediately redirected to apex.oracle.com as rule dictates.

4If I bypass loadbalancer and access ords instances through private network than database actions and admin resources are accessible

5I only want to serve HTTPS traffic to public internet so I create another rule set for redirecting HTTP to HTTPS

6In order to strengthen security for production deployments user names should be chosen different, ADMIN and Workspace logins should be disabled. As well as ADMIN user should be locked.

Part 3: Serving APEX in Private Subnet behind Public Load Balancer

I have an Autonomous database with private endpoint, I've launched a compute instance on which I've installed java and ORDS. And configured ORDS to connect to my ATP instance. So with this part I will configure a public load balancer to serve my APEX installation

1I start with creating a public load balancer

2 My backend is serving HTTP over 8080 port, and I will do SSL termination on load balancer.

3 Load balancer will be listening to HTTPS traffic and then terminate SSL to backend

4 Once load balancer is created, I can configure backends.

Important: I prefer to add access rules manually as backend servers has network security group attached allowing traffic from/to public subnet for ports 8080/8443

5 Add Http Listener on port 80

6 Now I can configure Http to Https redirect as described here .

7 Now I edit Http Listener and Apply redirect rule I've just created

8 I create a new network security group to allow traffic towards load balancer ports 80 and 443. Then attach this NSG to load balancer.

9 Now I can test accessing it by pointing my browser to load balancer public IP. Both Http and Https protocols should work.

Troubleshooting connection issues

1 Use curl and wget to see redirects etc.

2 You can also use browser developer tools

3 I prefer using bastion service port forwarding session to access backend directly and bypass load balancer

Part 2: Installing Customer Managed ORDS on Compute Instance for Autonomous Database

For running your own ORDS instance, follow steps here

1 Create a Network Security Group for your instance, allow ingress traffic to 8080 and 8443 ports from private subnet. Also allow egress traffic. We will test the installation using bastion service.

2 Launch an instance in your private subnet. I preferred Oracle Linux 8 with VM.Standard.E4.Flex shape. Also enable Bastion service under Advances options > Oracle Cloud Agent

3 Attach NSG created in step 1.

4 Create a bastion service in the same private subnet and then create a managed SSH session to connect to instance.

5 Connect to instance and do following yum updates and installations with root user.

6 Create following folders and upload your Autonomous Database Wallet to config folder

Note: You can use use scp in combination with bastion session to copy your local file

7Install using ords interactive command line, refer to documentation for options

Note: I've enabled all features, you can choose None for production deployments

Note: I've preffered to serve ORDS over port 8080 using HTTP, as I plan to put a loadbalancer infront of the VM and serve HTTPS thus offloading SSL

8If you see the following CORS error message:

Login to ORDS instance, then edit /etc/ords/config/global/settings.xml to add the following entry:

9Open ports 8080 and 8443 on local firewall

10Enable ORDS service, surviving restarts

11Create a bastion port forwarding session

12Start local port forwarding session

13Test pointing your browser to localhost:8080

Serving Oracle APEX Static Resources with Oracle Content Delivery Network (CDN)

Normally as part of the ORDS and APEX installation we copy static images to htdocs. Here is an alternative to this.

If you want to revert it back to local, then

Part 1: Accessing Autonomous Database over Private Endpoint using SQL Developer

With autonomous database you can choose between different network access types:

When you choose a deployment with private endpoint , here is how you can connect to your ADB instance from your local machine by using Bastion Service and port forwarding sessions.

1Create a network security group, allow ingress traffic to ports 1521 (TLS), 1522 (mTLS) and 443 (Oracle APEX, Database Actions, and Oracle REST Data Services) from your private subnet CIDR where your bastion host will be, allow egress traffic as well. Attach it to your database.They should look similar to this:

2Create a bastion in the same subnet as your database private endpoint. Then create a port forwarding session using your database private IP and port

3Copy SSH command, choose a local port and start port forwarding session. I generally use 10522, to avoid conflict.

4Download ADB wallet, unzip it. Add an entry to tnsnames.ora file similar to below one, pointing to local port which will be forwarded to private endpoint by bastion host. Then zip the folder to use with sql developer.

5Use your choice of database client to connect. With sqldeveloper it should look something like this:

Flashback Transaction Query on ATP Shared

By default flashback transaction query is enabled on ATP-S. Let's see how it works.

Unfortunately most important columns ROWID and UNDO_SQL are NULL. In order to let Oracle populate these columns we need to enable supplemental logging .

Now we are able to see ROWID and UNDO_SQL.