Introduction:
In the current enterprise technology landscape, applications are increasingly required to integrate and exchange information across diverse systems in a secure and reliable manner. Oracle APEX, in conjunction with Oracle REST Data Services (ORDS), provides a comprehensive platform for designing, publishing, and consuming RESTful APIs that support these integration needs.
This document presents a structured approach to building and consuming REST APIs in Oracle APEX using ORDS and OAuth2-based security. It covers both declarative (configuration-driven) and programmatic (code-driven) methods, demonstrating how REST services can be implemented, secured, and invoked within and outside Oracle APEX environments.
Why we need to do / Cause of the issue:
This activity is required to address challenges arising from unsecured or inefficient integration between applications. When APIs are exposed without proper security mechanisms or standardized access methods, the following issues can occur:
- Unauthorized access to sensitive business data
- Inability to track or control API consumers
- Hard-coded credentials embedded in applications
- Compliance and audit risks
- Integration failures across systems
The issue typically occurs when REST services are created without OAuth2-based authentication or when legacy integrations rely on basic authentication. In such cases, access control, token management, and secure communication are not enforced, increasing the likelihood of security vulnerabilities and operational disruptions.
Implementing REST APIs using ORDS with OAuth2 ensures:
- Secure access through token-based authentication
- Centralized control of client credentials
- Better compliance with organizational security policies
- Reduced risk of data exposure
- Standardized, scalable integration framework
Overall, adopting this approach mitigates security risks and improves the reliability and interoperability of enterprise applications.
How do we solve:
Exposing REST APIs with ORDS
Enable a schema for REST access.
Before exposing any database objects as RESTful services, the target schema must be enabled for REST access using ORDS. This process registers the schema with ORDS and determines how its endpoints are represented in the URL structure.
Enabling the schema is a mandatory prerequisite for creating REST modules and handlers, as well as for applying security privileges. Without this configuration, ORDS cannot route incoming HTTP requests to the schema’s PL/SQL procedures, tables, or views.
Create a role.
Another way to create a new role is this:
Define a privilege.
After creating a role, you must define a privilege to link it with specific access permissions. In ORDS, privileges control access to protected REST modules or handlers, ensuring that only authorized users or clients can invoke those endpoints. This step is essential for securing sensitive data exposed through REST APIs.
Create OAuth2 client.
After linking the privilege to a role, external applications must authenticate to access the REST API. This is done by registering an OAuth2 client in ORDS. The client uses its ID and secret to obtain access tokens, which are included in each request to authenticate and authorize API calls. This completes the security setup by enabling secure, token-based access to REST endpoints.
The following statement creates a new client:
Grant Role to Client.
Creating an OAuth2 client alone does not grant access to protected resources. The client must be assigned an appropriate role to define its permission scope. This ensures that when the client authenticates and obtains an access token, the token carries the required roles to access secured REST endpoints.
The following statement grants the client role:
Verification
After creating and configuring the OAuth2 client, you can retrieve its credentials using a SQL query. The client ID and client secret are securely stored in the ORDS internal repository and are required to obtain access tokens during authentication.
Define Web Credentials
In Oracle APEX, Web Credentials must be defined in Shared Components to access protected REST APIs. They securely store authentication details such as usernames, passwords, API keys, or OAuth tokens, allowing APEX to authenticate API requests without hardcoding sensitive information and ensuring secure, maintainable integration with external services.
Register Remote Servers
In Oracle APEX, configuring two remote servers enables secure and efficient access to ORDS REST APIs. The Authentication remote server handles secure login and token management (such as OAuth), while the REST Data Source remote server manages data requests to the API. This separation improves security, flexibility, and maintainability by isolating authentication from data access.
Create Report Based on REST Data Source
Once the REST Data Source RDS_USERS is created and properly configured, you can use it as the data source for a classic or interactive report within your APEX application.
To create an Interactive Report that consumes data from the RDS_USERS REST endpoint:
- Go to App Builder and open your application.
- Create a new page using the wizard:
- Type: Interactive Report
- Name: Users’ report
- Data Source: REST Data Source
- In the Rest Data Source drop-down:
- Select: RDS_USERS (previously defined)
- Click on the “Create page” button and run the page.
The report will now dynamically fetch and display the data provided by the remote ORDS-based REST endpoint.
If you edit the Interactive report Page created, you can see something like this:
Conclusion:
Implementing REST APIs in Oracle APEX with ORDS and OAuth2 provides a secure, scalable, and standards-based integration framework. OAuth2 token authentication enables controlled access to data services while avoiding the risks of basic authentication and hard-coded credentials.
Using both declarative and programmatic features, APEX allows rapid development of REST services that can be securely consumed by internal and external systems, improving interoperability and supporting enterprise security and integration requirements.
