The ‘Google Workspace friendly application’ series – table of contents
- What is Google Workspace, and why should we integrate with it?
- Project configuration in Google API Console – use of Google API Client Library for JavaScript – Part I.
- Project configuration in Google API Console – use of Google API Client Library for JavaScript – Part II.
- Configuration of Oracle Database and APEX application – use of Google API Client Library for JavaScript.
- Project configuration in Google API Console – use of Google Service Account.
- 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
-- ACL - from SYS user - for Service Account DECLARE v_acl_name VARCHAR2(30) := 'apex_20_2_acl_sa.xml'; v_acl_desc VARCHAR2(100) := 'Connection from APEX to Google Auth Token Service'; v_acl_pric VARCHAR2(30) := 'APEX_200200'; -- change if you installed newer APEX v_acl_host VARCHAR2(30) := 'oauth2.googleapis.com'; v_acl_port_lower NUMBER := null; -- change if needed v_acl_port_upper NUMBER := null; -- change if needed BEGIN dbms_network_acl_admin.create_acl(acl => v_acl_name, description => v_acl_desc, principal => v_acl_pric, is_grant => true, privilege => 'connect'); dbms_network_acl_admin.add_privilege(acl => v_acl_name, principal => v_acl_pric, is_grant => true, privilege => 'resolve'); dbms_network_acl_admin.assign_acl(acl => v_acl_name, host => v_acl_host, lower_port => v_acl_port_lower, upper_port => v_acl_port_upper); COMMIT; END; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
DECLARE v_acl_name VARCHAR2(30) := 'apex_20_2_acl.xml'; v_acl_desc VARCHAR2(100) := 'Connection from APEX to Google API'; v_acl_pric VARCHAR2(30) := 'APEX_200200'; -- change if you installed newer APEX v_acl_host VARCHAR2(30) := '*.googleapis.com'; v_acl_port_lower NUMBER := null; -- change if needed v_acl_port_upper NUMBER := null; -- change if needed BEGIN dbms_network_acl_admin.create_acl(acl => v_acl_name, description => v_acl_desc, principal => v_acl_pric, is_grant => true, privilege => 'connect'); dbms_network_acl_admin.add_privilege(acl => v_acl_name, principal => v_acl_pric, is_grant => true, privilege => 'resolve'); dbms_network_acl_admin.assign_acl(acl => v_acl_name, host => v_acl_host, lower_port => v_acl_port_lower, upper_port => v_acl_port_upper); COMMIT; END; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
// Service Account - Java Class create or replace and compile java source named "SHA256RSA" as import java.nio.charset.StandardCharsets; import java.security.KeyFactory; import java.security.PrivateKey; import java.security.Signature; import java.security.spec.PKCS8EncodedKeySpec; import java.util.Base64; public class SHA256RSA { private final static String PRIVATE_KEY = "-----BEGIN PRIVATE KEY-----\n***\n-----END PRIVATE KEY-----\n"; private final static String RSA_SHA256 = "SHA256withRSA"; private final static String KEY_TAG_PATTERN = "-----[A-Z ]+-----"; private final static String RSA_ALGORITH = "RSA"; private static String getBase64URLString(String str) { return Base64.getUrlEncoder().withoutPadding().encodeToString(str.getBytes(StandardCharsets.UTF_8)); } private static byte[] keyStringToBytes(String keyString) { keyString = keyString .replaceAll(KEY_TAG_PATTERN, "") .replaceAll("\\s", ""); return Base64.getDecoder().decode(keyString); } private static PrivateKey getPrivateKey(String privateKeyStr) throws Exception { byte[] privateKeyBytes = keyStringToBytes(privateKeyStr); PKCS8EncodedKeySpec keySpec = new PKCS8EncodedKeySpec(privateKeyBytes); KeyFactory kf = KeyFactory.getInstance(RSA_ALGORITH); PrivateKey privateKey = kf.generatePrivate(keySpec); return privateKey; } private static String sign(String content, String privateKeyString) throws Exception { PrivateKey privateKey = getPrivateKey(privateKeyString); Signature signer = Signature.getInstance(RSA_SHA256); signer.initSign(privateKey); signer.update(content.getBytes("UTF-8")); byte[] signatureBytes = signer.sign(); String signature = Base64.getUrlEncoder().encodeToString(signatureBytes); return signature; } public static String signContent(String header, String claim) throws Exception { String jwtContent = getBase64URLString(header) + "." + getBase64URLString(claim); return jwtContent + "." + sign(jwtContent, PRIVATE_KEY); } } |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
create or replace package google_auth_pkg as function f_get_google_token(pi_iss in varchar2, pi_scope in varchar2) return varchar2; end google_auth_pkg; / create or replace package body google_auth_pkg as function f_get_jwt_signed(pi_header in varchar2, pi_claim in varchar2) return varchar2 as language java name 'SHA256RSA.signContent(java.lang.String, java.lang.String) return java.lang.String'; function f_get_google_token(pi_iss in varchar2, pi_scope in varchar2) return varchar2 as l_header varchar2(4000); l_claim varchar2(4000); l_jwt varchar2(4000); l_token_url varchar2(4000); l_token_clob clob; l_return_token varchar2(4000); begin l_header := '{"alg":"RS256", "typ":"JWT"}'; l_claim := '{'|| '"iss":"'|| pi_iss || '",'|| '"scope":"' || pi_scope ||'",' || '"aud":"https://oauth2.googleapis.com/token",'|| '"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)) || '}'; l_jwt := f_get_jwt_signed(pi_header => l_header, pi_claim => l_claim); l_token_url := 'https://oauth2.googleapis.com/token?grant_type=urn%3Aietf%3Aparams%3Aoauth%3Agrant-type%3Ajwt-bearer' || chr(38)|| 'assertion=' || l_jwt; apex_web_service.g_request_headers.delete; apex_web_service.g_request_headers(1).name := 'Content-Length'; apex_web_service.g_request_headers(1).value := 0; l_token_clob := apex_web_service.make_rest_request(p_url => l_token_url, p_http_method => 'POST', p_wallet_path => 'file:C:\app\pretius\product\18.0.0\admin\wallet'); apex_json.parse(l_token_clob); l_return_token := apex_json.get_varchar2(p_path => 'token_type') || ' ' || apex_json.get_varchar2(p_path => 'access_token'); return l_return_token; exception when others then raise; end f_get_google_token; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
declare v_access_token varchar2(4000); v_file_id varchar2(4000) := 'your_file_id'; v_clob clob; begin v_access_token := google_auth_pkg.f_get_google_token(pi_iss => 'pretius-blog@pretius-blog.iam.gserviceaccount.com', pi_scope => 'https://www.googleapis.com/auth/drive.readonly https://www.googleapis.com/auth/spreadsheets.readonly'); dbms_output.put_line(v_access_token); apex_web_service.g_request_headers.delete; apex_web_service.g_request_headers(1).name := 'Authorization'; apex_web_service.g_request_headers(1).value := v_access_token; v_clob := apex_web_service.make_rest_request(p_url => 'https://www.googleapis.com/drive/v3/files/' || v_file_id, p_http_method => 'GET', p_wallet_path => 'file:C:\app\pretius\product\18.0.0\admin\wallet'); dbms_output.put_line(v_clob); end; |
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!
