Fetch API for Oracle APEX - Part 3: Security SIMPLIFIED

Fetch API series:

• Part 1: Basic Usage
• Part 2: Securing Endpoints
• Part 3: Security SIMPLIFIED

In my last blog post, I explained how to make the Fetch API work against an APEX session using OAuth. I was quite proud of the solution, but turns out there is a better way.

Instead of using OAuth, we will use First Party Authentication, allowing an APEX Session to securely connect to ORDS using the Fetch API.

Here's my demo app for Part 3. Use login test1, test2 or test3 with any password. The goal of this app: Show different cards based on :app_user. The top region is a regular classic report and the bottom region is a secured Fetch API call.

How to secure the Fetch API

Prerequisite: DDL for demo

Here's the DDL that I will use for the demo.

create table projects (
  id number not null enable
	, project varchar2(30) not null enable
	, icon_css_classes varchar2(50)
	, created timestamp (6) with local time zone
	, created_by varchar2(255)
)
/

insert into projects (id, project, icon_css_classes, created, created_by) values (1, 'Maintain Support Systems', 'fa-awesome-face fa-lg', localtimestamp, 'TEST1');
insert into projects (id, project, icon_css_classes, created, created_by) values (2, 'Load Software', 'fa-dot-circle-o fa-lg', localtimestamp, 'TEST1');
insert into projects (id, project, icon_css_classes, created, created_by) values (3, 'Email Integration', 'fa-hand-grab-o fa-lg', localtimestamp, 'TEST1');
insert into projects (id, project, icon_css_classes, created, created_by) values (4, 'Documentation', 'fa-ambulance fa-lg', localtimestamp, 'TEST1');
insert into projects (id, project, icon_css_classes, created, created_by) values (5, 'Training', 'fa-credit-card fa-lg', localtimestamp, 'TEST1');
insert into projects (id, project, icon_css_classes, created, created_by) values (6, 'Bug Tracker', 'fa-spinner fa-lg', localtimestamp, 'TEST2');
insert into projects (id, project, icon_css_classes, created, created_by) values (7, 'Migrate Old Systems', 'fa-scissors fa-lg', localtimestamp, 'TEST2');
insert into projects (id, project, icon_css_classes, created, created_by) values (8, 'Software Projects Tracking', 'fa-rocket fa-lg', localtimestamp, 'TEST2');
insert into projects (id, project, icon_css_classes, created, created_by) values (9, 'Public Website', 'fa-bicycle fa-lg', localtimestamp, 'TEST2');
insert into projects (id, project, icon_css_classes, created, created_by) values (10, 'Early Adopter Release', 'fa-cutlery fa-lg', localtimestamp, 'TEST2');
insert into projects (id, project, icon_css_classes, created, created_by) values (11, 'Environment Configuration', 'fa-flask fa-lg', localtimestamp, 'TEST3');
insert into projects (id, project, icon_css_classes, created, created_by) values (12, 'Customer Satisfaction Survey', 'fa-hashtag fa-lg', localtimestamp, 'TEST3');
insert into projects (id, project, icon_css_classes, created, created_by) values (13, 'Convert Excel Spreadsheet', 'fa-shield fa-lg', localtimestamp, 'TEST3');
insert into projects (id, project, icon_css_classes, created, created_by) values (14, 'Upgrade Equipment', 'fa-tags fa-lg', localtimestamp, 'TEST3');	

Step 1: Create an ORDS endpoint

Create ORDS module, URI template and Resource Handler as follow:

RESTful Service Module: fetch3
Module Base Path: /fetch3/
URI Template: projects
Full URL: https://apex.oracle.com/pls/apex/vmorneau/fetch3/projects
Method: GET
Source Type: Collection Query

Source:

select p.id
  , p.project card_title
  , p.icon_css_classes card_icon
from projects p
where p.created_by = :current_user -- this is equivalent to :app_user in APEX
order by card_title

Because this query lives in ORDS, it does't have access to :app_user like we would need. Instead, ORDS offers an implicit parameter :current_user which will play the same role. See in Step 3 how to make :current_user have the same value as the APEX session :app_user.

Step 2: Adding ORDS security

Create an ORDS privilege and have it protect our fetch3 endpoint.

-- Create ORDS privilege to protect module fetch3
declare
  l_roles     owa.vc_arr;
  l_modules   owa.vc_arr;
  l_patterns  owa.vc_arr;
begin
  l_modules(1) := 'fetch3';

  ords.define_privilege(
    p_privilege_name => 'fetch3_priv',
    p_roles          => l_roles,
    p_patterns       => l_patterns,
    p_modules        => l_modules,
    p_label          => 'fetch3_priv',
    p_description    => '',
    p_comments       => null
  );

  l_roles.delete;
  l_modules.delete;
  l_patterns.delete;

  commit;
end;
/

This secures access to the fetch3 endpoint, so only a valid APEX Session can request access.

Step 3: Add Fetch API JavaScript to your page

To call ORDS and pass the APEX :app_user we need to set a particular HTTP Header: Apex-Session.

const request = await fetch('https://apex.oracle.com/pls/apex/vmorneau/fetch3/projects', {
    method: "GET",
    headers: { 'Apex-Session': '&APP_ID.,&SESSION.' }
  });

Apex-Session is a header object containing the current APP_ID and SESSION value. In conjunction with the APEX cookie (sent implicitly), together they allow to authenticate to ORDS as First Party Authentication.

Complete JavaScript to your page Function and Global Variable Declaration:

const doFetch = async () => {
  // Fetches an ORDS endpoint (GET)
  const request = await fetch('https://apex.oracle.com/pls/apex/vmorneau/fetch3/projects', {
    method: "GET",
    headers: { 'Apex-Session': '&APP_ID.,&SESSION.' }
  });
  // Turns the request into readable text
  const response = await request.text();
  // Turns into a JSON object for easier manipulation of the data
  const data = JSON.parse(response);

  // Design the card template we want to build
  const templateBeforeRows = `<ul class="t-Cards u-colors t-Cards--featured t-Cards--block force-fa-lg t-Cards--displayIcons t-Cards--5cols t-Cards--animColorFill t-Cards--hideBody">`;
  const template = `
  <li class="t-Cards-item #CARD_MODIFIERS#">
    <div class="t-Card">
      <div class="t-Card-wrap">
        <div class="t-Card-icon u-color #CARD_COLOR#"><span class="t-Icon fa #CARD_ICON#"><span class="t-Card-initials" role="presentation">#CARD_INITIALS#</span></span></div>
        <div class="t-Card-titleWrap"><h3 class="t-Card-title">#CARD_TITLE#</h3><h4 class="t-Card-subtitle">#CARD_SUBTITLE#</h4></div>
        <div class="t-Card-body">
          <div class="t-Card-desc">#CARD_TEXT#</div>
          <div class="t-Card-info">#CARD_SUBTEXT#</div>
        </div>
        <span class="t-Card-colorFill u-color #CARD_COLOR#"></span>
      </div>
    </div>
  </li>
  `;
  const templateAfterRows = `</ul>`;

  // Append the Cards wrapper (before and after rows) to a region on our page
  apex.jQuery("#RegionFetchAPI").append(templateBeforeRows);
  apex.jQuery("#RegionFetchAPI").append(templateAfterRows);

  /**
   * Turns an object coming from an ORDS request into a usable placeholder for apex.util.applyTemplate
   * @param {object} item - Object from an ORDS request
   **/
  const getPlaceholders = item => {
    let placeholders = {};

    for (let key in item) {
      placeholders[key.toUpperCase()] = item[key];
    }
  
    return placeholders;
  };

  // Append the rows coming from ORDS
  data.items.forEach(item => {
    let options = { placeholders: getPlaceholders(item) };
    let result = apex.util.applyTemplate(template, options);
    apex.jQuery("#RegionFetchAPI .t-Cards").append(result);
  });
};

And of course, execute the JavaScript function on page load:

doFetch();

How did we get here?

Following [Part 2], Kris Rice replied to my tweet with 1 line of code, destroying my entire approach:

It's one of my favorite things: be offered a free code tip that ends up trimming my code. I enjoy the learning journey so I'm more than happy to be off, but in all fairness Oracle did not document the Apex-Session HTTP header very well. The only documentation I could find after Kris' tweet was this: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/20.3/aelig/REST-development-tutorial.html#GUID-FB0CCF4A-FC81-4726-963E-75E0E26C3B8E and it uses an old xhrAPI.

I still think there is value in [Part 2] because the OAuth method can be used outside of APEX, so the entire process is more universal. However ORDS and APEX being so integrated with each other, there is a clear winner for APEX development.

Anyway I hope this new way is useful for anyone.