Securely using API Keys in Oracle APEX
Utilizing Web Credentials and the APEX_WEB_SERVICE API
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).
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
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)
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.
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;
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! 🔔