Building Secure RESTful Services in Oracle APEX 24.2 Using ORDS, OAuth2 Client Credentials, and PL/SQL
📘 Introduction
Oracle APEX 24.2 provides powerful capabilities to expose database data as secure RESTful APIs using Oracle REST Data Services (ORDS).
In modern enterprise architectures, APIs are essential for enabling system-to-system communication. Instead of direct database access, RESTful services allow controlled and secure data sharing.
In this blog, we will walk through how to create a REST API in Oracle APEX 24.2 and secure it using privileges and OAuth authentication.
🎯 Objective
The goal of this implementation is to:
• Expose database data through REST APIs
• Create structured API endpoints
• Secure APIs using OAuth
• Enable controlled external access
🧱 Prerequisites
Before starting, ensure:
• Oracle APEX 24.2 is installed
• ORDS 20.x or later is configured
• RESTful Services are enabled
• A working table exists (Example: HR.EMPLOYEES)
🌐 REST API URL Structure
The generated API follows this format:
https://host:port/ords/schema/module/template
⚙️ Implementation Steps
PART 1 — Creating a RESTful Service (Expose Data via API)
Step 1: Navigate to RESTful Services
Go to:
SQL Workshop → RESTful Services
Here you will find:
• Modules
• Privileges
• OAuth Clients
Step 2: Create a Resource Module
A module is your API's root container.
- In the left panel, click Modules.
- Click Create Module (top-right corner).
- Fill in the fields:
| Field | Value (Example) |
|---|---|
| Module Name | hr.api |
| Base Path | /hr/ |
| Is Published | Yes |
| Pagination Size | 25 |
- Click Create Module
Step 3: Create a Resource Template
A template defines the URL endpoint pattern under your module.
- Under your newly created module
hr.api, click Create Template. - Fill in:
| Field | Value (Example) |
|---|---|
| URI Template | employees/ |
This means your endpoint will be: .../hr/employees/
- Click Create Template.
Step 4: Create a GET Handler (Read Data)
- Under your template
employees/, click Create Handler. - Fill in:
| Field | Value |
|---|---|
| HTTP Method | GET |
| Source Type | Query |
| Format | JSON |
| Source (SQL) | (see below) |
select empno, ename, deptno, job from emp
where ((select job from emp where ename = :empname) IN ('PRESIDENT', 'MANAGER'))
OR
(deptno = (select deptno from emp where ename = :empname))
order by deptno, ename
Step 5: Test the GET Endpoint
1. Click the Test button (or copy the URL shown).
2. Open a browser and navigate to:
```
https://<your-host>/ords/<workspace_schema>/hr/employees/- You should get a JSON response like:
PART 2 — Securing Your REST API
Step 6: Create a Privilege (Access Control)
- In the RESTful Services left panel, click Privileges.
- Click Create Privilege.
- Fill in:
| Field | Value |
|---|---|
| Name | hr.privilege |
| Label | HR API Access |
| Protected Modules | Select hr.api |
| Roles | Select or create a role |
- Click Create Privilege.
5.Create Roles.
Step 7: Configure OAuth Client
OAuth Clients were created to enable secure token-based access.
This allows:
• Token generation
• Controlled API usage
• Secure integration
Instead of exposing database endpoints directly.
1. Create Client.
CODE:
BEGIN
OAUTH.create_client(
p_name => 'hr_client',
p_grant_type => 'client_credentials',
p_owner => 'Rest API',
p_description => 'An example client created for my blog.',
p_support_email => 'your email id @gmail.com',
p_privilege_names => 'HR Access'
);
COMMIT;
END;
2.Create the grant client role.
CODE :
BEGIN
oauth.grant_client_role(p_client_name => 'hr_client',
p_role_name => 'EMPLOYEE');
END;
Now created the Client ID and Client Secret for OAuth Client. Now check client id and client secret using below SQL Query .
select * from user_ords_clients where name='hr_client'
Step 7: 🔎 Testing the Secured REST API Using OAuth2 in Postman
After creating and securing the RESTful service using OAuth and Privileges in Oracle APEX, the next step is to validate the API using an external client.
Postman can be used to generate an OAuth2 access token and successfully invoke the secured endpoint.
Step by Step Approaches in Postman:
🧩 Step 1: Open Postman
Create a new request and enter your secured REST endpoint:
Example:
🧩 Step 2: Configure Authorization
Navigate to:
Authorization Tab → Select Auth Type → OAuth 2.0
🧩 Step 3: Configure Token Settings
Click:
Configure New Token
Provide the following details:
| Field | Value |
|---|---|
| Token Name | test api |
| Grant Type | Client Credentials |
| Access Token URL | OAuth Token Endpoint URL |
| Client ID | Provided during OAuth Client creation |
| Client Secret | Provided during OAuth Client creation |
| Scope | (Optional) |
| Client Authentication | Send as Basic Auth Header |
This configuration allows Postman to request an access token automatically.
🧩 Step 4: Generate Access Token
Click:
Get New Access Token
Postman will:
✔ Call the ORDS OAuth Token Endpoint
✔ Authenticate using Client ID & Secret
✔ Generate Access Token
🧩 Step 5: Use Token to Call API
Once token is generated:
Click:
Use Token
Now click:
Send Request
Configure Token Settings
📥 Response
If authentication is successful:
ORDS validates:
✔ OAuth Token
✔ Assigned Privileges
Then returns employee data in JSON format from the secured endpoint.
JSON Response after authentication is successful (OAuth2)
Generated Token
🔄 Behind the Scenes Flow
Postman
↓
Requests OAuth Token
↓
ORDS Validates Client
↓
Access Token Issued
↓
API Request Sent with Token
↓
ORDS Validates Token
↓
Data Returned
🔐 Why This Matters
Using OAuth2 ensures:
• No direct API exposure
• Token-based authentication
• Secure system-to-system communication
• Controlled API access
Step 8: 🔎 Testing the Secured REST API Directly from Oracle APEX
In addition to testing the secured REST API using external tools like Postman, Oracle APEX also provides the capability to invoke OAuth-protected APIs directly using PL/SQL.
This allows developers to validate secured endpoints internally without relying on third-party tools.
Step by Step Approaches in Oracle APEX:
🔐 Step 1: Authenticate Using OAuth2
Using the apex_web_service.oauth_authenticate procedure, an access token can be generated dynamically within APEX.
This is done by calling the OAuth Token Endpoint and passing:
• Client ID
• Client Secret
Example Flow:
APEX
↓
Calls OAuth Token Endpoint
↓
Receives Access Token
🌐 Step 2: Invoke Secured REST API
Once authentication is successful, the secured API can be invoked using APEX web service utilities.
The request is sent along with the OAuth token generated in the previous step.
This enables secure communication with ORDS-protected REST endpoints.
📥 Step 3: Retrieve Response
After successful authentication and API invocation:
ORDS validates:
✔ OAuth Token
✔ Assigned Privileges
Then returns employee data in JSON format.
This confirms that:
• The REST API is secured
• OAuth authentication works correctly
• API can be consumed internally from APEX
The following PL/SQL full code for invoke OAuth-protected APIs directly using PL/SQL in Oracle APEX :
CODE :
Declare
v_response clob;
v_buffer varchar2(32767);
v_buffer_size number := 32000;
v_offset number := 1;
v_error_code number;
v_error_msg varchar2(4000);
V_PARAM_NAMES apex_application_global.vc_arr2;
V_PARAM_VALUES apex_application_global.vc_arr2;
v_get_token varchar2(4000);
begin
-- Set connection and invoke REST API.
apex_web_service.g_request_headers.delete;
BEGIN
apex_web_service.oauth_authenticate(p_token_url => 'https://oracleapex.com/ords/sabarinath/oauth/token',
p_client_id => 'your client id',
p_client_secret => 'your client secret');
v_get_token := 'Bearer '||apex_web_service.oauth_get_last_token;
--return v_get_token;
END;
apex_web_service.g_request_headers.delete;
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 := 'Authorization';
apex_web_service.g_request_headers(2).VALUE := v_get_token;
-- V_PARAM_NAMES(1) := 'forum_name';
-- V_PARAM_VALUES(1) := 'Global Action Tracker Development Team';
-- V_PARAM_NAMES(2) := 'last_modified_date';
-- V_PARAM_VALUES(2) := null;
v_response := apex_web_service.make_rest_request(
p_url => 'https://oracleapex.com/ords/sabarinath/hr/empinfo/',
p_http_method => 'GET',
p_parm_name => V_PARAM_NAMES,
p_parm_value => V_PARAM_VALUES
);
dbms_output.put_line('-v_response-'||v_response);
dbms_output.put_line('-status code-'||apex_web_service.g_status_code);
end;
🏁 Summary
By using apex_web_service.oauth_authenticate along with make_rest_request, Oracle APEX can securely consume OAuth-protected REST APIs directly from PL/SQL.
This enables seamless enterprise integrations without exposing sensitive credentials or relying on external clients.
🌟 Benefits
• Secure API exposure
• Token-based authentication
• Controlled access via privileges
• Industry-standard integration
• Scalable architecture
🏁 Conclusion
This implementation demonstrates how Oracle APEX 24.2 and ORDS can be used to build and secure RESTful APIs directly from database queries.
By combining REST Modules, Templates, Handlers, Privileges, and OAuth Clients, organizations can safely expose their data for external integrations while maintaining strong security controls.
Comments
Post a Comment