Configuration of Oracle Database and APEX application – use of Google Service Account. The ‘Google Workspace friendly application’ series.

29 December 2020 Wojciech Sowa

The ‘Google Workspace friendly application’ series – table of contents

  1. What is Google Workspace, and why should we integrate with it?
  2. Project configuration in Google API Console – use of Google API Client Library for JavaScript – Part I.
  3. Project configuration in Google API Console – use of Google API Client Library for JavaScript – Part II.
  4. Configuration of Oracle Database and APEX application – use of Google API Client Library for JavaScript.
  5. Project configuration in Google API Console – use of Google Service Account.
  6. Configuration of Oracle Database and APEX application – use of Google Service Account.

Introduction

In the previous article, we configured the project in Google API Console and created the Service Account. Now it’s time to configure Oracle Database and APEX application to use the Google Service Account. According to Google’s documentation https://developers.google.com/identity/protocols/oauth2#serviceaccount, Service Account can implement the OAuth 2.0 protocol based on available libraries for many programming languages or use the dedicated code responsible for creating JSON Web Tokens objects. In our example, we will use the second approach, where the component responsible for creating the JWT will be a dedicated Java class with an appropriate method.

The created JSON Web Token must be signed with the RSA algorithm using SHA-256 as the hash function. At the time of writing, this is the only signature mechanism supported by Google OAuth 2.0 authorization servers. 

Step by step guide

The first step will be to add a new permission to the ACL list, which will associate the database user ‘APEX_200200’ (for APEX 20.2 version) with the Google token service address. The code below creates a separate ACL, but you can use an existing ACL as well. Pay attention to the address format in the v_acl_host variable – it does not have the HTTP(s) and www prefixes.

In the same way, add a new permission to the ACL list for the Google API service. However, you can type the address of the specific API. Create a new entry in the ACL list or use the existing one.

Create an Oracle Wallet or use an existing one to add a certificate responsible for HTTPS communication with the token service. Activate the ‘Auto Login’ function at the configuration stage.

Warning! After creating the wallet, ensure that the system user ‘oracle’ has access to the wallet files. Otherwise, Google API calls from PL/SQL will raise an exception!

Run the code below to install a dedicated Java class responsible for creating JWT. Remember to change the PRIVATE_KEY value to the value of the private key from the JSON file downloaded at the stage of creating the ‘Service Account’ account.

Install the ‘google_auth_pkg’ package. There is a function ‘f_get_google_token()’, which will return a token for communication with Google API using all the mechanisms described in the previous steps. The token is valid for 60 minutes from the moment when it is generated.

Briefly about the input parameters of the ‘f_get_google_token’ function. The value for the ‘pi_iss’ parameter must be the value of the ‘client_email’ attribute from the JSON file downloaded at the stage of creating the ‘Service Account’ account.

The ‘pi_scope’ parameter is responsible for determining the range of permissions to use the API. Google API has an extensive list of scope for enabled API libraries. Thanks to this, it is possible to indicate permitted actions on the API. Subsequent scopes are separated from each other with a single space.

What’s more, the use of the scopes provided in this parameter must be on the list of authorized scopes set by the Google Workspace administrator in your organization. Otherwise, access to the unauthorized API will not be possible, which is another aspect of API access control.

Values ​​for the ‘pi_iss’ and ‘pi_scope’ parameters can be stored as package constants or in the table with your application’s properties.

Almost done! As the Service account is not associated with any personal Google account, you still need to share your Google Drive content with this special account. Otherwise, you will get a response ‘404 Not Found’. When sharing, enter the ‘client_email’ from the JSON file describing the Service account’s private data.

Now we are ready to use Google API in combination with the APEX application and Service Account. The only thing you have to remember is requesting a new access token after the current token expires.

This article closes the entire series of articles, where I described how to configure both Google and Oracle environments. Now the sky is the limit, so don’t wait any longer and create your own Google Workspace friendly application!

Tagged with: , , , ,

Hire us!

Pretius is a software development company.
We create web applications using: Java, Oracle DB, Oracle Apex, AngularJS.
Contact us to talk about how we can help you with your software project!