Introduction

Oracle APEX Web Credentials supports many standard REST API authentication schemes. However, not all APIs play nicely - some use custom or unsupported authentication flows.

When that happens, securing and reusing credentials becomes harder. You lose the built-in power of APEX Web Credentials and the ability to use features like REST Data Sources.

Goals

• Built a reusable and secure authentication solution for non-standard APIs

• Leveraged Oracle TDE and Data Redaction for at-rest and at-access credential protection

• Enabled native APEX Web Credential reuse through persistent token injection

Use Case

I am working on a project to extract Quote information from servicePath. servicePath is a SaaS based Configure, Price, Quote solution (CPQ) focused on technology sales.

The servicePath REST APIs use an HTTP Header Bearer token for Authentication. A token can be obtained from the Generate Access Token endpoint. Here is where the fun starts!

Getting a servicePath Access Token

The servicePath token service requires that you send a username and password in the body of a POST request:

After calling the token service, you receive a Bearer token (access token) in a JSON response:

{
  "Bearer": "eyJhbG...", 
  "Refresh": "I4-8Cun..."
}

The Bearer (access token) is used in the ‘Authorization’ HTTP Header variable to authenticate when calling servicePath APIs:

curl --location 'https://example.servicepath.io/api/v3/Quotes?page_size=500&last_modified_from=2025-04-23' \
--header 'Content-Type: application/json' \
--header 'Authorization: Bearer eyJhbG...'

The servicePath approach rules out using an APEX Web Credential in the classical sense. But all is not lost; we can still use APEX Web Credentials and benefit from REST Data Sources, etc. Read on to find out how.

Approach

This diagram illustrates the idea I am trying to convey in this post. We use custom code to get and refresh the Bearer token, then store it persistently in an HTTP Header type APEX Web Credential to be available to other APEX sessions.

Storing Credentials Securely

As we cannot use APEX Web Credentials, we must first find a secure way to store the API credentials (in this case, a username and password).

The approach described in this section is much more straightforward and sufficiently secure for most use cases. It relies on Oracle Transparent Data Encryption (TDE) to secure the data at rest, and Oracle Data Redaction to keep the credentials secure from access via SQL. This approach works seamlessly on OCI Autonomous Databases or on the OCI APEX Service, which uses TDE on all tablespaces without any setup required on your part.

Step 1: Create a New Schema

-- For OCI ATP, run as ADMIN
-- Having a separate schema separates securing the API keys from your APEX Parsing Schema.
CREATE USER api_sec
  IDENTIFIED BY "ComplexPassword"
  QUOTA 10M ON users
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  PROFILE default;

-- Create Limited Grants to the new user
GRANT CREATE SESSION                 TO api_sec;
GRANT CREATE TABLE, CREATE PROCEDURE TO api_sec;
ALTER USER api_sec DEFAULT ROLE NONE;

Step 2: Create the Credentials Table in the New Schema

-- Run as user api_sec or admin
CREATE TABLE api_sec.api_credentials 
 (credential_code VARCHAR2(30)   PRIMARY KEY,
  api_user        VARCHAR2(100)  NOT NULL,
  api_pwd         VARCHAR2(4000) NOT NULL);

Step 3: Create a Data Redaction Policy for the New Table

-- Run as ADMIN
-- Any user except for your parsing schema user will see NULL for the API_PWD column
BEGIN
  DBMS_REDACT.ADD_POLICY
   (object_schema   => 'API_SEC',
    object_name     => 'API_CREDENTIALS',
    column_name     => 'API_PWD',
    policy_name     => 'REDCT_API_CRED_PWD',
    function_type   => dbms_redact.full,
    expression      => 'sys_context(''userenv'',''current_user'') <> ''PARSING_SCHEMA''',
    enable          => TRUE);
END;
-- Where PARSING_SCHEMA is your APEX Parsing Schema

Step 4: Create a PL/SQL Package in the New Schema

This package will handle all interactions with the api_credentials table.

Create the package spec in the new API_SEC schema.

Package Spec

CREATE OR REPLACE PACKAGE api_credentials_pk AUTHID DEFINER AS

-- This procedure is specific to servicePath
-- You would create specific procedures similar to this for 
--   every API that requires their own unique authentication approach.
PROCEDURE set_servicepath_credentials 
  (p_apex_credential IN VARCHAR2,
   p_token_api_url   IN VARCHAR2);

-- Create a new record in api_credentials
PROCEDURE create_credential
 (p_credential_code IN api_credentials.credential_code%TYPE,
  p_api_user        IN api_credentials.api_user%TYPE,
  p_new_password    IN api_credentials.api_pwd%TYPE);

-- TBD Create APIs to change a password and delete a credential.

END api_credentials_pk;

• Create with DEFINER rights so that when we call the APIs from our APEX parsing schema, the APIs can access the un-redacted password in the API_SEC schema.

Package Body

CREATE OR REPLACE PACKAGE BODY api_credentials_pk AS

----------------------------------------------------------
-- Notice that we never return the value for API_PWD.
-- This prevents a developer from every seeing the password.
PROCEDURE set_servicepath_credentials
  (p_apex_credential IN VARCHAR2,
   p_token_api_url   IN VARCHAR2) IS

  CURSOR cr_credentials IS
    SELECT api_user
    ,      api_pwd
    FROM   api_credentials
    WHERE  credential_code = 'SERVICEPATH';

  lr_credentials      cr_credentials%ROWTYPE;
  l_body_json         VARCHAR2(1000);
  l_response          CLOB;
  l_access_token      VARCHAR2(4000);
  l_response_obj      json_object_t;

BEGIN

  apex_automation.log_info ('** Start Refresh servicePath Bearer Token **');
  apex_automation.log_info ('APEX Credential: '|| p_apex_credential);

  -- Get servicePath Credentials
  OPEN cr_credentials;
  FETCH cr_credentials INTO lr_credentials;
  CLOSE cr_credentials;

  -- Build JSON with Username and Password.
  l_body_json := JSON_OBJECT('Username' VALUE lr_credentials.api_user,
                             'Password' VALUE lr_credentials.api_pwd);

  -- Set HTTP Headers.
  apex_web_service.set_request_headers 
   (p_name_01   => 'Content-Type', 
    p_value_01  => 'application/json',
    p_name_02   => 'Accept', 
    p_value_02  => 'application/json',
    p_name_03   => 'User-Agent', 
    p_value_03  => 'APEX-Integration',
    p_reset     => TRUE);

  -- Call the servicePath Token Web Service.
  l_response := apex_web_service.make_rest_request
                 (p_url         => p_token_api_url,
                  p_body        => l_body_json,
                  p_http_method => 'POST');
  apex_automation.log_info ('Token API HTTP Response: '|| apex_web_service.g_status_code);

  IF apex_web_service.g_status_code <> 201 THEN
    apex_automation.log_error ('Token API Call Failed. Response: '|| l_response);
    raise_application_error(-20010, 'Error getting servicePath Token. HTTP Status Code: ' || apex_web_service.g_status_code);
  ELSE
    -- Store the Bearer token persistently in an APEX HTTP Header Web Credential
    l_response_obj := json_object_t.parse(l_response);
    l_access_token := l_response_obj.get_String('Bearer');
    apex_credential.set_persistent_credentials
     (p_credential_static_id => p_apex_credential,
      p_key                  => 'Authorization',
      p_value                => 'Bearer ' || l_access_token);
    apex_automation.log_info ('Token Set: '|| SUBSTR(l_access_token,1,10)||'...');
  END IF;

  apex_automation.log_info ('** End Refresh servicePath Bearer Token **');

EXCEPTION WHEN OTHERS THEN 
  apex_automation.log_error ('Unhandled Error ['|| SQLERRM || ']');
  RAISE; 
END set_servicepath_credentials;

----------------------------------------------------------
PROCEDURE create_credential 
 (p_credential_code IN api_credentials.credential_code%TYPE,
  p_api_user        IN api_credentials.api_user%TYPE,
  p_new_password    IN api_credentials.api_pwd%TYPE) IS

BEGIN
  INSERT INTO api_credentials
    (credential_code, api_user, api_pwd)
  VALUES 
    (p_credential_code, p_api_user, p_new_password);
END create_credential;

END api_credentials_pk;

Step 5: Grant Execute to the Package

Next, allow your APEX parsing schema to run APIs in the package:

-- Run as API_SEC or ADMIN
GRANT EXECUTE ON api_sec.api_credentials_pk TO <<PARSING_SCHEMA>>;

Step 6: Add a Credential

Create a credential:

-- Run from APEX Parsing Schema.
BEGIN
  api_credentials_pk.create_credential
   (p_credential_code => 'SERVICEPATH',
    p_api_user        => 'myusername',
    p_new_password    => 'mypassword');
END;

Step 7: Lock the New Schema

-- Run as ADMIN
-- Prevents anyone logging into this schema
ALTER USER api_sec ACCOUNT LOCK;

Summary

• No user can see the API_PWD column, except for the ADMIN user. Even the API_SEC user cannot see it because we locked the account.

• There is no API to get the password. Instead, we have an API to set the APEX Web Credential with the Access token. This prevents developers from seeing the API password (or the token).

• No schemas have access to the api_credentials table except for API_SEC (which is locked).

• The only way to affect the api_credentials is via the PL/SQL package api_credentials_pk which can only be run from your APEX parsing schema.

Putting it all Together

In this section, we will create an APEX Web Credential to store the Bearer Token and an APEX Automation to update the APEX Web Credential with a new Bearer token on a schedule.

APEX Web Credential

Before creating the Automation, we must create an ‘HTTP Header’ type APEX Web Credential to store the Bearer Token:

The set_servicepath_credentials procedure uses APEX_CREDENTIAL.SET_PERSISTENT_CREDENTIALS to store the Bearer token. apex_credential.set_persistent_credentials sets the Credential Name and Secret so all APEX sessions can utilize it.

BEGIN
  apex_credential.set_persistent_credentials
   (p_credential_static_id => p_apex_credential,
    p_key                  => 'Authorization',
    p_value                => 'Bearer ' || l_access_token);
END;

• The p_key parameter is stored in the ‘Credential Name’ field of the Web Credential.

• The p_value parameter is stored in the ‘Credential Secret’ field of the Web Credential.

There is an overloaded version of this procedure. It does the same thing with different parameter names.

BEGIN
  apex_credential.set_persistent_credentials
   (p_credential_static_id => p_apex_credential,
    p_username             => 'YourUserName',
    p_password             => 'YourPassword');
END;

APEX Automation

We can use an APEX Automation to update the Web Credential with a new Bearer token on a schedule. In the case of servicePath, the Bearer token is valid for eight hours. Given this, we may want to run the automation every seven hours.

• In the Automation Action, we call the set_servicepath_credentials API to set the Bearer token in the APEX Credential with a static ID called ‘servicepath’.

• We also pass in the token URL for the servicePath token endpoint.

Using the Web Credential

Now that the Bearer token is stored persistently in an APEX Web Credential (and refreshed on a schedule), we can use the HTTP Header type APEX Web Credential the same way as any other APEX Web Credential.

Used in APEX_WEB_SERVICE:

  l_response := apex_web_service.make_rest_request
                 (p_url                   => l_api_url,
                  p_http_method           => 'GET',
                  p_credential_static_id  => 'servicepath');

Used in an APEX REST Data Source:

Conclusion

While Oracle APEX Web Credentials don’t natively support every API authentication scheme, with the right architecture, we can work around those limitations without compromising on security or maintainability.

In this example, we:

✅ Handled non-standard API auth — Built a flow to retrieve and refresh Bearer tokens from a servicePath API that doesn’t follow standard auth protocols

✅ Secured credentials effectively — Used Oracle Transparent Data Encryption and Data Redaction to store credentials securely on OCI ATP without external key management

✅ Enabled full APEX integration — Persisted tokens in APEX Web Credentials so REST Data Sources and APEX_WEB_SERVICE calls can use them transparently

✅ Automated refresh securely — Leveraged APEX Automation to update tokens on schedule, preserving a fully native APEX experience