Securely using API Keys in Oracle APEX

APEX Web Credentials

Web Credentials securely store and encrypts authentication credentials for use by Oracle APEX components and APIs. Credentials cannot be retrieved back in clear text. Credentials are stored at the workspace-level and therefore are visible in all applications.

You can protect Web credentials by adding valid URLs to the Valid for URLs attribute. Adding URLs to the Valid for URLs attribute prevents APEX from accidentally sending a sensitive credentials to a different server. Whenever a Web credential is used, APEX checks whether the URL matches what is defined in defined in the Valid for URLs attribute.

When adding URLs to this attribute, place each URL into a new line. The URL endpoint being used must start with one of the URLs provided here.

Web Credentials can be created in two ways - using the UI of the APEX Builder or using the APEX_CREDENTIAL APIs.

REST API calls without using Web Credentials

Here are examples of using REST API Keys is PL/SQL procedures, using the apex_web_service API. In the first example, the API key is required in a HTTP Header, specified by the REST API provider. It could be named differently - in the example below - it's called x-api-key. In the second example, the API key is expected as part of the URL - as URL Query String. In the example below, it's called apiKey. Both examples require different approach when being used - the HTTP Headers need to be set (using apex_web_service.g_request_headers) before the REST API (using apex_web_service.make_rest_request).

💡

It is the REST API provider's decision how the API Keys should be supplied. So check their documentation first - there are often nice cURL and Postman examples included. apex_web_service supports all possible types - HTTP Headers, URL Query Strings or Body Parameters.

💠 When API Key is provided using the HTTP Header

-- APEX_WEB_SERVICE.MAKE_REST_REQUEST
--  Calling a REST Service which uses the API Key in the HTTP Header
DECLARE
    l_body     CLOB;
    l_response CLOB;
BEGIN

    apex_web_service.clear_request_headers;
    apex_web_service.g_request_headers(1).name  := 'Content-Type';
    apex_web_service.g_request_headers(1).value := 'application/json';     
    apex_web_service.g_request_headers(2).name  := 'x-api-key';
    apex_web_service.g_request_headers(2).value := 'YOUR_API_KEY';   

    l_body := '{some web service body parameters if needed}'

    l_response := apex_web_service.make_rest_request (
       p_url         => 'https://some_rest_service.com/v1/messages',
       p_http_method => 'POST',
       p_body        => l_body ); 

END;

💠 When API Key is provided using URL Query String

-- APEX_WEB_SERVICE.MAKE_REST_REQUEST
--  Calling a REST Service which uses the API Key as URL Query String
DECLARE
    l_body     CLOB;
    l_response CLOB;
BEGIN

    apex_web_service.clear_request_headers;
    apex_web_service.g_request_headers(1).name  := 'Content-Type';
    apex_web_service.g_request_headers(1).value := 'application/json'; 

    l_body := '{some web service body parameters if needed}'

    l_response := apex_web_service.make_rest_request (
       p_url         => 'https://another_rest_service.com/v1/messages&apiKey=YOUR_API_KEY',
       p_http_method => 'POST',
       p_body        => l_body ); 

END;

❗️Although the above examples look good - there is one major downside:
In both cases your API keys are exposed in plain text! It is ofcourse a major security issue and not a best practice.
💡 A possible solution - although not optimal - is to store such Keys and other sensitive information in special configuration tables in your database. If configured correctly, they could only be exposed for use to specific packages (and/or users) and so limiting any unauthorized people from seeing them.

Creating Web Credentials

Oracle APEX supports the following authentication types:

Basic Authentication - Sends username and password in Base64-encoded form as the Authorization request header.
OAuth2 Client Credentials - Oracle APEX exchanges the client ID and client secret for an Access Token using a token server URL. The access token is then used to perform the actual request. If the access token is expired, Oracle APEX will transparently request a new one.
OCI Native Authentication - Oracle APEX signs requests to the Oracle Cloud Infrastructure (OCI) REST API. Only available in Oracle Database 18c and higher.
HTTP Header - The credential is added to the REST request as an HTTP Header. The name of the credential is used as the HTTP Header name, and the Secret of the credential is used as the HTTP Header value.
URL Query String - The credential is added to the URL of the REST request as a Query String Parameter (for example: ?name=value).
Key Pair - A key pair credential consists of a public key, openly shared for encrypting data, and a private key, securely kept secret for decrypting data, together ensuring a secure data exchange.

1️⃣ Creating Web Credentials using the APEX Builder

💡

To create a new credential using the APEX Builder, you need to go to: App Builder / Workspace Utilities / All Workspace Utilities / Web Credentials

💠 When using API Key in the HTTP Header

apex_web_service.g_request_headers(2).name  := 'x-api-key';
apex_web_service.g_request_headers(2).value := 'YOUR_API_KEY';

When using the HTTP Header type, you define the Header Name and Header Value (API Key), as they should be entered as follows:

name ➡️ Credential Name (for example x-api-key)
value ➡️ Credential Secret (your API Key)

💠 When using API Key as URL Query String

l_response := apex_web_service.make_rest_request (
  p_url         => 'https://xxx.com/v1/messages&apiKey=YOUR_API_KEY',
  p_http_method => 'POST',
  p_body        => l_body );

When using the URL Query String type, you define the Query String Parameters (for example: ?apiKey=YOUR_API_KEY (or ?name=value) ) as follows:

name ➡️ Credential Name (for example apiKey)
value ➡️ Credential Secret (your API Key)

💡

Learn all about Web Credentials and the APEX Builder use in the Oracle Documentation 📚: https://docs.oracle.com/en/database/oracle/apex/23.2/htmdb/managing-credentials.html

2️⃣ Creating Web Credentials by APEX_CREDENTIAL API

To create a new Credential, using the APEX_CREDENTIAL API, you need to call two methods - create_credential (to create it with the associated credential name and type), followed by set_persistent_credential (to set the credential secret). Set_persistent_credential comes with two different signatures, depending on the type of Credentials you are creating.

💡

To see the full list of methods supported by APEX_CREDENTIAL, visit the Oracle APEX API Reference Documentation 📚: https://docs.oracle.com/en/database/oracle/apex/23.2/aeapi/APEX_CREDENTIAL

Here is a quick example:

BEGIN
  -- Set the Workspace.
  apex_util.set_workspace(p_workspace => 'MY_WORKSPACE');

  -- Create the Credential
  apex_credential.create_credential (
    p_credential_name      => 'XXXXXX API Key',
    p_credential_static_id => 'MY_HTTP_HEADER_API_KEY',
    p_authentication_type  => apex_credential.C_TYPE_HTTP_HEADER,
    p_scope                => NULL,
    p_allowed_urls         => apex_t_varchar2('https://some_rest_service.com/v1/messages'),
    p_prompt_on_install    => true,
    p_credential_comment   => 'To be used with the XXXXXX PL/SQL REST API calls.');

  -- Add the API Key / Client Secret for the new Web Credential
  apex_credential.set_persistent_credentials (
    p_credential_static_id => 'MY_HTTP_HEADER_API_KEY',
    p_client_id            => 'x-api-key',
    p_client_secret        => 'YOUR_API_KEY' );

  -- Add the API Key / Client Secret for the new Web Credential
  /* apex_credential.set_persistent_credentials (
    p_credential_static_id => 'MY_HTTP_HEADER_API_KEY',
    p_username             => 'x-api-key',
    p_password             => 'YOUR_API_KEY' ); */  

  -- Adding the API credentials name and value seems to work with both
  --  set_persistent_credentials signatures, which is quite confusing, 
  --  as they should be used for the Basic Authentication, OAuth2 
  --  and OCI Authentication types (as the Oracle Documentation suggests)

  COMMIT;

END;

Similarly, you can create Credentials of type URL Query String. Just change the following line:

p_authentication_type  => apex_credential.C_TYPE_HTTP_QUERY_STRING

❗️The Oracle APEX API Reference Documentation does not have an example of setting the above two types of credentials using the procedure apex_credential.set_persistent_credentials.

However, I have tested it and it works when you use both of the versions above. It will be highly appreciated if Oracle do two things:
🔸 Add more examples in the Documentation to cover the cases when HTTP Header and URL Query String are used as Authentication type.
🔸 Add another parameter (and/or set_persistent_credentials signature) to match the APEX Builder UI inputs for HTTP Header and URL Query String.

Making REST requests using the newly created Web Credentials

After we have the Web Credentials created (through the APEX Builder UI or the APEX_CREDENTIAL API), we are ready to use them in our code. The only thing needed is to get the Credential Static Id (MY_HTTP_HEADER_API_KEY or MY_QUERY_STRING_API_KEY in the examples above) that you set on creation.

💠 When API Key is provided using the HTTP Header

-- APEX_WEB_SERVICE.MAKE_REST_REQUEST
--  Calling a REST Service which uses the API Key in the HTTP Header
DECLARE
    l_body     CLOB;
    l_response CLOB;
BEGIN

    apex_web_service.clear_request_headers;
    apex_web_service.g_request_headers(1).name  := 'Content-Type';
    apex_web_service.g_request_headers(1).value := 'application/json';
    --Not needed anymore, they will be added by the apex_web_service.make_rest_request call     
    --apex_web_service.g_request_headers(2).name  := 'x-api-key';
    --apex_web_service.g_request_headers(2).value := 'YOUR_API_KEY';   

    l_body := '{some web service body parameters if needed}'

    l_response := apex_web_service.make_rest_request (
       p_url         => 'https://some_rest_service.com/v1/messages',
       p_http_method => 'POST',
       p_body        => l_body,
       p_credential_static_id => 'MY_HTTP_HEADER_API_KEY' ); 

END;

💠 When API Key is provided using URL Query String

-- APEX_WEB_SERVICE.MAKE_REST_REQUEST
--  Calling a REST Service which uses the API Key as URL Query String
DECLARE
    l_body     CLOB;
    l_response CLOB;
BEGIN

    apex_web_service.clear_request_headers;
    apex_web_service.g_request_headers(1).name  := 'Content-Type';
    apex_web_service.g_request_headers(1).value := 'application/json'; 

    l_body := '{some web service body parameters if needed}'

    l_response := apex_web_service.make_rest_request (
       p_url         => 'https://another_rest_service.com/v1/messages', --&apiKey=YOUR_API_KEY',
       p_http_method => 'POST',
       p_body        => l_body,
       p_credential_static_id => 'MY_QUERY_STRING_API_KEY'  ); 

END;

💡

Doing so not only enforces security on your code by hiding the actual API Keys in the code, but also makes it impossible for anyone to read them. A bonus benefit is that you no longer need to manually add the HTTP Headers or the Query String when invoking the API using apex_web_service.make_rest_request.

More information

To see how Web Credentials can be used with the other Authentication Types, how credentials can be updated, exported and then imported, as well as see some other examples, check the Oracle Documentation, API Reference and Jon Dixon's blog post:

🔸 https://docs.oracle.com/en/database/oracle/apex/23.2/htmdb/managing-credentials.html
🔸 https://docs.oracle.com/en/database/oracle/apex/23.2/aeapi/APEX_CREDENTIAL
🔸 https://blog.cloudnueva.com/apex-web-credentials

Follow me

Did you like this blog post? Follow me! 🔔