Build and Consume Secure REST APIs in Oracle APEX using ORDS and OAuth2

Introduction

In a world where applications must be increasingly connected and interoperable, Oracle APEX and ORDS offer a robust platform for building and consuming RESTful APIs quickly, securely, and scalable. This tutorial walks you through how to build and consume an Oracle APEX REST API using ORDS, OAuth2, and both declarative and programmatic approaches.

Basic concepts

What is a REST API?

A REST API (Representational State Transfer Application Programming Interface) is a web-based architecture that allows systems to exchange data using standard HTTP methods such as GET, POST, PUT, DELETE, and PATCH. These operations are used to retrieve, create, update, and delete resources in a stateless and lightweight way, making REST ideal for exposing services over the internet or within enterprise systems.

It’s important to note that while REST as an architectural style defines these five core HTTP methods, Oracle REST Data Services (ORDS) currently supports GET, POST, PUT, and DELETE, but doesn’t implement the PATCH method. This should be taken into account when designing APIs that require partial updates to resources.

What is ORDS?

Oracle REST Data Services (ORDS) enables developers to expose database objects, such as tables, views, and PL/SQL procedures, as RESTful APIs. ORDS acts as a bridge between the Oracle database and web clients, converting HTTP(S) requests into SQL or PL/SQL calls internally.

Exposing REST APIs with ORDS

Enable a schema for REST access.

Before you can expose any database objects as RESTful services, you must enable the corresponding schema for REST access using ORDS. This step registers the schema with ORDS and defines how its endpoints will be mapped in the URL structure.

Enabling the schema is a prerequisite for defining REST modules, handlers, and securing them via privileges. Without this, ORDS cannot route incoming HTTP requests to PL/SQL procedures, tables, or views in that schema:

Copy to Clipboard
Syntax Highlighterbegin
    -- Enables the current schema
    ords.enable_schema(p_schema => 'EXAMPLE2');
end;

Alternatively, you can also enable it as follows:

Register Schema with ORDS

Securing your REST APIs

By default, ORDS modules are publicly accessible unless explicitly protected. To secure access to your REST endpoints, it’s important to implement role-based access control using privileges and OAuth2 authentication. This ensures that only authorized clients can invoke sensitive operations exposed by your API.

The first step in securing your REST API is to define a role that will later be associated with specific privileges and OAuth2 clients.

Let’s see how to do it!

Create a role.

The following statement creates a new role:

Another way to create a new role is this:

ORDS Role Definition

Define a privilege.

After defining a role, the next step is to associate that role with specific access permissions by creating a privilege. In ORDS, privileges are used to restrict access to protected REST modules or specific handlers.

Only users or clients with the assigned privilege will be authorized to execute those endpoints. This setup ensures fine-grained control over who can access what, and it is an essential step before exposing sensitive business data through REST APIs.

The following statement creates a new privilege:

Copy to Clipboard
Syntax Highlighter-- Privilege
declare
   l_roles OWA.VC_ARR;
begin
   l_roles(1) := 'my_app_api';
    
   ords.define_privilege
   (
       p_privilege_name => 'my_app.api.privilege',
       p_roles          => l_roles,
       p_label          => 'MY APP API Privilege',
       p_description    => 'Privilege for MY APP API'
   );
     
   commit;
end; 

You can also define it as follows:

Privilege definition

Once we have developed our modules (GET, PUT, POST, or DELETE actions), we can protect them:

Modules protected

Create OAuth2 client.

Once the privilege is defined and linked to a role, external applications need a way to authenticate against your REST API. To do this, you register an OAuth2 client using ORDS’ built-in OAuth support.

The client credentials (client ID and secret) will be used to request access tokens, which are then sent with each request to authenticate and authorize API calls. This step completes the security chain, enabling secure, token-based access to your REST endpoints.

The following statement creates a new client:

Copy to Clipboard
Syntax Highlighter-- Create client
begin
   OAUTH.create_client
   (
      p_name            => 'api_client',
      p_grant_type      => 'client_credentials',
      p_owner           => 'DEV_MYAPP_DATA',
      p_support_email   => 'info@myapp.com',
      p_privilege_names => 'api_priv'
   );

   COMMIT;
end;

Grant Role to Client.

Once the OAuth2 client is created, it still lacks the authority to access protected resources. By granting the client a predefined role, you establish the client’s permission scope.

This is a mandatory step to ensure that when the client authenticates and receives an access token, that token includes the roles required to invoke protected REST endpoints.

The following statement grants the client role:

Copy to Clipboard
Syntax Highlighterbegin
  oauth.grant_client_role(p_client_name => 'api_client',
                          p_role_name   => 'my_app_api');

  COMMIT;
end;

Verification

After creating and configuring the OAuth2 client, you can retrieve its credentials using a SQL query. These credentials “client ID” and “client secret” are securely stored by ORDS in its internal repository and are required to request access tokens during authentication:

Client Id and Client Secret

⚠️ Make sure this data is handled securely and only accessible to authorized administrators or automation processes.

Consuming REST APIs in Oracle APEX

Define Web Credentials

In Oracle APEX, it’s essential to define Web Credentials within the Shared Components section to consume protected REST APIs.

Web Credentials store the necessary authentication details, such as usernames, passwords, API keys, or OAuth tokens, required to securely access external APIs. By configuring these credentials, APEX applications can authenticate API requests without hardcoding sensitive information, ensuring a more secure and manageable connection to external web services.

Web Credentials

Register Remote Servers

In Oracle APEX, defining two remote servers ensures secure and efficient communication with RESTful APIs created in ORDS.

• The Authentication remote server manages secure authentication, ensuring that access to the API is protected with proper credentials or tokens (e.g., OAuth).

• The REST Data Source remote server handles data retrieval and interactions with the REST APIs, enabling APEX to access the data securely.

By separating these concerns, APEX ensures both secure access to API data and robust authentication management, allowing more flexibility and enhanced security.

For example:

Remote servers

Create REST Data Source

To continue the example of the users’ module (created in the ORDS) and for accessing to the corresponding handlers, we are going to define the REST Data Source “RDS_USERS” as follows:

REST DataSource Cration

Create REST Data Source from Scratch

REST Data Source Type

REST Data Source definition

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:

1. Go to App Builder and open your application.

2. Create a new page using the wizard:

   • Type: Interactive Report

   • Name: Users’ report

   • Data Source: REST Data Source

3. In the Rest Data Source dropwdown:

   • Select: RDS_USERS (previously defined)

4. 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:

Interactive report linked to a REST Source

Additional techniques

While REST Data Sources provide a powerful declarative way to integrate external APIs into APEX, sometimes custom logic is required, for example, invoking APIs conditionally, transforming data, or chaining multiple API calls. In such cases, the APEX_WEB_SERVICE package provides programmatic control.

Here’s an example of how to consume the same user module from ORDS using apex_web_service.make_rest_request:

Expected JSON format:

Copy to Clipboard
Syntax Highlighter{
    "items": [
        { "id": 1, "name": "John", "email": "john@example.com", "active": "Y"  },
        { "id": 2, "name": "Elisabeth", "email": "elisa@example.com", "active": "N"  },
        ...
    ]
}

Then, you can create a Report based on the API_USERS collection.

Conclusion

Thanks to Oracle ORDS and APEX, developers can declaratively or programmatically expose and consume secure REST APIs. Whether you’re building internal microservices or exposing data to partners, this approach ensures that your applications remain scalable, maintainable, and secure within the Oracle ecosystem.

By embracing REST and Oracle’s low-code stack, you’re not just building services, you’re modernizing your architecture.

If your organization needs support designing secure RESTful services, optimizing API integration, or implementing robust authentication with OAuth2, our team is here to help.
Visit novoshore.com or contact us directly to learn how we can assist you with Oracle APEX.