ORDS, APEX and secure REST APIs (Part 1 – secure the API)

Part 1: Securing your REST API

Some time ago we created a simple REST API using APEX RESTful Services to serve us for some reports. I wouldn’t go into details on how to create such, as there are many sources out there describing how to create a REST service in APEX. You could take a look at this Workshop by Oracle Labs if you just get started. I will focus on the process of securing it in this blog post.

By default the modules (APIs) you create using the APEX interface are not secured. If you have a GET handler method for example, you could see the JSON result by simply copy/pasting the URL in your browser without any authorization.

But of course, when this is a publicly available URL and it holds some sensitive information you wouldn’t want to leave it accessible for everyone. In addition to that you might have a number of partners that should have access to different APIs and different methods, and they should be able to authorize with their own credentials securely.

Building Blocks

  • Module (API) – the REST enabled service we want to protect
  • Role – it will be associated the Privilege and Client we create later and is used to make access distribution to different modules easier
  • Privilege – the main object that defines the module security. It links the Module and Role together.
  • Client – an object that is granted some access to a module, using the already created Role and Privilege. The Client has two main properties:
    • Client ID – similar to a username that is used for authentication.
    • Client Secret – similar to a password for a username. It always goes hand by hand with the Client Id.
  • Token – generated when a correct Client Id and Client Secret are passed to ORDS. The Token is then used as a header is the HTTP request and authorizes you for accessing the API

Ways to Authenticate

ORDS provides two built-in ways to authenticate your API calls:

  • Basic Authentication – using Username and Password
  • OAuth2 – using a Client credentials (Client ID and Client Secret)

The other way to authenticate is using a third-party provider, such as OKTA, Azure, etc. It offloads all of the efforts to issue, renew, revoke credentials and is a preffered method in enterprise solutions.

Basic Authentication

  1. You need to create a user/password pair. You have two options to do it:
  • Using a Database User and a Database Role
CREATE USER api_user IDENTIFIED BY api_user_pass;

CREATE ROLE customer_api_role;
GRANT customer_api_role TO api_user;
  • or using an APEX User and APEX Group
    • Create a new APEX Group, named customer_api_role
    • Create a new APEX User, named api_user and add it to the customer_api_role group
  1. Create a Role and Privilege using the ORDS RESTful Services
  • In APEX, go to SQL WorkshopRESTful Services
  • Click on Roles, Create Role button and enter customer_api_role as a Role Name

The same could be done using the ORDS package:

	    p_role_name => 'customer_api_role' ); 
  • Go to Privileges and click on Create Privilege button. Enter customer_api_priv as a Name, select customer_api_role from the list of roles that you want to associate with that privilege and select the module you want to secure in Protected Modules section.

The same could be done using the ORDS package (Full documentation of the package – in the ORDS Reference):


		p_name          => 'customer_api_priv',
		p_role_name     => 'customer_api_role',
		p_label         => 'Test Privilege',
		p_description   => 'A test privilege created using PL/SQL.');
		p_privilege_name => 'customer_api_priv',
		p_pattern        => '/customer/*');     


It is very important that you name your Database Role or your APEX Group with exactly the same name as your ORDS Role – in our example it’s customer_api_role. This will allow ORDS to map the ORDS REST service Role correctly.

OAuth2 Authentication

  • First step is to create an ORDS Role and Privilege, following the steps above – either using the APEX interface or manually by using the ORDS package.
  • Second step is to create Client credentials. There is no user interface in APEX that allows you to do that, but it is possible using a simple call to the AUTH package.

    p_name            => 'big_partner_company',
    p_grant_type      => 'client_credentials',
    p_owner           => 'My company name',
    p_description     => 'A client for API integrations by the Big Partner Company',
    p_support_email   => 'myemail@mydomain.com',
    p_privilege_names => 'customer_api_priv'

    p_client_name     => 'big_partner_company',
    p_role_name       => 'customer_api_role'



That step includes also a mapping of our newly created Client credentials to the ORDS Role that we created earlier.

Full documentation of this package could be found in the OAUTH Reference

  • Now you should already have the Client credentials created. To see them, use the following query:
SELECT id, name, auth_flow, response_type, client_id, client_secret

What you need are the Client_Id and Client_Secret.

  • The URL where ORDS will issue a token, based on a correct Client_Id and Client_Secret. It always follows this pattern:

For example:


Well done! Following these steps you now have secured your REST API.

In Part 2, see some different methods of accessing your secured API, including curl, postman and PL/SQL.

Additional resources

– OAUTH API reference


– ORDS API reference




– Blog posts:



3 thoughts on “ORDS, APEX and secure REST APIs (Part 1 – secure the API)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s