Blog

Oracle Cloud, going down the rabbit hole – Part 4

Share this article

Allow DBaaS access to your company

In the previous post we connected to our database using ssh port forwarding.
Although this works well for client connections, it can be clumsy (and an added dependency) to use this method on servers.
Of course, Oracle has solutions such as VPN-as-a-Service or FastConnect to tackle this, but for our POC this is all a bit too much.
What we can do is to change the DBaaS access rules to allow connections to the database from our public IP.

An easy way to get your public IP address (other then just asking your network team for it) is to check your ip address with opendns using dig or nslookup:

dhoogfr@dhoogfr-lpt1 ~ $ dig +short myip.opendns.com @resolver1.opendns.com
78.20.XXX.XXX

If you’re on Windows, or don’t feel like using CLI, you can also use https://www.whatismyip.com/.

Now that we have this piece of information, we can start changing the access rules.

Open the DBaaS service console.
In the hamburger menu next to your guest, choose “Access Rules” (note that your guest must be running).

Oracle DBaaS access - hamburger menu access rules

This will show you all the default DBaaS access rules, most of which are disabled by default.
These default rules cannot be modified, only enabled or disabled.

Oracle DBaaS access - Default access rules

As such, we need to create our own rule (which cannot start with ora_ or sys_) to allow access from our own network to the database.
Click on “create rule” and give the new rule a meaningful name.
Source can be “DB_1” (which is the DBaaS guest), “PAAS-INFRA” (no documentation found, but seems to allow access between the PaaS service manager and the DB), “PUBLIC-INTERNET” (obviously…) or “Custom”.
Choose “Custom” and add the public IP from your company (or home) network.
Set the destination field to “DB_1”, the destination port to 1521 (or whatever port you picked for the db listener) and the protocol to “TCP”.

Oracle DBaaS access - Create access rule

Click on “Create” to submit the creation request.
It can take 20-30 seconds before the rule is actually created (and you might have to refresh the screen).

Oracle DBaaS access - add dblistener rule

Once the rule has been added, you can access the database without having to create an SSH tunnel.

dhoogfr@dhoogfr-lpt1 ~ $ sqlplus sys@\"130.162.100.152/labcon.598838623.oraclecloud.internal:1521\" as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Mar 25 19:49:32 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter password: 
Last Successful login time: Sun Mar 25 2018 19:49:20 EUROPE/BRUSSELS CEST

Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production


INSTANCE_NAME    HOST_NAME                      STATUS       ROLE
---------------- ------------------------------ ------------ -------------------------------------------------------
labcon           lab-db-01                      OPEN         PRIMARY

sys@LABCON> @whoami

USERNAME                       SID      CURRENT_SCHEMA                 INSTANCE_NAME   DATABASE_ROLE        OS_USER              CLIENT_IP            CLIENT_HOSTNAME      SERVER_HOSTNAME
------------------------------ -------- ------------------------------ --------------- -------------------- -------------------- -------------------- -------------------- --------------------
SYS                            33       SYS                            labcon          PRIMARY              dhoogfr              78.20.xxx.xxx        dhoogfr-lpt1         lab-db-01

sys@LABCON> exit
Disconnected from Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

Allowing access to the monitoring tools

You might have noticed that in the list of default access rules there was a disabled rule for DB Express / DB Console.
And in the instance menu, there was also something called ‘DBaaS Monitoring Console’.

Either enable the default rules (which allow worldwide access) or add your own rules as we did with the database access.
For EM Express (as this is a 12c database), allow access to port 5500.
The DBaaS Monitoring Console is listening on https (port 443).

Oracle DBaaS access - Added monitoring rules

With the rules added (or defaults enabled), go back to the instance list and open the hamburger menu next to your instance.

Oracle DBaaS access - hamburger menu monitoring tools

Click on “Open EM Console”.

This will open a new tab with the (after you have accepted the certificate exception) DB Express login page.

Oracle DBaaS access - DB Express login

Provide the requested credentials and click on login.
DB Express requires flash (ugh), so ensure you have a flash plugin installed and enabled.

Oracle DBaaS access - DB Express

With the DBaaS monitoring console you can do basic monitoring of the guest OS and the database.
Click on “Open DBaaS Monitoring Console” in the hamburger menu to open a new browser tab.

Oracle DBaaS access - Monitor login

Set the username to dbaas_monitor and the password to the DB password you provided when creating the DBaaS guest.

Oracle DBaaS access - Monitor

Open http access from IaaS

For our POC we need to be able to connect from the IaaS guest to the Apex listener on the DBaaS guest.
This means that we need to allow http (should be https in reality) between the IaaS guest and our DBaaS guest.
To do this I will allow http access from the entire IP network I created earlier.

Go back to the “Access Rules”, and add the following rule:

Oracle DBaaS access - create IaaS http access rule

As you can see, we used the 192.168.6.0/29 notation to allow access for an entire IP range.
Note that in reality you would use https instead of http. Even traffic flowing between our private ip addresses, will be going over a network part that is not managed by us.

Summary

In this post, we created our our own DBaaS access rules to allow direct connections from our own public ip to the database and monitoring dashboards, so we don’t need to use ssh tunnels anymore.
With this, the DBaaS part of this blog series is finished.
Next we will start with Oracle IaaS IP Networks.

Tags: Blog
Strange things with the Oracle optimizer
Oracle Cloud, going down the rabbit hole – Part 3

You May Also Like