Welcome to part one of this two-part blog describing RESTful Services authentication with APEX. Part one will focus on basic authentication, and part two will focus on OAuth 2.0 authentication.
Oracle Application Express (APEX) is the world’s most popular low-code development platform for enterprise apps. APEX enables you to build scalable and secure enterprise apps, with world-class features that can be deployed anywhere. Most middle-tier apps must define application data structures to hold, convert, interact, and maintain data flow between the app to the database—APEX eliminates all of this. In addition, APEX reduces the need for complex remote APIs for a large majority of use cases. It is not architected as distributed components, and remote calls to the database are eliminated.
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 an 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, username + password logins don’t make sense.
APEX runs on an Oracle Database—this is what makes it so unique and powerful. Therefore, a lot of concepts and documentation crossover between APEX and standard Oracle Database. One of these is Oracle REST Data Services (ORDS). ORDS is a java application that allows you to access your Oracle Database resources via REST. It bridges HTTPS and your Oracle Database, pushing the content generated by APEX in the database to the webserver, where it serves the pages to your browser or mobile device. Even though I am referring to ORDS here, it is also true for APEX.
Let’s start with a diagram to show the security model for ORDS (diagram credit to Jon Dixon’s Using Roles and Privileges to fine tune access to ORDS Web Services article).
Role: Acts as a link between one or more users and one or more privileges.
Privilege: Details what is required to access a resource. When creating a privilege, you can secure either an entire module or secure based on URL patterns.
I will try to explain in a simple way. A privilege locks down a resource. When you click Yes for Authorization Required, APEX automatically makes a privilege for your table. A role is just a link between users and privileges. Roles can be added to multiple privileges, which will give that role access to multiple privileges
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.
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).
AutoREST provides individual tables and views an API. For more flexibility, such as customized output formats or extra validation, you can also configure your own handlers.
The flow for basic authentication is:
1. Click the arrow next to SQL Workshop and navigate to RESTful Services.
2. When you click Authorization Required for the table, ORDS automatically generates a privilege. You can see this privilege under Privileges. Mine is called oracle.dbtools.autorest.privilege.TEST_WS.FOOTBALL_QUARTERBACKS.
3. Scrolling down you can see the privilege uses patterns to protect the resource.
4. While ORDS also automatically generates a role (mine is called oracle.dbtools.role.autorest.TEST_WS.FOOTBALL_QUARTERBACKS), I am going to create my own. Feel free to use the auto-generated one. Navigate to Roles on the left and click Create Role.
5. Name your role whatever you like, I will call mine football_quarterbacks_role. Then Create Role.
6. Go back to the privilege and add the role you just created.
7. Lastly, you need to create a group and user in APEX. On the top bar click the administration icon and then Manage Users and Groups.
8. Navigate to Groups and Create User Group.
9. Here is the important part: name the group the same as the role you just created. For me, I will name my group football_quarterbacks_role.
10. Next, create a user. Name this user whatever you like, I will call mine football_quarterbacks_user.
11. Scroll down and assign this user to the group you just created.
12. Navigate to the URI of your table and try logging in with the user credentials you just made.
13. Voila! You can now access your table with the authenticated user you created.
In this post, we explored basic authentication for APEX applications. However, passing around usernames/passwords is not the best security practice, so OAuth 2.0 is the recommended solution. View part two of this blog series for OAuth 2.0 instructions.
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 basic authentication.
If you want to test this out yourself a great starting point is Oracle’s Free Tier, which includes US$300 in credit to get you started with a range of services including two always-free Autonomous Databases with APEX, compute, storage, and networking.