How to Build a Secure REST API with OAuth 2.0 Authentication in Oracle APEX

APEX

Securing your REST API

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 Oracle 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;
GRANT CREATE SESSION TO api_user;
 
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 Workshop – RESTful Services
  • Click on RolesCreate Role button and enter customer_api_role as a Role Name

The same could be done using the ORDS package:

begin
    ORDS.create_role(
        p_role_name => 'customer_api_role' ); 
end;
  • 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):

begin
 
    ORDS.create_privilege(
        p_name          => 'customer_api_priv',
        p_role_name     => 'customer_api_role',
        p_label         => 'Test Privilege',
        p_description   => 'A test privilege created using PL/SQL.');
         
    ORDS.create_privilege_mapping(
        p_privilege_name => 'customer_api_priv',
        p_pattern        => '/customer/*');     
         
    commit;
 
end;

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.
begin
 
  OAUTH.create_client(
    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   => '[email protected]',
    p_privilege_names => 'customer_api_priv'
  );
 
  OAUTH.grant_client_role(
    p_client_name     => 'big_partner_company',
    p_role_name       => 'customer_api_role'
  );
 
  commit;
 
end;

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
FROM USER_ORDS_CLIENTS;

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:
https://your-host/your-web-server/your-workspace/oauth/token

For example:

https://apex.oracle.com/pls/apex/test_workspace/oauth/token

2 Comments

  1. Your site loads so quickly that it nearly looks like you’re using a special technique. You are an extremely talented webmaster. You’ve done a great job with this; the contents are outstanding.

Leave a Reply

Your email address will not be published. Required fields are marked *