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.
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).
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).
The flow for OAuth 2.0 Client Credentials (defined in OAuth 2.0 RFC 6749, section 4.4) authentication is:
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:
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!
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).
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.