Autonomous Database (ADB) offers many cool features when it comes to interacting with external data sources, from creating database links to non-Oracle databases to accessing various cloud object stores using DBMS_CLOUD APIs. More importantly, these external data sources do not have to be deployed or stored in OCI for you to be able to take advantage of these features. Nowadays, we come across more and more use-cases where customers invest in different services from different cloud providers. When multi-cloud is in the picture, our goal is to build bridges, not walls for our customers. Oracle Database Service for Azure (ODSA), Oracle-managed gateways for accessing non-Oracle databases, and principal-based authentication for AWS and Azure are some recent examples towards this goal. Today, I’m excited to announce that Autonomous Database now supports principal-based authentication for Google Cloud Platform (GCP) as well, and it’s yet another bridge for us and our customers!

Before we get started on how easily you can access your GCP resources from your Autonomous Database using this feature, I want to touch couple points. Firstly, as you may remember from my previous blogs, different cloud providers use different names for this concept (i.e. principal authentication). For example, it is resource principal for OCI, Amazon resource name for AWS, and service principal for Azure. Similarly, GCP calls it service accounts, which is how I’ll be referring to it for the rest of this blog post. Secondly, I will not be doing a deep dive on principal authentication since we already covered it in previous blog posts; however, I still would like to re-emphasize its benefits. As it was the case with OCI resource principal or Azure service principal, GCP service accounts provide a secure and easy access to GCP resources by also offering granular access control via role assignments. This means that you do not have to store any IAM user credential in your database. Enough talk, let’s see some action!

In this blog post, our goal will be to load some data from GCP cloud storage similar to what we did previously for OCI and Azure. Here are the steps that we’ll follow:

  • Enable GCP service account in ADB
  • Create a role with permissions in GCP
  • Obtain GCP service account details in ADB
  • Assign role and service account for GCP cloud storage bucket
  • Load data from GCP cloud storage via GCP service account

Enable GCP service account in ADB

As the first step we are going to enable GCP service account in our ADB using the DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH procedure. It’s important to note that if we only specify the provider parameter as we are doing in this exercise, the GCP service account will be enabled for ADMIN user. Please see our documentation on how to enable it for other database users.

BEGIN    
    DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH(provider => 'GCP');                        
END;                                                                 
/

PL/SQL procedure successfully completed.

Create a role with permissions in GCP

We’ll be jumping onto the GCP console for the next step. In GCP IAM, we will create a custom role to be used by our service account, and it will only include permission for read access to the objects:

role1

role2

role3

Obtain GCP service account details in ADB

When we enable the GCP service account in our ADB, a unique identifier is created, and this metadata is stored in the CLOUD_INTEGRATIONS view. We need to obtain and take a note of this identifier since it’ll allow the handshake between GCP and our ADB.

SQL> select * from cloud_integrations where param_name like 'gcp%';

PARAM_NAME             PARAM_VALUE 
______________________ _____________________________________________________________ 
gcp_service_account    GCP-SA-***************************.iam.gserviceaccount.com

Assign role and service account for GCP cloud storage bucket

Before we move onto loading data into our ADB, our GCP storage bucket needs to be associated with our ADB service account and the custom role we just created. This basically completes the handshake process we mentioned in the previous step.

bucket_role

bucket_role2

Load data from GCP cloud storage via GCP service account

We are now ready to load some data from our GCP cloud storage bucket. Please note that we constructed the file URI in virtual hosted-style, and used GCP$PA as the credential_name in DBMS_CLOUD.COPY_DATA procedure.

CREATE TABLE CHANNELS
  (channel_id CHAR(1),
   channel_desc VARCHAR2(20),
   channel_class VARCHAR2(20)
  );

Table CHANNELS created.

BEGIN
    DBMS_CLOUD.COPY_DATA(
        table_name =>'CHANNELS',
        credential_name =>'GCP$PA',
        file_uri_list =>'https://saleschannel.storage.googleapis.com/chan_v3.dat',
        format => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true')
    );
END;
/

PL/SQL procedure successfully completed.

SQL> select count(*) from CHANNELS;

COUNT(*) 
________
   5

To summarize, you can now use GCP service accounts in your Autonomous Database for easy and secure access to your GCP resources. The enablement of this principal-based authentication consists of just a few simple steps that need to performed as one-time operations. The rest is as simple as just specifying the GCP specific credential name in DBMS_CLOUD procedures. If you’d like to learn more about accessing cloud resource from your ADB, please check out our documentation.