Introduction

Welcome to my post about integrating an APEX application with Google Drive (or any Google API in general) using Service Account credentials, all created in PL/SQL. If you are asking yourself what kind of problem I am going to solve, please read my introduction post which describes the use case.

As some of you may know, I described Google integration aspects few years ago. Here you can expect sum of my lessons learned, more insights and more advices on what you should be really careful about.

Motivation

The objective for Service Account approach is exactly the same as it was for JavaScript-based one. So, at the level of desired output you will not see any differences.

But, when we were doing the analysis of what is the best for Steven's application, it turned out that JavaScript style is not optimal. Some concerns we had that moment were not really abvious, however they impact intuitiveness of using the application.

• The Consent Screen (a.k.a. Google account selector) is presented to the user for each request to Google API. This is the limitation of Google Identity Library - access tokens are not stored anywhere to be reused. I solved that issue by saving tokens in APEX collection. Also, the token refreshing procedure was handled by myself. Anyway, user will still be prompted to select his proper Google Account to continue his work in case when the existing token has expired. Telling the truth, this can be quite confusing and look heavy. Worst case scenario is the strange impression, that user calls the API via Plug-in from the same APEX session in quite long time periods. Then user feedback can be a bit painful - "Do you want me to select my Google account every time?!"

• JavaScript-based approach shows another drawback. In order to call the API correctly and get the right results from that, I need Google account at all. Also, my Gmail address must be added to the sharing list for particular resources. Well, having the Google account should not be mandatory for Steven's application users. We should develop a better solution to show images straight from Google Drive for every user.

• Another ineffectiveness is related to usage of access tokens. The flow of JavaScript approach assumes that the first call of API in particular APEX session should request a token, then save it in collection and call the API finally. Second and another calls uses the token from APEX collection. When the user logs out and log in again, he is backed to start. So, the access token is connected with particular user's APEX session. Also, every user has to request its own token, because of separed sessions. That issue increases the number of token requests (which is also limited on Google side for security) and is dependent on number of application users.

  💡

  However, it is important to note here that a Service Account combined with the "Anyone with a link" sharing option works perfectly fine. Therefore, basing the integration on the JavaScript library might be less beneficial compared to how much the user's work is interrupted by repeated requests to choose a Google account.
  💡

  The situation may be different when individual application users have varying access to resources on the Google side. For example, User A might see a few more files than User B. A Service Account is not linked to any physical Google user, so it is just a technical account. It will have access to resources shared with the "Anyone with a link" option or when the email address of this account is on the allowed users list. It's worth keeping this in mind when planning your solution and analyzing how detailed your permission system on the Google side is.

• If possible, we decided to get rid off consent screen.

• As the Service Account approach is entirely done in 100% PL/SQL, this is the only skill needed. If you are not familiar with AJAX calls in JavaScript, you will find safe place here. Good information is that APEX_WEB_SERVICE.MAKE_REST_REQUEST function behaves synchronously, so the flow of code is exactly the same as with any other PL/SQL process.

Prerequisites

• Setup your project in Google API console. Comparing to JavaScript approach, it is much simpler - you just need to enable Google Drive API, generate credentials for Service Account and add new key. In my case, I created a new key and saved the JSON file with private key on my laptop. As you can read below in this article, the private key is the core information from the integration perspective.

• As I want to utilize access tokens as much as possible, I decided to create a dedicated table for this. It contains only one row of data inserted right after creating a table. Then, my PL/SQL package will just update that row with fresh access token. No worries, sensitive data will be encrypted!

    -- 1. create table
    create table google_access_token (
      id                      number primary key,
      token_hex               varchar2(4000),
      key_bytes_hex           varchar2(4000),
      is_locked_to_refresh_yn varchar2(1)    not null,
      updated_user            varchar2(100)  default nvl(sys_context('APEX$SESSION', 'APP_USER'), user) not null,
      updated_date            date           default sysdate not null 
    );
  
    -- 2. insert the only row (it will be updated only)
    insert into google_access_token (
      id,
      token_hex,
      key_bytes_hex,
      is_locked_to_refresh_yn
    ) values (
      1,
      null,
      null,
      'N'
    );
    commit;
  

• For data encryption purposes, I went for standard DBMS_CRYPTO package. Make sure your database schema has been granted access to it.

    GRANT EXECUTE ON SYS.DBMS_CRYPTO TO X;
  

• To achieve valid access token which can be used with Google API, you have to follow the official documentation explaining the things. Long story short, you need to prepare a JSON Web Token (JWT) filled by data like your Service Account e-mail address and requested API scope. Then, your private key is used to compute the signature - Google supports RSA using SHA-256 hashing algorithm. To be honent, I could not find any standard package ready to use from Oracle. However, I have found extremely useful package AS_CRYPTO which does most of heavylifting for you.

• And you need my PL/SQL package of course. In this article, I am showing the most important parts here. If you are curious of more details and want something to be explained, please let me know!

Step by Step Setup

How to Request Access Token

In this place, I would like to show how you can perform the operation of requesting an access token. I decided to include code snippets that cover the most important aspects of this challenge.

First, I defined a few constants that store the email address of my Service Account, a list of API scopes needed to perform my task (values separated by spaces), and two URLs. The first one is the Google token service address, and the second is a pre-defined target address to obtain the token, to which I will attach a properly prepared JWT with data.

c_google_api_service_account_email constant varchar2(4000) := 'my-service-account@rewildearth.iam.gserviceaccount.com';
c_google_api_scopes                constant varchar2(4000) := 'https://www.googleapis.com/auth/drive.metadata.readonly';
c_google_api_token_endpoint        constant varchar2(4000) := 'https://oauth2.googleapis.com/token';
c_google_api_token_request_url     constant varchar2(4000) := 'https://oauth2.googleapis.com/token?grant_type=urn:ietf:params:oauth:grant-type:jwt-bearer' || chr(38) ||'assertion=';

The next step is to prepare the JWT content, which consists of two parts called header and claim. To fill the JWT with data, I will use the constants defined earlier and an SQL expression that reflects the expected value described in the documentation.

l_jwt_header := '{"alg":"RS256", "typ":"JWT"}';
l_jwt_claim  := '{' ||
                '"iss":"' || c_google_api_service_account_email || '"' ||
                ',"scope":"' || c_google_api_scopes || '"' ||
                ',"aud":"' || c_google_api_token_endpoint || '"' ||
                ',"exp":' || to_char( round( ( cast(sys_extract_utc(systimestamp) as date) - to_date('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') ) * 24 * 60 * 60 ) + 3600  ) ||
                ',"iat":' || to_char( round( ( cast(sys_extract_utc(systimestamp) as date) - to_date('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') ) * 24 * 60 * 60 )  ) ||
                '}';

The variables prepared with the JWT content can be converted to Base64 in a version ready for transmission in a URL. Here, the AS_CRYPTO package is useful.

l_jwt_header_base64 := as_crypto.base64url_encode(p_txt => l_jwt_header);
l_jwt_claim_base64  := as_crypto.base64url_encode(p_txt => l_jwt_claim);

We are ready to call the function implementing the RS256 algorithm. Fortunately, the AS_CRYPTO package has such a function, so I decided to use it. Note that besides the JWT information, I also pass the private key from the JSON file. This key is stored as a constant in the package. For increased security, it is encrypted using the DBMS_CRYPTO package and stored in hex format. I wanted to ensure that the private key is not stored in plain text form.

l_private_key := f_get_service_account_private_key;

l_sign := as_crypto.sign(src        => utl_raw.cast_to_raw(c => l_jwt_header_base64 || '.' || l_jwt_claim_base64),
                         prv_key    => utl_raw.cast_to_raw(c => l_private_key),
                         pubkey_alg => as_crypto.key_type_rsa,
                         sign_alg   => as_crypto.sign_sha256_rsa);

l_signature := as_crypto.base64url_encode(p_raw => l_sign);     

l_full_jwt_base64 := l_jwt_header_base64 || '.' || l_jwt_claim_base64 || '.' || l_signature;

Having prepared the JWT with signature data, we are ready to call the Google API for obtaining an access token. At this point, I am using the APEX_WEB_SERVICE package.

-- request access token
apex_web_service.g_request_headers.delete;
apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).value := 'application/x-www-form-urlencoded';    
apex_web_service.g_request_headers(2).name := 'Content-Length';
apex_web_service.g_request_headers(2).value := 0;

l_clob := apex_web_service.make_rest_request(
  p_url         => c_google_api_token_request_url || l_full_jwt_base64,
  p_http_method => 'POST'
);

if apex_web_service.g_status_code = 200 then
  apex_json.parse(l_clob);

  -- return new values
  l_return := apex_json.get_varchar2('token_type') || ' ' || apex_json.get_varchar2('access_token'); 

  -- save token in collection or any other place to store it
  p_store_access_token(pi_access_token => l_return);
else
  -- there is a problem with token request
  raise e_token_endpoint_call_error;
end if;

At this stage, we have a working access token that we can use to call the Google API. The token is valid for 60 minutes. After that time, you should generate an access token in a similar way.

How to Call Google API

This place probably looks familiar to you. We can call the Google API using the APEX_WEB_SERVICE package. The full version of the request URL differs slightly from the standard one. First, I'm looking for items located in the folder passed to the input of my procedure, which is in the package specification. Then, I indicate which fields among the file metadata should be returned - in my case, specifically the id and name fields.

-- get access token
l_access_token := f_get_access_token;

-- call the api to list files
apex_web_service.g_request_headers.delete;
apex_web_service.g_request_headers(1).name := 'Authorization';
apex_web_service.g_request_headers(1).value := l_access_token;

l_list_files_api_url := 
  'https://www.googleapis.com/drive/v3/files?' ||
  chr(38) ||
  'q=''' || pi_folder_id || ''' in parents and trashed=false' ||
  chr(38) ||
  'fields=incompleteSearch,nextPageToken,files(id,name)'
  ;

l_clob := apex_web_service.make_rest_request(
  p_url         => l_list_files_api_url,
  p_http_method => 'GET'
);

if apex_web_service.g_status_code = 200 then
  apex_collection.create_collection(
    p_collection_name    => c_collection_name,
    p_truncate_if_exists => 'YES'
  );

  -- transform data got from api and load into apex collection
  for rec in cur_images_from_google_api(p_clob => l_clob) loop
    apex_collection.add_member(
      p_collection_name => c_collection_name,
      p_c001            => rec.name,
      p_c002            => rec.thumbnail_url
    );
  end loop;

  l_return := true;
elsif apex_web_service.g_status_code = 401 then
  -- current access token is not valid anymore, so we try to repeat this function
  -- so get the new token. this function will also save it in its storage
  l_access_token := f_get_access_token(pi_must_get_new_token => true);

  l_return := false;
else
  raise e_api_endpoint_call_error;
end if;

Results of the call in case of status 200 are processed accordingly in my cursor. Much depends on your business case and how you want to work with the data obtained through the API.

It is worth noting two additional issues here. Firstly, the API response may not contain all results. This can happen when the query is executed on a large dataset. In such cases, pagination implementation is necessary, and the API call needs to be repeated for subsequent result pages. The second important issue is how the expiration of the access token is handled. A separate condition for code 401 is provided for such situations. In this case, the function's goal will be to obtain a new token and store it in the table. Consequently, subsequent API calls will use the new token, which can be used for API calls for the next 60 minutes.

Few Words About DBMS_CRYPTO

I have come to the conclusion that storing sensitive data as plain text embedded in the code can have serious consequences for the security of the application.

Cases in which I used the encryption:

• Encryption of the private key, which is stored as a constant in the package.

• Encryption of the access token stored in the table.

I did exactly what the DBMS_CRYPTO documentation describes, including data preparation, encoding, and final encryption.

Side Topic - What to Remember When Storing Access Token in a Table

As you can see, the issue of storing acquired access tokens has been resolved using a table in the database. Each call to the Google API first looks up the table and decrypts the token. After this step, the API call is made. If I encounter a 401 error, it indicates that I need to get a fresh token first and then retry my request. Everything should work the second time.

But... Hold on! Let's imagine a situation where at least two APEX sessions do the API call very close to each other (when looking at the timeline). The first one encounters a 401 error and starts the process of requesting for a new token. The old token is still in the table, which is used by other sessions. This is where the problems begin.

The first session has not put the new token back in the table yet. The other sessions don not have the new token, so they start requesting a new one too. In practice, this means several requests for a new token, when only one request would be needed!

To solve this problem, I came up with a simple mechanism for locking work on the table, which conceptually looks like below. It is a kind of access lock.

declare
  l_google_access_token_row google_access_token%rowtype;
begin
  select *
    into l_google_access_token_row
    from google_access_token
   where id = 1;

  if l_google_access_token_row.is_locked_to_refresh_yn = 'N' then
    update google_access_token
       set is_locked_to_refresh_yn = 'Y'
     where id = 1;

    commit; -- this must happen because other db sessions should see that

    -- DO YOU STUFF HERE
    -- THIS PART CAN RAISE SOME EXCEPTIONS HERE SO REMEMBER TURN THE ROW BACK TO PREVIOUS STEP (N)

    update google_access_token
       set is_locked_to_refresh_yn = 'N'
     where id = 1;

    commit; -- this must happen because other db sessions should see that
  else
    dbms_output.put_line('Row is locked by other user now. Try again in a moment.'); 
  end if;
exception
    update google_access_token
       set is_locked_to_refresh_yn = 'N'
     where id = 1;
    commit;
    -- idea: or just put rollback here...    
end;

Also, as I mentioned in the previous section, I used the DBMS_CRYPTO package to encrypt token data, which is then decrypted for Google API calls.

Side Topic - How Service Account Can Access Resources

Exploring this topic brought me many surprises and a bit of a headache. You might be wondering why. Let me describe my experience to you, and it is worth remembering that as well.

Google Drive offers several options for sharing resources with other users. By default, only the creator of the resource has access to it. If we want to share it with several other people, we usually enter their email addresses, and that's it. It's similar when it comes to a Service Account. The email address of this account must also be on the list. So, we copy this address from the JSON file with the private key, and from that moment, our Service Account "sees" the resources through the Google API.

The situation looks way different when you call the Google API, and it returns results as the Service Account email address has been on the sharing list forever! How is that possible? After all, this email has not yet been added to the access list. In such cases, pay special attention to the global access type. If it is set to "Anyone with the link," knowing the link to any folder on Google Drive allows you to browse its contents. I think this is worth noting, and from my perspective, it seems quite problematic in terms of data security.

Therefore, it's important to mention the scope of the access token. It should be minimal, sufficient to perform operations on the API. In other words, if we use the API to list the contents of a folder, the scope should be limited to that only. A scope that is too rich can be used with bad intentions on Google Drive resource that has the setting 'Anyone with the link'.

Side Topic - Map of Responses from Google Drive API / What is ThumbnailURL

These topic were covered in detail in my previous article, and I highly recommend reading it. In my opinion, this is a quite important aspect of working with the Google Drive API and will result in significant time savings.

Summary

I was so excited when I started this part of my work. Service Accounts are extremely powerful and gives the best what Google integration can bring to users. I am fan of this approach, but I can also imagine that this kind of meta-user will not work in some circustances, policies, etc. Anyway, it is definitely worth it to give it a chance.