ORDS OAuth 2.0 Authentication with Oracle APEX

Introduction

Welcome to part two of this two-part blog describing RESTful Services authentication with APEX. Part one began by describing how roles and privileges work together to protect resources, and then walked through setting up basic authentication in APEX. Part two will begin with an authentication refresher, and then dive into implementing OAuth 2.0 authentication with APEX.

Refresher from Part One

There are two types of authentication you can utilize to access your table with REST.

1. Basic Authentication (First Party): Provide username and password

2. OAuth 2.0 Client Credentials Flow: pass along Client ID and Client Secret to authenticate and get access token

Storing usernames and passwords in an application is not recommended, so OAuth 2.0 is the recommended and most secure solution. Additionally, with machine-to-machine (M2M) communication, such as CLIs, daemons, or back-end services, where username + password logins don’t make sense.

Privileges lock down a table. Users are assigned roles. This role can be assigned to one or more privileges. This gives those roles, and therefore users assigned that role, access to the tables those privileges lock down (credit to Jon Dixon’s Using Roles and Privileges to fine tune access to ORDS Web Services article for the diagram).

Prerequisites

We will be deploying a protect REST API on a table in Oracle Database. If you don’t have a table, create one in SQL Workshop > Object Browser. Alternatively, you can install a sample data set in SQL Workshop > Utilities > Sample Datasets. Enable AutoREST by navigating to the REST tab and clicking Yes for Rest Enable Object and Authorization Role (you may need to Register Schema with ORDS first).

OAuth 2.0 Steps

The flow for OAuth 2.0 Client Credentials (defined in OAuth 2.0 RFC 6749, section 4.4) authentication is:

• Make role
• Add role to auto-generated privilege
• Use SQL Command to generate OAuth client
• Use SQL Command to grant the client to role
• Use Client ID and Client Secret to get access token in Postman

You already created a role and added the role to the auto-generated privilege, which is securing the table, during basic authentication. Please navigate to part one if you need assistance with this.

1. Navigate to SQL Commands.

2. Create OAuth client with following command:

BEGIN
oauth.create_client(
p_name => 'any_name_you_want',
p_grant_type => 'client_credentials',
p_description => 'any_description_you_want',
p_support_email => 'your_email',
p_privilege_names => 'privilege name'
);
commit;
end;

Here is what mine looks like:

BEGIN
oauth.create_client(
p_name => 'football_quarterback_client',
p_grant_type => 'client_credentials',
p_description => 'client for football_quarterbacks access',
p_support_email => 'example@oracle.com',
p_privilege_names => 'oracle.dbtools.autorest.privilege.TEST_WS.FOOTBALL_QUARTERBACKS'
);
commit;
end;

3. Similarly, grant the client to the role with following command:

BEGIN
oauth.grant_client_role(
p_client_name => 'name_from_last_command',
p_role_name => 'role_name'
);
commit;
end;

Here is what mine looks like:

BEGIN
oauth.grant_client_role(
p_client_name => 'football_quarterback_client',
p_role_name => 'football_quarterbacks_role'
);
commit;
end;

4. Retrieve client_id and client_secret with following command:

Select name, auth_flow, response_type, client_id, client_secret from user_ords_clients

5. Open Postman and create a new request.

6. Under Auth select OAuth 2.0 and then Get New Access Token.

7. Enter the following:

• Token Name: Any name you like
• Access Token URL: This can be complicated to find. The best way I can think to find it is taking the URI of your table and remove the table-specific part. For me, I remove the ‘/football_quarterbacks/’ and am left with https://mvdvitnosgjllaz-apexadb.adb.us-ashburn-1.oraclecloudapps.com/ords/test_ws. From here, add ‘/oauth/token’. So, my Access Token URL is https://mvdvitnosgjllaz-apexadb.adb.us-ashburn-1.oraclecloudapps.com/ords/test_ws/oauth/token
• Client ID: From command you ran earlier
• Client Secret: From command you ran earlier

8. If done correctly, clicking Request Token will return the token details. If you get an error, your Access Token URL is most likely incorrect. Heads up, the lifetime of the token is 3600 seconds (1 hour).

9. Click Use Token. When you hit the URI of your table you will be able to retrieve the data with OAuth 2.0 authentication!

Conclusion

In this post we explored authentication for APEX applications. Passing around usernames/passwords is not best security practice, so OAuth 2.0 is the recommended solution. It is also ideal for M2M communication scenarios. It is important to note lifetime of the token issued by ORDS is 3600 seconds (1 hour).

Next Steps

I acknowledge this was an extremely simple example. AutoREST on a table is more than just seeing the data—you can use those APIs to load more data, update, delete, or even query. Resource handlers, which are queries or anonymous PL/SQL blocks responsible for handling a particular HTTP method, also provide much more flexibility than AutoREST. I encourage you to design an application that combines resource handlers with OAuth 2.0 authentication.

If you want to test this out yourself a great starting point is Oracle’s Free Tier or a 30-day free trial, which includes US$300 in credit to get you started with a range of services including two Autonomous Databases with APEX, compute, storage, and networking.