# Handle Non-Standard API Authentication in APEX # Introduction [Oracle APEX Web Credentials](https://blog.cloudnueva.com/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.
💡
This post will focus on these authentication outliers. I will show you how, in many cases, we can still use APEX Web Credentials, even if APEX does not support the authentication type of the API you are trying to call.
## 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](https://servicepath.co/). servicePath is a SaaS based Configure, Price, Quote solution (CPQ) focused on technology sales. The [servicePath REST APIs](https://developer-hub.servicepath.io/reference/intro/getting-started) use an HTTP Header Bearer token for Authentication. A token can be obtained from the [Generate Access Token](https://developer-hub.servicepath.io/reference/post_api-v3-security-tokens) 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: ![Postman screenshot showing Service Path Token Service](https://cdn.hashnode.com/res/hashnode/image/upload/v1746899891292/a3870a3b-f9c3-497f-b772-b89de8bf8930.png align="center") After calling the token service, you receive a Bearer token (access token) in a JSON response: ```json { "Bearer": "eyJhbG...", "Refresh": "I4-8Cun..." } ``` The Bearer (access token) is used in the ‘Authorization’ HTTP Header variable to authenticate when calling servicePath APIs: ```bash 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...' ```
🤦‍♂
I don't understand why servicePath did not use basic authentication (or even better, OAuth2).
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. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1746922023031/619908fa-2941-4892-adce-0ad7411a533c.png align="center") # 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).
⚠
For ultimate security, you may want to consider using DBMS_CRYPTO or an external keystore to secure these credentials.
The approach described in this section is much more straightforward and sufficiently secure for most use cases. It relies on [Oracle Transparent Data Encryption](https://docs.oracle.com/en/database/oracle/oracle-database/23/dbtde/changes-this-release-oracle-database-transparent-data-encryption-guide.html) (TDE) to secure the data at rest, and [Oracle Data Redaction](https://docs.oracle.com/en/database/oracle/oracle-database/23/dbred/index.html) 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 ```sql -- 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 ```sql -- 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 ```sql -- 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 ```sql 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 ```sql 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: ```sql -- Run as API_SEC or ADMIN GRANT EXECUTE ON api_sec.api_credentials_pk TO <>; ``` ## Step 6: Add a Credential Create a credential: ```sql -- 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 ```sql -- 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: ![APEX Web Credential](https://cdn.hashnode.com/res/hashnode/image/upload/v1746984086278/3443647b-eece-4407-a6e0-f8e6e832b633.png align="center") The `set_servicepath_credentials` procedure uses [APEX\_CREDENTIAL.SET\_PERSISTENT\_CREDENTIALS](https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/APEX_CREDENTIAL.SET_PERSISTENT_CREDENTIALS-Procedure-Signature-3.html) to store the Bearer token. `apex_credential.set_persistent_credentials` sets the Credential Name and Secret so all APEX sessions can utilize it. ```sql 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.
💡
Note: Unlike the ‘OAuth2 Client Credentials’ type Web Credential, APEX will not refresh the token for you. This is why we will use an APEX Automation.
There is an overloaded version of this procedure. It does the same thing with different parameter names. ```sql 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](https://blog.cloudnueva.com/apex-automations) 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. ![APEX Automation to Refresh the Token.](https://cdn.hashnode.com/res/hashnode/image/upload/v1746984313611/cf0b7dae-5b3b-4b35-a795-7d78538f4457.png align="center") ![APEX Automation Action](https://cdn.hashnode.com/res/hashnode/image/upload/v1746984357266/9dd79841-0a0a-4c6b-8790-368226348da0.png align="center") * 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`: ```sql 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: ![Using the Credential in an APEX REST Source](https://cdn.hashnode.com/res/hashnode/image/upload/v1746985657367/eb3284a2-d406-48ee-af17-ae73fde1db24.png align="center") # 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