Oracle Rest Data Services (ORDS) RESTful Services Basic Authentication with Oracle APEX user

Oracle Rest Data Services (ORDS) RESTful Services Basic Authentication with Oracle APEX user

Thank you to Suresh Sangaran and Mohammed Pasha for the information and presentation on this topic.Follow along with the attached presentation containing detailed screenshots for each step.

Introduction:

Authentication and Protecting RESTful API is a primary topic we see with ORDS and APEX product users. By default, ORDS provides many ways to protect its RESTful API's in terms of "Authentication". Below is a list of Authentication methods used by ORDS.

1.    First Party Cookie-Based Authentication
2.    Third Party OAuth 2.0-Based Authentication

   
In this Blog, we will talk about method 1 - First Party Cookie-Based Authentication also widely called "Basic Authentication". Basic Authentication means providing "UserName" and "Password" to authenticate RESTful API. This type of authentication can be implemented in the below ways
 
   1. File-Based User Repository - Create ORDS user role and credentials.
         (This type is only intended for the purposes of demonstration and testing, and is not supported for production use)
   2. Database Schema Authentication - This feature provides basic authentication for PL/SQL gateway calls.
   3. APEX user-based Authentication by Oracle REST Data Services Roles - This looks more interesting right Let us explore this feature in detail.
   
Oracle APEX is a feature-rich GUI to build REST API and templates. By default, Oracle APEX users do not have any of the Oracle REST Data Services predefined user roles. This means that, by default, APEX users cannot:

• Invoke protected RESTful Services if protected.
• Register OAuth 2.0 applications.
• Use Oracle SQL Developer to develop RESTful services.

So how to use APEX users to authenticate against ORDS RESTFul API?

Prerequisites:

We assume you are using the latest version of Oracle APEX and ORDS or the APEX Service available in the Autonomous database.This demonstration does not require any tables, so no pre Authorization or pre-hook is required.

Note: For the Autonomous database, the APEX users created by default will be Database users created internally with the database. Further Reading for Autonomous Database can be found on The Quick Guide for Getting Started with APEX on Autonomous Database Serverless.

Disclaimer: 

The example(s) provided are for demonstration purposes only and represent a fictitious sample (based on made-up data used in the Oracle Demo instance). Any similarity to actual data, web sites is purely coincidental and not intended in any manner. For any queries, please contact Oracle Support Services.

Assumptions:

This demo uses Apex Workspace name: learn and Apex Workspace User Name: learn_admin

A) Create an APEX group and User, and assign a user with the group.

    1. Login to the APEX application as a Workspace Admin User.
    2. Click on the Apex Administration icon and go to-->"Manage Users and Groups"
    3. Go to "Groups" and create a group. In this example, let us name the group as "REST_API_USER_GROUP" 
          3.1 - Grant the group with "RESTful Services" role.
          3.2 - For Autonomous database, "SQL Developer" role also should be added.
  
Please see the below documentation for more details on the requirement of "SQL Developer" role for an Autonomous database.

• https://docs.oracle.com/en/cloud/paas/autonomous-database/dedicated/adbce/index.html#ADBCE-GUID-3D237239-2239-45A1-A77D-1028CD18F83A
• https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/ords-rest.html#GUID-3D237239-2239-45A1-A77D-1028CD18F83A

  
    4. Go to "Users" and create a user. In this example, let us name the APEX user as "RESTAPIUSER"
          4.1 - Do not grant the user a Workspace Administrator. 
          4.2 - Under "Group Assignments", add the role created in Step 3, for example - "REST_API_USER_GROUP"
  
    5. Verify under "Group Assignments", if created group and user are assigned correctly.
  
B) Create a RESTFul API and protect with APEX user as Basic Authentication.

    1. Go to SQL Workshop-->RESTFul Services
    2. Go to "Roles" and create a role with the same as APEX user group created in Section A)-3 above. So, in this example, the role name is "REST_API_USER_GROUP".

Note: The above step # 2 bridges the ORDS Role and APEX user for Authentication.

    3. Go to "Modules" and create a REST API Module.
           3.1 - Module Name: 'mresttest', Base Path: 'mresttest' and leaving other sections as default. 
                 3.1.a) So now the URI should be http://localhost:8080/ords/learn/mresttest/ and next click "Create Template"
            3.2 - Create a Template as  URI Template: 'uresttest' and leaving other sections as default. 
                 3.2.a) So now the URI  should be http://localhost:8080/ords/learn/mresttest/uresttest and next click "Create Handler"
            3.3 - Create a REST Handler with Method: 'GET', Source Type: 'Collection Query', Source: select :current_user from dual
   
Note: ":current_user" is an implicit parameter provided by ORDS, which can display the identity of the user authenticated for the request.   
   
    4. Go to the "Privileges" section and create a privilege. In this example, we can name the privilege as "REST_API_USER_PRIVS".
          4.1 - Add the role "REST_API_USER_GROUP" to the privilege.
          4.2 - Let us protect the URL(as mentioned in 3.2.a) using this privilege. Add "mresttest" to the "Protected Modules" section.
         4.3 - Add a row under "Protected Resources", and Add "/mresttest/*" as the pattern that we would like to protect and click on "Create Privilege".

    5. Now login to the RESTFul API -  http://localhost:8080/ords/learn/mresttest/uresttest. You should be prompted with "401 Unauthorized" page.
    6. Click on "sign in" to see a page prompting to enter credentials.
    7. Login with the credentials as "RESTAPIUSER" to see the API results.

Further Reading:
About Oracle Application Express Users and Oracle REST Data Services Roles