Database Tools in Oracle Cloud Infrastructure (OCI)

Service Overview

In November of 2021, the Oracle Database Development Tools team launched a service in OCI. This service, called  Database Tools, aimed to create a reusable resource for connecting to cloud databases via REST enabled SQL by consolidating all the pieces of information you need to have to connect to a database. No need to remember that the database on the server called ncr3342szw is development while the one on ncr3342szx is UAT. Or remembering passwords for database connections which someone inevitability locks the account for too many failed attempts at least once a week. Add the ability to use OCI policies and define dynamic groups who can use these connections; we can securely store the hostname, port, database name, username, and password creating a reusable, portable resource that can be utilized across clouds, development tools, services, and pipelines.

Connections

We must create one before we can utilize these database connections across OCI or development tools. Throughout this service, we aimed to aid the user as much as possible to help discover database details where ever we could. We wanted the service to be used not only by DBAs but by application developers, citizen data scientists, anyone who wants to use a database connection. One of the features added to the service to help guide users is found in the Create Connection user flow.

Someone using a database for the first time may not understand the critical information they need. Hostnames, ports, and database identifiers are not always easily found, let alone given to anyone other than DBAs. This lack of database connection information is a point we immediately address when creating a connection with the Database Tools Service.

After naming the connection, all you need to do is choose a database type from an Autonomous database, a database created on a VM, or a database created on an Exadata Infrastructure. Once that option is selected, available databases are pre-filled in the database select list. Once you find the one you want to work with (and policies can be placed on these databases so only ones that a user should see will be shown), select it. The service will pre-fill all other connect information such as database home, database name, and then let you select a PDB to use. In the case of databases on Exadata Infrastructure, you can choose from among database homes as well. Once the database you want to use is selected, you can see that the connect string is created for you automatically.

Next up are usernames and passwords. We can approach this in two ways. One is that whoever creates the service has the username and password for the database they want to connect. If so, the DB Tools Service will help you store this password securely, encrypted in an OCI vault. Another use case could be that the user comes to the service with a username, and they can select the password using the dropdown; they just look for the entry that matches their username. Whichever way is chosen, they enter the user and password, and the connection details are complete.

Private Endpoints and Database Connections

Depending on how you have the database set up, a Private Endpoint may be needed. So, what is a private endpoint, and why do I need one? 

Cloud Databases are usually protected by multiple layers of network security so that they are isolated from the public network and only allow access from particular IPs or applications. For the Database Tools service to access these database services, a path into a virtual cloud network (VCN) must exist so that the service can connect to these databases.

The diagram below shows that the DB Tools Service can deploy a private endpoint into a private subnet in a VNC. The private endpoint is then given an IP address from that subnet and acts as a secure bridge from the database tools service to that secure database with a private IP in that private subnet.

To note here, only the database tools service can utilize this private endpoint. It cannot be used independent of the Database Tools Service or by any other service in OCI and must be created by an end-user. The service will not self/auto-create private endpoints on behalf of a connection or when it sees that database is using private IPs. OCI policies also control the creation of private endpoints; just having the ability to use or create a connection in the Database Tools Service does not entitle a user to create Private Endpoints. You can read about the Database Tools Service policies here and how we recommend splitting up into different management roles within the service.

An excellent example of using a Private Endpoint with the Database Tools Service is when you want to connect to a database that is using a private IP address. These databases are usually deployed with multiple layers of security such as hardened Subnet-wide Security Lists and Network Security Groups; configured to only allow communication over a specific port to and from a particular IP address. Looking at this database that was created in a private subnet, it is now assigned the address of 10.0.1.60.

We can start by adding a Network Security Group to the DB VM instance, allowing us to add specific firewall rules to the VNIC of the DB System so we can restrict traffic. Network Security Groups offer a more granular level of security than Subnet level Security Lists.

After the Network Security Group (NSG) has been added to the DB VM, we can go find the details of our Private Endpoint for our Database Tools Service.

Here we want to take note of the two Reverse Connection Source IPs. Once we have those, we can add these IP addresses to the NSG with access over port 1521:

Now, using the Validate button on the Database Tools Connection Details page, we can test the connection and see that we can access the Database.

Wallets Details

Some connections need to include a wallet or java keystore for additional security. The Wallet details step allows you to upload these credentials or let the service automatically get the wallet from the autonomous database you are using. Wallets are then encrypted and stored in the OCI vault and only used when connecting to the database.

SQL Worksheet

SQL Worksheet is available to use with any Database Connection you have access to use. Run SQL commands right from the OCI console in your browser.

The SQL Worksheet also has some tricks such as code insight similar to SQL Developer

code case conversion

save and load SQL scripts from your local drive or from cloud object storage

the ability to change your connection right in the SQL Worksheet and start working with another database immediately

and you can even download the results of a SQL query in multiple different formats

Connection Details Page

The Connection Details page has some valuable features to help you get connected to the database as quickly as possible. You can see the Launch SQLcl button at the top of the page.

This button will open up the OCI Cloud Shell and log you directly into the database with SQLcl. Additionally, suppose the database you are connecting to is in a private subnet using a Private Endpoint. In that case, the Database Tools Service will use the OCI Bastion as a Service to create a connection right there in the UI for you.

and then proceed to log you into the database with SQLcl via an SSH Tunnel.

Use Cases

 

Secure Database Tools Connections with OCIDs in OCI via Policies

So now that we can store database connection information as a resource, what can we do with it? To start, we can secure them down to the resource ID/OCID. Database Connections are a top-level resource in OCI which means you can reference that OCID in policies. You can create a group/dynamic group consisting of a specific user class then assign or restrict access to a connection or an entire set of connections within a particular compartment.

For example, we have a set of connections we want to give to data scientists in an OCI group called Analytics.

Now, assign users to this group

Once the users are in the group, we can create a policy that restricts access to Database Tools Connections in a specific compartment (the Data_Science compartment). The following table is from the documents for the service just updated to reflect the groups and compartments we are using as an example.

allow group Analytics to use database-tools-connections in compartment Tools_PM

allow group Analytics to read database-family in compartment Tools_PM

allow group Analytics to read autonomous-database-family in compartment Tools_PM

allow group Analytics to read secret-family in compartment Tools_PM

allow group Analytics to read database-tools-family in compartment Tools_PM

Just create the policy in the Identity & Security category in the OCI console, and the connection is secured.

Suppose you want to restrict access to a specific connection. In that case, you can add the OCID of a connection to the

allow group Analytics to use database-tools-connections in compartment Tools_PM

policy. For example, you wanted to restrict this groups access to just one connection, you would add:

allow group Analytics to use database-tools-connections in compartment Tools_PM where target.DatabaseToolsConnection.id = 'ocid1.databasetoolsconnection.oc1.eu-frankfurt-1.amaaaaaau3i6amaaaaaau3i6amaaaaaau3i6amaaaaaau3i6amaaaaaau3i6'

or a group of connection OCIDs:

allow group Analytics to use database-tools-connections in compartment Tools_PM where

any

{target.DatabaseToolsConnection.id = 'ocid1.databasetoolsconnection.oc1.eu-frankfurt-1.amaaaaaau3i6amaaaaaau3i6amaaaaaau3i6amaaaaaau3i6amaaaaaau3i6',  

 target.DatabaseToolsConnection.id = 'ocid1.databasetoolsconnection.oc1.eu-frankfurt-1.a443xxsedsda443xxsedssd443xxsedsdxama443xxsedsdxa443xxsedsdx',

 target.DatabaseToolsConnection.id = 'ocid1.databasetoolsconnection.oc1.eu-frankfurt-1.addddaau1XXXaddddaau1XXXaddddaau1XXXaddddaau1XXXaddddaau1XXX'}

SQLcl and Database Tools Connections with OCIDs

Say goodbye to TNS Names files and hello to Database Tools Connections with OCIDs. No more do you need to remember connection strings, username, and passwords in SQLplus. Use the Database Tools Connection OCID and SQLcl to log in directly to the database. On the Connections details page, just copy the OCID of the connection.

Set your OCI profile in SQLcl

SQL> oci profile DEFAULT

and connect via the OCID.

And that’s it; you are connected to the database with the Tools Service doing all the heavy lifting.

OCI DevOps pipelines with Database Tools Connection OCIDs for Deployment

We have written a lot on CICD with the database using SQLcl, but in an upcoming article, we will be going over pipelines to test code and deployment. One of the gotchas to watch out for in these pipelines is passing sensitive information such as IP addresses, usernames, and passwords. With that in mind, we can combine the above two use cases to create a secure connection to specific deployment targets within CICD pipelines.

You can create a Dynamic Group in OCI that contains just a compute instance you want to use as a deployment platform using policy security. For example, here is a sample Dynamic Group Policy with the OCID of a compute instance that is contained in a private subnet in OCI:

instance.id = 'ocid1.instance.oc1.eu-frankfurt-1.antheljantheljantheljantheljantheljantheljantheljantheljantheljantheljantheljanthelj'

Now that the dynamic group is defined, we can create policies for this group and the Database Connection OCID:

allow dynamic-group DeploymentInstance to read database-family in compartment Tools_PM

allow dynamic-group DeploymentInstance to read autonomous-database-family in compartment Tools_PM

allow dynamic-group DeploymentInstance to read secret-family in compartment Tools_PM

allow dynamic-group DeploymentInstance to read database-tools-family in compartment Tools_PM

allow dynamic-group DeploymentInstance to use database-tools-connections compartment Tools_PM where target.DatabaseToolsConnection.id = 'ocid1.databasetoolsconnection.oc1.eu-frankfurt-1.amaaaaaau3i6amaaaaaau3i6amaaaaaau3i6amaaaaaau3i6amaaaaaau3i6'

Then, when creating our deployment configuration in OCI DevOps for a deployment pipeline, we can include the following code in the YAML file. Combine connecting DB Tools Service Connection OCIDs with Liquibase in SQLcl and you have a secure full database CICD pipeline!

    command: |

        cd /var/lib/ocarun/

        OCI_SQLCL_CONFIG_FILE=/var/lib/ocarun/.oci/config

        export OCI_SQLCL_CONFIG_FILE

        env

        {

        echo “oci profile DEFAULT”

        echo "conn ocid1.databasetoolsconnection.oc1.eu-frankfurt-1.amaaaaaau3i6amaaaaaau3i6amaaaaaau3i6amaaaaaau3i6amaaaaaau3i6"

        echo “lb update -changelog controller.xml”

        echo “exit”

        } | sql /nolog

No passwords or sensitive information in the logs or any static files plus by using the policies, we have secured this connection to only run on the compute defined in the Dynamic Group.

Wrap-up

The Database Tools Service brings order to connection chaos by consolidating all the essential information for database access. Reusable, secure, and flexible resources can be used across OCI services and locally when using development tools such as SQLcl. What’s even more exciting is that the service is just getting started. We have many new features and enhancements coming in the near and far future that deal with additional database flavors, global logins, and bringing SQL Developer Web to the OCI Web Console.

Just click Database Tools in the Developer Services category on the OCI Web Console Menu to get started.

Source: oracle.com