Secure RESTful Web Service in Oracle APEX with Basic Authentication

Heard they say you should secure your web service?👮 

Stick with me in this article, and I’ll show you a professional way to secure your RESTful Web Service with Basic Authentication

 In the previous article, I explained how to CREATE  a RESTful Web Services inside Oracle APEX.
https://lidagholizadeh.blogspot.com/2025/08/all-about-restful-webservice-in-oracle.html
In this Article I will shows you how to build a professional, secure JSON RESTful web service in Oracle APEX that returns employee details from the HR.EMPLOYEES table, protected by Basic Authentication with PBKDF2 password hashing.
Prerequisites

Oracle Database 19c or later 

Oracle APEX 21.1 or newer

HR sample schema unlocked and available

EXECUTE on DBMS_CRYPTO

A workspace with privileges to create RESTful Services
🚴 Let's GO 

Step 1 – Create a Table to Store Web Service Users
CREATE TABLE ws_users (
    username      VARCHAR2(100)  PRIMARY KEY,
    password_hash VARCHAR2(64)   NOT NULL,   -- we store hex PBKDF2-SHA1 (32 bytes → 64 hex chars)
    created_on    DATE           DEFAULT SYSDATE,
    created_by    VARCHAR2(100),
    kommentar     VARCHAR2(1000)
);
Step 2 – Store a Global Salt (recommended: random 16-byte hex string)
-- Create a simple properties table if you don’t have one already
CREATE TABLE app_properties (
    attribute VARCHAR2(100) PRIMARY KEY,
    value     VARCHAR2(4000)
);

INSERT INTO app_properties (attribute, value)
VALUES ('WEBSERVICE_SALT', 'A1B2C3D4E5F678901234567890ABCDEF');  -- 32 hex chars = 16 bytes
COMMIT;

Step 3 – PBKDF2 Hash Function 
CREATE OR REPLACE FUNCTION pbkdf2_hash(
    p_password VARCHAR2,
    p_salt     VARCHAR2
) RETURN VARCHAR2 IS
    c_iterations CONSTANT PLS_INTEGER := 10000;
    c_key_len    CONSTANT PLS_INTEGER := 256;   -- bits
    c_hash_len   CONSTANT PLS_INTEGER := 20;    -- SHA1 raw bytes
    l_blocks     PLS_INTEGER;
    l_result     RAW(32767) := NULL;
    l_xorsum     RAW(32767);
    l_last       RAW(32767);
BEGIN
    l_blocks := CEIL(c_key_len / (c_hash_len * 8));
    FOR i IN 1 .. l_blocks LOOP
        l_last := UTL_RAW.CONCAT(
                     UTL_RAW.CAST_TO_RAW(p_salt),
                     UTL_RAW.CAST_FROM_BINARY_INTEGER(i, UTL_RAW.BIG_ENDIAN)
                  );
        l_xorsum := NULL;

        FOR j IN 1 .. c_iterations LOOP
            l_last := DBMS_CRYPTO.MAC(
                        src => l_last,
                        typ => DBMS_CRYPTO.HMAC_SH1,
                        key => UTL_RAW.CAST_TO_RAW(p_password)
                     );
            l_xorsum := CASE WHEN l_xorsum IS NULL
                             THEN l_last
                             ELSE UTL_RAW.BIT_XOR(l_xorsum, l_last)
                        END;
        END LOOP;

        l_result := UTL_RAW.CONCAT(l_result, l_xorsum);
    END LOOP;

    RETURN LOWER(RAWTOHEX(UTL_RAW.SUBSTR(l_result, 1, 32))); -- 32 bytes = 64 hex chars
END;
/
Step 4 – Helper Procedure to Create Users
CREATE OR REPLACE PROCEDURE create_ws_user(
    p_username IN VARCHAR2,
    p_password IN VARCHAR2
) IS
    v_salt  VARCHAR2(32);
    v_hash  VARCHAR2(64);
BEGIN
    SELECT value INTO v_salt
    FROM app_properties
    WHERE attribute = 'WEBSERVICE_SALT';
    v_hash := pbkdf2_hash(p_password, v_salt);
    INSERT INTO ws_users (username, password_hash, created_by, kommentar)
    VALUES (p_username, v_hash, USER, 'Created via tutorial');
    COMMIT;
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        RAISE_APPLICATION_ERROR(-20001, 'User already exists');
END;
/
Execute once for testing:
BEGIN
    create_ws_user('api_user', 'MyStrongP@ssw0rd2025');
END;
/
Step 5 – Core Package for the Web Service
CREATE OR REPLACE PACKAGE employee_ws AS
    TYPE t_params IS RECORD (
        call_ts      VARCHAR2(30),
        authorization VARCHAR2(4000),
        employee_id  NUMBER
    );

    user_not_authenticated EXCEPTION;
    PRAGMA EXCEPTION_INIT(user_not_authenticated, -20001);

    PROCEDURE check_basic_auth(p_auth IN VARCHAR2);
    PROCEDURE write_json_error(p_msg IN VARCHAR2, p_code IN NUMBER := 500, p_callts IN VARCHAR2 := NULL);
    PROCEDURE get_employee_detail(p_params IN t_params, p_status OUT NUMBER);
END;
/

CREATE OR REPLACE PACKAGE BODY employee_ws AS

    PROCEDURE check_basic_auth(p_auth IN VARCHAR2) IS
        v_salt     VARCHAR2(32);
        v_b64      VARCHAR2(4000);
        v_parts    APEX_T_VARCHAR2;
        v_user     VARCHAR2(100);
        v_pass     VARCHAR2(500);
        v_hash     VARCHAR2(64);
        v_cnt      NUMBER;
    BEGIN
        SELECT value INTO v_salt
        FROM app_properties WHERE attribute = 'WEBSERVICE_SALT';

        IF p_auth IS NULL OR NOT REGEXP_LIKE(p_auth, '^Basic ', 'i') THEN
            RAISE user_not_authenticated;
        END IF;

        v_b64 := REGEXP_REPLACE(p_auth, '^Basic\s+', '', 1, 1, 'i');
        v_parts := APEX_STRING.SPLIT(UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(v_b64)), ':');

        v_user := v_parts(1);
        v_pass := v_parts(2);

        IF v_user IS NULL OR v_pass IS NULL THEN
            RAISE user_not_authenticated;
        END IF;

        v_hash := pbkdf2_hash(v_pass, v_salt);

        SELECT COUNT(*) INTO v_cnt
        FROM ws_users
        WHERE LOWER(username) = LOWER(v_user)
          AND password_hash = v_hash;

        IF v_cnt = 0 THEN
            RAISE user_not_authenticated;
        END IF;
    END check_basic_auth;
/
    PROCEDURE write_json_error(p_msg    IN VARCHAR2,
                               p_code    IN NUMBER := 500,
                               p_callts  IN VARCHAR2 := NULL) IS
    BEGIN
        OWA_UTIL.mime_header('application/json', FALSE, 'UTF-8');
        HTP.p('Cache-Control: no-cache');
        OWA_UTIL.http_header_close;
        APEX_JSON.open_object;
        APEX_JSON.write('error', p_msg);
        APEX_JSON.write('code', p_code);
        APEX_JSON.write('timestamp', TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"'));
        IF p_callts IS NOT NULL THEN APEX_JSON.write('call_ts', p_callts); END IF;
        APEX_JSON.close_object;
    END write_json_error;

    PROCEDURE get_employee_detail(p_params IN t_params, 
p_status OUT NUMBER) 
IS
        v_found BOOLEAN := FALSE;
    BEGIN
        check_basic_auth(p_params.authorization);
        OWA_UTIL.mime_header('application/json', FALSE, 'UTF-8');
        HTP.p('Cache-Control: no-cache');
        OWA_UTIL.http_header_close;
        APEX_JSON.open_object;
        APEX_JSON.open_array('items');

        FOR r IN (SELECT employee_id,
                         first_name,
                         last_name,
                         email,
                         phone_number,
                         hire_date,
                         job_id,
                         salary,
                         commission_pct,
                         manager_id,
                         department_id
                  FROM hr.employees
                  WHERE employee_id = p_params.employee_id)
        LOOP
            v_found := TRUE;
            APEX_JSON.open_object;
            APEX_JSON.write('employee_id', r.employee_id);
            APEX_JSON.write('first_name', r.first_name);
            APEX_JSON.write('last_name', r.last_name);
            APEX_JSON.write('email', r.email);
            APEX_JSON.write('phone_number', r.phone_number);
            APEX_JSON.write('hire_date', TO_CHAR(r.hire_date, 'YYYY-MM-DD'));
            APEX_JSON.write('job_id', r.job_id);
            APEX_JSON.write('salary', r.salary);
            APEX_JSON.write('commission_pct', r.commission_pct);
            APEX_JSON.write('manager_id', r.manager_id);
            APEX_JSON.write('department_id', r.department_id);
            APEX_JSON.close_object;
        END LOOP;
        APEX_JSON.close_array;
        APEX_JSON.close_object;
        p_status := 200;
    EXCEPTION
        WHEN user_not_authenticated THEN
            p_status := 401;
            write_json_error('Unauthorized – Invalid credentials', 401, p_params.call_ts);
        WHEN NO_DATA_FOUND THEN
            p_status := 200;  -- empty result is acceptable
            OWA_UTIL.mime_header('application/json', FALSE, 'UTF-8');
            HTP.p('Cache-Control: no-cache');
            OWA_UTIL.http_header_close;
            APEX_JSON.open_object;
            APEX_JSON.open_array('items');
            APEX_JSON.close_array;
            APEX_JSON.close_object;
        WHEN OTHERS THEN
            p_status := 500;
            write_json_error('Internal Server Error', 500, p_params.call_ts);
    END get_employee_detail;
END employee_ws;
/

Step 6 – Create the RESTful Service Handler in APEX💗
Go to SQL Workshop → RESTful Services

Create Module → Name: EMPLOYEE_API

Create Template → /employee

Create Handler

Method: GET

Source Type: PL/SQL

Requires Authentication: No (we handle it manually)

Source:
DECLARE
    v_params   employee_ws.t_params;
    v_status   NUMBER;
    v_callts   VARCHAR2(30) := TO_CHAR(SYSTIMESTAMP, 'yyyymmddhh24missff3');
BEGIN
    v_params.call_ts      := v_callts;
    v_params.authorization := :authorization;          -- APEX binds header
    v_params.employee_id  := :employee_id;             -- bind from URI or query string
    IF v_params.employee_id IS NULL THEN
        :status_code := 400;
        employee_ws.write_json_error('Bad Request – employee_id is required', 400, v_callts);
        RETURN;
    END IF;
    employee_ws.get_employee_detail(v_params, v_status);
    :status_code := v_status;
END;
Parameters:

Name: employee_id
Source Type: URI
Parameter Type: String (or Number)
Required: Yes

Step 7 – Test the Web Service
Using curl:
Bash
# Correct credentials
curl -i -u api_user:MyStrongP@ssw0rd2025 \
     "https://your-apex-server.com/ords/your_schema/employee_api/employee/107"

# Wrong password → 401
curl -i -u api_user:wrongpassword \
     "https://your-apex-server.com/ords/your_schema/employee_api/employee/107"

# No credentials → 401
curl -i "https://your-apex-server.com/ords/your_schema/employee_api/employee/107"
Expected successful response (HTTP 200):

MY DEAR JSON 😍😃 
{
  "items": [
    {
      "employee_id": 107,
      "first_name": "Diana",
      "last_name": "Lorentz",
      "email": "DIANALORENTZ",
      "phone_number": "590.423.5567",
      "hire_date": "2007-02-07",
      "job_id": "IT_PROG",
      "salary": 4200,
      "commission_pct": null,
      "manager_id": 103,
      "department_id": 60
    }
  ]
}

THANK YOU FOR YOUR READING 🙏
I HOPE IT WAS USEFUL FOR YOU